<jupyter_start><jupyter_text>This document outlines the process for importing data and basic data cleaning that I followed for this project. Initial cleaning examples are done on a smaller dataframe to save processing time. Importing data into my database Importing the MTA Turnstile data:Step 1: Run `python get_mta_turnstiles.py "(19|20|21|22)"` in terminal to get the turnstile data. I wanted all of the data from 2019-2021 in my database, so I included 22 in the regex to make sure I got everything from 2021 (since the last few days of 2021 were included in the first upload in 2022). I could have been more specific here, but this worked for my purposes.  Importing the MTA Fare data:Note: due to formatting differences in the MTA Fare data, I had to modify the get_mta.py file and split it into get_mta_fares.py and get_mta_fares_old.py.Step 1: Run `python get_mta_fares_new.py "(20|21|22)"` to get the bulk of the new format of MTA fare data.Step 2: Run `python get_mta_fares_old.py "19"` to get the remaining MTA fare data for 2019. Importing the MTA Turnstiles Remote-Complex Lookup:I found a hand-built resource from <NAME> that matches MTA station data found at `http://web.mta.info/developers/data/nyct/subway/Stations.csv` to the fields in the turnstile dataset.<jupyter_code>reset -fs
import pandas as pd
import datetime as dt
import sqlite3
from sqlalchemy import create_engine

engine = create_engine('sqlite:///database/mta_data.db')
remote_lookup_df = pd.read_csv('database/mta_remote_lookup.csv')
remote_lookup_df.columns = [column.strip().upper() for column in remote_lookup_df.columns]
remote_lookup_df.head()
# strip whitespace from entries - just to be on the safe side
lookup = (['REMOTE', 
             'BOOTH', 
             'COMPLEX_ID', 
             'STATION', 
             'LINE_NAME', 
             'DIVISION'])
(remote_lookup_df[lookup].str.strip() for column in lookup)
remote_lookup_df.head()
remote_lookup_df.COMPLEX_ID
remote_lookup_df['REMOTE'] = remote_lookup_df['REMOTE'].astype(object)
remote_lookup_df['BOOTH'] = remote_lookup_df['BOOTH'].astype(object)
remote_lookup_df['COMPLEX_ID'] = remote_lookup_df['COMPLEX_ID'].astype(object)
remote_lookup_df['STATION'] = remote_lookup_df['STATION'].astype(object)
remote_lookup_df['LINE_NAME'] = remote_lookup_df['LINE_NAME'].astype(object)
remote_lookup_df['DIVISION'] = remote_lookup_df['DIVISION'].astype(object)
remote_lookup_df.REMOTE
#remote_lookup_df.to_sql('remote_lookup',engine)<jupyter_output><empty_output><jupyter_text>Processing Fare DataIn order to get one table containing all fare types, including discontinued ones, I ran this:<jupyter_code>fare_data_df = pd.read_sql('''SELECT [REMOTE], [STATION], [START_DATE], [END_DATE], [FF], [SEN/DIS], 
                            [7-D AFAS UNL], [30-D AFAS/RMF UNL], [JOINT RR TKT], [7-D UNL], [30-D UNL], 
                            [14-D RFM UNL], [1-D UNL], [14-D UNL], [7D-XBUS PASS], [TCMC], [RF 2 TRIP], 
                            [RR UNL NO TRADE], [TCMC ANNUAL MC], [MR EZPAY EXP], [MR EZPAY UNL], 
                            [PATH 2-T], [AIRTRAIN FF], [AIRTRAIN 30-D], [AIRTRAIN 10-T], 
                            [AIRTRAIN MTHLY], [STUDENTS], [NICE 2-T], [CUNY-120],[CUNY-60], 
                            '0' as [FF VALUE], '0' as [FF 7-DAY], '0' as [FF 30-DAY]
                         FROM mta_fare_data_old
                         UNION ALL
                         SELECT [REMOTE], [STATION], [START_DATE], [END_DATE], [FF], [SEN/DIS], 
                         [7-D AFAS UNL], [30-D AFAS/RMF UNL], [JOINT RR TKT], [7-D UNL], [30-D UNL], 
                         '0' as [14-D RFM UNL], '0' as [1-D UNL], '0' as [14-D UNL], 
                         [7D-XBUS PASS], [TCMC], [RF 2 TRIP], [RR UNL NO TRADE], [TCMC ANNUAL MC], 
                         [MR EZPAY EXP], [MR EZPAY UNL], [PATH 2-T], [AIRTRAIN FF], [AIRTRAIN 30-D], 
                         [AIRTRAIN 10-T], [AIRTRAIN MTHLY], [STUDENTS], [NICE 2-T], [CUNY-120], [CUNY-60], 
                         [FF VALUE], [FF 7-DAY], [FF 30-DAY]
                         FROM mta_fare_data_new;''', engine)
fare_data_df.head()<jupyter_output><empty_output><jupyter_text>Note that this query creates new columns in each of the tables to match the missing fare types from the other and sets the values to zero. The UNION ALL command is used to combine into one table. The SQL query is pretty clunky, but it gets the job done. I then strip the trailing whitespace from the entries in the STATION column and add the table to my database:<jupyter_code>fare_data_df['STATION'] = fare_data_df['STATION'].str.strip()
fare_data_df.columns
#fare_data_df.to_sql('fare_data_total',engine)

fare_data_df.shape
# remove bus data
#bus = ['R553', 'R554', 'R555', 'R556', 'R557', 'R558', 'R559', 'R560', 'R561', 'R562', 'R563', 'R564', 'R565', 'R566', 'R567', 'R568', 'R569', 'R573', 'R574', 'R575'] 

#fare_data_df = fare_data_df[(fare_data_df.REMOTE != [remove])] 

#fare_data_df = fare_data_df[~fare_data_df.REMOTE.isin(bus)]
#fare_data_df.shape<jupyter_output><empty_output><jupyter_text>Then I insert the values from the `remote_lookup_df` into the `fare_data_df` for ease of reference:<jupyter_code>fare_data_df.rename(columns={'STATION': 'STOP'}, inplace=True)
fare_data_df.shape
#fare_data_df.insert(1,'BOOTH',0)
#fare_data_df.insert(2,'COMPLEX_ID',0)
#fare_data_df.insert(3,'STATION',0)
#fare_data_df.insert(5,'LINE_NAME',0)
#fare_data_df.insert(6,'DIVISION',0)
#fare_data_df.shape
fare_data_df = fare_data_df.merge(remote_lookup_df,on='REMOTE',how="left")
fare_data_df.columns<jupyter_output><empty_output><jupyter_text>Computing Commuter PercentageI want to know the percentage of likely commuter traffic for each station in this dataset. First I sum the total swipes across all categories:<jupyter_code>fare_data_df['TOTAL'] = fare_data_df.iloc[:,4:33].sum(axis=1)
fare_data_df['TOTAL']<jupyter_output>/var/folders/_g/0w700wks7s1f9mzvhbcn7vvc0000gn/T/ipykernel_55257/1067136374.py:1: FutureWarning:

Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.<jupyter_text>Now I want to sum only those columns that I want to use as proxy for commuters. I look at the MTA Field Descriptions on the Fare Data page and compare them to the columns in `fare_data_df`:<jupyter_code>fare_data_df.columns<jupyter_output><empty_output><jupyter_text>Field Description for the columns:\FF = full fare\SEN/DIS = senior citizen/disabled\7-D AFAS UNL = 7-day unlimited ADA farecard access system\30-D AFAS/RFM UNL = 30-day unlimited ADA farecard access system/reduced fare media\JOINT RR TKT = Joint railroad ticket\7-D UNL = 7-day unlimited\30-D UNL = 30-day unlimited\14-D RFM UNL = 14-day reduced fare media unlimited\1-D UNL = one day unlimited\14-D UNL = 14-day unlimited\7D-XBUS PASS = 7-day express bus pass\TCMC = Transit check metrocard\RF 2 TRIP = reduced fare 2 trip\RR UNL NO TRADE = Railroad unlimited no trade\TCMC ANNUAL MC = Transit check annual metrocard\MR EZPAY EXP = Mail and Ride EZ-pay express\MR EZPAY UNL = Mail and Ride EZ-pay unlimited\PATH 2-T = PATH train 2-trip\AIRTRAIN FF = Airtrain full fare\AIRTRAIN 30-D = Airtrain 30-day\AIRTRAIN 10-T = Airtrain 10-trip\AIRTRAIN MTHLY = Airtrain monthly\STUDENTS = School-age students (non-college)\NICE 2-T = ?? two-trip card\CUNY-120 = CUNY community college 120-day\CUNY-60 = CUNY community college 60-day\FF VALUE = Fair fares NYC (half price for low-income NYC residents)\FF 7-DAY = Fair Fares 7-Day\FF 30-DAY = Fair Fares 30-DayBased on the field descriptions for the columns, I create a new COMMUTERS column, which includes all student, ADA, and fare reductions for high-value trips or long durations (14-day, monthly, annual). I don't include EZ-pay, bus or rail/train cards except for the Airtrain 30-day, 10-trip and monthly cards. (Those cards are likely used by airport workers.) I also exclude full fare and one- and two-day unlimited cards, as those are more likely used by visitors.<jupyter_code>commuters = (['SEN/DIS', 
             '7-D AFAS UNL', 
             '30-D AFAS/RMF UNL', 
             '7-D UNL', 
             '30-D UNL', 
             '14-D RFM UNL', 
             '14-D UNL', 
             'TCMC', 
             'TCMC ANNUAL MC', 
             'AIRTRAIN 30-D', 
             'AIRTRAIN 10-T', 
             'AIRTRAIN MTHLY',
             'STUDENTS', 
             'CUNY-120', 
             'CUNY-60', 
             'FF VALUE', 
             'FF 7-DAY', 
             'FF 30-DAY'])
fare_data_df['COMMUTERS'] = fare_data_df[commuters].sum(axis=1)
fare_data_df['COMMUTERS']             
fare_data_df.sort_values(['START_DATE'], inplace = True)
fare_data_df.head()<jupyter_output><empty_output><jupyter_text>Visualizing the commuter percentage at a stationNow I take a look at the commuter percentage for one station, to get a visual sense of the pattern from 2019-2021.<jupyter_code>commuters_df = (fare_data_df
                        .groupby(['BOOTH', 'STATION', 'END_DATE', 'TOTAL', 'COMMUTERS'],as_index=False)
                        .TOTAL.first())
commuters_df.shape<jupyter_output><empty_output><jupyter_text>To get the station totals, I need to aggregate:<jupyter_code>station_weekly_totals = (commuters_df
                     .groupby(['STATION', 'END_DATE'])
                     [['COMMUTERS', 'TOTAL']].sum().reset_index())

station_weekly_totals.shape<jupyter_output><empty_output><jupyter_text>I then divide the COMMUTERS column by the TOTAL column to get the percentage of likely commuters:<jupyter_code>station_weekly_totals['COMMUTER_PERCENT'] = (station_weekly_totals['COMMUTERS']
                                 .div(station_weekly_totals['TOTAL']).multiply(100))
station_weekly_totals.shape<jupyter_output><empty_output><jupyter_text>Now I can plot a station's total swipes and likely commuter percentage over 2019-2021:<jupyter_code># data
commuters_times_sq = (station_weekly_totals[station_weekly_totals['STATION'] == 
                                             '42 ST-TIMES SQ'].copy()) #using mask to segment df
# plot
fig = (px.line(commuters_times_sq, 
               x='END_DATE', 
               y=commuters_times_sq.columns[2:4], 
               hover_data=['COMMUTER_PERCENT']))

# Change the layout
fig.update_layout(title_text='MTA Ridership at Times Square from 2019-2021', 
                  legend=dict(
                      title=None)) 
fig.show()<jupyter_output><empty_output><jupyter_text>Turnstile Data CleaningTo save processing time, I've run a `processing.py` python script on the full dataset from 2019-2021. Below are examples of the methods and functions run on smaller sets of the data. I essentially followed the process outlined in the pair programming exercises.<jupyter_code>turnstiles_df = pd.read_sql('SELECT * FROM mta_turnstile_data WHERE [DATE] LIKE "12/%/2019"', engine)
turnstiles_df.head()
# Create a single datetime column
import datetime

turnstiles_df['DATE_TIME'] = pd.to_datetime(turnstiles_df.DATE + ' ' + turnstiles_df.TIME, 
                                            format='%m/%d/%Y %H:%M:%S')
# Get rid of the duplicate entries 
turnstiles_df.sort_values(['C/A', 'UNIT', 'SCP', 'STATION', 'DATE_TIME'], 
                          inplace=True, ascending=False)
turnstiles_df.drop_duplicates(subset=['C/A', 'UNIT', 'SCP', 'STATION', 'DATE_TIME'], inplace=True)
# get maximum entries per day per turnstile
turnstiles_daily = (turnstiles_df
                        .groupby(['C/A', 'UNIT', 'SCP', 'STATION', 'DATE'],as_index=False)
                        .ENTRIES.first())
turnstiles_daily.head()
# get total entries per day per turnstile                      
turnstiles_daily[['PREV_DATE', 'PREV_ENTRIES']] = (turnstiles_daily
                                                       .groupby(['C/A', 'UNIT', 'SCP', 'STATION'])[['DATE', 'ENTRIES']]
                                                       .shift(1))
turnstiles_daily.head()
# Drop the rows for the earliest date in the df
turnstiles_daily.dropna(subset=['PREV_DATE'], axis=0, inplace=True)
turnstiles_daily.head()<jupyter_output><empty_output><jupyter_text>for the `get_daily_counts` function, I set the `max counter` value to 20000:<jupyter_code>def get_daily_counts(row, max_counter):
    counter = row['ENTRIES'] - row['PREV_ENTRIES']
    
    if counter < 0:
        counter = -counter  # adjust for "reverse" counter
        
    if counter > max_counter:
        # Maybe counter was reset, so it may make sense to take the minimum
        print(f"ENTRIES: {row['ENTRIES']} <-- {row['PREV_ENTRIES']}")
        counter = min(row['ENTRIES'], row['PREV_ENTRIES'])
        
    if counter > max_counter:
        # If we still get a counter that is too big, set to zero
        return 0
    
    return counter

turnstiles_daily['DAILY_ENTRIES'] = turnstiles_daily.apply(get_daily_counts, axis=1, max_counter=20000)                                                   
#turnstiles_daily.to_sql('mta_turnstiles_daily', engine)<jupyter_output><empty_output><jupyter_text>MTA Visualizations<jupyter_code>#Read in the data for turnstiles
turnstiles_2019 = pd.read_sql('SELECT * FROM mta_turnstiles_daily WHERE [DATE] LIKE "%/2019"', engine)
turnstiles_2021 = pd.read_sql('SELECT * FROM mta_turnstiles_daily WHERE [DATE] LIKE "%/2021"', engine)<jupyter_output><empty_output><jupyter_text>Creating a daily count of entries by station for 2019 and 2021:<jupyter_code>station_daily_2019 = turnstiles_2019.groupby(['STATION', 'DATE'])[['DAILY_ENTRIES']].sum().reset_index()
station_daily_2021 = turnstiles_2021.groupby(['STATION', 'DATE'])[['DAILY_ENTRIES']].sum().reset_index()
station_daily_2019.head(20)
station_daily_2021.head()<jupyter_output><empty_output><jupyter_text>Sorting by the largest daily entries to get the highest-traffic stations:<jupyter_code>station_totals_2019 = (station_daily_2019.groupby('STATION')['DAILY_ENTRIES'].sum()
    .reset_index()
    .sort_values('DAILY_ENTRIES', ascending=False))

station_totals_2019.head()
station_totals_2021 = (station_daily_2021.groupby('STATION')['DAILY_ENTRIES'].sum()
    .reset_index()
    .sort_values('DAILY_ENTRIES', ascending=False))

station_totals_2021.head()<jupyter_output><empty_output><jupyter_text>Computing the percentage with respect to 2019 traffic for 2021:<jupyter_code>station_totals_2021['CHANGE'] = (station_totals_2021['DAILY_ENTRIES']
                                 .div(station_totals_2019['DAILY_ENTRIES']))
#station_totals_2021['CHANGE'] = station_totals_2021['CHANGE'].abs()

station_totals_2021.head()<jupyter_output><empty_output><jupyter_text>Adding a `2019_ENTRIES` column to the dataframe, and sorting by percentage of 2019 traffic:I created the `smallest_change_2021` dataframe in order to select the top 200 stations out of the list of stations that have retained the most traffic from pre-pandemic levels.<jupyter_code>station_totals_2021['2019_ENTRIES'] = station_totals_2019['DAILY_ENTRIES']
smallest_change_2021 = station_totals_2021.reset_index().sort_values(by=['CHANGE'], ascending = False).head(200)
smallest_change_2021.head()<jupyter_output><empty_output><jupyter_text>Finding the stations from the previous list that have the largest total entries:In order to get that sweet spot between total volume and retained volume. I want to be able to recommend a selection of ten stations that the city health department should choose for their newest COVID testing and vaccination stations, so I want high volume but also high traffic retention (which implies larger commuter/local usage).<jupyter_code>largest_sorted_2021 = smallest_change_2021.reset_index().sort_values(by=['DAILY_ENTRIES', 'CHANGE'], ascending = False).head(50)
largest_sorted_2021.head(10)<jupyter_output><empty_output><jupyter_text>EXAMPLE: plotting the highest traffic stations from 2019 relative to their 2021 traffic:<jupyter_code>top_stations=station_totals_2021['station'][:10]

fig = go.Figure(data=[
    go.Bar(name='2019 Entries', 
           marker_color='dimgrey', 
           opacity=0.9, 
           x=top_stations, 
           y=station_totals_2021['2019_entries'][:10]),
    go.Bar(name='2021 Entries', 
           marker_color='firebrick', 
           opacity=0.9, 
           x=top_stations, 
           y=station_totals_2021['daily_entries'][:10])
])
# Change the layout
fig.update_layout(title_text='MTA Usage Pre-Pandemic vs. 2021',
                   barmode='group',
                   hovermode='x unified',
                   xaxis_tickangle=75, 
                   template="simple_white") 
fig.show()<jupyter_output><empty_output><jupyter_text>By comparison, my analysis shows much higher traffic retention outside of Manhattan<jupyter_code>top_stations=largest_sorted_2021['station'][:10]

fig = go.Figure(data=[
    go.Bar(name='2019 Entries', 
           marker_color='dimgrey', 
           opacity=0.9, 
           x=top_stations, 
           y=largest_sorted_2021['2019_entries'][:10]),
    go.Bar(name='2021 Entries', 
           marker_color='firebrick', 
           opacity=0.9, 
           x=top_stations, 
           y=largest_sorted_2021['daily_entries'][:10])
])
# Change the layout
fig.update_layout(title_text='MTA Usage Pre-Pandemic vs. 2021',
                   barmode='group',
                   hovermode='x unified',
                   xaxis_tickangle=75, 
                   template="simple_white") 
fig.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1120812646236375, 'fraction_numerical': 0.034297780060491924, 'mean_word_length': 3.63107822410148, 'pattern_counts': {'":': 0, '<': 87, '<?xml version=': 0, '>': 87, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 13, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '489715', 'license': 'Apache-2.0', 'n_tokens_mistral': 6325, 'n_tokens_neox': 5681, 'n_words': 1493, 'path': 'MTA EDA Process.ipynb', 'repo_name': 'saramoira/MTA-COVID-Testing-EDA', 'size': '17528'}