# Let's grab the modules and data from pandas-intro-1.ipynb
# Recall that "tip" is the tip expressed as a fraction of the pre-tip amount due
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
df = pd.read_csv('taxi.csv')
tip = df['tip_amount']/(df['total_amount']-df['tip_amount'])
# Something useful for the recitation is the dtypes attribute
# This returns all of the datatypes of the dataframe's columns
df.dtypes
# Illustrate logical operators on Series
x = pd.Series([True, True, False, False])
y = pd.Series([True, False, True, False])
x
y
x | y
x & y
x || y
# Let's remove some outliers from our data before we plot.
# We'll remove all rows where the tip is bigger than 100% or the trip is longer than 100 mi.
outlier=(tip>1)|(df['trip_distance']>100)|(df['total_amount']>1000)|(df['total_amount']<0)
# Be careful: all of the following are wrong
# outlier = (tip > 1) || (df['trip_distance']>100)
# outlier = (tip>1) or (df['trip_distance']>100)
# outlier = (tip>1) | df['trip_distance']>100 # This is silently wrong
df[outlier]
outlier
~outlier
df[~outlier]
# Here we use the ~ operator to get all records that are not outliers
df2 = df[~outlier].copy() # The copy keeps us safe from accidentally modifying the original data
df2['tip'] = tip[~outlier].copy()
df2['pre-tip']=df2['total_amount']-df2['tip_amount']
a = pd.DataFrame({'col1' : [1,2,3]})
b = a
a
b['col1'][0] = 5 # Also modifies the dataframe "a"
a
Question 1
How would we select records whose total amount paid is less than $10 and whose trip distance is less than 1 mile?
df[df['total_amount']<10 & df['trip_distance']<1]df[(df['total_amount']<10) & (df['trip_distance']<1)]df[df['total_amount']<10 && df['trip_distance']<1]df[df['total_amount']<10 and df['trip_distance']<1]df[(df['total_amount']<10) & (df['trip_distance']<1)]
# Let's plot the tip fraction versus the pre-tip amount
plt.plot(df2['pre-tip'],df2['tip'],'ks')
plt.xlabel('Pre-tip amount ($)')
plt.ylabel('Tip fraction')
plt.plot(df2['pre-tip'],df2['tip'],'.',markersize=.05)
# It's hard to see what's going on. Let's use a smaller dot to plot.
# Let's also focus on trips with a pre-tip amount of less than $100
plt.plot(df2['pre-tip'],df2['tip'],'.',markersize=.05)
plt.xlim(0,100) # Plot from 0 to 100 on the x-axis
plt.xlabel('Pre-tip amount ($)')
plt.ylabel('Tip fraction')
# What are the horizontal lines?
# What are the curved lines?
# What are the vertical lines?
# Let's plot the tip in $ rather than as a fraction
plt.plot(df2['pre-tip'],df2['tip_amount'],'.',markersize=.05)
plt.xlim(0,100)
plt.ylim(0,30)
plt.xlabel('Pre-tip amount ($)')
plt.ylabel('Tip ($)')
# Why do you think that some horizontal lines are darker than others?
Question 2
What is the slope of the steepest dark diagonal line?
# Calculate the mean of the pre-tip amount
df2['pre-tip'].mean()
# Calculate the standard deviation of the pre-tip amount
df2['pre-tip'].std()
# Calculate the sum of the pre-tip amount
np.log10(df2['pre-tip'].sum())
df2['pre-tip'].count()
# Calculate the number of records, excluding NA values ("not applicable" or "missing" values), in each column.
# NA values are like NULL in SQL.
df2.count()
# We got NA (in this case NaN, or "not a number") values in tip by dividing by 0.
df2[['tip','pre-tip']][df2['tip'].isna()]
Pandas group by is similar to SQL's GROUP BY keyword.
Documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html
Let's learn through examples.
# Calculate the mean of each column, grouping by PULocationID
df2.groupby('PULocationID').mean()
# Another way to calculate the same thing
df2.groupby('PULocationID').mean()['pre-tip']
# Calculate the mean of the 'pre-tip' column, grouping by PULocationID
df2.groupby('PULocationID')['pre-tip'].mean()
# Calculate the number of non-missing values of tip, grouping by PULocationID
df2.groupby('PULocationID')['tip'].count()
# Another way to do it
df2[['PULocationID','tip']].groupby('PULocationID').count()
# Calculate the number of non-missing values of tip, grouping by PULocationID, DOLocationID
df2.groupby(['PULocationID','DOLocationID'])['tip'].count()
# This gives us the hour of day and day of week, for Question 3
df2['hour'] = pd.to_datetime(df2['tpep_pickup_datetime']).dt.hour
df2['dayofweek'] = pd.to_datetime(df2['tpep_pickup_datetime']).dt.dayofweek
Question 3
How would we get the total number of trips, broken out by hour of day and day of week?
df2.groupby(['hour','dayofweek']).count()df2.groupby(['hour','dayofweek'])['hour'].count()df2.groupby('hour','dayofweek')['hour'].count()df2.groupby('hour','dayofweek').count()df2.groupby(['hour','dayofweek']).count()
y = df2.groupby(['hour','dayofweek'])['mta_tax'].count()
y.name = 'count'
z = y.reset_index()
z[z['dayofweek']==0]
df2.groupby(['hour','dayofweek'])['tip'].count()
df2.groupby('hour','dayofweek').count()
df2['hourofweek'] = df2['hour']+df2['dayofweek']*24
y = df2.groupby('hourofweek')['hour'].count()
plt.plot(y,'.-')
plt.xlabel('Hour of Week')
plt.ylabel('# of Trips')
mean_grp = df2.groupby(by=['PULocationID','DOLocationID'])['pre-tip'].mean()
std_grp = df2.groupby(by=['PULocationID','DOLocationID'])['pre-tip'].std()
cnt_grp = df2.groupby(by=['PULocationID','DOLocationID'])['pre-tip'].count()
df2.groupby(sort=True,by=['PULocationID','DOLocationID'])['pre-tip'].mean()
# Note that "Name" at the bottom is "pre-tip", because that's the column it came from
mean_grp
The index of each of these Series resulting from groupby is what's called a MultiIndex: it consists of both PULocationID and DOLocationID. This is because it came from a group by on these two fields. We can refer to a record in grp using a pair of values, one for each of the fields in the MultiIndex, like this:
mean_grp[1,48]
# Their names are all pre-tip, so we need to rename them to enable concatenation
mean_grp.name = 'mean'
std_grp.name = 'std'
cnt_grp.name = 'count'
mean_grp
# axis = 1 tells pandas to concatenate columns, like a join.
# The default is axis = 0, which concatenates rows
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html
grp = pd.concat([mean_grp,std_grp,cnt_grp],axis=1)
grp
# The resulting dataframe has the same index as each of the series that was passed to concat.
# If we want to access a value, we can grab the series (like grp['mean']) and then ask for a particular PULocationID and DOLocationID.
grp['mean'][1,1]
Question 4
How would we get the standard deviation of pre-tip amounts for all trips picking up in location 4 and dropping off in location 7?
grp['std'][4][7]grp[4,7]['std']grp['std'][7,4]grp['std'][4,7]grp['std'][4,7]
# standard deviations aren't meaningful when the number of datapoints is too small
# Remove these records
grp = grp[grp['count']>3]
# Instead of working with a multi-index, we want to work with PULocationID and DOLocationID as normal fields.
# To do this, we reset the index to simply be a number that counts rows.
# If we set inplace=False, then this will return the new dataframe but won't alter grp.
# By passing inplace=True, we alter grp.
grp.reset_index(inplace=True)
grp
# We can sort the values by standard devation.
# This will help us look for PU / DO zone pairs that have low standard deviation
grp = grp.sort_values('std')
grp
We see that there are a bunch of PU / DO location pairs where the standard deviation is extremely small. Let's get some human-readable names for these zones so that we can understand what these locations are.
# This file comes from https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page
# (Taxi Zone Lookup Table)
zones = pd.read_csv('taxi_zone_lookup.csv')
zones
#zones['LocationID'] = zones['LocationID'].astype(int)
#zones.dtypes
# Let's create a single human-readable field describing the zone
zones['Zone'] = zones['Borough'] + ': ' + zones['Zone']
zones = zones.drop(columns=['Borough','service_zone']) # This drops the columns Borough and service_zone
zones
# Pandas has a join command similar to SQL.
# This joins grp and zones by finding records where grp['PULocationID'] (this is what "on" means)
# is equal to the index of the first argument (which we set to be zones['LocationID']).
# We do a left join, where grp is the left dataframe.
grp = grp.join(zones.set_index('LocationID'), on='PULocationID', how='left')
grp
# Now we have to rename the column "Zone" that came from our join
grp.rename(columns = {'Zone':'PUZone'},inplace=True)
grp
Question 5
Now we'd like to repeat this to join zones['LocationID'] on grp['DOLocationID']
to create a new field grp['DOZone']. How do we do that?
(A) grp.join(zones.set_index('LocationID'), on='DOLocationID', how='left')
(B) grp.join(zones.set_index('DOLocationID'), on='LocationID', how='left')
(C) grp.join(zones.set_index('LocationID'), on='DOLocationID', how='right')
(D) grp.join(zones.set_index('DOLocationID'), on='LocationID', how='right')
grp.join(zones.set_index('LocationID'), on='DOLocationID', how='right')
grp.join(zones.set_index('LocationID'), on='DOLocationID', how='left')
grp = grp.join(zones.set_index('LocationID'), on='DOLocationID', how='left')
# Insert the answer from the question above here
grp.rename(columns = {'Zone':'DOZone'},inplace=True)
grp[0:5]
So what are the vertical lines in the plots of tip amount and tip vs. pre-tip?
Hint: https://www1.nyc.gov/site/tlc/passengers/taxi-fare.page