<jupyter_start><jupyter_text>Ford GoBike Bike Sharing Exploratory Analysis  Import Data<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
from scipy import stats

from requests import get
from os import path, getcwd, makedirs, listdir 
from io import BytesIO
from zipfile import ZipFile

import warnings
warnings.filterwarnings('ignore')

folder_name_of_csvs = 'trip_data_files'

for month in range(1, 13):
    month_string = str(month)
    month_leading_zero = month_string.zfill(2) #zfill() 返回指定长度的字符串，原字符串右对齐，前面填充0
    
    bike_data_url = 'https://s3.amazonaws.com/fordgobike-data/2018' + month_leading_zero + '-fordgobike-tripdata.csv.zip'
    response = get(bike_data_url) #get(): return the target value, if none, return defalut value
        
    # code below opens zip file; BytesIO returns a readable and writeable view of the contents;
    unzipped_file = ZipFile(BytesIO(response.content))
        
    # puts extracted zip file into folder trip_data_files
    unzipped_file.extractall(folder_name_of_csvs)
list_csvs = []
for file_name in listdir(folder_name_of_csvs):
    list_csvs.append(pd.read_csv(folder_name_of_csvs+'/'+file_name))
df_18 = pd.concat(list_csvs)
df_18.to_csv('data.csv')
df_18 = pd.read_csv('data.csv')<jupyter_output><empty_output><jupyter_text>Assess Data<jupyter_code>df_18.head()
df_18.start_time.sort_values().head(1), df_18.start_time.sort_values().tail(1)
df_18.info()
df_18.nunique()<jupyter_output><empty_output><jupyter_text>**Question: According to the above information, there are 11,771 null values of start/end stations ids and names, so I further check which stations they are to try to find the reason and check whether they will make the analysis in accurate.**<jupyter_code>df_18.count()
df_18[df_18['start_station_id'].isnull() == True].head()
df_18[df_18['start_station_id'].isnull() == True].start_station_latitude.unique()<jupyter_output><empty_output><jupyter_text>**Answer:It appears that the missing value of station id and station names are all in San Jose. Despite no value of station id and name, there are still longtitude and latitude to distinguish the location. So I am not going to remove the null values.**  Wrangle Data  1. Convert member birthday into the age format<jupyter_code>#Code
df_18['member_age'] = 2019 - df_18['member_birth_year'].fillna(0).astype(int)
#Test
df_18.head(1)<jupyter_output><empty_output><jupyter_text>**Note: But there are null value of birth year in the data, so we need to ignore the value when making plot.**<jupyter_code>#Code
#Step1: Process birth year with NaN value, which is 110718 in total
df_18[df_18.member_birth_year.isnull()].bike_id.count()<jupyter_output><empty_output><jupyter_text>2. Convert time data into the formats of time, day, and month<jupyter_code>#Code
from datetime import datetime, timedelta

df_18['start_time'] = pd.to_datetime(df_18['start_time'])
df_18['time'] = df_18['start_time'].apply(lambda t: t.strftime('%H'))
df_18['day'] = df_18['start_time'].apply(lambda t: t.strftime('%A'))
df_18['month'] = df_18['start_time'].apply(lambda t: t.strftime('%B'))
#Test
df_18.head(2)<jupyter_output><empty_output><jupyter_text>Data Visualization  Questions        1: What is the number of customer and subscriber?    2: What is the number of gender of all members?    3: What is the number of age of all members?    4: When are most popular bike time for 20-40-year-old members in terms of month of a year?    5: When are most trips taken by user types in terms of time of day, day of the week, and month of a year?    6: What is the duration distribution of overall / customer / subscriber?    7: How long does the average trip take in overall / by customer and subscriber?    8: What is the number of overtime by subscriber and customer?    9: How many people return their bikes at the same station they start their trip?    10: How many stations in total and which stations are the most popular?  Q1. What is the number of customer and subscriber?<jupyter_code>df_18.user_type.value_counts()
df_18.user_type.value_counts().plot(kind = 'pie', radius = 1.2)
plt.title('The Number of Customer and Subscriber')
plt.savefig('user_type01.png');<jupyter_output><empty_output><jupyter_text>**Finding:Among 1,863,721 members, there are 1,583,554 subscribers (85%) and 280,167 customers (15%).**  Q2: What is the number of gender of members?¶<jupyter_code>s_gender = df_18.query('user_type == "Subscriber"').groupby('member_gender').bike_id.count()
c_gender = df_18.query('user_type == "Customer"').groupby('member_gender').bike_id.count()
labels = ['Female', 'Male', 'Other']

def absolute_value(val):
    a  = np.round(val/100*s_gender.sum(), 0)
    return int(a)

def absolute_value_c(val):
    a  = np.round(val/100*c_gender.sum(), 0)
    return int(a)

plt.figure(0)
plt.title('The Count by Gender of Subscriber')
plt.pie(s_gender, labels = labels, autopct = absolute_value, radius = 1, colors=['yellow', 'orange', 'pink'])

plt.figure(1)
plt.title('The Count by Gender of Customer')
plt.pie(c_gender, labels = labels, autopct = absolute_value_c, radius = 1, colors=['yellow', 'orange', 'pink'])

plt.savefig('gender_01.png');<jupyter_output><empty_output><jupyter_text>**Finding:In terms of the age, the males users are more than the female users in two user types. For customers, they have female (64,047) with the higher in-group ratio (31.7%) than the female of subscribers (374141, 24.1%).**  Q3 What is the number of members' ages?<jupyter_code>df_18.member_age.describe()
df_18.member_age.sort_values().tail()
plt.figure(figsize = [10,4])
plt.hist(df_18.member_age, label = 'gender count')
plt.legend(['age'])
plt.xlabel("Age")  
plt.ylabel("Count")
plt.title('The Ages of All Members');<jupyter_output><empty_output><jupyter_text>**Note: Several age values which are 2019 should be removal in the plot.**<jupyter_code>age = df_18.query('member_age < 2019')
x = [0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150]
plt.figure(figsize = [10,4])
plt.hist(age.member_age, bins = 40)
plt.hist(age.query('user_type == "Subscriber"').member_age, bins = 40, alpha = .5)
plt.hist(age.query('user_type == "Customer"').member_age, bins = 40, alpha = .7)
plt.legend(['All', 'Subscriber', 'Customer'])
plt.xticks(x)
plt.xlabel('Age')
plt.ylabel('Count')
plt.title('The Age Distribution of Members')
plt.savefig('age01.png');<jupyter_output><empty_output><jupyter_text>**Finding:According to the above age distribution, most of members are 20-40 years old. For the subscriber, most of people are 25-30 and 32-35 years old.**  Q4: When are most popular bike time for 20-40-year-old members in terms of month of a year?<jupyter_code>df_age_new = df_18.query('member_age >= 20 & member_age >= 40')
plt.figure(figsize = [10, 4])
order = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 
         'September','October', 'November', 'December']
df_age_new.groupby('month').bike_id.count().sort_values(ascending = False).plot(kind = 'bar', color = 'g')
plt.legend(['count'])
plt.xlabel('Month')
plt.ylabel('Count')
plt.title('Biking Month of 20-40-year-old Members')
plt.savefig('Month_of_A_Year02.png');<jupyter_output><empty_output><jupyter_text>**Finding:Delving into the 20-40-year-old group, they prefer to go to bike trip during summer, from Mau to August.**  Q5: When are most popular bike time for two user types in terms of time of day, day of the week, and month of a year? **Time of a Day**<jupyter_code>def plot_user_time(df, x, y, z):
    df1 = df.groupby([x, y])[z].count().reset_index().sort_values(by = z)
    #plot
    plt.figure(figsize = [10, 4])
    sns.barplot(data = df1, x = x, y = z, hue = y);
plot_user_time(df_18, 'time', 'user_type', 'bike_id')
plt.title('The Popular Time for Trips by Customer / Subscriber')
plt.savefig('Time_of_A_Day01.png')<jupyter_output><empty_output><jupyter_text>**Finding:Looking to user behavior through the time of a day, for subscribers, there are two prefer time segments they ride bikes, which are 7:00 - 9:00 and 16:00 - 18:00. While for customers, they have more consistent using time, and the peak is around 17:00.** **Day of a Month**<jupyter_code>plot_user_time(df_18, 'day', 'user_type', 'bike_id')
plt.title('The Popular Day for Trips by Customer / Subscriber')
plt.savefig('Day_of_A_Month01.png')<jupyter_output><empty_output><jupyter_text>**Finding:In terms of the day of a month, subscribers often ride bikes during weekday, from Monday to Friday, while customers prefer to rider bike on weekends.** **Month of a Year**<jupyter_code>plot_user_time(df_18, 'month', 'user_type', 'bike_id')
plt.title('The Popular Month for Trips by Customer / Subscriber')
plt.savefig('Month_of_A_Year01.png')<jupyter_output><empty_output><jupyter_text>**Finding:In terms of the month of a year, subscribers tend to ride bikes from summer to fall, especially on October and customers also use bike on summer more frequently.**  Q6: What is the duration distribution of overall / customer / subscriber?<jupyter_code>all_duration = df_18['duration_sec']
S_Duration = df_18.query('user_type == "Subscriber"').duration_sec
C_Duration = df_18.query('user_type == "Customer"').duration_sec

plt.figure(figsize = [10,4])
plt.hist(all_duration, bins = 50)
plt.hist(S_Duration, bins = 50, alpha = .7)
plt.hist(C_Duration, bins = 50, alpha = .7)
plt.title('The Trip Duration of Members')
plt.legend(['All', 'Subscriber', 'Customer'])
plt.xlabel('Duration')
plt.ylabel('Count')
plt.savefig('Durtion01.png');<jupyter_output><empty_output><jupyter_text>**Finding:The majority trip duraton is located beloew 3000 second (50 min) probably because if users ride bike over 30 mins, they will be charged additional fee of using bikes.**<jupyter_code>plt.figure(figsize = [10,4])
plt.hist(df_18[df_18['duration_sec'] < 3000].duration_sec, bins = 50)
plt.hist(S_Duration[S_Duration < 3000], bins = 50, alpha = .5)
plt.hist(C_Duration[C_Duration < 3000], bins = 50, alpha = .7)
plt.axvline(1800, color = 'g')
plt.title('The Major Trip Duration of Members')
plt.legend(['Overtime Line','All', 'Subscriber', 'Customer'])
plt.xlabel('Duration')
plt.ylabel('Count')
plt.savefig('Durtion02.png');<jupyter_output><empty_output><jupyter_text>**Finding:So let's look deeper into those who ride bikes under 3000 sec and plot the 30 min line to see how many people ride overtime. According to the above plot, most of people use bike within 5-10 minutes. They might only use bikes for the short-distance trip.**  Q7: How long does the average trip take in overall / by customer and subscriber?<jupyter_code>print('The average time of trip in overall: ' + repr(df_18.duration_sec.mean()))
print()
print('Plot those whose duration is less than 3000 sec:')
plt.figure(figsize = [10,4])
sns.boxplot(df_18[df_18.duration_sec <3000].duration_sec)
plt.title('The Average Time of Trip')
plt.ylabel('All Member')
plt.savefig('AvgTime01.png');
print('The average time of trip for customers: ' + repr(C_Duration.mean()))
print('The average time of trip for subscribers: ' + repr(S_Duration.mean()))
print()
print('Plot those whose duration is less than 3000 sec:')
plt.figure(figsize = [10,4])
sns.boxplot( x = df_18[df_18.duration_sec <3000].duration_sec, y = df_18.user_type)
plt.title('The Average Time of Trip by Customer / Subscriber')
plt.savefig('AvgTime02.png');<jupyter_output>The average time of trip for customers: 1922.7795279244165
The average time of trip for subscribers: 668.7952750585076

Plot those whose duration is less than 3000 sec:<jupyter_text>**Finding: The average time of trip for customers: 1923 sec (32 min). The average time of trip for subscribers: 669 sec (11 min). So apparently, as opposed to subscribers, more customers have overtime ride partly due to the different charging mechnism for this group of users. They joined the membership program of GoBike, so they pay lower if they rider overtime.**  Q8: What is the number of overtime by subscriber and customer?<jupyter_code>print('Overall:')
print('The number of those who use bike within 30 minutes: ' + repr(all_duration[all_duration <= 1800].count()))
print('The number of those who use bike over 30 minutes: ' + repr(all_duration[all_duration > 1800].count()))
print()
print('Subscriber:')
print('The number of subscribers use bike within 30 minutes: ' + repr(S_Duration[S_Duration <= 1800].count()))
print('The number of subscribers use bike over 30 minutes: ' + repr(S_Duration[S_Duration > 1800].count()))
print()
print('Customer:')
print('The number of customers use bike within 30 minutes: ' + repr(C_Duration[C_Duration <= 1800].count()))
print('The number of customers use bike over 30 minutes: ' + repr(C_Duration[C_Duration > 1800].count()))
df1 = df_18[['duration_sec', 'user_type']]
df3 = df1[df1['duration_sec'] <= 1800].groupby('user_type').count()
df3.rename(columns = {'duration_sec': 'within 30 mins'}, inplace = True);
df4 = df1[df1['duration_sec'] > 1800].groupby('user_type').count()
df4.rename(columns = {'duration_sec': 'over 30 mins'}, inplace = True)

df5 = df4.join(df3)
df5['total'] = df5['within 30 mins'] + df5['over 30 mins']
df5.loc['Total'] = df5.sum()
df5
df5.plot(kind = 'bar', figsize = [10,4])
plt.title('The Number of Overtime Users')
plt.ylabel('count')
plt.savefig('Overtime01.png');<jupyter_output><empty_output><jupyter_text>**Overtime Percentage by User Type**<jupyter_code>df6 = df5.copy()
df6.drop('total', axis = 1, inplace = True)
df6.iloc[0,0] = df5.iloc[0,0] / df5.iloc[2,0]
df6.iloc[1,0] = df5.iloc[1,0] / df5.iloc[2,0]
df6.iloc[2,0] = df5.iloc[2,0] / df5.iloc[2,0]

df6.iloc[0,1] = df5.iloc[0,1] / df5.iloc[2,1]
df6.iloc[1,1] = df5.iloc[1,1] / df5.iloc[2,1]
df6.iloc[2,1] = df5.iloc[2,1] / df5.iloc[2,1]
df6
df6.plot(kind = 'bar', figsize = [10,4])
plt.title('The Percentage of Overtime Users')
plt.ylabel('percentage of overtime users')
plt.savefig('Overtime02.png');<jupyter_output><empty_output><jupyter_text>**Finding:Nearly 56.7% customers ride bikes over 30 minutes. So consistent to the findings in the previous plots, it is more likely for customers to rider overtime due to their membership.**  Q9: How many people return their bikes at the same station they start their trip?<jupyter_code>print('The number of users who return bike at the same station: ' + repr(df_18.query('start_station_name == end_station_name').start_station_id .count()))
print('The number of users who return bike at different station: ' + repr(df_18.query('start_station_name != end_station_name').start_station_id .count()))
print()
print('The number of customers who return bike at the same station: ' + repr(df_18.query('start_station_name == end_station_name & user_type == "Customer"').start_station_id .count()))
print('The number of customers who return bike at different station: ' + repr(df_18.query('start_station_name != end_station_name & user_type == "Customer"').start_station_id .count()))
print()
print('The number of subscribers who return bike at the same station: ' + repr(df_18.query('start_station_name == end_station_name & user_type == "Subscriber"').start_station_id .count()))
print('The number of subscribers who return bike at different station: ' + repr(df_18.query('start_station_name != end_station_name & user_type == "Subscriber"').start_station_id .count()))


df_12 = df_18.query('start_station_name == end_station_name')[['user_type','bike_id']]
df_13 = df_18.query('start_station_name != end_station_name')[['user_type','bike_id']]
#one-way: pickup and dropoff at the same place
df_12.groupby('user_type').count()
#pickup and dropoff at the different place
df_13.groupby('user_type').count()
df_12.groupby('user_type').count().plot(kind = 'bar', figsize = [10,4], legend = False, title = 'One-way Bike Rental')
plt.ylabel('count')
df_13.groupby('user_type').count().plot(kind = 'bar', figsize = [10,4], legend = False, title = 'Non-One-way Bike Rental')
plt.ylabel('count')
plt.savefig('OneWay01.png');<jupyter_output><empty_output><jupyter_text>**Finding:Most of people choose to return bikes at the same station they rent the bikes, especially for the subscriber. Yet only look to one-way rental, it is more likely for customers to return their bike at the different station they rent the bike. Maybe it is because that customers tend to have long-distance trips, so it will be more convenient for them to return at the different stations.**  Q10: How many stations in total? Which station is the most popular one?<jupyter_code>print('The total station number: ' + repr(df_18['start_station_id'].nunique()))
plt.figure(figsize = [10,6])
plt.scatter(df_18['start_station_latitude'], df_18['start_station_longitude'])
plt.title('The Locations of Bike Stations')
plt.legend(['Bike Stations'])
plt.xlabel('station latitude')
plt.ylabel('station longitude')
plt.savefig('Station01.png');<jupyter_output>The total station number: 331<jupyter_text>**Filter out the docks in San Francisco Bay Area**<jupyter_code>max_longitude_sf = -122.3597
min_longitude_sf = -122.5147
max_latitude_sf = 37.8121
min_latitude_sf = 37.7092
start_lat = (df_18['start_station_latitude']>=min_latitude_sf) & (df_18['start_station_latitude']<=max_latitude_sf)
end_lat = (df_18['end_station_latitude']>=min_latitude_sf) & (df_18['end_station_latitude']<=max_latitude_sf)
start_lon = (df_18['start_station_longitude']>=min_longitude_sf) & (df_18['start_station_longitude']<=max_longitude_sf)
end_lon = (df_18['end_station_longitude']>=min_longitude_sf) & (df_18['end_station_longitude']<=max_longitude_sf)

df_sf = df_18[start_lat & end_lat & start_lon & end_lon]
df_sf.start_station_latitude.max(), df_sf.start_station_latitude.min(), df_sf.start_station_longitude.max(), df_sf.start_station_longitude.min()

df_sf.head(1)
print('The total station number in San Francisco: ' + repr(df_sf['start_station_id'].nunique()))

plt.figure(figsize = [20,10])
plt.scatter(df_sf['start_station_latitude'], df_sf['start_station_longitude'], color = 'navy')
plt.title('The Total Stations in San Francisco')
plt.legend(['Bike Stations'])
plt.xlabel('station latitude')
plt.ylabel('station longitude');
df_sf_top = pd.DataFrame(df_18['start_station_name'].value_counts())
df_sf_top = df_sf_top.reset_index().rename(columns = {'index':'start_station_name', 'start_station_name':'count'})
df_sf_top.head().start_station_name<jupyter_output><empty_output><jupyter_text>**Top 5 most popular stations in SF**<jupyter_code>df_top5 = df_18.query('start_station_name == ["San Francisco Ferry Building (Harry Bridges Plaza)" ,"San Francisco Caltrain Station 2  (Townsend St at 4th St)", "San Francisco Caltrain (Townsend St at 4th St)", "Market St at 10th St", "Berry St at 4th St"]')

name = [[df_top5['start_station_latitude'], df_top5['start_station_longitude']]]

plt.figure(figsize = [10,6])
plt.scatter(df_top5['start_station_latitude'], df_top5['start_station_longitude'])
plt.title('The Locations of Top 5 Stations in San Francisco')
plt.legend(['Bike Stations'])
plt.xlabel('station latitude')
plt.ylabel('station longitude')
plt.savefig('Station03.png');
#find top 5 stations among all sf stations
print('The total station number in San Francisco: ' + repr(df_sf['start_station_id'].nunique()))

plt.figure(figsize = [20,10])
plt.scatter(df_sf['start_station_latitude'], df_sf['start_station_longitude'], color = 'c')
#highlight top 5 stations
plt.scatter(df_top5['start_station_latitude'], df_top5['start_station_longitude'], color = 'r')
plt.title('The Top 5 Popular Stations in San Francisco')
plt.title('The Total Stations in San Francisco')
plt.legend(['Bike Stations'])
plt.xlabel('station latitude')
plt.ylabel('station longitude')
plt.savefig('Station02.png');

top_20 = df_18['start_station_name'].value_counts().head(20)
plt.figure(figsize = [10,6])
top_20.plot(kind = 'bar', color = 'deepskyblue')
plt.title('Top 20 Popular Stations')
plt.legend(['Usage Count'])
plt.xlabel('station name')
plt.ylabel('count')
plt.savefig('Station04.png');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1353604953065708, 'fraction_numerical': 0.038396245256640704, 'mean_word_length': 6.315193571950329, 'pattern_counts': {'":': 0, '<': 105, '<?xml version=': 0, '>': 101, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '14499', 'license': 'MIT', 'n_tokens_mistral': 7448, 'n_tokens_neox': 6720, 'n_words': 1903, 'path': 'FordGoBikeSystem_Analysis.ipynb', 'repo_name': 'yuyaya2016/Ford_GoBikeSystem_Analysis', 'size': '20028'}