In [1]:
# Hyo Sung Kim and Chin Yee Lee
# MUSA 620 - Final Project
In [1]:
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
In [2]:
import geopandas as gpd
import cartopy.crs as ccrs
from shapely.geometry import Point
In [ ]:
In [4]:
# Importing Uber Data
In [169]:
# 2014
uber_apr14 = pd.read_csv("./UberData/uber-raw-data-apr14.csv")
uber_may14 = pd.read_csv("./UberData/uber-raw-data-may14.csv")
uber_jun14 = pd.read_csv("./UberData/uber-raw-data-jun14.csv")
uber_jul14 = pd.read_csv("./UberData/uber-raw-data-jul14.csv")
uber_aug14 = pd.read_csv("./UberData/uber-raw-data-aug14.csv")
uber_sep14 = pd.read_csv("./UberData/uber-raw-data-sep14.csv")
In [170]:
# Combined df should have x rows:
uber_apr14.shape[0]+uber_may14.shape[0]+uber_jun14.shape[0]+uber_jul14.shape[0]+uber_aug14.shape[0]+uber_sep14.shape[0]
Out[170]:
In [351]:
# Combine 2014 data into one dataframe
frames = [uber_apr14, uber_may14, uber_jun14, uber_jul14, uber_aug14, uber_sep14]
uber_2014 = pd.concat(frames)
In [352]:
print(uber_2014.shape)
uber_2014.head()
Out[352]:
In [353]:
# Rename columns(Date/Time to DateTime)
uber_2014.columns = ['DateTime', 'Lat', 'Lon', 'Base']
In [354]:
# Add Type Column
uber_2014['Type'] = 'Uber'
In [355]:
# Add Hour Column
uber_2014['Hour'] = uber_2014['DateTime'].str[9:]
uber_2014['Hour'] = uber_2014['Hour'].str[:-6]
uber_2014.Hour=uber_2014.Hour.astype(int)
# Check
uber_2014.head()
#uber_2014.Hour.unique()
Out[355]:
In [356]:
uber_2014.head()
Out[356]:
In [ ]:
In [357]:
#Add Date Column
date=uber_2014['DateTime'].str.rsplit(" ", n=1, expand=True)
date=date.reset_index()
date['date']=date[0]
date=date[['date']]
date.head()
Out[357]:
In [359]:
import datetime as dt
date['date']= date['date'].apply(lambda x:
dt.datetime.strptime(x, '%m/%d/%Y'))
In [360]:
date['date']=date['date'].apply(lambda x:
dt.datetime.strftime(x,'%m/%d/%Y'))
#d.strftime('%d-%m-%Y')
date.head()
Out[360]:
In [361]:
uber_2014=pd.concat([uber_2014.reset_index(drop=True), date.reset_index(drop=True)], axis=1)
uber_2014.head()
Out[361]:
In [362]:
# Select Columns of Interest
uber_cols = ['DateTime', 'Lat', 'Lon', 'Type', 'Hour', 'date']
uber_2014 = uber_2014[uber_cols]
In [363]:
uber_2014.head()
Out[363]:
In [364]:
# Create Month Column
uber_2014['Month']=uber_2014['DateTime'].str[0]
uber_2014.Month=uber_2014.Month.astype(int)
# Check
uber_2014.head()
#uber_2014.Month.unique()
Out[364]:
In [365]:
# Create Month Name Column
uber_2014.loc[uber_2014.Month == 4,'MonthName'] = 'April'
uber_2014.loc[uber_2014.Month == 5,'MonthName'] = 'May'
uber_2014.loc[uber_2014.Month == 6,'MonthName'] = 'June'
uber_2014.loc[uber_2014.Month == 7,'MonthName'] = 'July'
uber_2014.loc[uber_2014.Month == 8,'MonthName'] = 'August'
uber_2014.loc[uber_2014.Month == 9,'MonthName'] = 'September'
In [366]:
# Check
uber_2014.head()
#uber_2014.MonthName.unique()
Out[366]:
In [22]:
# Import NYC Taxi data via API call
# not using Green cab data (for now)
In [222]:
import requests
import json
In [223]:
#!pip install sodapy
from sodapy import Socrata
In [25]:
# If API Token is needed:
#Name: NYC_Picks
#Description: MUSA620-Project
#App Token: Jh3PX6F3BDgnCmENslklEj3iZ
In [367]:
#takes about +/- 5~10 minutes
#source: https://dev.socrata.com/foundry/data.cityofnewyork.us/gkne-dk5s
# Unauthenticated client only works with public data sets. Note 'None' in place of application token, and no username or password:
client = Socrata("data.cityofnewyork.us", None)
# Get 4.5M results (~similar to uber_2014), returned as JSON from API / converted to Python list of dictionaries by sodapy.
results = client.get("gkne-dk5s", select="vendor_id, pickup_datetime, pickup_longitude, pickup_latitude",where="pickup_datetime between '2014-04-01T00:00:00.000' and '2014-10-01T00:00:00.000'",limit=4500000)
# Convert to pandas DataFrame
results_df = pd.DataFrame.from_records(results)
In [368]:
taxi_2014 = results_df
In [369]:
taxi_2014.head()
Out[369]:
In [370]:
# Rename columns(Date/Time to DateTime)
taxi_2014.columns = ['DateTime', 'Lat', 'Lon', 'Vendor_ID']
In [371]:
# Add Type Column
taxi_2014['Type'] = 'Taxi'
In [372]:
# Add Hour Column
taxi_2014['Hour'] = taxi_2014['DateTime'].str[11:13]
taxi_2014.Hour=taxi_2014.Hour.astype(int)
# Check
taxi_2014.head()
#taxi_2014.Hour.unique()
Out[372]:
In [373]:
# Select Columns of Interest
taxi_cols = ['DateTime', 'Lat', 'Lon', 'Type', 'Hour']
taxi_2014 = taxi_2014[taxi_cols]
taxi_2014.head()
Out[373]:
In [374]:
#Add Date Column
date=taxi_2014['DateTime'].str.rsplit("T", n=1, expand=True)
date=date.reset_index()
date['date']=date[0]
date=date[['date']]
date.head()
Out[374]:
In [375]:
import datetime as dt
date['date']= date['date'].apply(lambda x:
dt.datetime.strptime(x, '%Y-%m-%d'))
date.head()
Out[375]:
In [376]:
date['date']=date['date'].apply(lambda x:
dt.datetime.strftime(x,'%m/%d/%Y'))
#d.strftime('%d-%m-%Y')
date.head()
Out[376]:
In [377]:
taxi_2014=pd.concat([taxi_2014.reset_index(drop=True), date.reset_index(drop=True)], axis=1)
taxi_2014.head()
Out[377]:
In [378]:
# Select Columns of Interest
taxi_cols = ['DateTime', 'Lat', 'Lon', 'Type', 'Hour', 'date']
taxi_2014 = taxi_2014[taxi_cols]
taxi_2014.head()
Out[378]:
In [379]:
# Create Month Column
taxi_2014['Month']=taxi_2014['DateTime'].str[5:7]
taxi_2014.Month=taxi_2014.Month.astype(int)
# Check
taxi_2014.head()
#taxi_2014.Month.unique()
Out[379]:
In [380]:
# Create Month Name Column
taxi_2014.loc[taxi_2014.Month == 4,'MonthName'] = 'April'
taxi_2014.loc[taxi_2014.Month == 5,'MonthName'] = 'May'
taxi_2014.loc[taxi_2014.Month == 6,'MonthName'] = 'June'
taxi_2014.loc[taxi_2014.Month == 7,'MonthName'] = 'July'
taxi_2014.loc[taxi_2014.Month == 8,'MonthName'] = 'August'
taxi_2014.loc[taxi_2014.Month == 9,'MonthName'] = 'September'
In [381]:
# Check
taxi_2014.head()
#taxi_2014.MonthName.unique()
Out[381]:
In [28]:
#print(taxi_2014['MonthName'].value_counts())
In [382]:
# Make Lat Lon Columns to Numeric
taxi_2014['Lat']=pd.to_numeric(taxi_2014['Lat'])
taxi_2014['Lon']=pd.to_numeric(taxi_2014['Lon'])
taxi_2014.head()
Out[382]:
In [ ]:
In [ ]:
In [74]:
# Check and Compare Uber and Taxi df
In [383]:
uber_2014.head()
Out[383]:
In [384]:
taxi_2014.head()
Out[384]:
In [ ]:
In [ ]:
In [64]:
# Comparing Uber vs Taxi Count Trips Between April - September 2014
In [424]:
# Combine Uber and Taxi
# make copies first
uber_byMonth = uber_2014
taxi_byMonth = taxi_2014
In [425]:
# Append, ignore index
uber_taxi_byMonth = uber_byMonth.append(taxi_byMonth, ignore_index=True)
uber_taxi_byMonth.head()
Out[425]:
In [426]:
uber_taxi_cols=['Type', 'date', 'Month', 'Hour']
uber_taxi_byMonth=uber_taxi_byMonth[uber_taxi_cols]
uber_taxi_byMonth.head()
Out[426]:
In [427]:
uber_taxi_byMonth['dateCount']=0
uber_taxi_byMonth=uber_taxi_byMonth.groupby(['Month','date','Type'])['dateCount'].count()
uber_taxi_byMonth.head()
Out[427]:
In [428]:
uber_taxi_byMonth = uber_taxi_byMonth.reset_index()
uber_taxi_byMonth.head()
Out[428]:
In [450]:
import altair as alt
source = uber_taxi_byMonth
color = alt.Scale(domain=('Taxi', 'Uber'),
range=["Gold", "Black"])
slider = alt.binding_range(min=4,
max=9,
step = 1)
select_Month = alt.selection_single(name="Month", fields=['Month'], bind=slider)
base=alt.Chart(source, title='Total number of Taxi and Uber Trips Per Day | April 2014 - September 2014').encode(
x=alt.X('date:N', title=None),
y=alt.Y('dateCount:Q', scale=alt.Scale(domain=(0, 45000))),
color=alt.Color('Type:N', scale=color),
tooltip=['Type', 'date', 'dateCount']
).properties(
width=1000
).add_selection(
select_Month
).transform_filter(
select_Month
)
chart1=base.mark_line()
chart1= chart1.configure_axisX(labels=False)
chart1
Out[450]:
In [449]:
chart1.save("chart1.html")
In [34]:
import seaborn as sns
import altair as alt
alt.renderers.enable('notebook')
Out[34]:
In [451]:
uber_byMonth = uber_2014
taxi_byMonth = taxi_2014
uber_taxi_byMonth = uber_byMonth.append(taxi_byMonth, ignore_index=True)
uber_taxi_byMonth.head()
Out[451]:
In [153]:
# Workaround: Order by Month Number
In [155]:
# To do:
# Change colors for type
# Add Title
# ...
In [ ]:
In [452]:
# Avg Pick Ups by Hour (Counts) - Avg is done later on
# make copies first
uber_byHourC = uber_2014
taxi_byHourC = taxi_2014
In [453]:
# Append, ignore index
uber_taxi_byHourC = uber_byHourC.append(taxi_byHourC, ignore_index=True)
uber_taxi_byHourC.head()
Out[453]:
In [454]:
uber_taxi_byHourC.shape
Out[454]:
In [455]:
# Groupby Hour and Type to get Hourly pickup counts for each type of transportation
uber_taxi_byHourC['HourCount']=0
uber_taxi_byHourC=uber_taxi_byHourC.groupby(['Hour','Type'])['HourCount'].count()
uber_taxi_byHourC.head()
Out[455]:
In [456]:
# Reset Index
uber_taxi_byHourC = uber_taxi_byHourC.reset_index()
uber_taxi_byHourC.head() #should have 24 hours months, 2 types - total of 48 rows
Out[456]:
In [457]:
uber_taxi_byHourC['AverageHourCount']=uber_taxi_byHourC.HourCount/183
uber_taxi_byHourC.head()
Out[457]:
In [460]:
#get the total vehicles by hour
uber_taxi_byHourC_HourTotal=uber_taxi_byHourC.groupby(['Hour'])[['AverageHourCount']].sum()
uber_taxi_byHourC_HourTotal['HourTotal']=uber_taxi_byHourC_HourTotal['AverageHourCount']
uber_taxi_byHourC_HourTotal=uber_taxi_byHourC_HourTotal.reset_index()
uber_taxi_byHourC_HourTotal=uber_taxi_byHourC_HourTotal[['Hour', 'HourTotal']]
uber_taxi_byHourC_HourTotal
Out[460]:
In [461]:
uber_taxi_byHourC=pd.merge(uber_taxi_byHourC, uber_taxi_byHourC_HourTotal, on='Hour')
In [462]:
uber_taxi_byHourC.head()
Out[462]:
In [465]:
uber_taxi_byHourC['HourProp']=uber_taxi_byHourC.AverageHourCount/uber_taxi_byHourC.HourTotal
uber_taxi_byHourC.head()
Out[465]:
In [471]:
# Line Plot of Pick Up Counts by Hour by Type with Interactive Bar Plot on the bottom
from altair import Chart, Color, Scale
brush = alt.selection(type='interval', encodings=['x'])
# top line graph
points2 = alt.Chart(uber_taxi_byHourC, title='Average number of pick-ups by hour').mark_line().encode(
Color('Type:N',
scale=Scale(domain=['Taxi', 'Uber'],
range=['gold', 'black'])),
x='Hour:N',
y='AverageHourCount:Q',
#color='Type:N',
tooltip=['Hour:Q', 'Type:N', 'AverageHourCount:Q']
).properties(
selection=brush,
width=800
)
# the bottom bar plot 2
bars2 = alt.Chart(title='Average total number of pick-ups').mark_bar().encode(
y='Type:N',
color='Type:N',
x='sum(AverageHourCount):Q'
)
text = bars2.mark_text(
align='left',
baseline='middle',
dx=3 # Nudges text to right so it doesn't appear on top of the bar
).encode(
text='sum(AverageHourCount):Q'
)
bars2b=(bars2 + text).properties(width=600).transform_filter(brush.ref())
# the bottom bar plot
bars3= alt.Chart(title='Proportion of taxi and uber pick-ups').mark_bar().encode(
x="Hour:N",
y=alt.Y("HourProp:Q", stack="normalize"),
color="Type:N",
tooltip=['Hour:Q', 'Type:N', 'HourProp:Q']
).transform_filter(
brush.ref()
)
chart2 = alt.vconcat(points2, bars2b, bars3, data=uber_taxi_byHourC, center=True) # vertical stacking
chart2
Out[471]:
In [472]:
# Save to later embed in HMTL file
chart2.save("chart2.html")
In [ ]:
In [58]:
In [57]:
In [ ]:
In [ ]:
# Traffic Data
In [230]:
traffic = pd.read_csv("Traffic_Volume_Counts__2012-2013.csv")
In [231]:
traffic.head()
Out[231]:
In [ ]:
# Groupby SegmentID and sum by hourly counts
In [ ]:
In [ ]:
# Join by Neighborhoods?
# Fishnet/Hex Bin Map?
# Heat Map?
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [166]:
uber_2014.head()
Out[166]:
In [167]:
taxi_2014.head()
Out[167]:
In [ ]:
In [ ]: