Data Sources

Capital Bikeshare Data

Code

import pandas as pd
import numpy as np
import datetime
import matplotlib.pyplot as plt

# Read in All CSV Files

Q1_15 = pd.read_csv("./Data/Cabi Data/2015Q1-capitalbikeshare-tripdata.csv")
Q2_15 = pd.read_csv("./Data/Cabi Data/2015Q2-capitalbikeshare-tripdata.csv")
Q3_15 = pd.read_csv("./Data/Cabi Data/2015Q3-capitalbikeshare-tripdata.csv")
Q4_15 = pd.read_csv("./Data/Cabi Data/2015Q4-capitalbikeshare-tripdata.csv")

Q1_16 = pd.read_csv("./Data/Cabi Data/2016Q1-capitalbikeshare-tripdata.csv")
Q2_16 = pd.read_csv("./Data/Cabi Data/2016Q2-capitalbikeshare-tripdata.csv")
Q3_16 = pd.read_csv("./Data/Cabi Data/2016Q3-capitalbikeshare-tripdata.csv")
Q4_16 = pd.read_csv("./Data/Cabi Data/2016Q4-capitalbikeshare-tripdata.csv")

Q1_17 = pd.read_csv("./Data/Cabi Data/2017Q1-capitalbikeshare-tripdata.csv")
Q2_17 = pd.read_csv("./Data/Cabi Data/2017Q2-capitalbikeshare-tripdata.csv")
Q3_17 = pd.read_csv("./Data/Cabi Data/2017Q3-capitalbikeshare-tripdata.csv")
Q4_17 = pd.read_csv("./Data/Cabi Data/2017Q4-capitalbikeshare-tripdata.csv")

jan18 = pd.read_csv("./Data/Cabi Data/201801-capitalbikeshare-tripdata.csv")
feb18 = pd.read_csv("./Data/Cabi Data/201802-capitalbikeshare-tripdata.csv")
mar18 = pd.read_csv("./Data/Cabi Data/201803-capitalbikeshare-tripdata.csv")
apr18 = pd.read_csv("./Data/Cabi Data/201804-capitalbikeshare-tripdata.csv")
may18 = pd.read_csv("./Data/Cabi Data/201805-capitalbikeshare-tripdata.csv")
jun18 = pd.read_csv("./Data/Cabi Data/201806-capitalbikeshare-tripdata.csv")
jul18 = pd.read_csv("./Data/Cabi Data/201807-capitalbikeshare-tripdata.csv")
aug18 = pd.read_csv("./Data/Cabi Data/201808-capitalbikeshare-tripdata.csv")
sep18 = pd.read_csv("./Data/Cabi Data/201809-capitalbikeshare-tripdata.csv")
oct18 = pd.read_csv("./Data/Cabi Data/201810-capitalbikeshare-tripdata.csv")
nov18 = pd.read_csv("./Data/Cabi Data/201811-capitalbikeshare-tripdata.csv")
dec18 = pd.read_csv("./Data/Cabi Data/201812-capitalbikeshare-tripdata.csv")

jan19 = pd.read_csv("./Data/Cabi Data/201901-capitalbikeshare-tripdata.csv")
feb19 = pd.read_csv("./Data/Cabi Data/201902-capitalbikeshare-tripdata.csv")
mar19 = pd.read_csv("./Data/Cabi Data/201903-capitalbikeshare-tripdata.csv")
apr19 = pd.read_csv("./Data/Cabi Data/201904-capitalbikeshare-tripdata.csv")
may19 = pd.read_csv("./Data/Cabi Data/201905-capitalbikeshare-tripdata.csv")
jun19 = pd.read_csv("./Data/Cabi Data/201906-capitalbikeshare-tripdata.csv")
jul19 = pd.read_csv("./Data/Cabi Data/201907-capitalbikeshare-tripdata.csv")
aug19 = pd.read_csv("./Data/Cabi Data/201908-capitalbikeshare-tripdata.csv")
sep19 = pd.read_csv("./Data/Cabi Data/201909-capitalbikeshare-tripdata.csv")
oct19 = pd.read_csv("./Data/Cabi Data/201910-capitalbikeshare-tripdata.csv")
nov19 = pd.read_csv("./Data/Cabi Data/201911-capitalbikeshare-tripdata.csv")
dec19 = pd.read_csv("./Data/Cabi Data/201912-capitalbikeshare-tripdata.csv")

jan20 = pd.read_csv("./Data/Cabi Data/202001-capitalbikeshare-tripdata.csv")
feb20 = pd.read_csv("./Data/Cabi Data/202002-capitalbikeshare-tripdata.csv")
mar20 = pd.read_csv("./Data/Cabi Data/202003-capitalbikeshare-tripdata.csv")
apr20 = pd.read_csv("./Data/Cabi Data/202004-capitalbikeshare-tripdata.csv")
may20 = pd.read_csv("./Data/Cabi Data/202005-capitalbikeshare-tripdata.csv")
jun20 = pd.read_csv("./Data/Cabi Data/202006-capitalbikeshare-tripdata.csv")
jul20 = pd.read_csv("./Data/Cabi Data/202007-capitalbikeshare-tripdata.csv")
aug20 = pd.read_csv("./Data/Cabi Data/202008-capitalbikeshare-tripdata.csv")
sep20 = pd.read_csv("./Data/Cabi Data/202009-capitalbikeshare-tripdata.csv")
oct20 = pd.read_csv("./Data/Cabi Data/202010-capitalbikeshare-tripdata.csv")
nov20 = pd.read_csv("./Data/Cabi Data/202011-capitalbikeshare-tripdata.csv")
dec20 = pd.read_csv("./Data/Cabi Data/202012-capitalbikeshare-tripdata.csv")

jan21 = pd.read_csv("./Data/Cabi Data/202101-capitalbikeshare-tripdata.csv")
feb21 = pd.read_csv("./Data/Cabi Data/202102-capitalbikeshare-tripdata.csv")
mar21 = pd.read_csv("./Data/Cabi Data/202103-capitalbikeshare-tripdata.csv")
apr21 = pd.read_csv("./Data/Cabi Data/202104-capitalbikeshare-tripdata.csv")
may21 = pd.read_csv("./Data/Cabi Data/202105-capitalbikeshare-tripdata.csv")
jun21 = pd.read_csv("./Data/Cabi Data/202106-capitalbikeshare-tripdata.csv")
jul21 = pd.read_csv("./Data/Cabi Data/202107-capitalbikeshare-tripdata.csv")
aug21 = pd.read_csv("./Data/Cabi Data/202108-capitalbikeshare-tripdata.csv")
sep21 = pd.read_csv("./Data/Cabi Data/202109-capitalbikeshare-tripdata.csv")
oct21 = pd.read_csv("./Data/Cabi Data/202110-capitalbikeshare-tripdata.csv")
nov21 = pd.read_csv("./Data/Cabi Data/202111-capitalbikeshare-tripdata.csv")
dec21 = pd.read_csv("./Data/Cabi Data/202112-capitalbikeshare-tripdata.csv")
jan22 = pd.read_csv("./Data/Cabi Data/202201-capitalbikeshare-tripdata.csv")
feb22 = pd.read_csv("./Data/Cabi Data/202202-capitalbikeshare-tripdata.csv")
mar22 = pd.read_csv("./Data/Cabi Data/202203-capitalbikeshare-tripdata.csv")
apr22 = pd.read_csv("./Data/Cabi Data/202204-capitalbikeshare-tripdata.csv")
may22 = pd.read_csv("./Data/Cabi Data/202205-capitalbikeshare-tripdata.csv")
jun22 = pd.read_csv("./Data/Cabi Data/202206-capitalbikeshare-tripdata.csv")
jul22 = pd.read_csv("./Data/Cabi Data/202207-capitalbikeshare-tripdata.csv")
aug22 = pd.read_csv("./Data/Cabi Data/202208-capitalbikeshare-tripdata.csv")
sep22 = pd.read_csv("./Data/Cabi Data/202209-capitalbikeshare-tripdata.csv")
oct22 = pd.read_csv("./Data/Cabi Data/202210-capitalbikeshare-tripdata.csv")
nov22 = pd.read_csv("./Data/Cabi Data/202211-capitalbikeshare-tripdata.csv")
dec22 = pd.read_csv("./Data/Cabi Data/202212-capitalbikeshare-tripdata.csv")

# Append All Datasets together
df = pd.concat([jan21, feb21,mar21, apr21, may21, jun21, jul21, aug21, sep21, oct21, nov21, dec21, jan22, feb22, mar22, apr22, may22, jun22, jul22, aug22, sep22, oct22, nov22, dec22, jan20,feb20, mar20, apr20, may20, jun20, jul20, aug20, sep20, oct20, nov20, dec20, jan19, feb19, mar19, apr19, may19, jun19, jul19, aug19, sep19, oct19, nov19, dec19, jan18, feb18, mar18, apr18, may18, jun18, jul18, aug18, sep18, oct18, nov18, dec18, Q1_15, Q2_15, Q3_15, Q4_15, Q1_16, Q2_16, Q3_16, Q4_16,Q1_17,Q2_17,Q3_17,Q4_17  ])

# Drop unecessary columns
df = df.drop(columns=['ride_id', 'rideable_type',  'ended_at',
                      'start_station_name', 'start_station_id', 'end_station_name',
                      'end_station_id', 'start_lat', 'start_lng', 'end_lat', 'end_lng',
                      'member_casual', 'Duration', 'End date',
                      'Start station number', 'Start station', 'End station number',
                      'End station', 'Bike number', 'Member type'])

# create dataframe of data
df1 = df
df2 = pd.DataFrame(df1)

# Split dataframe into two dataframes based on missing values
df3 = df2[df2["started_at"] == df2["started_at"]]
df4 = df2[df2["started_at"] != df2["started_at"]]

# Drop old variables
df3.drop(columns=["Start date"], inplace=True)
df4.drop(columns=["started_at"], inplace=True)

# Rename variables to match 
df4.rename(columns={"Start date": "started_at"}, inplace=True)

# Concat the two dataframes
df1 = pd.concat([df3,df4])

# Change Date to Datetime type
df1["Date"] = pd.to_datetime(df1["started_at"]).dt.date
df1.drop(columns=["started_at"], inplace = True)

# Group By Date to get count for each day
df2 = df1.groupby(['Date']).size().reset_index()
df2.columns = ['date', 'count']
df2['date'] = pd.to_datetime(df2['date'])
df2 = df2.set_index("date")

Capital Bikeshare is a bicycle sharing organization that operates in Washington DC, and the surrounding areas of Maryland and Virginia. Capital Bikeshare has two types of users, casual and member. Members are those who purchase a yearly pass that gives them unlimited free rides, whereas casual riders purchase a one time, daily, or weekly pass. Many people working and living in DC use the bikes to commute in the city, and to access other areas of the city for groceries, restaurants, or meeting with friends. Given that it is an affordable and accessible alternative method of transit, it will be useful to understand the trends of ridership and future demand for the service. The dataset was collected from the capital bikeshare website, they post monthly csv files containing details of each ride taken with the service. With some data cleaning the dataset that can be used for analysis contains total number of rides taken with capital bikeshare on each day between January 2015 and December 2022.

Metro Rail Data

In Washington DC, one of the primary modes of public transportation is the Metro which runs underground. The city has 6 metro lines that operate within DC and the nearby surrounding areas of Maryland and Virginia. The Metro works on a distance based fare system, with fares increasing based on distance travelled. There is also an option to purchase monthly unlimited passes for commuters. Metro connects commuters to work and residents to all the amenities of the city. Especially for commuters, it is essential that there are enough trains running to accomomdate the number of riders. Therefore it will be useful to understand the trends of ridership and forecast the future demand for ridership. The data was collected from the WMATA (Washington Metropolitan Area Transit Authority) dashboard, and includes daily average number of trips taken by metro for each month between 2011 and 2022. At this time there is no option to download to excel or csv so the monthly data was recorded by hand and saved in an excel document.

Metro Bus Data

WMATA also operates the Metro Bus system in Washington DC and its nearby surrounding areas. The cost to ride is a flat fare, with some exceptions for express routes and senior discounts. As of summer 2023 Metro Bus will be free to all riders which will likely increase ridership. For areas of DC not well served by Metro Rail, Metro Bus is the primary way for residents and commuters to travel the city using public transportation. Understanding ridership trends and future demand for ridership will be useful to ensure that the number of buses can accommodate all those who wish to use it. The Data was also collected from the WMATA dashboard and consists of average daily trips taken for each month between 2011 and 2022.

Weather Data

To better understand how weather affects transportation usage it is useful to have weather data. This data was collected from NOAA and contains temperature and precipitation for each day between 2015 and 2023. The data is from the National Arboretum station which is located in North East DC.