Analyzing MTA Turnstile Data

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime as dt
import pickle

%matplotlib inline

# Source: http://web.mta.info/developers/dfturnstile.html
def get_data(week_nums):
    url = "http://web.mta.info/developers/data/nyct/turnstile/turnstile_{}.txt"
    dfs = []
    for week_num in week_nums:
        file_url = url.format(week_num)
        dfs.append(pd.read_csv(file_url))
    return pd.concat(dfs)
        
week_nums = [160903, 160910, 160917]
turnstiles_df = get_data(week_nums)

turnstiles_df.head()

 turnstiles_df.columns

turnstiles_df.columns = [column.strip() for column in turnstiles_df.columns]

turnstiles_df.columns

turnstiles_df.head()

turnstiles_df.DATE.value_counts().sort_index()

# Take the date and time fields into a single datetime column
turnstiles_df["DATE_TIME"] = pd.to_datetime(turnstiles_df.DATE + " " + turnstiles_df.TIME, format="%m/%d/%Y %H:%M:%S")

turnstiles_df.head()

(turnstiles_df
 .groupby(["C/A", "UNIT", "SCP", "STATION", "DATE_TIME"])  
 .ENTRIES.count()
 .reset_index()  # or use as_index = False; otherwise makes groupby columns new index 
 .sort_values("ENTRIES", ascending=False)).head(5)

mask = ((turnstiles_df["C/A"] == "R504") & 
(turnstiles_df["UNIT"] == "R276") & 
(turnstiles_df["SCP"] == "00-00-01") & 
(turnstiles_df["STATION"] == "VERNON-JACKSON") &
(turnstiles_df["DATE_TIME"].dt.date == datetime.datetime(2016, 9, 16).date()))

turnstiles_df[mask].head()

turnstiles_df.DESC.value_counts()

# Get rid of the duplicate entry
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)

(turnstiles_df
 .groupby(["C/A", "UNIT", "SCP", "STATION", "DATE_TIME"])
 .ENTRIES.count()
 .reset_index()
 .sort_values("ENTRIES", ascending=False)).head(5)

# Drop Exits and Desc columns. To prevent errors in multiple run of cell, 
# errors on drop is ignored (e.g. if some columns were dropped already)
turnstiles_df = turnstiles_df.drop(["EXITS", "DESC"], axis=1, errors="ignore")

turnstiles_daily = turnstiles_df.groupby(["C/A", "UNIT", "SCP", "STATION", "DATE"])\
.ENTRIES.first().reset_index()

turnstiles_daily.head()

turnstiles_daily[["PREV_DATE", "PREV_ENTRIES"]] = (turnstiles_daily
                                                       .groupby(["C/A", "UNIT", "SCP", "STATION"])["DATE", "ENTRIES"]
                                                       .transform(lambda grp: grp.shift(1)))
# transform() takes a function as parameter
# shift moves the index by the number of periods given (positive or negative)

turnstiles_daily.head()

# Drop the rows for first date
turnstiles_daily.dropna(subset=["PREV_DATE"], axis=0, inplace=True)
# axis = 0 means index (=1 means column)

turnstiles_daily[turnstiles_daily["ENTRIES"] < turnstiles_daily["PREV_ENTRIES"]].head()

mask = ((turnstiles_df["C/A"] == "A011") & 
(turnstiles_df["UNIT"] == "R080") & 
(turnstiles_df["SCP"] == "01-00-00") & 
(turnstiles_df["STATION"] == "57 ST-7 AV") &
(turnstiles_df["DATE_TIME"].dt.date == datetime.datetime(2016, 8, 27).date())) 

# datetime is both name of module and name of constructor of datetime object
turnstiles_df[mask].head()

(turnstiles_daily[turnstiles_daily["ENTRIES"] < turnstiles_daily["PREV_ENTRIES"]]
    .groupby(["C/A", "UNIT", "SCP", "STATION"])
    .size()) # size() behaves same as if we'd done .DATE.count() 

def get_daily_counts(row, max_counter):
    counter = row["ENTRIES"] - row["PREV_ENTRIES"]
    if counter < 0:
        counter = -counter
    if counter > max_counter:
        print(row["ENTRIES"], row["PREV_ENTRIES"])
        return 0
    return counter

# If counter is > 1 million, then the counter might have been reset.  
# Just set it to zero as different counters have different cycle limits
turnstiles_daily["DAILY_ENTRIES"] = turnstiles_daily.apply(get_daily_counts, axis=1, max_counter=1000000) 

# apply takes a function as parameter and applies it along the given axis (1=apply by row) 
# apply takes care of passing each row to the function

turnstiles_daily.head()

picklefile = "/Users/kevin/Metis/Projects/1/Project_1/sorted_traffic_dataframe2.pickle"

with open(picklefile,'rb') as read_file:
    new_df = pickle.load(read_file)
    
new_df.head()

new_df.groupby(['Station_x'])['Total_Traffic'].sum().sort_values(ascending = False)

list_of_stations = ['34 ST-PENN STA', 'GRD CNTRL-42 ST', '23 ST', '34 ST-HERALD SQ', 'TIMES SQ-42 ST']

def foot_traffic_graph(station_list):
    '''This function graphs the foot traffic through a list of stations that are passed in via a list
    args: A list of the stations to be graphed.
    
    returns: Graphs for the foot traffic for every day the first 6 months of the year 
    '''
    
    
    for index, station in enumerate(station_list):
        
        mask = new_df['Station_x'] == station # Selects only the input station
        grouped_station = new_df[mask].groupby(['Station_x', 'Date_x'])['Total_Traffic'].sum() # Sums the traffic per day
        filtered_station = grouped_station[grouped_station < grouped_station.quantile(.96)] # Remove outliers
        six_months_station = filtered_station.values[:len(filtered_station)//2] # Grab only the first 6 months
        nine_nine = pd.Series(six_months_station).quantile(.99) # Create 99th percentile line
        topper = np.full(len(six_months_station),1)*nine_nine 
        one = pd.Series(six_months_station).quantile(.1) # Create 1 percentile line
        botper = np.full(len(six_months_station),1)*one
    
    
        plt.figure(figsize=[15,10])
        plt.plot(six_months_station, 'b')
        ticks_x = np.linspace(0, len(six_months_station), 7)
        months = ['Jan','Feb','Mar','Apr', 'May', 'June', 'July']
        plt.xlabel('Months', fontsize = '20')
        plt.ylabel('Number of People', fontsize = '20')
        plt.ylim(50000, 350000)
        plt.title('Foot Traffic Through ' + station, fontsize = 20);
        plt.xticks(ticks_x, months);
        plt.plot(topper)
        plt.plot(botper)
        #plt.savefig('FootTrafficThoughStations.png')
        
    

foot_traffic_graph(list_of_stations)

weeklypicklefile = '/Users/kevin/Downloads/df2017_Top5.pickle'

with open(weeklypicklefile,'rb') as read_file:
    df2017_Top5 = pickle.load(read_file)
    
new_df.head(5)

df1 = df2017_Top5.groupby('DATE')['TOTAL_TRAF'].sum().reset_index()

df1['datetime'] = pd.to_datetime(df1['DATE'], infer_datetime_format=True)

df1['weekday'] = df1['datetime'].dt.dayofweek

df1.groupby('weekday')['TOTAL_TRAF'].mean().reset_index().sort_values('weekday')

plt.figure(figsize=[14,8])
plt.xlabel('Days of Week', fontsize = 20)
plt.ylabel('Number of Foot Traffic per Day', fontsize = 20)
plt.title('Traffic by Day of Week', fontsize = 40)
plt.bar(days, df1.groupby('weekday')['TOTAL_TRAF'].mean());
plt.savefig('weekly.png')