<jupyter_start><jupyter_text>Separating Station Data informationSince we will be working with many observations (over 2 million), it is a good idea to separate the station data information (id and name) in a separate table. This approach is more efficient and will improve the workflow performance.<jupyter_code>endStationData = complete[['end_station_name', 'end_station_id']].copy()
startStationData = complete[['start_station_name','start_station_id']].copy()
endStationData.rename(columns={'end_station_name':'stationName', 'end_station_id':'stationId'}, inplace=True)
startStationData.rename(columns={'start_station_name':'stationName', 'start_station_id':'stationId'}, inplace=True);
stationData = pd.concat([endStationData, startStationData], sort=False, ignore_index=True)
stationData.info()
stationData.drop_duplicates(inplace=True)
stationData.reset_index(drop=True)
stationData.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 395 entries, 0 to 3026976
Data columns (total 2 columns):
stationName    394 non-null object
stationId      394 non-null float64
dtypes: float64(1), object(1)
memory usage: 9.3+ KB<jupyter_text>Looks like there is 1 null entry. Let's investigate it:<jupyter_code>null_entry = stationData[stationData.isnull().any(axis=1)]
null_entry<jupyter_output><empty_output><jupyter_text>Since it is just a null entry for both the station ID and station Name, let's go ahead and remove it from our station data table:<jupyter_code>stationData.drop(index=null_entry.index, inplace=True)
stationData.info()
# set stationId as string
stationData.stationId = stationData.stationId.astype(str);
stationData.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 394 entries, 0 to 3026976
Data columns (total 2 columns):
stationName    394 non-null object
stationId      394 non-null object
dtypes: object(2)
memory usage: 9.2+ KB<jupyter_text>Finally, we can save our Station Data as a csv file for future reference. Whenever necessary, we can merge the station name back to the station ID.<jupyter_code>stationData.to_csv('stationData.csv', index=False)<jupyter_output><empty_output><jupyter_text>Preparing Complete DatabaseAfter separating the Station name data for future reference, when needed, let's prep the main dataset by dropping unecessary columns and investigating it's integrity:<jupyter_code>complete.head(2)
# Checking for missing data
complete.isnull().sum()<jupyter_output><empty_output><jupyter_text>There are 12,501 observations without data for the Star/End station ID, which violates the assumption the bike was rented and returned.Therefore, let's remove these entries:<jupyter_code>missing_station_id = complete[complete.end_station_id.isnull()]
complete.drop(index=missing_station_id.index, inplace=True)
complete.isnull().sum()<jupyter_output><empty_output><jupyter_text>The "Bike share for All" is only available for data after 2017. It is a program providing special pricing for qualifying low-income users in the San Francisco Bay Area.Let's fill all 2017 missing data as 'No', since the program didn't existed at the time:<jupyter_code>complete.bike_share_for_all_trip.fillna('No', inplace=True);
# Transforming into binary 1:yes | 0:no
complete['low_income_trip'] = np.where(complete.bike_share_for_all_trip == 'Yes', 1,0)
# Droping bike-share-for-all-trip column
complete.drop(columns='bike_share_for_all_trip', inplace=True)
complete.member_birth_year.isnull().sum() / len(complete)<jupyter_output><empty_output><jupyter_text>We still have around 220 thousand observations missing data for birth year and gender. We could resort to imputation techniques, filling the missing values with the median and mode, for numerical and categorical variables, respectively.But since our dataset has over 3.2 million observations, we can just drop these rows with missing values (6.7%) without compromising the representativeness of the data.<jupyter_code># Remove all entries with missing value
complete.dropna(inplace=True)
complete.isnull().sum()
# Removing Station Name
complete.drop(columns=['start_station_name', 'end_station_name'], inplace=True)
# Transforming User Type into Subscriber (1:yes | 0:no)
complete.user_type.value_counts()
complete['subscriber'] = np.where(complete.user_type == 'Subscriber',1,0)
complete.drop(columns='user_type', inplace=True)
# Transforming Gender into binary (1:male | 0:female)
complete['gender'] = np.where(complete.member_gender == 'Male', 1, 0)
complete.drop(columns='member_gender', inplace=True)
# After handling missing data, set appropriate data types
complete.end_station_id = complete.end_station_id.astype(int).astype(str)
complete.start_station_id = complete.start_station_id.astype(int).astype(str)
complete.member_birth_year = complete.member_birth_year.astype(int)

complete.end_time = pd.to_datetime(complete.end_time)
complete.start_time = pd.to_datetime(complete.start_time)
# Transforming duration from Seconds to Minutes
complete['duration_min'] = round(complete.duration_sec / 60, 1)
# Drop duration in seconds
complete.drop(columns='duration_sec', inplace=True)
# Calculate user age at time of usage
complete['user_age'] = complete.end_time.dt.year - complete.member_birth_year
# Drop member birth year
complete.drop(columns='member_birth_year', inplace=True)
# Extract time components from start time
complete['year'] = complete.start_time.dt.year
complete['month'] = complete.start_time.dt.month
complete['week'] = complete.start_time.dt.week
complete['day'] = complete.start_time.dt.day
complete['weekday'] = complete.start_time.dt.weekday # 0: Mon | 6: Sun
complete['hour'] = complete.start_time.dt.hour
# Drop End Time, since we already have start time and duration
complete.drop(columns='end_time', inplace=True)
complete.reset_index(drop=True).head(3)
complete.shape<jupyter_output><empty_output><jupyter_text>In order to make the final file size smaller for easier remote workflow, respecting the GitHub max file size policy (50mb), let's filter only data from 2017:<jupyter_code>df_part = complete.query('year ==2017')[['bike_id', 'end_station_id', 'end_station_latitude',
       'end_station_longitude', 'start_station_id', 'start_station_latitude',
       'start_station_longitude', 'start_time', 'low_income_trip',
       'subscriber', 'gender', 'duration_min', 'user_age']]
df_part.info()
df_part.to_csv('Clean_FordGoBike_2017.csv', index=False)<jupyter_output><empty_output><jupyter_text>Finally, we are ready to save the final complete data into a csv file for further EDA.<jupyter_code># complete.to_csv('Clean_FordGoBike_2017_2019.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1049698795180723, 'fraction_numerical': 0.017319277108433735, 'mean_word_length': 7.580103359173127, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '358684', 'license': 'MIT', 'n_tokens_mistral': 2130, 'n_tokens_neox': 1995, 'n_words': 552, 'path': '2_Project_ FordGoBike/0_DataPrep.ipynb', 'repo_name': 'marcellovictorino/DAND_5_DataViz', 'size': '6640'}