<jupyter_start><jupyter_text>Import Libraries<jupyter_code>import pandas as pd
import numpy as np

# convert scientific notation to decimals
pd.set_option('display.float_format', lambda x: '%.2f' % x)<jupyter_output><empty_output><jupyter_text>Load Dataset from Google Drive<jupyter_code># Make connection with Google Drive
from google.colab import drive
drive.mount('/content/gdrive')
# Get access the GDrive
!ls -la /content/gdrive/My\ Drive/Colab\ Notebooks/
# Explore the contents of GDrive
!ls -la /content/gdrive/My\ Drive/Colab\ Notebooks/bixi-montreal/raw-data
# Collect csv files with same pattern
from glob import glob 

bixi_trip_data_2014to2018 = glob('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/BIXIDataSet/OD*.csv')
mtl_weather_hourly = glob('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/mtl-weather-hourly/eng-hourly*.csv')

# Load Station csv file to pandas DataFrame

bixi_stations_2014 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/BIXIDataSet/Stations_2014.csv', error_bad_lines=False)
bixi_stations_2014['year'] = 2014

bixi_stations_2015 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/BIXIDataSet/Stations_2015.csv', error_bad_lines=False)
bixi_stations_2015['year'] = 2015

bixi_stations_2016 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/BIXIDataSet/Stations_2016.csv', error_bad_lines=False)
bixi_stations_2016['year'] = 2016

bixi_stations_2017 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/BIXIDataSet/Stations_2017.csv', error_bad_lines=False)
bixi_stations_2017['year'] = 2017

bixi_stations_2018 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/BIXIDataSet/Stations_2017.csv', error_bad_lines=False)
bixi_stations_2018['year'] = 2018

# Load Weather csv file to pandas DataFrame

mtl_weather_daily_2014 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/mtl-weather-daily/eng-daily-01012014-12312014.csv', 
                                     header = 0, skiprows=24, error_bad_lines=False)
mtl_weather_daily_2015 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/mtl-weather-daily/eng-daily-01012015-12312015.csv', 
                                     header = 0, skiprows=24, error_bad_lines=False)
mtl_weather_daily_2016 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/mtl-weather-daily/eng-daily-01012016-12312016.csv', 
                                     header = 0, skiprows=24, error_bad_lines=False)
mtl_weather_daily_2017 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/mtl-weather-daily/eng-daily-01012017-12312017.csv', 
                                     header = 0, skiprows=24, error_bad_lines=False)
mtl_weather_daily_2018 = pd.read_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/raw-data/mtl-weather-daily/eng-daily-01012018-12312018.csv', 
                                     header = 0, skiprows=24, error_bad_lines=False)<jupyter_output><empty_output><jupyter_text>Concate Dataset and Save in Google Drive<jupyter_code># Concat all bixi trip dataset from 2014 to 2018

dlist = [bixi_trip_data_2014to2018]

for d in dlist:

	# Merge data using List comprehension
	df_trip = pd.concat([pd.read_csv(f) for f in d])

	# Write or save merge data into csv file in specific Google Drive folder
# 	df_trip.to_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/processed-data/bixi_trip_2014_2018.csv', index=False, encoding='utf-8-sig')

# Concat bixi stations dataset from 2014 to 2018

df_stations = pd.concat([bixi_stations_2014, bixi_stations_2015, bixi_stations_2016, bixi_stations_2017, bixi_stations_2018], axis = 0)

# Write or save merge data into csv file in specific Google Drive folder
# df_stations.to_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/processed-data/bixi_stations_2014_2018.csv', index=False, encoding='utf-8-sig')

# Concat Montreal Weather Hourly dataset from 2014 to 2018

dlist = [mtl_weather_hourly]

for d in dlist:

	# Merge data using List comprehension
	df_mtl_weather_hourly = pd.concat([pd.read_csv(f, header = 1, skiprows=[1,2,3,4,5,6,7,8,9,10,11,12,13,14]) for f in d])

	# Write or save merge data into csv file in specific folder
# 	df_mtl_weather_hourly.to_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/processed-data/mtl_weather_hourly_2014_2018.csv', index=False, encoding='utf-8-sig')<jupyter_output><empty_output><jupyter_text>Summarizing Data for Inspection<jupyter_code># Summarizing BIXI Trip Data

print(df_trip.shape)
print(df_trip.columns)
print(df_trip.head())
print(df_trip.describe())
# Summarizing BIXI Station Data

print(df_stations.shape)
print(df_stations.columns)
print(df_stations.head())
# Summarizing Montreal Weather Hourly Data

print(df_mtl_weather_hourly.shape)
print(df_mtl_weather_hourly.columns)
print(df_mtl_weather_hourly.head())
print(df_mtl_weather_hourly.describe())
# Keep required columns from weather dataset

new_order = ['Date/Time', 'Year', 'Month', 'Day', 'Time', 'Temp (°C)', 'Dew Point Temp (°C)', 'Rel Hum (%)', 'Wind Dir (10s deg)', 'Wind Spd (km/h)', 
                         'Visibility (km)', 'Stn Press (kPa)', 'Wind Chill', 'Weather']
df_mtl_weather_hourly = df_mtl_weather_hourly[new_order]
df_mtl_weather_hourly.head()<jupyter_output><empty_output><jupyter_text>Converting Data Types<jupyter_code># Get the Data Type of BIXI Trip Dataset
df_trip.dtypes
# # Convert Data Type
# df_trip['start_station_code'] = df_trip['start_station_code'].astype(str)
# df_trip['end_station_code'] = df_trip['end_station_code'].astype(str)


# BIXI Trip Dataset: Transform object/string Start Date and End Date Columns data to DateTime
df_trip['start_date'] = pd.to_datetime(df_trip['start_date'])
df_trip['end_date'] = pd.to_datetime(df_trip['end_date'])
df_trip.head()
# COnfirm converting the Data Type of BIXI Trip Dataset
df_trip.dtypes
# Get the Data Type of each column of Station Data
df_stations.dtypes
# Convert Data Type
# df_stations['code'] = df_stations['code'].astype(str)

df_stations.tail()
# Montreal Weather hourly Data: Get the Data Type of each column 
df_mtl_weather_hourly.dtypes
# Montreal Weather Hourly Data: Transform object/string Date/Time data to datetime
df_mtl_weather_hourly['Date/Time'] = pd.to_datetime(df_mtl_weather_hourly['Date/Time'])

df_mtl_weather_hourly.head()<jupyter_output><empty_output><jupyter_text>Creating New Column<jupyter_code># BIXI Trip Dataset: Create new columns based on the start_date and end_date(month, year, day, time)
df_trip['start_year'] = df_trip['start_date'].dt.year
df_trip['start_month'] = df_trip['start_date'].dt.month
df_trip['start_day'] = df_trip['start_date'].dt.day
df_trip['start_week_day'] = df_trip['start_date'].dt.dayofweek
df_trip['start_hour'] = df_trip['start_date'].dt.hour
df_trip['start_minute'] = df_trip['start_date'].dt.minute
df_trip['end_year'] = df_trip['end_date'].dt.year
df_trip['end_month'] = df_trip['end_date'].dt.month
df_trip['end_day'] = df_trip['end_date'].dt.day
df_trip['end_week_day'] = df_trip['end_date'].dt.dayofweek
df_trip['end_hour'] = df_trip['end_date'].dt.hour
df_trip['end_minute'] = df_trip['end_date'].dt.minute

df_trip.head()
# BIXI Trip Dataset: Get the column names and order of dataframe
df_trip.columns
# Montreal Weather Dataset: Create new columns based on the dates

df_mtl_weather_hourly['Week_day'] = df_mtl_weather_hourly['Date/Time'].dt.dayofweek
df_mtl_weather_hourly['Hour'] = df_mtl_weather_hourly['Date/Time'].dt.hour

df_mtl_weather_hourly.head()
# Montreal Weather Dataset: Get the column names
df_mtl_weather_hourly.columns<jupyter_output><empty_output><jupyter_text>Dealing with Missing Values<jupyter_code># BIXI Trip Dataframe: Number of missing values by columns
print(df_trip.isnull().sum())
# BIXI Station Dataframe: Number of missing values by columns
print(df_stations.isnull().sum())
# BIXI Weather Dataframe: Number of missing values by columns
print(df_mtl_weather_hourly.isnull().sum())
# Fill NaN values with previous values in column

df_mtl_weather_hourly['Temp (°C)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Dew Point Temp (°C)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Rel Hum (%)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Wind Dir (10s deg)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Wind Spd (km/h)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Visibility (km)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Stn Press (kPa)'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Wind Chill'].fillna(method='ffill', inplace=True)
df_mtl_weather_hourly['Weather'].fillna(method='ffill', inplace=True)

print(df_mtl_weather_hourly.isnull().sum())

# Get the NaN value rows of Weather Column
df_mtl_weather_hourly[df_mtl_weather_hourly.Weather.isnull()]
# Set value for particular cell using index 

df_mtl_weather_hourly.at[0, 'Weather'] = "Mostly Cloudy"
df_mtl_weather_hourly.head()
# Recheck the number of missing values by Column
print(df_mtl_weather_hourly.isnull().sum())<jupyter_output>Date/Time              0
Year                   0
Month                  0
Day                    0
Time                   0
Temp (°C)              0
Dew Point Temp (°C)    0
Rel Hum (%)            0
Wind Dir (10s deg)     0
Wind Spd (km/h)        0
Visibility (km)        0
Stn Press (kPa)        0
Wind Chill             0
Weather                0
Week_day               0
Hour                   0
dtype: int64<jupyter_text>Dealing with duplicate rows<jupyter_code># Get Duplicate rows of BIXI Trip Dataframe
duplicate_rows = df_trip[df_trip.duplicated()]
print("Duplicate Rows except first occurrence based on all columns:")
print(duplicate_rows)<jupyter_output>Duplicate Rows except first occurrence based on all columns:
                start_date  start_station_code  ... end_hour  end_minute
6849   2014-04-17 13:23:00                6213  ...       13          30
10215  2014-04-17 21:33:00                6194  ...       21          52
10392  2014-04-17 22:35:00                6233  ...       22          52
14636  2014-04-18 18:27:00                6155  ...       18          30
15025  2014-04-18 19:24:00                6086  ...       19          39
15594  2014-04-18 22:05:00                6186  ...       22          16
15620  2014-04-18 22:11:00                6234  ...       22          14
17054  2014-04-19 11:29:00                6258  ...       11          43
17066  2014-04-19 11:30:00                6083  ...       11          34
17082  2014-04-19 11:34:00                6087  ...       12           6
17170  2014-04-19 11:49:00                6155  ...       12           6
17474  2014-04-19 12:39:00                6257  ...       13   [...]<jupyter_text>**Note: Keeping the duplicate values in the DataFrame because group of people could start same time from a station and end also together in another station with same time.   **  Grouping Station wise Hourly Bike Move Out and Move In<jupyter_code># Copy df_trip DataFrame with specic columns
new_df_col = ['start_year', 'start_month', 'start_day', 'start_hour', 'start_station_code', 'end_station_code'] 
df_trip_copy = df_trip[new_df_col]
df_trip_copy.head()
# Count the number of bikes move in and move out from specific station hourly
df_trip_hourly = df_trip_copy.melt(['start_year', 'start_month', 'start_day', 'start_hour']).groupby(['start_year', 'start_month', 'start_day', 'start_hour','variable']).value.value_counts().unstack(level=4,fill_value=0).reset_index()
df_trip_hourly.head()
# Columns of DataFrame
df_trip_hourly.columns
# Rename df_trip_hourly Column Name
df_trip_hourly.columns = ['Start_year', 'Start_month', 'Start_day', 'Start_hour', 'Station_code', 'Move_in', 'Move_out']
df_trip_hourly.head()<jupyter_output><empty_output><jupyter_text>Merge DataFrames<jupyter_code># Merge df_trip_hourly datafrane with df_weather DataFrame and station DataFrame
df_merged = pd.merge(left=df_trip_hourly, right=df_mtl_weather_hourly, how='left', left_on=['Start_year', 'Start_month', 'Start_day', 'Start_hour'], 
                     right_on=['Year', 'Month', 'Day', 'Hour'])

df_merged = pd.merge(left=df_merged, right=df_stations, how='left', left_on=['Station_code', 'Year'], right_on=['code', 'year'])

df_merged.head()
# Get the column names and order of Merged Dataframe
df_merged.columns
# Change the order of dataframe columns
new_order = ['Station_code', 'name', 'latitude', 'longitude', 'Year', 'Month', 'Day', 'Week_day', 'Hour', 'Move_out', 'Move_in',  
             'Temp (°C)', 'Dew Point Temp (°C)', 'Rel Hum (%)', 'Wind Dir (10s deg)', 'Wind Spd (km/h)', 
             'Visibility (km)', 'Stn Press (kPa)', 'Wind Chill', 'Weather']
df_merged = df_merged[new_order]

# Rename columns names
df_merged.columns = ['Station_Code', 'Station_Name', 'Latitude', 'Longitude', 'Year', 'Month', 'Day', 'Week_Day', 'Hour', 'Bike_Move_Out', 'Bike_Move_In',  
             'Temp_(°C)', 'Dew_Point_Temp_(°C)', 'Rel_Hum_(%)', 'Wind_Dir_(10s_deg)', 'Wind_Spd_(km/h)', 
             'Visibility_(km)', 'Stn_Press_(kPa)', 'Wind_Chill', 'Weather']
df_merged.head()
df_merged.to_csv('/content/gdrive/My Drive/Colab Notebooks/bixi-montreal/processed-data/bixi-trip-weather-data-hourly-2014-2018.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12334148691720406, 'fraction_numerical': 0.0490697502038396, 'mean_word_length': 4.416298675230831, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '603007', 'license': 'MIT', 'n_tokens_mistral': 5423, 'n_tokens_neox': 4733, 'n_words': 984, 'path': 'bixi_montreal_data_wrangling.ipynb', 'repo_name': 'bmshahrier/bixi-montreal', 'size': '13491'}