Pandas Demo 2

In [53]:
# 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'])

Part 1: Outlier removal

  • df.dtypes
  • AND and OR in dataframe filters
  • The ~ operator (NOT)
  • Copying a dataframe and why you might want to do that
In [54]:
# Something useful for the recitation is the dtypes attribute
# This returns all of the datatypes of the dataframe's columns
df.dtypes
Out[54]:
VendorID                   int64
tpep_pickup_datetime      object
tpep_dropoff_datetime     object
passenger_count            int64
trip_distance            float64
RatecodeID                 int64
store_and_fwd_flag        object
PULocationID               int64
DOLocationID               int64
payment_type               int64
fare_amount              float64
extra                    float64
mta_tax                  float64
tip_amount               float64
tolls_amount             float64
improvement_surcharge    float64
total_amount             float64
dtype: object
In [55]:
# Illustrate logical operators on Series
x = pd.Series([True, True, False, False])
y = pd.Series([True, False, True, False])
In [56]:
x
Out[56]:
0     True
1     True
2    False
3    False
dtype: bool
In [57]:
y
Out[57]:
0     True
1    False
2     True
3    False
dtype: bool
In [58]:
x | y
Out[58]:
0     True
1     True
2     True
3    False
dtype: bool
In [59]:
x & y
Out[59]:
0     True
1    False
2    False
3    False
dtype: bool
In [60]:
x || y
  File "<ipython-input-60-07f4a16baca7>", line 1
    x || y
       ^
SyntaxError: invalid syntax
In [61]:
# 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
In [62]:
df[outlier]
Out[62]:
VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance RatecodeID store_and_fwd_flag PULocationID DOLocationID payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount
786 1 2017-06-09 18:04:59 2017-06-09 18:05:23 1 0.00 1 N 158 158 1 2.5 1.0 0.5 8.00 0.0 0.3 12.30
3408 1 2017-06-09 18:22:37 2017-06-09 18:24:30 1 0.50 1 N 48 163 1 4.0 1.0 0.5 10.00 0.0 0.3 15.80
3548 1 2017-06-09 18:23:33 2017-06-09 18:28:26 1 0.60 1 Y 229 233 1 5.0 1.0 0.5 15.00 0.0 0.3 21.80
3594 1 2017-06-09 18:23:53 2017-06-09 18:37:43 1 1.90 1 N 138 138 1 10.0 1.0 0.5 25.00 0.0 0.3 36.80
5149 1 2017-06-09 18:34:05 2017-06-09 18:34:25 1 0.00 1 N 142 142 1 2.5 1.0 0.5 5.00 0.0 0.3 9.30
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2300550 2 2017-06-11 23:40:52 2017-06-11 23:54:10 4 1.63 1 N 239 143 1 8.5 0.5 0.5 37.06 0.0 0.3 46.86
2301013 2 2017-06-11 23:46:07 2017-06-11 23:46:11 1 0.00 1 N 264 170 1 2.5 0.5 0.5 10.00 0.0 0.3 13.80
2301189 2 2017-06-11 23:48:15 2017-06-11 23:50:05 1 0.41 1 N 177 35 1 3.5 0.5 0.5 12.00 0.0 0.3 16.80
2301797 2 2017-06-11 23:55:23 2017-06-12 00:00:38 6 0.69 1 N 49 49 3 -5.5 -0.5 -0.5 0.00 0.0 -0.3 -6.80
2301898 2 2017-06-11 23:56:33 2017-06-11 23:57:11 1 0.32 1 N 107 107 1 3.0 0.5 0.5 5.88 0.0 0.3 10.18

2557 rows × 17 columns

In [63]:
outlier
Out[63]:
0          False
1          False
2          False
3          False
4          False
           ...  
2302082    False
2302083    False
2302084    False
2302085    False
2302086    False
Length: 2302087, dtype: bool
In [64]:
~outlier
Out[64]:
0          True
1          True
2          True
3          True
4          True
           ... 
2302082    True
2302083    True
2302084    True
2302085    True
2302086    True
Length: 2302087, dtype: bool
In [65]:
df[~outlier]
Out[65]:
VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance RatecodeID store_and_fwd_flag PULocationID DOLocationID payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount
0 2 2017-06-08 07:52:31 2017-06-08 08:01:32 6 1.03 1 N 161 140 1 7.5 1.0 0.5 1.86 0.0 0.3 11.16
1 2 2017-06-08 08:08:18 2017-06-08 08:14:00 6 1.03 1 N 162 233 1 6.0 1.0 0.5 2.34 0.0 0.3 10.14
2 2 2017-06-08 08:16:49 2017-06-08 15:43:22 6 5.63 1 N 137 41 2 21.5 1.0 0.5 0.00 0.0 0.3 23.30
3 1 2017-06-09 17:59:37 2017-06-09 18:04:40 1 0.60 1 N 164 234 1 5.0 1.0 0.5 1.02 0.0 0.3 7.82
4 1 2017-06-09 17:59:37 2017-06-09 18:19:26 1 2.80 1 N 229 113 1 14.5 1.0 0.5 3.25 0.0 0.3 19.55
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2302082 2 2017-06-11 23:59:50 2017-06-12 00:04:10 5 1.31 1 N 7 193 2 6.0 0.5 0.5 0.00 0.0 0.3 7.30
2302083 2 2017-06-11 23:59:52 2017-06-12 00:11:03 1 3.17 1 N 234 236 2 11.5 0.5 0.5 0.00 0.0 0.3 12.80
2302084 2 2017-06-11 23:59:58 2017-06-12 00:13:24 1 3.65 1 N 148 17 2 14.0 0.5 0.5 0.00 0.0 0.3 15.30
2302085 2 2017-06-11 23:59:58 2017-06-12 00:07:21 1 1.01 1 N 230 163 2 6.5 0.5 0.5 0.00 0.0 0.3 7.80
2302086 2 2017-06-11 23:59:59 2017-06-12 00:11:15 1 2.07 1 N 48 249 1 9.5 0.5 0.5 2.16 0.0 0.3 12.96

2299530 rows × 17 columns

In [66]:
# 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']
In [67]:
a = pd.DataFrame({'col1' : [1,2,3]})
b = a
a
Out[67]:
col1
0 1
1 2
2 3
In [68]:
b['col1'][0] = 5 # Also modifies the dataframe "a"
a
Out[68]:
col1
0 5
1 2
2 3

Question 1

How would we select records whose total amount paid is less than $10 and whose trip distance is less than 1 mile?

  • (a) df[df['total_amount']<10 & df['trip_distance']<1]
  • (b) df[(df['total_amount']<10) & (df['trip_distance']<1)]
  • (c) df[df['total_amount']<10 && df['trip_distance']<1]
  • (d) df[df['total_amount']<10 and df['trip_distance']<1]
  • (e) more than one of the above works
In [69]:
df[(df['total_amount']<10) & (df['trip_distance']<1)]
Out[69]:
VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance RatecodeID store_and_fwd_flag PULocationID DOLocationID payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount
3 1 2017-06-09 17:59:37 2017-06-09 18:04:40 1 0.60 1 N 164 234 1 5.0 1.0 0.5 1.02 0.0 0.3 7.82
11 1 2017-06-09 17:59:41 2017-06-09 18:08:47 1 0.70 1 N 163 161 2 7.0 1.0 0.5 0.00 0.0 0.3 8.80
14 1 2017-06-09 17:59:42 2017-06-09 18:06:28 1 0.70 1 N 79 79 2 6.0 1.0 0.5 0.00 0.0 0.3 7.80
16 1 2017-06-09 17:59:43 2017-06-09 18:10:44 1 0.80 1 N 230 161 4 8.0 1.0 0.5 0.00 0.0 0.3 9.80
17 1 2017-06-09 17:59:44 2017-06-09 18:05:53 2 0.90 1 N 151 239 1 6.5 1.0 0.5 1.25 0.0 0.3 9.55
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2302022 2 2017-06-11 23:58:34 2017-06-11 23:59:02 2 0.00 1 N 238 238 2 2.5 0.5 0.5 0.00 0.0 0.3 3.80
2302039 2 2017-06-11 23:58:49 2017-06-12 00:03:07 1 0.82 1 N 230 230 1 5.0 0.5 0.5 0.00 0.0 0.3 6.30
2302052 2 2017-06-11 23:59:05 2017-06-12 00:03:21 2 0.46 1 N 43 237 1 4.5 0.5 0.5 1.74 0.0 0.3 7.54
2302054 2 2017-06-11 23:59:11 2017-06-12 00:01:49 1 0.70 1 N 264 264 2 4.5 0.5 0.5 0.00 0.0 0.3 5.80
2302074 2 2017-06-11 23:59:37 2017-06-12 00:03:23 1 0.47 1 N 7 7 1 4.5 0.5 0.5 1.45 0.0 0.3 7.25

508400 rows × 17 columns

Part 2: Tip vs. Pre-tip fare

  • Scatterplots
In [70]:
# 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')
Out[70]:
Text(0, 0.5, 'Tip fraction')
In [71]:
plt.plot(df2['pre-tip'],df2['tip'],'.',markersize=.05)
Out[71]:
[<matplotlib.lines.Line2D at 0x7fd58b112b10>]
In [73]:
# 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')
Out[73]:
Text(0, 0.5, 'Tip fraction')
In [74]:
# What are the horizontal lines?
# What are the curved lines?
# What are the vertical lines?
In [75]:
# 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 ($)')
Out[75]:
Text(0, 0.5, 'Tip ($)')
In [76]:
# Why do you think that some horizontal lines are darker than others?

Question 2

What is the slope of the steepest dark diagonal line?

  • (a) 40%
  • (b) 35%
  • (c) 30%
  • (d) 25%
  • (e) 20%
  • (f) 18%
  • (g) 15%
In [ ]:
 

Part 3: Aggregation and Group By

  • aggregation operators (mean, sum, std, count)
  • NA
  • group by

Aggregation examples without group by (yet)

In [125]:
# Calculate the mean of the pre-tip amount
df2['pre-tip'].mean()
Out[125]:
14.651936761440652
In [126]:
# Calculate the standard deviation of the pre-tip amount
df2['pre-tip'].std()
Out[126]:
12.739944883854003
In [128]:
# Calculate the sum of the pre-tip amount
np.log10(df2['pre-tip'].sum())
Out[128]:
7.5275341153599555
In [129]:
df2['pre-tip'].count()
Out[129]:
2299530
In [17]:
# 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()
Out[17]:
VendorID                 2299530
tpep_pickup_datetime     2299530
tpep_dropoff_datetime    2299530
passenger_count          2299530
trip_distance            2299530
RatecodeID               2299530
store_and_fwd_flag       2299530
PULocationID             2299530
DOLocationID             2299530
payment_type             2299530
fare_amount              2299530
extra                    2299530
mta_tax                  2299530
tip_amount               2299530
tolls_amount             2299530
improvement_surcharge    2299530
total_amount             2299530
tip                      2299205
pre-tip                  2299530
dtype: int64
In [19]:
# 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()]
Out[19]:
tip pre-tip
7850 NaN 0.0
24321 NaN 0.0
37924 NaN 0.0
62276 NaN 0.0
64978 NaN 0.0
... ... ...
2294931 NaN 0.0
2298708 NaN 0.0
2298781 NaN 0.0
2298856 NaN 0.0
2301392 NaN 0.0

325 rows × 2 columns

Group by examples

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.

In [46]:
# Calculate the mean of each column, grouping by PULocationID
df2.groupby('PULocationID').mean()
Out[46]:
VendorID passenger_count trip_distance RatecodeID DOLocationID payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount tip pre-tip hour dayofweek hourofweek
PULocationID
1 1.350877 1.350877 1.743158 4.614035 12.807018 1.345029 77.029649 0.008772 0.149123 8.961462 3.495614 0.294737 89.939357 0.100366 80.977895 12.941520 3.327485 92.801170
2 1.666667 1.666667 12.846667 1.666667 161.333333 1.333333 37.666667 0.000000 0.500000 3.853333 1.920000 0.300000 44.240000 0.100680 40.386667 13.333333 4.000000 109.333333
3 1.600000 1.000000 4.134000 2.000000 84.800000 1.200000 25.100000 0.000000 0.400000 2.658000 1.152000 0.300000 31.590000 0.070914 28.932000 14.800000 2.400000 72.400000
4 1.540594 1.632476 2.675199 1.015585 138.589344 1.339797 11.964145 0.323940 0.498885 1.565542 0.067079 0.299891 14.752646 0.116682 13.187104 11.763139 3.553461 97.046212
6 1.142857 1.000000 2.975714 1.000000 64.000000 1.714286 14.071429 0.285714 0.500000 2.880000 15.728571 0.300000 33.765714 0.064233 30.885714 7.000000 3.285714 85.857143
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
261 1.555308 1.686951 4.084203 1.045297 152.613280 1.365044 16.736848 0.312055 0.495787 2.202279 0.303463 0.299892 20.352226 0.118400 18.149947 14.294397 3.128835 89.386432
262 1.565029 1.633912 2.352015 1.014494 180.607624 1.322321 10.818927 0.240009 0.499325 1.560410 0.172837 0.299957 13.600860 0.125305 12.040450 12.744645 2.887918 82.054673
263 1.556152 1.618396 2.240751 1.013974 173.402049 1.322914 10.342187 0.292013 0.499322 1.489476 0.150915 0.299993 13.079465 0.125969 11.589989 13.173592 3.093550 87.418793
264 1.323350 1.464064 2.739784 1.307732 245.433655 1.345828 14.176665 0.345514 0.489689 1.943099 0.307009 0.298337 17.561154 0.123411 15.618055 13.874567 3.017915 86.304517
265 1.448893 1.355196 3.218773 3.960818 255.057070 1.367121 75.019719 0.091823 0.215928 7.816814 2.605911 0.300937 86.058671 0.094787 78.241857 12.305792 3.068995 85.961670

250 rows × 18 columns

In [302]:
# Another way to calculate the same thing
df2.groupby('PULocationID').mean()['pre-tip']
Out[302]:
PULocationID
1      80.977895
2      40.386667
3      28.932000
4      13.187104
6      30.885714
         ...    
261    18.149947
262    12.040450
263    11.589989
264    15.618055
265    78.241857
Name: pre-tip, Length: 250, dtype: float64
In [301]:
# Calculate the mean of the 'pre-tip' column, grouping by PULocationID
df2.groupby('PULocationID')['pre-tip'].mean()
Out[301]:
PULocationID
1      80.977895
2      40.386667
3      28.932000
4      13.187104
6      30.885714
         ...    
261    18.149947
262    12.040450
263    11.589989
264    15.618055
265    78.241857
Name: pre-tip, Length: 250, dtype: float64
In [20]:
# Calculate the number of non-missing values of tip, grouping by PULocationID
df2.groupby('PULocationID')['tip'].count()
Out[20]:
PULocationID
1        171
2          3
3          5
4       5517
6          7
       ...  
261    13886
262    28147
263    40791
264    32566
265     1169
Name: tip, Length: 250, dtype: int64
In [130]:
# Another way to do it
df2[['PULocationID','tip']].groupby('PULocationID').count()
Out[130]:
tip
PULocationID
1 171
2 3
3 5
4 5517
6 7
... ...
261 13886
262 28147
263 40791
264 32566
265 1169

250 rows × 1 columns

In [21]:
# Calculate the number of non-missing values of tip, grouping by PULocationID, DOLocationID
df2.groupby(['PULocationID','DOLocationID'])['tip'].count()
Out[21]:
PULocationID  DOLocationID
1             1                162
              48                 1
              163                1
              230                1
              264                3
                              ... 
265           259                1
              260                1
              262                1
              264                7
              265             1077
Name: tip, Length: 18986, dtype: int64
In [29]:
# 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?

  • (A) df2.groupby(['hour','dayofweek']).count()
  • (B) df2.groupby(['hour','dayofweek'])['hour'].count()
  • (C) df2.groupby('hour','dayofweek')['hour'].count()
  • (D) df2.groupby('hour','dayofweek').count()
In [190]:
df2.groupby(['hour','dayofweek']).count()
Out[190]:
VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance RatecodeID store_and_fwd_flag PULocationID DOLocationID payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount tip pre-tip hourofweek
hour dayofweek
0 0 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6405 6400 6405 6405
1 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7609 7606 7609 7609
2 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8715 8713 8715 8715
3 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11703 11701 11703 11703
4 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532 12532
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
23 2 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16247 16244 16247 16247
3 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17719 17717 17719 17719
4 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440 18440
5 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18717 18716 18717 18717
6 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10469 10465 10469 10469

168 rows × 20 columns

In [199]:
y = df2.groupby(['hour','dayofweek'])['mta_tax'].count()
y.name = 'count'
In [202]:
z = y.reset_index()
z[z['dayofweek']==0]
Out[202]:
hour dayofweek count
0 0 0 6405
7 1 0 3055
14 2 0 2076
21 3 0 1366
28 4 0 1840
35 5 0 3522
42 6 0 8716
49 7 0 15147
56 8 0 17624
63 9 0 16144
70 10 0 14251
77 11 0 14481
84 12 0 14795
91 13 0 14772
98 14 0 16173
105 15 0 16790
112 16 0 15253
119 17 0 17724
126 18 0 21221
133 19 0 20570
140 20 0 17490
147 21 0 17605
154 22 0 15399
161 23 0 11331
In [137]:
df2.groupby(['hour','dayofweek'])['tip'].count()
Out[137]:
hour  dayofweek
0     0             6400
      1             7606
      2             8713
      3            11701
      4            12532
                   ...  
23    2            16244
      3            17717
      4            18440
      5            18716
      6            10465
Name: tip, Length: 168, dtype: int64
In [131]:
df2.groupby('hour','dayofweek').count()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-131-37a0600fcc94> in <module>
----> 1 df2.groupby('hour','dayofweek').count()

/usr/local/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in groupby(self, by, axis, level, as_index, sort, group_keys, squeeze, observed, **kwargs)
   7881         if level is None and by is None:
   7882             raise TypeError("You have to supply one of 'by' and 'level'")
-> 7883         axis = self._get_axis_number(axis)
   7884         return groupby(
   7885             self,

/usr/local/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in _get_axis_number(cls, axis)
    409             except KeyError:
    410                 pass
--> 411         raise ValueError("No axis named {0} for object type {1}".format(axis, cls))
    412 
    413     @classmethod

ValueError: No axis named dayofweek for object type <class 'pandas.core.frame.DataFrame'>
In [ ]:
 
In [45]:
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')
Out[45]:
Text(0, 0.5, '# of Trips')

Part 4: Why are some pre-tip amounts so common?

  • More practice with aggregation operators and group by
  • concat
  • joins
  • reset_index()
In [165]:
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()
In [166]:
df2.groupby(sort=True,by=['PULocationID','DOLocationID'])['pre-tip'].mean()
Out[166]:
PULocationID  DOLocationID
1             1                81.889630
              48              112.800000
              163              97.300000
              230              63.000000
              264              70.200000
                                 ...    
265           259              51.300000
              260              46.300000
              262             120.800000
              264              24.255556
              265              80.849435
Name: pre-tip, Length: 18986, dtype: float64
In [167]:
# Note that "Name" at the bottom is "pre-tip", because that's the column it came from
mean_grp
Out[167]:
PULocationID  DOLocationID
1             1                81.889630
              48              112.800000
              163              97.300000
              230              63.000000
              264              70.200000
                                 ...    
265           259              51.300000
              260              46.300000
              262             120.800000
              264              24.255556
              265              80.849435
Name: pre-tip, Length: 18986, dtype: float64

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:

In [168]:
mean_grp[1,48]
Out[168]:
112.80000000000001
In [169]:
# 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'
In [170]:
mean_grp
Out[170]:
PULocationID  DOLocationID
1             1                81.889630
              48              112.800000
              163              97.300000
              230              63.000000
              264              70.200000
                                 ...    
265           259              51.300000
              260              46.300000
              262             120.800000
              264              24.255556
              265              80.849435
Name: mean, Length: 18986, dtype: float64
In [171]:
# 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)
In [172]:
grp
Out[172]:
mean std count
PULocationID DOLocationID
1 1 81.889630 41.834549 162
48 112.800000 NaN 1
163 97.300000 NaN 1
230 63.000000 NaN 1
264 70.200000 72.474340 3
... ... ... ... ...
265 259 51.300000 NaN 1
260 46.300000 NaN 1
262 120.800000 NaN 1
264 24.255556 31.032649 9
265 80.849435 73.429649 1079

18986 rows × 3 columns

In [173]:
# 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]
Out[173]:
81.88962962962948

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?

  • (a) grp['std'][4][7]
  • (b) grp[4,7]['std']
  • (c) grp['std'][7,4]
  • (d) grp['std'][4,7]
In [174]:
grp['std'][4,7]
Out[174]:
6.5620412389495755
In [175]:
# standard deviations aren't meaningful when the number of datapoints is too small
# Remove these records
grp = grp[grp['count']>3]
In [176]:
# 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
Out[176]:
PULocationID DOLocationID mean std count
0 1 1 81.889630 41.834549 162
1 4 1 82.912500 6.553673 4
2 4 4 8.872778 12.076631 180
3 4 7 27.379310 6.562041 29
4 4 12 13.800000 2.483277 4
... ... ... ... ... ...
10762 265 132 85.220000 54.989803 6
10763 265 163 65.425000 73.363677 4
10764 265 231 36.017143 51.141487 7
10765 265 264 24.255556 31.032649 9
10766 265 265 80.849435 73.429649 1079

10767 rows × 5 columns

In [177]:
# 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
Out[177]:
PULocationID DOLocationID mean std count
7869 219 230 58.560000 0.000000e+00 4
4144 130 263 58.560000 3.552714e-15 5
9622 243 132 58.560000 4.102320e-15 4
4138 130 186 58.608000 1.073313e-01 5
4130 130 68 58.660000 2.236068e-01 5
... ... ... ... ... ...
460 22 22 62.428571 9.429209e+01 7
3946 117 117 123.072000 1.235311e+02 5
7587 197 197 58.107692 1.323261e+02 13
3952 124 124 214.187273 2.305588e+02 11
3178 100 9 165.204000 2.528154e+02 5

10767 rows × 5 columns

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.

In [178]:
# 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')
In [179]:
zones
Out[179]:
LocationID Borough Zone service_zone
0 1 EWR Newark Airport EWR
1 2 Queens Jamaica Bay Boro Zone
2 3 Bronx Allerton/Pelham Gardens Boro Zone
3 4 Manhattan Alphabet City Yellow Zone
4 5 Staten Island Arden Heights Boro Zone
... ... ... ... ...
260 261 Manhattan World Trade Center Yellow Zone
261 262 Manhattan Yorkville East Yellow Zone
262 263 Manhattan Yorkville West Yellow Zone
263 264 Unknown NV NaN
264 265 Unknown NaN NaN

265 rows × 4 columns

In [180]:
#zones['LocationID'] = zones['LocationID'].astype(int)
#zones.dtypes
In [181]:
# 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
Out[181]:
LocationID Zone
0 1 EWR: Newark Airport
1 2 Queens: Jamaica Bay
2 3 Bronx: Allerton/Pelham Gardens
3 4 Manhattan: Alphabet City
4 5 Staten Island: Arden Heights
... ... ...
260 261 Manhattan: World Trade Center
261 262 Manhattan: Yorkville East
262 263 Manhattan: Yorkville West
263 264 Unknown: NV
264 265 NaN

265 rows × 2 columns

In [182]:
# 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')
In [183]:
grp
Out[183]:
PULocationID DOLocationID mean std count Zone
7869 219 230 58.560000 0.000000e+00 4 Queens: Springfield Gardens South
4144 130 263 58.560000 3.552714e-15 5 Queens: Jamaica
9622 243 132 58.560000 4.102320e-15 4 Manhattan: Washington Heights North
4138 130 186 58.608000 1.073313e-01 5 Queens: Jamaica
4130 130 68 58.660000 2.236068e-01 5 Queens: Jamaica
... ... ... ... ... ... ...
460 22 22 62.428571 9.429209e+01 7 Brooklyn: Bensonhurst West
3946 117 117 123.072000 1.235311e+02 5 Queens: Hammels/Arverne
7587 197 197 58.107692 1.323261e+02 13 Queens: Richmond Hill
3952 124 124 214.187273 2.305588e+02 11 Queens: Howard Beach
3178 100 9 165.204000 2.528154e+02 5 Manhattan: Garment District

10767 rows × 6 columns

In [184]:
# Now we have to rename the column "Zone" that came from our join
grp.rename(columns = {'Zone':'PUZone'},inplace=True)
In [185]:
grp
Out[185]:
PULocationID DOLocationID mean std count PUZone
7869 219 230 58.560000 0.000000e+00 4 Queens: Springfield Gardens South
4144 130 263 58.560000 3.552714e-15 5 Queens: Jamaica
9622 243 132 58.560000 4.102320e-15 4 Manhattan: Washington Heights North
4138 130 186 58.608000 1.073313e-01 5 Queens: Jamaica
4130 130 68 58.660000 2.236068e-01 5 Queens: Jamaica
... ... ... ... ... ... ...
460 22 22 62.428571 9.429209e+01 7 Brooklyn: Bensonhurst West
3946 117 117 123.072000 1.235311e+02 5 Queens: Hammels/Arverne
7587 197 197 58.107692 1.323261e+02 13 Queens: Richmond Hill
3952 124 124 214.187273 2.305588e+02 11 Queens: Howard Beach
3178 100 9 165.204000 2.528154e+02 5 Manhattan: Garment District

10767 rows × 6 columns

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')

In [187]:
grp.join(zones.set_index('LocationID'), on='DOLocationID', how='right')
Out[187]:
PULocationID DOLocationID mean std count PUZone Zone
7869.0 219.0 230 58.560000 0.000000 4.0 Queens: Springfield Gardens South Manhattan: Times Sq/Theatre District
10202.0 256.0 230 23.133333 1.437591 6.0 Brooklyn: Williamsburg (South Side) Manhattan: Times Sq/Theatre District
5954.0 157.0 230 59.416000 2.039284 5.0 Queens: Maspeth Manhattan: Times Sq/Theatre District
1565.0 50.0 230 8.339781 2.253286 1189.0 Manhattan: Clinton West Manhattan: Times Sq/Theatre District
3930.0 116.0 230 23.600000 2.292131 23.0 Manhattan: Hamilton Heights Manhattan: Times Sq/Theatre District
... ... ... ... ... ... ... ...
NaN NaN 184 NaN NaN NaN NaN Bronx: Pelham Bay Park
NaN NaN 187 NaN NaN NaN NaN Staten Island: Port Richmond
NaN NaN 199 NaN NaN NaN NaN Bronx: Rikers Island
NaN NaN 204 NaN NaN NaN NaN Staten Island: Rossville/Woodrow
NaN NaN 253 NaN NaN NaN NaN Queens: Willets Point

10785 rows × 7 columns

In [186]:
grp.join(zones.set_index('LocationID'), on='DOLocationID', how='left')
Out[186]:
PULocationID DOLocationID mean std count PUZone Zone
7869 219 230 58.560000 0.000000e+00 4 Queens: Springfield Gardens South Manhattan: Times Sq/Theatre District
4144 130 263 58.560000 3.552714e-15 5 Queens: Jamaica Manhattan: Yorkville West
9622 243 132 58.560000 4.102320e-15 4 Manhattan: Washington Heights North Queens: JFK Airport
4138 130 186 58.608000 1.073313e-01 5 Queens: Jamaica Manhattan: Penn Station/Madison Sq West
4130 130 68 58.660000 2.236068e-01 5 Queens: Jamaica Manhattan: East Chelsea
... ... ... ... ... ... ... ...
460 22 22 62.428571 9.429209e+01 7 Brooklyn: Bensonhurst West Brooklyn: Bensonhurst West
3946 117 117 123.072000 1.235311e+02 5 Queens: Hammels/Arverne Queens: Hammels/Arverne
7587 197 197 58.107692 1.323261e+02 13 Queens: Richmond Hill Queens: Richmond Hill
3952 124 124 214.187273 2.305588e+02 11 Queens: Howard Beach Queens: Howard Beach
3178 100 9 165.204000 2.528154e+02 5 Manhattan: Garment District Queens: Auburndale

10767 rows × 7 columns

In [188]:
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)
In [189]:
grp[0:5]
Out[189]:
PULocationID DOLocationID mean std count PUZone DOZone
7869 219 230 58.560 0.000000e+00 4 Queens: Springfield Gardens South Manhattan: Times Sq/Theatre District
4144 130 263 58.560 3.552714e-15 5 Queens: Jamaica Manhattan: Yorkville West
9622 243 132 58.560 4.102320e-15 4 Manhattan: Washington Heights North Queens: JFK Airport
4138 130 186 58.608 1.073313e-01 5 Queens: Jamaica Manhattan: Penn Station/Madison Sq West
4130 130 68 58.660 2.236068e-01 5 Queens: Jamaica Manhattan: East Chelsea

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