<jupyter_start><jupyter_text>Getting and inspecting the data Downloading the data. At the moment, only data from June 2015 is considered.<jupyter_code># list containing the link(s) to the csv file(s)
data_links = ['https://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-06.csv']

filenames = []
for link in data_links:
    filenames.append(link.split('/')[-1])
    if not(os.path.isfile(filenames[-1])): # do not download file if it already exists
        urllib.urlretrieve(link, filename)<jupyter_output><empty_output><jupyter_text>Loading the data into a pandas data frame and look at it:<jupyter_code>df = pd.DataFrame()
for filename in filenames:
    df = df.append(pd.read_csv(filename), ignore_index=True)
df.head()
df.info()
df.describe()<jupyter_output><empty_output><jupyter_text>Note that some of the numerical features like tip amount and fare amount actually contain negative values. Those invalid values will be deleted in the next section.  Data cleaning  1) Retaining only trips paid by credit cardOnly the credit card tips are recorded in the data set. Therefore, let's only retain trips with credit card payment. This might introduce some bias (as credit card payers may have a different tipping behaviour than others).As seen below, most of the trips are anyway paid by credit card (label "1", followed by cash payment, label "2").<jupyter_code>df.groupby('payment_type').size().plot(kind='bar');<jupyter_output><empty_output><jupyter_text>For some trips, people actually tipped with credit card, even though they did not pay with credit card:<jupyter_code>np.sum((df.payment_type != 1) & (df.tip_amount != 0))<jupyter_output><empty_output><jupyter_text>However, the number of those trips is negligible, so I ignore them here and only retain credit card trips. Then, the column "payment_type" can be removed:<jupyter_code>df = df[df.payment_type == 1]
df.drop('payment_type', axis=1, inplace=True)
df.shape<jupyter_output><empty_output><jupyter_text>2) Checking for unfeasible values in numerical featuresAs seen above, some of the numerical features contained negative values. Let's have a closer look...<jupyter_code>(df < 0).sum()<jupyter_output><empty_output><jupyter_text>...and remove the corresponding rows where negative values do not make any sense:<jupyter_code>col_names = ['total_amount', 'improvement_surcharge', 'tip_amount', 'mta_tax', 'extra', 'fare_amount']

# this removes all rows where at least one value of the columns in col_names is < 0
rows_to_keep = (df[col_names] >= 0).sum(axis=1) == len(col_names)
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]

(df[col_names] < 0).sum() # check if it worked<jupyter_output>removing 13 rows...<jupyter_text>3) Deleting "invalid" trips Inspecting trip distance<jupyter_code>ax = df.loc[sample(df.index, 30000)].plot(y='trip_distance',kind='hist', bins=200)
ax.set_xlim([0,25]);<jupyter_output><empty_output><jupyter_text>Delete trips that are longer than 50 miles...<jupyter_code>rows_to_keep = df.trip_distance <= 50
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 247 rows...<jupyter_text>...and shorter than 0.1 miles:<jupyter_code>rows_to_keep = df.trip_distance >= 0.1
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 30086 rows...<jupyter_text>Inspecting trip fare<jupyter_code>ax = df.loc[sample(df.index, 300000)].plot(y='fare_amount',kind='hist', bins=200)
ax.set_xlim([0,102]);<jupyter_output><empty_output><jupyter_text>There seem to be a decent amount of trips with a fixed rate of 50 USD (see spike above).Now let's remove rows where the fare is below 1 USD:<jupyter_code>rows_to_keep = df.fare_amount >= 1
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 274 rows...<jupyter_text>Inspecting trip duration<jupyter_code>df.tpep_pickup_datetime = pd.to_datetime(df.tpep_pickup_datetime)
df.tpep_dropoff_datetime = pd.to_datetime(df.tpep_dropoff_datetime)
df['trip_duration'] = df.tpep_dropoff_datetime - df.tpep_pickup_datetime 
df['trip_duration_minutes'] = df.trip_duration.dt.seconds/60
ax = df.loc[sample(df.index, 300000)].plot(y='trip_duration_minutes', kind='hist', bins=500)
ax.set_xlim([0,150]);<jupyter_output><empty_output><jupyter_text>Remove trips that took less than half a minute...<jupyter_code>rows_to_keep = df.trip_duration_minutes>0.5
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 3625 rows...<jupyter_text>...as well as trips with a duration of more than 2 hours:<jupyter_code>rows_to_keep = df.trip_duration_minutes<=2*60
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 5988 rows...<jupyter_text>Inspecting passenger count<jupyter_code>df.plot(y='passenger_count', kind='hist', bins=30);<jupyter_output><empty_output><jupyter_text>Remove trips with zero passenger count:<jupyter_code>rows_to_keep = df.passenger_count > 0
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 784 rows...<jupyter_text>Remove trips with a passenger count of more than 6:<jupyter_code>rows_to_keep = df.passenger_count <= 6
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]<jupyter_output>removing 17 rows...<jupyter_text>Removing invalid location coordinatesRemove trips that obviously did not start in NY:<jupyter_code>within_NY = (df.pickup_latitude > 40) & (df.pickup_latitude < 40.9) & \
            (df.pickup_longitude > -74.4) & (df.pickup_longitude < -73.4)

print 'removing '+ str((~within_NY).sum()) + ' rows...'
df = df[within_NY]<jupyter_output>removing 100456 rows...<jupyter_text>Plot the pickup locations to check if they look good. Choose a random sample of all trips, since plotting all trips would take quite a while.<jupyter_code>fig, ax = plt.subplots(figsize=(15, 10))
df.loc[sample(df.index, 200000)].plot(x='pickup_longitude', y='pickup_latitude', 
                                      kind='scatter', ax=ax, alpha=0.3, s=3)
ax.set_xlim([-74.2, -73.7])
ax.set_ylim([40.6, 40.9]);<jupyter_output><empty_output><jupyter_text>The above plot looks reasonable, you can clearly identify the geometry of New York. Let's plot a small subset of data points on a map. Next to central NY, one can identify small hotspots at the surrounding airports.<jupyter_code>subdf = df.loc[sample(df.index, 10000)] # subsample df 
data = subdf[['pickup_latitude', 'pickup_longitude']].values

mapa = folium.Map([40.7, -73.9], zoom_start=11, tiles='stamentoner') # create heatmap
mapa.add_children(plugins.HeatMap(data, min_opacity=0.005, max_zoom=18,
                                  max_val=0.01, radius=3, blur=3))
mapa<jupyter_output><empty_output><jupyter_text>Inspecting the tipAs the tip distribution below shows, people tend to tip whole numbers of dollars (see peaks at e.g. 1 and 2 dollars).<jupyter_code>fig, ax = plt.subplots(figsize=(12,4))
ax = df.loc[sample(df.index, 100000)].plot(y='tip_amount', kind='hist',bins=1500, ax=ax)
ax.set_xlim([0,10.5])
ax.set_xticks(np.arange(0, 11, 0.5));<jupyter_output><empty_output><jupyter_text>A useful metric for a taxi driver to compare tips is the percentage of tip given with respect to the total fare amount.<jupyter_code># check if the fares and fees sum up to total_amount
print pd.concat([df.tip_amount + df.fare_amount + df.tolls_amount + \
           df.extra + df.mta_tax  + df.improvement_surcharge, \
           df.total_amount], axis=1).head()

# calculate tip percentage
df['total_fare'] = df.total_amount - df.tip_amount
df['tip_percentage'] = df.tip_amount / df.total_fare * 100<jupyter_output>0  total_amount
1   8.30          8.30
2  11.00         11.00
3  17.16         17.16
5  10.55         10.55
6  16.30         16.30<jupyter_text>The tip percentage distribution below shows that people mostly seem to tip 0, 20, 25 or 30%.<jupyter_code>data = df.loc[sample(df.index, 100000)].tip_percentage.values
plt.hist(data, np.arange(min(data)-0.5, max(data)+1.5)) 
plt.gca().set_xlim([0,35])
plt.gca().set_xticks(np.arange(0, 51, 5));
plt.legend(['tip_percentage']);<jupyter_output><empty_output><jupyter_text>Remove trips where a tip of more than 100% was recorded, regarding them as invalid outliers.<jupyter_code>rows_to_keep = df.tip_percentage <= 100
print 'removing '+ str((~rows_to_keep).sum()) + ' rows...'
df = df[rows_to_keep]
df.tip_percentage.mean()
df.tip_percentage.median()
df.tip_percentage.mode()
df.tip_percentage.quantile(0.25)
# fig, ax = plt.subplots(figsize=(14,5))
# ax = df.loc[sample(df.index, 100000)].tip_percentage.plot(kind='hist',bins=2000, cumulative=True)
# ax.set_xlim([0,200])<jupyter_output><empty_output><jupyter_text>Tip percentage by day of the week (Monday=0, Sunday=6). People tend to tip a little less on weekends (day 5-6).<jupyter_code>fig, ax = plt.subplots(figsize=(12, 6))
for i in range(7):
    df[df.pickup_weekday==i].groupby('pickup_hour').mean().plot(y='tip_percentage', ax=ax)
plt.legend(['day ' + str(x) for x in range(7)])
ax.set_ylabel('average tip percentage')<jupyter_output><empty_output><jupyter_text>Let's look at the number of trips per hour and day:<jupyter_code>fig, ax = plt.subplots(figsize=(12, 6))
for i in range(7):
    df[df.pickup_weekday==i].groupby('pickup_hour').size().plot(ax=ax)
plt.legend(['day ' + str(x) for x in range(7)])
ax.set_ylabel('number of trips')<jupyter_output><empty_output><jupyter_text>The tip percentage does seem to depend too much on the number of passengers:<jupyter_code>fig, ax = plt.subplots(figsize=(8,7))
df.boxplot('tip_percentage', by='passenger_count', showmeans=True, ax=ax)
ax.set_ylim([15,21])<jupyter_output><empty_output><jupyter_text>Save the cleaned data frame to a file:<jupyter_code>df.to_pickle('df.pickle')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1365283930728957, 'fraction_numerical': 0.03282319774466371, 'mean_word_length': 5.794117647058823, 'pattern_counts': {'":': 0, '<': 116, '<?xml version=': 0, '>': 114, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '29', 'dir': 'jupyter-structured-clean-dedup', 'id': '529655', 'license': 'Apache-2.0', 'n_tokens_mistral': 3788, 'n_tokens_neox': 3503, 'n_words': 839, 'path': 'data_preparation.ipynb', 'repo_name': 'jdoepfert/nyc_taxi_tips', 'size': '9932'}