'Sane'itizing MTA's Turnstile Datasets: Geotagging

import pandas  # Good ol' pandas
import numpy as np
import urllib.request
from parsel import Selector  # Scrapy's parsel makes it easy to select html elements from a web document (https://github.com/scrapy/parsel)



# So, this function maps the date of the dataset to the dataset location. 
# Its not exactly necessary here, but I'm reusing code I already have so bear with me
def get_available_datasets():
    """
    fetches links to available datasets from MTA turnstile website.
    returns a dict of  date: url pairs.
    date format example: Saturday, April 11, 2020
    """

    MTA_URL = 'http://web.mta.info/developers/turnstile.html'
    DATA_URL_IDENTIFIER = 'data/nyct/turnstile/turnstile_'
    MTA_DOMAIN = 'http://web.mta.info/developers/'

    with urllib.request.urlopen(MTA_URL) as response:
      home_page = Selector(response.read().decode("utf8"))

    links = home_page.xpath('//a[contains(@href, "{}")]'.format(DATA_URL_IDENTIFIER))
    return {link.xpath('text()').get(): MTA_DOMAIN + link.xpath('@href').get() for link in links}

  
# Okay, let's pull a dataset and create a dataframe. We'll use last week's data (Saturday, June 13, 2020)

url = get_available_datasets()['Saturday, June 13, 2020']  # Ideally, what's returned from this function should be cached, but it'll run once so ¯\_(ツ)_/¯

with urllib.request.urlopen(url) as response:
  df = pandas.read_csv(response)

df.head(5)

# Computer, I command you to give me all the Station names with "59 ST" in them
df[df["STATION"].str.contains("59 ST")]["STATION"].unique()

with urllib.request.urlopen("http://web.mta.info/developers/data/nyct/subway/Stations.csv") as response:
  stationsdf = pandas.read_csv(response)

stationsdf.head(5)

# Computer, I am once again asking you to give me all the Station names with "59 St" in them
stationsdf[stationsdf["Stop Name"].str.contains("59 St")]

# What this one liner does is group the rows by "Complex ID", filter the groups that have more than one row, 
# and sort them so we can see the groups next to each other. 
stationsdf.groupby('Complex ID').filter(lambda g: len(g) > 1).sort_values("Complex ID", ascending=False)

df[df["STATION"] == "SOUTH FERRY"].head(1) 

# this needs to run ONLY ONCE or you'll get the world of duplicates
stationsdf["Daytime Routes"] = stationsdf[["Complex ID","Daytime Routes"]].groupby("Complex ID")["Daytime Routes"].transform(lambda x: ''.join([y.replace(' ', '') for y in x]))

# Check Lex av/59th
stationsdf[stationsdf["Stop Name"].str.contains("59 St")]

from fuzzywuzzy import process, fuzz
from operator import itemgetter


station_stops = {x[0].replace(' ', '').replace('-', ''): x[0] for x in zip(stationsdf["Stop Name"])}

station_stop_names = station_stops.keys()

def match_records(row):
  """
  Receives a unique (STATION, LINENAME) row from the turnstile dataset and attempts to match it with a record
  from the stop/station location dataset using the names and Routes.
  
  The function runs fuzzy search on the station name and the linename/routes and aggregates the results.
  Then it sorts the results in descending order by the match ratio of the name first, then the linename/route.
  Finally, it returns the result with the highest name and line/route match ratio
  
  Returns a dictionary in the format 
    {'station': '59 ST',        == Corresponds to the STATION column in the turnstile dataset
   'name_match': ('59St', 100), == Corresponds to the matched stop name keys & the match ratio
   'line_match': 'NWR456',      == Corresponds to the lines/Routes 
   'stop_name_match': '59 St',  == Corresponds to the actual, unmodified stop name
   'line_fuzz_ratio': 77}       == Corresponds to the match ratio of the Routes with that of the LINENAME From the
                                   turnstile dataset 
  """
  
  # preprocess the station name to increase the accuracy of fuzzy matching
  station_name = row["STATION"].replace(' ', '').replace('-', '')
  
  station_routes = row["LINENAME"]
  
  # first, get the potential matches. 
  candidates = process.extract(station_name, 
                               station_stop_names, scorer=fuzz.token_sort_ratio, limit=10)
  
  
  # stores the results of all the line matches for each name match 
  records = []
 
  for x in candidates:
    
    # Find corresponding record(s) from the station locations dataframe 
    candidate_match = stationsdf[stationsdf["Stop Name"] == station_stops[x[0]]]["Daytime Routes"].unique()
    
    for y in candidate_match:
      records.append({
        "station": row["STATION"],
        "name_match": x,
        "line_match": y,
        "station_line": station_routes,
        "stop_name_match": station_stops[x[0]],
        "line_fuzz_ratio": fuzz.token_sort_ratio(station_routes, y)
      })
  
  # sorts the results by name first, line/route second, in descending order, and returns the first result
  return sorted(records, key=lambda x: (x["name_match"][1], x["line_fuzz_ratio"]), reverse=True)[0]

# Now we apply the function to our dataframes
results = df[['STATION','LINENAME']].drop_duplicates().reset_index().apply(match_records, axis=1)

# Here's a list of items that weren't a perfect match for both station names and routes *surpressed to the first 2* 
# We'll review these and remove the ones we cant fix, or update the mismatches
[x for x in results if x['line_fuzz_ratio'] < 100 and x["name_match"][1] < 100][:2]

# Fixed missed matches here:
# the patched key is there to identify records that were manually corrected
mismatches = {
 'FLATBUSH AV-B.C': {'station': 'FLATBUSH AV-B.C',
  'name_match': ('Flatbush Av - Brooklyn College', 61),
  'line_match': '25',
  'station_line': '25',
  'stop_name_match': 'Flatbush Av - Brooklyn College',
  'line_fuzz_ratio': 100,
  'patched': True},
  
  'ROCKAWAY PARK B': {'station': 'ROCKAWAY PARK B',
  'name_match': ('RockawayParkBeach116St', 100),
  'line_match': 'AS',
  'station_line': 'AS',
  'stop_name_match': 'Rockaway Park - Beach 116 St',
  'line_fuzz_ratio': 100,
  'patched': True},
# ... surpressed
}

  # The location of these aren't in the dataset, so we'll ignore them. 
  # I don't know what some of the names correspond to either 
unknowns = [
  "RIT-ROOSEVELT",
  "RIT-MANHATTAN",
  "ST. GEORGE",
  "ORCHARD BEACH",
  "NEWARK HW BMEBE",
  "HARRISON",
  "JOURNAL SQUARE",
  'EXCHANGE PLACE',
  'PAVONIA/NEWPORT',
  'CITY / BUS',
  # ... surpressed
]

# Lets now filter the unknowns and fix the mismatches
results_filtered = [x for x in results if x['station'] not in unknowns]

# and also, fix the mismatches
results_fixed = [mismatches[x['station']] if mismatches.get(x['station'], None) is not None else x for x in results_filtered]

# maps the station and line to the geolocations

_ = [
  x.update(
    stationsdf[
      (stationsdf["Stop Name"] == x["stop_name_match"]) & 
      (stationsdf["Daytime Routes"] == x["line_match"])
    ]
    [["GTFS Latitude", "GTFS Longitude"]].head(1).to_dict('list')
  ) for x in results_fixed]

# Some records have the lat/long values in a list (like [40.824073] for example). the lines below fixes that
_ = [
  x.update(
    {'GTFS Latitude': x['GTFS Latitude'][0] if type(x['GTFS Latitude']) is list else x, 
     'GTFS Longitude': x['GTFS Longitude'][0] if type(x['GTFS Longitude']) is list else x}
  ) for x in results_fixed]


# I'll convert it into a df, then use 'station' and 'station_line' to join it on the turnstile dataset
mappingdf = pandas.DataFrame(results_fixed).drop(['name_match', 'line_match', 'stop_name_match', 'line_fuzz_ratio', 'patched'], axis=1)
pandas.merge(df, mappingdf, left_on=['STATION', 'LINENAME'], right_on=['station', 'station_line'], how='left')