import missingno as msno
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from datetime import datetime
import re Data Cleaning
Overview
Overarching Research Question: Analyze if the ongoing renovation project improved the reliability and performance of the MBTA system. Are there early indicators of significant improvement to the usage and performance of the MBTA system?
Goal: Cleaning existing raw datasets to prep it for exploratory data analysis. This involves addressing data quality issues such as missing values, inconsisent formatting, and irrelevant features. We want to simplify the datasets while transforming them to fit our EDA.
Motivation:
Enable Data Analysis: Cleaning is critical in ensuring accuracy of analysis and is an integral component of the pipeline. We want to avoid bias in results.Consistent Data Integration: We want each dataset to be able to work well with each other. This will enable the comprehensive and robust analysis of MBTA performance.
Objectives:
Handle Null valuesStandarize data formatsResolve duplicates or errorsData TransformatingRelabel features for formattingSubset dataset to maintain relevant features
Our Process
- Python was used to clean all the datasets.
- Key libraries used to clean, including pandas, numpy, seaborn, matplotlib, camelot and datetime, missingno, and re.
Accuracy Data
- Handled Missing Data: limited null values, so minimal imputation and omitted
- Standarized formats: clean up the dates data in the “weekly” column and the numberical minutes in the “bin” column
- Why? we wanted to see if there were any patterns in the number of correct predictions before and after the renovation program 1/1/2022.
- Outlier detection: Outliers were visualized and flagged or excluded based on relevance
- Why? we wanted to see if there were any patterns in the number of correct predictions before and after the renovation program 1/1/2022.
Reliability Data
- Cleaned up the dates and time values in the “trip_date” column
- Why? we wanted to merge the ride reliability datset with the financial audit datasets for the years 2023,2021, and 2019 to find trends between reliability and net position summary amounts
News Feed
- Standarized timestamp: removed the time and kept date
- Transformation: transformed json files to csvs for ease of analysis
- Text cleaning: removed punctation, special characters, extra spaces
Alerts Feed
- Outlier detection: visualized outliers and flagged them
- Text cleaning: removed punctation, special characters, extra spaces
- Subsetted the dataset: removed many columns by tracking null values and relevance
- Handled missing information: imputed with unknowns for string data.
Financial Data
- PDF to CSV Conversion: used camelot library
- Reformatting: removed blank columns or irrelevant information
- Standardizing Formats: standarized columns for consistency
Below, we will show how you how we cleaned the datasets we used for this project. We cleaned a significant number of datasets.
If you need a refresher on the data sources, visit the link below:
Code
Import Libraries:
Accuracy Prediction Cleaning
We handled the accuracy prediction dataset with these steps:
- Cleaned the “bin” column by separating the range values into their own columns- a column for the lower minute range of the time frame bin and another column for the higher minute range of time fram bin
- Converted values in the “weekly” column into datetime format
- Converted columns that were object data types to their respective string and integer data types
- Created a column for pre and post 2021 binary values
- Convert the date values under “weekly” column to numeric, then use if-else condition to convert all values before numeric version of 1/1/2021 to “0” and all dates after numeric version of 1/1/2021 to “1”.
- We decided to set 1/1/2021 as the marker for before and after timeline because the renovation program for the MBTA system began in 1/1/2021.
- Convert the date values under “weekly” column to numeric, then use if-else condition to convert all values before numeric version of 1/1/2021 to “0” and all dates after numeric version of 1/1/2021 to “1”.
Please view the code below!
Load dataset & View raw version
#load the "rapid_transit_and_bus_prediction_accuracy_data.csv" file
ride_accuracy = pd.read_csv("../../data/raw-data/rapid_transit_and_bus_prediction_accuracy_data.csv")
#convert csv file into a dataframe
ride_accuracy_df = pd.DataFrame(ride_accuracy)
#print the first 10 rows of dataframe
print(ride_accuracy_df.head(10))
#print shape of dataframe
print(ride_accuracy_df.shape) weekly mode route_id bin arrival_departure num_predictions \
0 2020-08-06 bus NaN 0-3 min departure 233967
1 2020-08-06 bus NaN 3-6 min departure 227406
2 2020-08-06 bus NaN 6-12 min departure 445848
3 2020-08-06 bus NaN 12-30 min departure 1261843
4 2020-08-13 bus NaN 0-3 min departure 240187
5 2020-08-13 bus NaN 3-6 min departure 233322
6 2020-08-13 bus NaN 6-12 min departure 455828
7 2020-08-13 bus NaN 12-30 min departure 1299411
8 2020-08-20 bus NaN 0-3 min departure 234504
9 2020-08-20 bus NaN 3-6 min departure 227992
num_accurate_predictions
0 183615
1 178297
2 368421
3 1101363
4 189546
5 184662
6 380440
7 1144723
8 185137
9 179140
(7964, 7)
Identify Missing Values
#subset dataframe to create a heatmap to visualize missing data. Keep numeric value columns
ride_accuracy_df_subset = ride_accuracy_df[[ "num_predictions", "num_accurate_predictions" ]]
#create heatmap of datafram before dropping NA values
sns.heatmap(ride_accuracy_df_subset)
plt.show() #show heatmapIdentifying Outlier
#find outliers before dropping na using describe()
print(ride_accuracy_df.describe())
#create boxplot of dataframe with na to visualize outliers
sns.boxplot(data=ride_accuracy_df) num_predictions num_accurate_predictions
count 7.964000e+03 7.964000e+03
mean 2.411512e+05 1.988910e+05
std 2.944540e+05 2.492383e+05
min 0.000000e+00 0.000000e+00
25% 8.053400e+04 7.106275e+04
50% 1.306255e+05 1.100235e+05
75% 3.003818e+05 2.470378e+05
max 2.070137e+06 1.818970e+06
Handling Null Values
#find na values in the columns
ride_accuracy_df_na = ride_accuracy_df.isna().sum()
print(ride_accuracy_df_na)
#drop rows with NA values from the route_id column because it is the only column with na values
ride_accuracy_df_dropna = ride_accuracy_df
ride_accuracy_df_dropna.dropna(inplace=True)
print(ride_accuracy_df_dropna.shape)weekly 0
mode 0
route_id 780
bin 0
arrival_departure 0
num_predictions 0
num_accurate_predictions 0
dtype: int64
(7184, 7)
Datatype Transformation
#check the data types of columns
print("----Data Types Before Transformation-----")
print(ride_accuracy_df_dropna.dtypes)
#convert "weekly" column data type to datetime
ride_accuracy_df_dropna["weekly"] = pd.to_datetime(ride_accuracy_df_dropna["weekly"])
#convert "mode" column data type to string
ride_accuracy_df_dropna["mode"] = ride_accuracy_df_dropna["mode"].astype(str)
#convert "route_id" column data type to string
ride_accuracy_df_dropna["route_id"] = ride_accuracy_df_dropna["route_id"].astype(str)
#convert "bin" column data type to string
ride_accuracy_df_dropna["bin"] = ride_accuracy_df_dropna["bin"].astype(str)
#convert "arrival_departure" column data type to string
ride_accuracy_df_dropna["arrival_departure"] = ride_accuracy_df_dropna["arrival_departure"].astype(str)
#check data types of columns after transformation
print("----Data Types After Transformation-----")
print(ride_accuracy_df_dropna.dtypes)----Data Types Before Transformation-----
weekly object
mode object
route_id object
bin object
arrival_departure object
num_predictions int64
num_accurate_predictions int64
dtype: object
----Data Types After Transformation-----
weekly datetime64[ns]
mode object
route_id object
bin object
arrival_departure object
num_predictions int64
num_accurate_predictions int64
dtype: object
#subset dataframe to create a heatmap to visualize missing data. Keep numeric value columns
ride_accuracy_df_no_na_subset = ride_accuracy_df_dropna[[ "num_predictions", "num_accurate_predictions" ]]
#create heatmap of dataframe after dropping NA values
sns.heatmap(ride_accuracy_df_no_na_subset)
plt.show() #show heatmap#find outliers after dropping na using describe()
print(ride_accuracy_df_dropna.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=ride_accuracy_df_dropna) weekly num_predictions \
count 7184 7.184000e+03
mean 2022-09-09 15:45:18.040088832 1.900083e+05
min 2020-08-03 00:00:00 0.000000e+00
25% 2021-08-30 00:00:00 7.617800e+04
50% 2022-09-26 00:00:00 1.199255e+05
75% 2023-09-25 00:00:00 2.237600e+05
max 2024-08-26 00:00:00 1.158573e+06
std NaN 1.842277e+05
num_accurate_predictions
count 7184.000000
mean 154576.004176
min 0.000000
25% 67576.000000
50% 101258.000000
75% 187399.250000
max 904424.000000
std 144457.231305
Cleaning Features
#split the "bin" column based on space character to separate the numerical range and the min unit
ride_accuracy_df_dropna[["a", "b"]] = ride_accuracy_df_dropna["bin"].str.split(" ", expand = True)
print(ride_accuracy_df_dropna.head())
#split the "a" column based on the "-" character to get a low range and high range of the bin values
ride_accuracy_df_dropna[["Low Range Bin Time (min)", "High Range Bin Time (min)"]] = ride_accuracy_df_dropna["a"].str.split("-", expand = True)
print(ride_accuracy_df_dropna.head())
#remove the unneeded "a" and "b" columns
ride_accuracy_df_drop_cols = ride_accuracy_df_dropna
ride_accuracy_df_drop_cols = ride_accuracy_df_drop_cols.drop(['a', 'b'], axis=1)
print(ride_accuracy_df_drop_cols.head(10)) weekly mode route_id bin arrival_departure num_predictions \
780 2020-08-03 subway Blue 0-3 min blended 63612
781 2020-08-03 subway Blue 3-6 min blended 65346
782 2020-08-03 subway Blue 6-12 min blended 124158
783 2020-08-03 subway Blue 12-30 min blended 350575
784 2020-08-03 subway Green-B 0-3 min blended 144067
num_accurate_predictions a b
780 58116 0-3 min
781 58809 3-6 min
782 112668 6-12 min
783 314248 12-30 min
784 117550 0-3 min
weekly mode route_id bin arrival_departure num_predictions \
780 2020-08-03 subway Blue 0-3 min blended 63612
781 2020-08-03 subway Blue 3-6 min blended 65346
782 2020-08-03 subway Blue 6-12 min blended 124158
783 2020-08-03 subway Blue 12-30 min blended 350575
784 2020-08-03 subway Green-B 0-3 min blended 144067
num_accurate_predictions a b Low Range Bin Time (min) \
780 58116 0-3 min 0
781 58809 3-6 min 3
782 112668 6-12 min 6
783 314248 12-30 min 12
784 117550 0-3 min 0
High Range Bin Time (min)
780 3
781 6
782 12
783 30
784 3
weekly mode route_id bin arrival_departure num_predictions \
780 2020-08-03 subway Blue 0-3 min blended 63612
781 2020-08-03 subway Blue 3-6 min blended 65346
782 2020-08-03 subway Blue 6-12 min blended 124158
783 2020-08-03 subway Blue 12-30 min blended 350575
784 2020-08-03 subway Green-B 0-3 min blended 144067
785 2020-08-03 subway Green-B 3-6 min blended 140653
786 2020-08-03 subway Green-B 6-12 min blended 263133
787 2020-08-03 subway Green-B 12-30 min blended 609628
788 2020-08-03 subway Green-C 0-3 min blended 109905
789 2020-08-03 subway Green-C 3-6 min blended 102057
num_accurate_predictions Low Range Bin Time (min) \
780 58116 0
781 58809 3
782 112668 6
783 314248 12
784 117550 0
785 115349 3
786 216940 6
787 477068 12
788 92240 0
789 84877 3
High Range Bin Time (min)
780 3
781 6
782 12
783 30
784 3
785 6
786 12
787 30
788 3
789 6
Export Cleaned Sheet
ride_accuracy_df_drop_cols.to_csv("../../data/processed-data/Clean_Ride_Accuracy.csv")Transforming to Binary
#alternative analysis: add a column to the dataframe for binary classification target
ride_accuracy_df_binary = ride_accuracy_df_drop_cols
print(ride_accuracy_df_binary["weekly"].dtype) #check id column is datatime type
#we want to observe all dates after 2022-01-01 as "1"'s and all dates before 2022-01-01 as "0"'s
#split the data pre and post 2022-01-01 because that was the year the MBTA infrastructure renovation program began
date_to_string = "2022-01-01" #set variable to 2022-01-01 to find its numeric format
date_convert = datetime.strptime(date_to_string, "%Y-%m-%d") #convert string date to datetime format
print(type(date_convert)) #check if it is datetime format
num_date = int(date_convert.timestamp()) #convert datetime to an integer
#num_date_result = num_date//10**9
#print(num_date_result)
print(num_date) #print the numeric conversion of the 2022-01-01 date which equals 1641013200, easier for if else statement to transform dates into binary values
ride_accuracy_df_binary["Pre and Post 2021"] = ride_accuracy_df_binary["weekly"].astype('int64')//10**9 #convert dates in the "weekly" column into numeric data type. Divide by 10**9 to convert the nanoseconds
print(ride_accuracy_df_binary["Pre and Post 2021"].head()) #print first few rows of numeric format of dates in the "Pre and Post 2021" column
ride_accuracy_df_binary["Pre and Post 2021 Binary"] = np.where(ride_accuracy_df_binary["Pre and Post 2021"] > 1641013200, 1, 0) #if value in "Pre and Post 2021" column is greater than numeric value of 2022-01-01 = 1641013200, then value will be 1 if less than the date then value will be 0
print(ride_accuracy_df_binary["Pre and Post 2021 Binary"].head()) #print first few rows of binary datesdatetime64[ns]
<class 'datetime.datetime'>
1641013200
780 1596412800
781 1596412800
782 1596412800
783 1596412800
784 1596412800
Name: Pre and Post 2021, dtype: int64
780 0
781 0
782 0
783 0
784 0
Name: Pre and Post 2021 Binary, dtype: int64
#export clean binary data csv file to processed-data folder
ride_accuracy_df_binary.to_csv("../../data/processed-data/Clean_Ride_Accuracy_Binary.csv")Reliability Cleaning
We handled the reliability dataset with these steps:
- Clean the “trip_date” column be separating the date and time values into their own columns
- Convert the dates into correct datetime data format
- Convert columns that were object data types to their respective string and integer data types
- Created a column for pre and post 2021 binary values
- Convert the date values under “weekly” column to numeric, then use if-else condition to convert all values before numeric version of 1/1/2021 to “0” and all dates after numeric version of 1/1/2021 to “1”.
Please view the code below!
ride_reliability = pd.read_csv("../../data/raw-data/MBTA_The_RIDE_Reliability.csv")
#convert csv file into a dataframe
ride_reliability_df = pd.DataFrame(ride_reliability)
#print the first 10 rows of dataframe
print(ride_reliability_df.head(10))
#print shape of dataframe
print(ride_reliability_df.shape) trip_date ontime_trip_count trip_count ObjectId
0 2014/07/01 04:00:00+00 5479 5884 1
1 2014/07/02 04:00:00+00 5297 5612 2
2 2014/07/03 04:00:00+00 4959 5306 3
3 2014/07/04 04:00:00+00 1530 1558 4
4 2014/07/05 04:00:00+00 2420 2580 5
5 2014/07/06 04:00:00+00 2202 2369 6
6 2014/07/07 04:00:00+00 5048 5332 7
7 2014/07/08 04:00:00+00 5492 5925 8
8 2014/07/09 04:00:00+00 5668 6163 9
9 2014/07/10 04:00:00+00 5487 6027 10
(3681, 4)
#find na values in the columns
ride_reliability_df_na = ride_reliability_df.isna().sum()
print(ride_reliability_df_na) #no NA values in the dataframetrip_date 0
ontime_trip_count 0
trip_count 0
ObjectId 0
dtype: int64
print(type(ride_reliability_df))
print(ride_reliability_df["trip_date"].dtypes)
print(ride_reliability_df["ontime_trip_count"].dtypes)
print(ride_reliability_df["trip_count"].dtypes)
print(ride_reliability_df["ObjectId"].dtypes)
#convert the "trip_date" column to string data type to prepare for splitting
ride_reliability_df["trip_date"] = ride_reliability_df["trip_date"].astype(str)
print("After converting to string type: ", ride_reliability_df["trip_date"].dtypes)<class 'pandas.core.frame.DataFrame'>
object
int64
int64
int64
After converting to string type: object
#split the "trip_date" column based on space to separate date and time
ride_reliability_df[["Date", "Time"]] = ride_reliability_df["trip_date"].str.split(" ", expand = True)
print(ride_reliability_df.head())
#convert "Date" column to YYYY-MM-DD date format to match the column in the cleaned Clean_Ride_Accuracy.csv file
ride_reliability_df["Date"] = pd.to_datetime(ride_reliability_df["Date"]) #convert "Date" column to dateime format
ride_reliability_df["Date"] = ride_reliability_df["Date"].dt.strftime('%Y-%m-%d') #reformat "Date" value as yyyy-mm-dd
print(ride_reliability_df["Date"].head()) #print first few rows of reformatted Date column trip_date ontime_trip_count trip_count ObjectId \
0 2014/07/01 04:00:00+00 5479 5884 1
1 2014/07/02 04:00:00+00 5297 5612 2
2 2014/07/03 04:00:00+00 4959 5306 3
3 2014/07/04 04:00:00+00 1530 1558 4
4 2014/07/05 04:00:00+00 2420 2580 5
Date Time
0 2014/07/01 04:00:00+00
1 2014/07/02 04:00:00+00
2 2014/07/03 04:00:00+00
3 2014/07/04 04:00:00+00
4 2014/07/05 04:00:00+00
0 2014-07-01
1 2014-07-02
2 2014-07-03
3 2014-07-04
4 2014-07-05
Name: Date, dtype: object
#subset dataframe to create a heatmap to visualize missing data - even though there are no missing data. Keep numeric value columns
ride_reliability_df_subset = ride_reliability_df[[ "ontime_trip_count", "trip_count", "ObjectId"]]
#create heatmap of dataframe, no NA values, no NA values needed to drop
sns.heatmap(ride_reliability_df_subset)
plt.show() #show heatmap#find outliers using describe()
print(ride_reliability_df.describe())
#create boxplot of dataframe to visualize outliers
sns.boxplot(data=ride_reliability_df) ontime_trip_count trip_count ObjectId
count 3681.000000 3681.000000 3681.000000
mean 3349.814453 3730.092910 1841.000000
std 1541.218043 1759.167967 1062.757498
min 150.000000 163.000000 1.000000
25% 2190.000000 2353.000000 921.000000
50% 3005.000000 3362.000000 1841.000000
75% 4943.000000 5529.000000 2761.000000
max 6181.000000 7045.000000 3681.000000
#export clean csv file to processed-data folder
ride_reliability_df.to_csv("../../data/processed-data/Clean_Ride_Reliability.csv")Alert Cleaning
We handled the alert dataset with these steps:
- Load Dataset
- Handle Null Values
- Subset / filter out certain features
- Detect Potential Outliers
- Extract affected direction and stops from description as new columns
- Clean text for formatting
- Export file to processed-data file
Please view the code below!
# Load the dataset
file_path = '../../data/raw-data/merged_alerts.csv'
merged_alerts = pd.read_csv(file_path)
print(merged_alerts.head()) # viewed the pre-processed dataset
shape = merged_alerts.shape
print(shape)
data_types = merged_alerts.dtypes
print(data_types)/var/folders/wx/8tzhq3ln59j92r3pl8v47rfw0000gn/T/ipykernel_83572/3812117322.py:4: DtypeWarning: Columns (16,25) have mixed types. Specify dtype option on import or set low_memory=False.
merged_alerts = pd.read_csv(file_path)
alert_id cause cause_detail effect effect_detail severity_level \
0 483702 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE
1 483702 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE
2 483702 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE
3 483702 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE
4 483702 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE
severity header description \
0 7 Route 117 inbound trips all day on Saturdays a... NaN
1 7 Route 117 inbound trips all day on Saturdays a... NaN
2 7 Route 117 inbound trips all day on Saturdays a... NaN
3 7 Route 117 inbound trips all day on Saturdays a... NaN
4 7 Route 117 inbound trips all day on Saturdays a... NaN
alert_lifecycle ... active_period_start_dt active_period_end_dt \
0 UPCOMING ... 2024-02-02T23:00:00Z 2024-02-03T02:30:00Z
1 UPCOMING ... 2024-02-02T23:00:00Z 2024-02-03T02:30:00Z
2 UPCOMING ... 2024-02-02T23:00:00Z 2024-02-03T02:30:00Z
3 UPCOMING ... 2024-02-02T23:00:00Z 2024-02-03T02:30:00Z
4 UPCOMING ... 2024-02-02T23:00:00Z 2024-02-03T02:30:00Z
last_push_notification_dt route_id route_type direction_id stop_id \
0 NaN 117 3.0 NaN NaN
1 NaN 117 3.0 NaN NaN
2 NaN 117 3.0 NaN NaN
3 NaN 117 3.0 NaN NaN
4 NaN 117 3.0 NaN NaN
facility_id activities year
0 NaN BOARD|EXIT 2024
1 NaN BOARD|EXIT 2024
2 NaN BOARD|EXIT 2024
3 NaN BOARD|EXIT 2024
4 NaN BOARD|EXIT 2024
[5 rows x 28 columns]
(2736828, 28)
alert_id int64
cause object
cause_detail object
effect object
effect_detail object
severity_level object
severity int64
header object
description object
alert_lifecycle object
duration_certainty object
service_effect object
timeframe_text object
recurrence_text object
created_dt object
last_modified_dt object
closed_dt object
active_period_start_date object
active_period_start_dt object
active_period_end_dt object
last_push_notification_dt object
route_id object
route_type float64
direction_id float64
stop_id object
facility_id object
activities object
year int64
dtype: object
Handle Null Values * visualize null * evaluate if columns with many null are essential or can be ommitted
msno.matrix(merged_alerts) # visualized it as a matrix
plt.show()missing_values_count = merged_alerts.isnull().sum()
print(missing_values_count)alert_id 0
cause 0
cause_detail 0
effect 0
effect_detail 0
severity_level 0
severity 0
header 0
description 604174
alert_lifecycle 683
duration_certainty 0
service_effect 371
timeframe_text 1620140
recurrence_text 1495277
created_dt 0
last_modified_dt 0
closed_dt 2168374
active_period_start_date 570060
active_period_start_dt 570060
active_period_end_dt 686506
last_push_notification_dt 417188
route_id 108719
route_type 225809
direction_id 2489627
stop_id 594191
facility_id 2511076
activities 47
year 0
dtype: int64
Accessing Relevant Features
Below is my assessment, given our information about nulls and the context, when filtering features.
description(604,174 missing)timeframe_text(1,620,140 missing)recurrence_text(1,495,277 missing)closed_dt(2,168,374 missing)direction_id(2,489,627 missing)facility_id(2,511,076 missing)
These columns have substantial missing values. However, some of these columns are critical to understanding lifecycle events.
Critical Columns Contextually
description: Shows what stops are affected, inbound/outbound.
recurrence_text: Explains what dates are affected. However, over 1 million are missing, which is a significantly high null, so I will remove this column.
Non-Essential Columns
timeframe_text
closed_dt
direction_id
facility_id
Contextually Unneeded Columns
alert_id
header
alert_lifecycle
duration_certainty
created_dt
last_modified_dt
closed_dt
active_period_start_dt
active_period_end_dt
last_push_notification_dt
service_effect
direction_id
stop_id
facility_id
Necessary Columns
cause: Cause of the alert.
cause_detail: Description of the alert.
effect: The effect of the alert on the affected entity.
effect_detail: Description of the effect.
severity_level: Severity level of the alert, expressed as a categorical value.
severity: Numeric severity (0 to 10).
description: Shows stops and routes affected.
route_id: Shows the specific routes affected.
route_type: Shows the type of route affected.stop_idL shows specific spots affected.
# Check for missing values and their distribution
missing_values = merged_alerts.isnull().sum()
# Display basic statistics for numeric columns
numeric_stats = merged_alerts.describe()
# Display basic statistics for categorical columns
categorical_columns = merged_alerts.select_dtypes(include=['object'])
categorical_stats = categorical_columns.describe()# Columns to drop
columns_to_drop = [
'alert_id',
'duration_certainty',
'recurrence_text',
'created_dt',
'last_modified_dt',
'closed_dt',
'active_period_start_date',
'active_period_start_dt',
'active_period_end_dt',
'last_push_notification_dt',
'direction_id',
'facility_id',
'timeframe_text',
'route_type',
'service_effect',
'alert_lifecycle',
'activities',
'header'
]
# Drop the irrelevant columns
merged_alerts_cleaned = merged_alerts.drop(columns=columns_to_drop, errors='ignore')
# Impute Blank Descriptions with unknown
merged_alerts_cleaned['description'].fillna('Unknown', inplace=True)
# For 'route_id' and 'stop_id, fill missing with "Unspecified"
merged_alerts_cleaned['route_id'].fillna('Unspecified', inplace=True)
merged_alerts_cleaned['stop_id'].fillna('Unspecified', inplace=True)
# Display results after cleaning
print(f"Remaining columns: {merged_alerts_cleaned.columns.tolist()}")
print(f"Shape after cleaning: {merged_alerts_cleaned.shape}")
print(f"Missing values after cleaning: \n{merged_alerts_cleaned.isnull().sum()}")
print(merged_alerts_cleaned.head(5))Remaining columns: ['cause', 'cause_detail', 'effect', 'effect_detail', 'severity_level', 'severity', 'description', 'route_id', 'stop_id', 'year']
Shape after cleaning: (2736828, 10)
Missing values after cleaning:
cause 0
cause_detail 0
effect 0
effect_detail 0
severity_level 0
severity 0
description 0
route_id 0
stop_id 0
year 0
dtype: int64
cause cause_detail effect effect_detail severity_level severity \
0 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE 7
1 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE 7
2 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE 7
3 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE 7
4 MAINTENANCE MAINTENANCE DETOUR DETOUR SEVERE 7
description route_id stop_id year
0 Unknown 117 Unspecified 2024
1 Unknown 117 Unspecified 2024
2 Unknown 117 Unspecified 2024
3 Unknown 117 Unspecified 2024
4 Unknown 117 Unspecified 2024
# Output basic statistics after cleaning
print("Numeric Statistics:")
print(merged_alerts_cleaned.describe())
print("Categorical Statistics:")
categorical_columns_cleaned = merged_alerts_cleaned.select_dtypes(include=['object'])
print(categorical_columns_cleaned.describe())Numeric Statistics:
severity year
count 2.736828e+06 2.736828e+06
mean 3.752940e+00 2.022461e+03
std 3.155161e+00 1.364026e+00
min 0.000000e+00 2.020000e+03
25% 1.000000e+00 2.021000e+03
50% 3.000000e+00 2.023000e+03
75% 7.000000e+00 2.024000e+03
max 1.000000e+01 2.024000e+03
Categorical Statistics:
cause cause_detail effect effect_detail \
count 2736828 2736828 2736828 2736828
unique 11 43 8 25
top UNKNOWN_CAUSE UNKNOWN_CAUSE OTHER_EFFECT DELAY
freq 1173449 1173449 1200557 897785
severity_level description route_id stop_id
count 2736828 2736828 2736828 2736828
unique 3 13461 417 7225
top SEVERE Unknown CR-Worcester Unspecified
freq 1117158 604174 288912 594191
Thoughts on Handling Outlier
Severity is the only value that should be looked at for outliers. However, we must think about the severity outliers representing real world events, that is severity values indicate MAJOR disruption (with grave consequences like death or injury). These should not be removed, since they provide valuable information about performance. Outliers should only be removd if there are data-entry errors.
Because we are keeping the outliers, we should analyze to understand the distribution of outlier data. Below is a box plot, which displays the distribution of outliers.
merged_alerts_cleaned['severity'].plot(kind='box', title='Severity Distribution')
plt.show()Thoughts: The whiskers extend to the minimum and maximum values within 1.5 times the IQR. Therefore, there are no points outside the whiskers, indicating that all the values for severity fall within the expected range of 0–10. We will expand more on the alerts in the EDA section.
Extracting Affected Direction and Stops from Description
def extract_direction(description):
if "Affected direction:" in description:
try:
return description.split("Affected direction:")[1].split("\n")[0].strip()
except IndexError:
return None
return None
def extract_stops(description):
if "Affected stops:" in description:
try:
stops = description.split("Affected stops:")[1].replace("\r", "").strip().split("\n")
stops = list(set([stop.strip() for stop in stops if stop.strip()]))
return stops
except IndexError:
return None
return None
merged_alerts_cleaned['affected_direction'] = merged_alerts_cleaned['description'].apply(extract_direction)
merged_alerts_cleaned['affected_stops'] = merged_alerts_cleaned['description'].apply(extract_stops)
print(merged_alerts_cleaned[['description', 'affected_direction', 'affected_stops']].head())
# Count rows where 'affected_direction' and 'affected_stops' are not na
affected_direction_count = merged_alerts_cleaned['affected_direction'].notna().sum()
affected_stops_count = merged_alerts_cleaned['affected_stops'].notna().sum()
description_count = (merged_alerts_cleaned['description'] != "Unknown").sum()
print(f"Number of rows with description as known: {description_count}")
print(f"Number of rows with values in 'affected_direction' (not 'None'): {affected_direction_count}")
print(f"Number of rows with values in 'affected_stops' (not 'None'): {affected_stops_count}")
description affected_direction affected_stops
0 Unknown None None
1 Unknown None None
2 Unknown None None
3 Unknown None None
4 Unknown None None
Number of rows with description as known: 2132654
Number of rows with values in 'affected_direction' (not 'None'): 680508
Number of rows with values in 'affected_stops' (not 'None'): 1362444
Clean texts
# Function to clean text
def clean_text(text):
if pd.isna(text):
return None
text = str(text).lower() # Convert to lowercase
text = re.sub(r'[^\w\s]', '', text) # Remove punctuation and special characters
text = re.sub(r'\s+', ' ', text).strip() # Remove extra spaces
text = re.sub(r'_', ' ', text) # Replace underscores with spaces
return text
# Cleaning each columns
merged_alerts_cleaned['cause'] = merged_alerts_cleaned['cause'].apply(clean_text)
merged_alerts_cleaned['cause_detail'] = merged_alerts_cleaned['cause_detail'].apply(clean_text)
merged_alerts_cleaned['effect'] = merged_alerts_cleaned['effect'].apply(clean_text)
merged_alerts_cleaned['effect_detail'] = merged_alerts_cleaned['effect_detail'].apply(clean_text)
print(merged_alerts_cleaned.head(5))
cause cause_detail effect effect_detail severity_level severity \
0 maintenance maintenance detour detour SEVERE 7
1 maintenance maintenance detour detour SEVERE 7
2 maintenance maintenance detour detour SEVERE 7
3 maintenance maintenance detour detour SEVERE 7
4 maintenance maintenance detour detour SEVERE 7
description route_id stop_id year affected_direction affected_stops
0 Unknown 117 Unspecified 2024 None None
1 Unknown 117 Unspecified 2024 None None
2 Unknown 117 Unspecified 2024 None None
3 Unknown 117 Unspecified 2024 None None
4 Unknown 117 Unspecified 2024 None None
output_file_path = '../../data/processed-data/processed_alerts.csv'
merged_alerts_cleaned.to_csv(output_file_path, index=False)
print(f"Cleaned data successfully saved to: {output_file_path}")Cleaned data successfully saved to: ../../data/processed-data/processed_alerts.csv
News Cleaning
We handled the news dataset with these steps: 1) Load Dataset 2) Choose significant features and ouput them as part of a csv
3) Cleaned datatime 4) Exported to processed-data file
Please view the code below!
import json
def extract_news_to_csv(json_file_path, output_csv_path):
try:
# Load the JSON data
with open(json_file_path, 'r') as file:
data = json.load(file)
# Extract relevant fields
simplified_news = []
for news in data:
simplified_news.append({
"title": news.get("title", "N/A"),
"date": news.get("date", "N/A"),
"source_name": news.get("source", {}).get("name", "N/A"),
})
# Convert to a pandas DataFrame
news_df = pd.DataFrame(simplified_news)
# Save to a CSV file
news_df.to_csv(output_csv_path, index=False)
print(f"CSV file successfully saved to: {output_csv_path}")
except FileNotFoundError:
print(f"File not found: {json_file_path}")
except json.JSONDecodeError:
print(f"Error decoding JSON from file: {json_file_path}")
except Exception as e:
print(f"An unexpected error occurred: {e}")
json_file_path = "../../data/raw-data/google_news_results.json"
output_csv_path = "../../data/raw-data/google_news_revised.csv"
extract_news_to_csv(json_file_path, output_csv_path)
CSV file successfully saved to: ../../data/raw-data/google_news_revised.csv
Clean up datetime
from datetime import datetime
def clean_date(time):
# Parse the datetime string
parsed_date = datetime.strptime(time.split(",")[0], "%m/%d/%Y")
# Format back to just the date
return parsed_date.strftime("%m/%d/%Y")
news_csv = pd.read_csv("../../data/raw-data/google_news_revised.csv")
news_csv['cleaned_date'] = news_csv['date'].apply(clean_date)
print(news_csv[['date', 'cleaned_date']].head()) date cleaned_date
0 12/16/2024, 01:52 AM, +0000 UTC 12/16/2024
1 12/16/2024, 03:25 AM, +0000 UTC 12/16/2024
2 12/15/2024, 07:18 PM, +0000 UTC 12/15/2024
3 12/13/2024, 05:12 PM, +0000 UTC 12/13/2024
4 12/13/2024, 12:28 AM, +0000 UTC 12/13/2024
news_csv['title'] = news_csv['title'].apply(clean_text)
output_file_path = '../../data/processed-data/processed_news.csv'
news_csv.to_csv(output_file_path, index=False)
print(f"Cleaned data successfully saved to: {output_file_path}")Cleaned data successfully saved to: ../../data/processed-data/processed_news.csv
Financial Statement Cleaning
We handled the financial statement pdfs with these steps:
- Load the pdf files into ipynb file in vs code.
- Use the camelot python library to extract the data tables from the pdf files and convert each table into dataframes. Data tables extracted from each year from 2023 to 2019 were net positions, expenses, revenues, and debts.
- For each of the net positions, expenses, revenues, and debts dataframes: drop unneeded columns and rows with NA and special characters, rename headers, re-name observations if needed due to weird spacing of the tables in the pdf files, remove any special characters from data values: “$”, “%”, “-”, and “,”, and convert all columns to their respective string or integer data types.
- Merging!
- Merged all the net position datasets from years 2023, 2021, and 2019 togethe
- Merged all the revenues datasets from years 2023, 2021, and 2019 together
- Merged all the expenses datasets from years 2023, 2021, and 2019 together
- Merged all the debts datasets from years 2023, 2021, and 2019 together
- Afterwards for each of the merged net position, revenue, expenses, and debts datasets: renamed the headers and melted the dataset to prepare merging with the ride reliability dataset to help answer our research questions.
- Merge the ride reliability binary dataset to the merged net position, revenue, expenses, and debts datasets on the “Date” column
- Export all cleaned and merged dataframes into csv files to prepare for eda.
Please view the code below!
WARNING!
Code below has been deprecated due to update of camelot, raising issues further on in this section. Errored code has been deprecated. If you would like to view the full financial statement cleaning with outputs for reference. Please Click Here
#attempt worked correctly with camelot library function
#import camelot library for table extraction from pdf
import camelot
#set pdf file path
pdf_file = "../../data/raw-data/financial-statement-fy2023.pdf"
#extract table from pdf on page 9 using 'stream" because table data has no border
tables = camelot.read_pdf(pdf_file, flavor='stream', pages='9')
#if it is a table and table is found, then convert the table into a dataframe
if tables:
df = tables[0].df
print(df)
else:
print("no tables found") 0 1 2 \
0
1 2023
2 Operating revenue:
3 Revenue from transportation $ 376,357
4 Other 76,616
5 Total operating revenues 452,973
6 Operating expenses:
7 Transportation services 822,671
8 Other operating expenses 968,312
9 Total operating expenses, excluding depreciation 1,790,983
10 Depreciation and amortization 562,247
11 Total operating expenses, including depreciati...
12 amortization 2,353,230
13 Operating loss (1,900,257)
14 Nonoperating revenue, net 1,812,580
15 Gain/(Loss) before capital grants and contribu... (87,677)
16 Capital grants and contributions 1,008,130
17 Increase in net position 920,453
18 Beginning of year, net position 6,682,694
19 End of year, net position $ 7,603,147
3 4
0 June 30
1 2022
2
3 $ 322,292
4 64,565
5 386,857
6
7 858,544
8 890,099
9 1,748,643
10 526,776
11
12 2,275,419
13 (1,888,562)
14 2,340,118
15 451,556
16 1,084,334
17 1,535,890
18 5,146,804
19 $ 6,682,694
#drop unneeded columns from the df dataframe. Drop columns with default headers 1 and 3
df.drop([1,3], axis=1, inplace=True)
#print(df) #print dataframe to check columns dropped#drop unneeded rows with indexes 0,1, and 2
df.drop([0,1,2], inplace=True)
#print(df)#rename headers in dataframe
df.rename(columns={0:"Statements of Revenue, Expenses and Changes in Net Position", 2:"June 30, 2023", 4:"June 30, 2022"}, inplace=True)
print(df) Statements of Revenue, Expenses and Changes in Net Position June 30, 2023 \
3 Revenue from transportation 376,357
4 Other 76,616
5 Total operating revenues 452,973
6 Operating expenses:
7 Transportation services 822,671
8 Other operating expenses 968,312
9 Total operating expenses, excluding depreciation 1,790,983
10 Depreciation and amortization 562,247
11 Total operating expenses, including depreciati...
12 amortization 2,353,230
13 Operating loss (1,900,257)
14 Nonoperating revenue, net 1,812,580
15 Gain/(Loss) before capital grants and contribu... (87,677)
16 Capital grants and contributions 1,008,130
17 Increase in net position 920,453
18 Beginning of year, net position 6,682,694
19 End of year, net position 7,603,147
June 30, 2022
3 322,292
4 64,565
5 386,857
6
7 858,544
8 890,099
9 1,748,643
10 526,776
11
12 2,275,419
13 (1,888,562)
14 2,340,118
15 451,556
16 1,084,334
17 1,535,890
18 5,146,804
19 6,682,694
#rename some values in the "Statements of Revenue, Expenses and Changes in Net Position" column because the table was formatted with too much space for those cells
df.at[4,"Statements of Revenue, Expenses and Changes in Net Position"] = "Other revenue"
df.at[12,"Statements of Revenue, Expenses and Changes in Net Position"] = "Total operating expenses, including depreciation and amortization"
#print(df)#drop rows index 6 and 11 because they had blank values and other rows were correctly renamed
df.drop([6,11], inplace=True)
print(df) Statements of Revenue, Expenses and Changes in Net Position June 30, 2023 \
3 Revenue from transportation 376,357
4 Other revenue 76,616
5 Total operating revenues 452,973
7 Transportation services 822,671
8 Other operating expenses 968,312
9 Total operating expenses, excluding depreciation 1,790,983
10 Depreciation and amortization 562,247
12 Total operating expenses, including depreciati... 2,353,230
13 Operating loss (1,900,257)
14 Nonoperating revenue, net 1,812,580
15 Gain/(Loss) before capital grants and contribu... (87,677)
16 Capital grants and contributions 1,008,130
17 Increase in net position 920,453
18 Beginning of year, net position 6,682,694
19 End of year, net position 7,603,147
June 30, 2022
3 322,292
4 64,565
5 386,857
7 858,544
8 890,099
9 1,748,643
10 526,776
12 2,275,419
13 (1,888,562)
14 2,340,118
15 451,556
16 1,084,334
17 1,535,890
18 5,146,804
19 6,682,694
#check the data types of columns in the dataframe
print(df.dtypes)
df["Statements of Revenue, Expenses and Changes in Net Position"] = df["Statements of Revenue, Expenses and Changes in Net Position"].astype(str) #transform data type from objects into strings in this column
df["June 30, 2023"] = df["June 30, 2023"].str.replace(",", "") #remove comma characters in column "June 30, 2023"
df["June 30, 2022"] = df["June 30, 2022"].str.replace(",", "") #remove comma characters in column "June 30, 2022"
df["June 30, 2023"] = df["June 30, 2023"].str.replace("(", "") #remove '(' characters in column "June 30, 2023"
df["June 30, 2023"] = df["June 30, 2023"].str.replace(")", "") #remove ')' characters in column "June 30, 2023"
df["June 30, 2022"] = df["June 30, 2022"].str.replace("(", "") #remove '(' characters in column "June 30, 2022"
df["June 30, 2022"] = df["June 30, 2022"].str.replace(")", "") #remove ')' characters in column "June 30, 2022"
df["June 30, 2023"] = df["June 30, 2023"].astype(int) #convert "June 30, 2023" column data type to numeric
df["June 30, 2022"] = df["June 30, 2022"].astype(int) #convert "June 30, 2022" column data type to numeric
print("After conversion", df.dtypes)
print(df)Statements of Revenue, Expenses and Changes in Net Position object
June 30, 2023 object
June 30, 2022 object
dtype: object
After conversion Statements of Revenue, Expenses and Changes in Net Position object
June 30, 2023 int64
June 30, 2022 int64
dtype: object
Statements of Revenue, Expenses and Changes in Net Position June 30, 2023 \
3 Revenue from transportation 376357
4 Other revenue 76616
5 Total operating revenues 452973
7 Transportation services 822671
8 Other operating expenses 968312
9 Total operating expenses, excluding depreciation 1790983
10 Depreciation and amortization 562247
12 Total operating expenses, including depreciati... 2353230
13 Operating loss 1900257
14 Nonoperating revenue, net 1812580
15 Gain/(Loss) before capital grants and contribu... 87677
16 Capital grants and contributions 1008130
17 Increase in net position 920453
18 Beginning of year, net position 6682694
19 End of year, net position 7603147
June 30, 2022
3 322292
4 64565
5 386857
7 858544
8 890099
9 1748643
10 526776
12 2275419
13 1888562
14 2340118
15 451556
16 1084334
17 1535890
18 5146804
19 6682694
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
df_subset = df[["June 30, 2023", "June 30, 2022"]]
#create heatmap of dataframe
sns.heatmap(df_subset)
plt.show() #show heatmap#find outliers in dataframe using describe()
print(df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=df) June 30, 2023 June 30, 2022
count 1.500000e+01 1.500000e+01
mean 1.827888e+06 1.746877e+06
std 2.270066e+06 1.860407e+06
min 7.661600e+04 6.456500e+04
25% 5.076100e+05 4.891660e+05
50% 9.683120e+05 1.084334e+06
75% 1.856418e+06 2.081990e+06
max 7.603147e+06 6.682694e+06
#export budget 2023 net positions data csv file to processed-data folder
df.to_csv("../../data/processed-data/Budget2023_net_positions.csv")#revenue_pdf_file = "../../data/raw-data/financial-statement-fy2023.pdf"
#extract revenue table from page 10 of the pdf file
revenue_table23 = camelot.read_pdf(pdf_file, flavor='stream', pages='10')
#if it is a table, then extract the second table from page 10
if revenue_table23:
revenue23_df = revenue_table23[1].df
print(revenue23_df)
else:
print("no tables found") 0 1 2 3 4 5
0 2022:
1 Percent Percent
2 2023 of total 2022 of total
3 Bus $ 76,549 17 % $ 62,305 16 %
4 Subway 176,521 39 % 172,174 45 %
5 Commuter rail 112,801 25 % 80,784 21 %
6 Other passenger 10,486 2 % 7,029 2 %
7 Other operating 76,616 17 % 64,565 16 %
8 $ 452,973 100 % $ 386,857 100 %
#drop unneeded columns from the df dataframe. Drop columns with default header 1
revenue23_df.drop(1, axis=1, inplace=True)
#print(revenue23_df) #print dataframe to check columns dropped#rename column headers in dataframe
revenue23_df.rename(columns={0:"Sources of Operating Revenue", 2:"June 30, 2023 Revenue", 3:"2023 Percent of Total Operating Revenue", 4:"June 30, 2022 Revenue", 5:"2022 Percent of Total Operating Revenue"}, inplace=True)
#print(revenue23_df)#drop rows index 6 and 11 because they had blank values and other rows were correctly renamed
revenue23_df.drop([0,1,2], inplace=True)
print(revenue23_df) Sources of Operating Revenue June 30, 2023 Revenue \
3 Bus 76,549
4 Subway 176,521
5 Commuter rail 112,801
6 Other passenger 10,486
7 Other operating 76,616
8 452,973
2023 Percent of Total Operating Revenue June 30, 2022 Revenue \
3 17 % $ 62,305
4 39 % 172,174
5 25 % 80,784
6 2 % 7,029
7 17 % 64,565
8 100 % $ 386,857
2022 Percent of Total Operating Revenue
3 16 %
4 45 %
5 21 %
6 2 %
7 16 %
8 100 %
#rename some values in the "Sources of Operating Revenue" column because the table was formatted with too much space for those cells
revenue23_df.at[8,"Sources of Operating Revenue"] = "Total Operating Revenue"
print(revenue23_df) Sources of Operating Revenue June 30, 2023 Revenue \
3 Bus 76,549
4 Subway 176,521
5 Commuter rail 112,801
6 Other passenger 10,486
7 Other operating 76,616
8 Total Operating Revenue 452,973
2023 Percent of Total Operating Revenue June 30, 2022 Revenue \
3 17 % $ 62,305
4 39 % 172,174
5 25 % 80,784
6 2 % 7,029
7 17 % 64,565
8 100 % $ 386,857
2022 Percent of Total Operating Revenue
3 16 %
4 45 %
5 21 %
6 2 %
7 16 %
8 100 %
#strip white spaces, $ and % characters from values in the dataframe
revenue23_df["2023 Percent of Total Operating Revenue"] = revenue23_df["2023 Percent of Total Operating Revenue"].str.strip().str.replace("$", "").str.replace("%", "")
revenue23_df["2022 Percent of Total Operating Revenue"] = revenue23_df["2022 Percent of Total Operating Revenue"].str.strip().str.replace("$", "").str.replace("%", "")
revenue23_df["June 30, 2023 Revenue"] = revenue23_df["June 30, 2023 Revenue"].str.strip().str.replace(",", "")
revenue23_df["June 30, 2022 Revenue"] = revenue23_df["June 30, 2022 Revenue"].str.strip().str.replace(",", "")
print(revenue23_df)
### stop here for now Sources of Operating Revenue June 30, 2023 Revenue \
3 Bus 76549
4 Subway 176521
5 Commuter rail 112801
6 Other passenger 10486
7 Other operating 76616
8 Total Operating Revenue 452973
2023 Percent of Total Operating Revenue June 30, 2022 Revenue \
3 17 62305
4 39 172174
5 25 80784
6 2 7029
7 17 64565
8 100 386857
2022 Percent of Total Operating Revenue
3 16
4 45
5 21
6 2
7 16
8 100
print(revenue23_df.dtypes) #print the data types of columns in dataframe
revenue23_df["Sources of Operating Revenue"] = revenue23_df["Sources of Operating Revenue"].astype(str)
revenue23_df["June 30, 2023 Revenue"] = revenue23_df["June 30, 2023 Revenue"].astype(int)
revenue23_df["2023 Percent of Total Operating Revenue"] = revenue23_df["2023 Percent of Total Operating Revenue"].astype(int)
revenue23_df["June 30, 2022 Revenue"] = revenue23_df["June 30, 2022 Revenue"].astype(int)
revenue23_df["2022 Percent of Total Operating Revenue"] = revenue23_df["2022 Percent of Total Operating Revenue"].astype(int)
print("After data type conversion", revenue23_df.dtypes)Sources of Operating Revenue object
June 30, 2023 Revenue object
2023 Percent of Total Operating Revenue object
June 30, 2022 Revenue object
2022 Percent of Total Operating Revenue object
dtype: object
After data type conversion Sources of Operating Revenue object
June 30, 2023 Revenue int64
2023 Percent of Total Operating Revenue int64
June 30, 2022 Revenue int64
2022 Percent of Total Operating Revenue int64
dtype: object
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
revenue23_df_subset = revenue23_df[[ "June 30, 2023 Revenue", "2023 Percent of Total Operating Revenue", "June 30, 2022 Revenue", "2022 Percent of Total Operating Revenue"]]
#create heatmap of dataframe
sns.heatmap(revenue23_df_subset)
plt.show() #show heatmap#find outliers in dataframe using describe()
print(revenue23_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=revenue23_df) June 30, 2023 Revenue 2023 Percent of Total Operating Revenue \
count 6.000000 6.000000
mean 150991.000000 33.333333
std 157555.255284 34.817620
min 10486.000000 2.000000
25% 76565.750000 17.000000
50% 94708.500000 21.000000
75% 160591.000000 35.500000
max 452973.000000 100.000000
June 30, 2022 Revenue 2022 Percent of Total Operating Revenue
count 6.000000 6.000000
mean 128952.333333 33.333333
std 137226.828892 35.539649
min 7029.000000 2.000000
25% 62870.000000 16.000000
50% 72674.500000 18.500000
75% 149326.500000 39.000000
max 386857.000000 100.000000
#export cleaned 2023 operating revenue dataframe in csv file
revenue23_df.to_csv("../../data/processed-data/Budget2023_operating_revenue.csv")#extract expenses table from pdf on page 11 using 'stream" because table data has no border
expenses_table = camelot.read_pdf(pdf_file, flavor='stream', pages='11')
#if it is a table and table is found, then extract table and convert the table into a dataframe
if expenses_table :
expenses23_df = expenses_table[0].df
print(expenses23_df)
else:
print("no tables found") 0 1 2 3 4 5
0 and 2022:
1 Percent Percent
2 2023 of total 2022 of total
3 Wages and benefits $ 822,671 35 % $ 858,544 38 %
4 Commuter rail 634,244 27 % 581,500 26 %
5 Depreciation and amortization 562,247 24 % 526,776 23 %
6 Material and supplies 304,646 13 % 283,548 12 %
7 Other operating 29,422 1 % 25,051 1 %
8 $ 2,353,230 100 % $ 2,275,419 100 %
#drop unneeded columns from the dataframe. Drop columns with default header 1
expenses23_df.drop(1, axis=1, inplace=True)
#print(expenses23_df) #print dataframe to check columns dropped#rename column headers in dataframe
expenses23_df.rename(columns={0:"Sources of Operating Expenses", 2:"June 30, 2023 Expenses", 3:"2023 Percent of Total Operating Expenses", 4:"June 30, 2022 Expenses", 5:"2022 Percent of Total Operating Expenses"}, inplace=True)
#print(expenses23_df)#drop rows indexes 0,1,2 because they had blank values and other rows were correctly renamed
expenses23_df.drop([0,1,2], inplace=True)
#print(expenses23_df)#rename total value column "Sources of Operating Expenses" because the table was missing a label for the total operating expenses at bottom of dataframe
expenses23_df.at[8,"Sources of Operating Expenses"] = "Total Operating Expenses"
#print(expenses23_df)#strip white spaces, $ and % characters from values in the dataframe
expenses23_df["2023 Percent of Total Operating Expenses"] = expenses23_df["2023 Percent of Total Operating Expenses"].str.strip().str.replace("$", "").str.replace("%", "")
expenses23_df["2022 Percent of Total Operating Expenses"] = expenses23_df["2022 Percent of Total Operating Expenses"].str.strip().str.replace("$", "").str.replace("%", "")
expenses23_df["June 30, 2023 Expenses"] = expenses23_df["June 30, 2023 Expenses"].str.strip().str.replace(",", "")
expenses23_df["June 30, 2022 Expenses"] = expenses23_df["June 30, 2022 Expenses"].str.strip().str.replace(",", "")
#print(expenses23_df)print(expenses23_df.dtypes) #print the data types of columns in dataframe
expenses23_df["Sources of Operating Expenses"] = expenses23_df["Sources of Operating Expenses"].astype(str)
expenses23_df["June 30, 2023 Expenses"] = expenses23_df["June 30, 2023 Expenses"].astype(int)
expenses23_df["2023 Percent of Total Operating Expenses"] = expenses23_df["2023 Percent of Total Operating Expenses"].astype(int)
expenses23_df["June 30, 2022 Expenses"] = expenses23_df["June 30, 2022 Expenses"].astype(int)
expenses23_df["2022 Percent of Total Operating Expenses"] = expenses23_df["2022 Percent of Total Operating Expenses"].astype(int)
print("After data type conversion", expenses23_df.dtypes)Sources of Operating Expenses object
June 30, 2023 Expenses object
2023 Percent of Total Operating Expenses object
June 30, 2022 Expenses object
2022 Percent of Total Operating Expenses object
dtype: object
After data type conversion Sources of Operating Expenses object
June 30, 2023 Expenses int64
2023 Percent of Total Operating Expenses int64
June 30, 2022 Expenses int64
2022 Percent of Total Operating Expenses int64
dtype: object
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
expenses23_df_subset = expenses23_df[[ "June 30, 2023 Expenses", "2023 Percent of Total Operating Expenses", "June 30, 2022 Expenses", "2022 Percent of Total Operating Expenses"]]
#create heatmap of dataframe
sns.heatmap(expenses23_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(expenses23_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=expenses23_df) June 30, 2023 Expenses 2023 Percent of Total Operating Expenses \
count 6.000000e+00 6.000000
mean 7.844100e+05 33.333333
std 8.166697e+05 34.737108
min 2.942200e+04 1.000000
25% 3.690462e+05 15.750000
50% 5.982455e+05 25.500000
75% 7.755642e+05 33.000000
max 2.353230e+06 100.000000
June 30, 2022 Expenses 2022 Percent of Total Operating Expenses
count 6.000000e+00 6.000000
mean 7.584730e+05 33.333333
std 7.949345e+05 35.006666
min 2.505100e+04 1.000000
25% 3.443550e+05 14.750000
50% 5.541380e+05 24.500000
75% 7.892830e+05 35.000000
max 2.275419e+06 100.000000
#export cleaned 2023 operating expenses dataframe in csv file
expenses23_df.to_csv("../../data/processed-data/Budget2023_operating_expenses.csv")
#extract debt table from pdf on page 12 using 'stream" because table data has no border
debt_table = camelot.read_pdf(pdf_file, flavor='stream', pages='12')
#if it is a table and table is found, then extract table and convert the table into a dataframe
if debt_table:
debts23_df = debt_table[0].df
print(debts23_df)
else:
print("no tables found") 0 1 2 3 \
0 Bonds and notes outstanding for the fiscal yea...
1 2023
2 General Transportation System bonds $ 99,470 $
3 Revenue bonds, net 4,248,525
4 RRIFs 647,545
5 Commercial paper 125,000
6 BABs 394,300
7 $ 5,514,840 $
4
0
1 2022
2 110,210
3 4,441,827
4 370,392
5 40,000
6 402,550
7 5,364,979
#drop unneeded columns from the dataframe. Drop columns with default header 1
debts23_df.drop([1,3], axis=1, inplace=True)
print(debts23_df) 0 2 4
0 Bonds and notes outstanding for the fiscal yea...
1 2023 2022
2 General Transportation System bonds 99,470 110,210
3 Revenue bonds, net 4,248,525 4,441,827
4 RRIFs 647,545 370,392
5 Commercial paper 125,000 40,000
6 BABs 394,300 402,550
7 5,514,840 5,364,979
#rename column headers in dataframe
debts23_df.rename(columns={0:"Sources of Debt", 2:"June 30, 2023 Debt", 4:"June 30, 2022 Debt"}, inplace=True)#drop rows indexes 0 because they had blank values and other rows were correctly renamed
debts23_df.drop(0, inplace=True)#rename total value column "Sources of Debt" because the table was missing a label for the total debt at bottom of dataframe
debts23_df.at[7, "Sources of Debt"] = "Total Debt"#strip white spaces, $ and % characters from values in the dataframe
debts23_df["June 30, 2023 Debt"] = debts23_df["June 30, 2023 Debt"].str.strip().str.replace(",", "")
debts23_df["June 30, 2022 Debt"] = debts23_df["June 30, 2022 Debt"].str.strip().str.replace(",", "")
#print(expenses23_df)print(debts23_df.dtypes) #print the data types of columns in dataframe
debts23_df["Sources of Debt"] = debts23_df["Sources of Debt"].astype(str)
debts23_df["June 30, 2023 Debt"] = debts23_df["June 30, 2023 Debt"].astype(int)
debts23_df["June 30, 2022 Debt"] = debts23_df["June 30, 2022 Debt"].astype(int)
print("After data type conversion", debts23_df.dtypes)Sources of Debt object
June 30, 2023 Debt object
June 30, 2022 Debt object
dtype: object
After data type conversion Sources of Debt object
June 30, 2023 Debt int64
June 30, 2022 Debt int64
dtype: object
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
debts23_df_subset = debts23_df[["June 30, 2023 Debt", "June 30, 2022 Debt"]]
#create heatmap of dataframe
sns.heatmap(debts23_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(debts23_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=debts23_df) June 30, 2023 Debt June 30, 2022 Debt
count 7.000000e+00 7.000000e+00
mean 1.575958e+06 1.533140e+06
std 2.297761e+06 2.322792e+06
min 2.023000e+03 2.022000e+03
25% 1.122350e+05 7.510500e+04
50% 3.943000e+05 3.703920e+05
75% 2.448035e+06 2.422188e+06
max 5.514840e+06 5.364979e+06
#export cleaned 2023 debts dataframe in csv file
debts23_df.to_csv("../../data/processed-data/Budget2023_debts.csv")"""
#set pdf file path for the 2021 budget pdf file
#pdf_file21 = "../../data/raw-data/fy21-audited-financial-statements.pdf"
#extract table from pdf on page 9 using 'stream" because table data has no border
table_file21_p1 = camelot.read_pdf(pdf_file21, flavor='stream', pages='7')
#if it is a table and table is found, then convert the table into a dataframe
if table_file21_p1:
net21_p1_df = table_file21_p1[1].df
print(net21_p1_df)
else:
print("no tables found")
#extract table from pdf on page 9 using 'stream" because table data has no border
table_file21 = camelot.read_pdf(pdf_file21, flavor='stream', pages='8')
#if it is a table and table is found, then convert the table into a dataframe
if table_file21:
net21_df = table_file21[0].df
print(net21_df)
else:
print("no tables found")
#stack the two dataframes using concat becuase the table was split into two pages on the pdf file
combined_net21_df = pd.concat([net21_p1_df, net21_df])
combined_net21_df_index = combined_net21_df.reset_index()
print(combined_net21_df_index)
"""'\n#set pdf file path for the 2021 budget pdf file\n#pdf_file21 = "../../data/raw-data/fy21-audited-financial-statements.pdf"\n\n#extract table from pdf on page 9 using \'stream" because table data has no border\ntable_file21_p1 = camelot.read_pdf(pdf_file21, flavor=\'stream\', pages=\'7\')\n\n#if it is a table and table is found, then convert the table into a dataframe\nif table_file21_p1:\n net21_p1_df = table_file21_p1[1].df\n print(net21_p1_df)\nelse:\n print("no tables found")\n\n#extract table from pdf on page 9 using \'stream" because table data has no border\ntable_file21 = camelot.read_pdf(pdf_file21, flavor=\'stream\', pages=\'8\')\n\n#if it is a table and table is found, then convert the table into a dataframe\nif table_file21:\n net21_df = table_file21[0].df\n print(net21_df)\nelse:\n print("no tables found")\n\n#stack the two dataframes using concat becuase the table was split into two pages on the pdf file\ncombined_net21_df = pd.concat([net21_p1_df, net21_df])\n\ncombined_net21_df_index = combined_net21_df.reset_index()\n\nprint(combined_net21_df_index)\n'
"""
#drop unneeded columns from the df dataframe. Drop columns with default header 1 and 4
combined_net21_df_index.drop([1,4, "index"], axis=1, inplace=True)
#print(combined_net21_df) #print dataframe to check columns dropped
""""""
#rename headers in dataframe
combined_net21_df_index.rename(columns={0:"Statements of Revenue, Expenses and Changes in Net Position", 2:"June 30, 2021", 3:"June 30, 2020"}, inplace=True)
print(combined_net21_df_index)
""""""
#rename some values in the "Statements of Revenue, Expenses and Changes in Net Position" column because the table was formatted with too much space for those cells
combined_net21_df_index.at[4,"Statements of Revenue, Expenses and Changes in Net Position"] = "Other revenue"
combined_net21_df_index.at[13,"Statements of Revenue, Expenses and Changes in Net Position"] = "Total operating expenses, including depreciation and amortization"
print(combined_net21_df_index)
""""""
#drop rows index 6 and 11 because they had blank values and other rows were correctly renamed
combined_net21_df_index.drop([0,1,2,6,10,12], inplace=True)
print(combined_net21_df_index)
""""""
#check the data types of columns in the dataframe
print(combined_net21_df_index.dtypes)
combined_net21_df_index["Statements of Revenue, Expenses and Changes in Net Position"] = combined_net21_df_index["Statements of Revenue, Expenses and Changes in Net Position"].astype(str) #transform data type from objects into strings in this column
combined_net21_df_index["June 30, 2021"] = combined_net21_df_index["June 30, 2021"].str.replace(",", "") #remove comma characters in column "June 30, 2021"
combined_net21_df_index["June 30, 2020"] = combined_net21_df_index["June 30, 2020"].str.replace(",", "") #remove comma characters in column "June 30, 2020"
combined_net21_df_index["June 30, 2021"] = combined_net21_df_index["June 30, 2021"].str.replace("(", "") #remove '(' characters in column "June 30, 2021"
combined_net21_df_index["June 30, 2020"] = combined_net21_df_index["June 30, 2020"].str.replace("(", "") #remove '(' characters in column "June 30, 2020"
combined_net21_df_index["June 30, 2021"] = combined_net21_df_index["June 30, 2021"].str.replace(")", "") #remove ')' characters in column "June 30, 2021"
combined_net21_df_index["June 30, 2020"] = combined_net21_df_index["June 30, 2020"].str.replace(")", "") #remove ')' characters in column "June 30, 2020"
combined_net21_df_index["June 30, 2021"] = combined_net21_df_index["June 30, 2021"].astype(int) #convert "June 30, 2021" column data type to numeric
combined_net21_df_index["June 30, 2020"] = combined_net21_df_index["June 30, 2020"].astype(int) #convert "June 30, 2020" column data type to numeric
print("After conversion", combined_net21_df_index.dtypes)
""""""
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
combined_net21_df_index_subset = combined_net21_df_index[[ "June 30, 2021", "June 30, 2020"]]
#create heatmap of dataframe
sns.heatmap(combined_net21_df_index_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(combined_net21_df_index.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=combined_net21_df_index)
""""""
#export cleaned 2021-2020 debts dataframe in csv file
combined_net21_df_index.to_csv("../../data/processed-data/Budget2021_2020_net_positions.csv")
""""""
#extract table from pdf on page 9 using 'stream" because table data has no border: revenue table
table21revenue = camelot.read_pdf(pdf_file21, flavor='stream', pages='9')
#if it is a table and table is found, then convert the table into a dataframe
if table21revenue:
revenue21_df = table21revenue[0].df
print("Revenue table 2021 \n", revenue21_df)
else:
print("no tables found")
""""""
#extract table from pdf on page 9 using 'stream" because table data has no border: expenses table
table21expenses = camelot.read_pdf(pdf_file21, flavor='stream', pages='9')
#if it is a table and table is found, then convert the table into a dataframe
if table21expenses:
expenses21_df = table21expenses[1].df
print("Expenses table 2021 \n", expenses21_df)
else:
print("no tables found")
""""""
#drop unneeded columns from the df dataframe. Drop columns with default header 1
revenue21_df = revenue21_df.drop(1, axis=1)
#print(revenue21_df) #print dataframe to check columns dropped
#rename column headers in dataframe
revenue21_df = revenue21_df.rename(columns={0:"Sources of Operating Revenue", 2:"June 30, 2021 Revenue", 3:"2021 Percent of Total Operating Revenue", 4:"June 30, 2020 Revenue", 5:"2020 Percent of Total Operating Revenue"})
#print(revenue21_df)
#drop rows indexs because they had blank values and other rows were correctly renamed
revenue21_df.drop([0,1,2], inplace=True)
print(revenue21_df)
""""""
#rename some values in the "Sources of Operating Revenue" column because the table was formatted with too much space for those cells
revenue21_df.at[8,"Sources of Operating Revenue"] = "Total Operating Revenue"
print(revenue21_df)
""""""
#strip white spaces, $ and % characters from values in the dataframe
revenue21_df["2021 Percent of Total Operating Revenue"] = revenue21_df["2021 Percent of Total Operating Revenue"].str.strip().str.replace("$", "").str.replace("%", "")
revenue21_df["2020 Percent of Total Operating Revenue"] = revenue21_df["2020 Percent of Total Operating Revenue"].str.strip().str.replace("$", "").str.replace("%", "")
revenue21_df["June 30, 2021 Revenue"] = revenue21_df["June 30, 2021 Revenue"].str.strip().str.replace(",", "")
revenue21_df["June 30, 2020 Revenue"] = revenue21_df["June 30, 2020 Revenue"].str.strip().str.replace(",", "")
print(revenue21_df)
print(revenue21_df.dtypes) #print the data types of columns in dataframe
revenue21_df["Sources of Operating Revenue"] = revenue21_df["Sources of Operating Revenue"].astype(str)
revenue21_df["June 30, 2021 Revenue"] = revenue21_df["June 30, 2021 Revenue"].astype(int)
revenue21_df["2021 Percent of Total Operating Revenue"] = revenue21_df["2021 Percent of Total Operating Revenue"].astype(int)
revenue21_df["June 30, 2020 Revenue"] = revenue21_df["June 30, 2020 Revenue"].astype(int)
revenue21_df["2020 Percent of Total Operating Revenue"] = revenue21_df["2020 Percent of Total Operating Revenue"].astype(int)
print("After data type conversion", revenue21_df.dtypes)
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
revenue21_df_subset = revenue21_df[["June 30, 2021 Revenue", "2021 Percent of Total Operating Revenue", "June 30, 2020 Revenue", "2020 Percent of Total Operating Revenue"]]
#create heatmap of dataframe
sns.heatmap(revenue21_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(revenue21_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=revenue21_df)
""""""
#export cleaned 2021-2020 revenue dataframe in csv file
revenue21_df.to_csv("../../data/processed-data/Budget2021_2020_revenue.csv")
""""""
#2021-2020 expenses
#drop unneeded columns from the df dataframe. Drop columns with default header 1
expenses21_df = expenses21_df.drop(1, axis=1)
#rename column headers in dataframe
expenses21_df = expenses21_df.rename(columns={0:"Sources of Operating Expenses", 2:"June 30, 2021 Expenses", 3:"2021 Percent of Total Operating Expenses", 4:"June 30, 2020 Expenses", 5:"2020 Percent of Total Operating Expenses"})
print(expenses21_df)
""""""
#drop rows indexs because they had blank values and other rows were correctly renamed
expenses21_df.drop([0,1,2], inplace=True)
#print(expenses21_df)
""""""
#rename some values in the "Sources of Operating Expenses" column because the table was formatted with too much space for those cells
expenses21_df.at[8,"Sources of Operating Expenses"] = "Total Operating Expenses"
#print(expenses21_df)
""""""
#strip white spaces, $ and % characters from values in the dataframe
expenses21_df["2021 Percent of Total Operating Expenses"] = expenses21_df["2021 Percent of Total Operating Expenses"].str.strip().str.replace("$", "").str.replace("%", "")
expenses21_df["2020 Percent of Total Operating Expenses"] = expenses21_df["2020 Percent of Total Operating Expenses"].str.strip().str.replace("$", "").str.replace("%", "")
expenses21_df["June 30, 2021 Expenses"] = expenses21_df["June 30, 2021 Expenses"].str.strip().str.replace(",", "")
expenses21_df["June 30, 2020 Expenses"] = expenses21_df["June 30, 2020 Expenses"].str.strip().str.replace(",", "")
print(expenses21_df.dtypes) #print the data types of columns in dataframe
expenses21_df["Sources of Operating Expenses"] = expenses21_df["Sources of Operating Expenses"].astype(str)
expenses21_df["June 30, 2021 Expenses"] = expenses21_df["June 30, 2021 Expenses"].astype(int)
expenses21_df["2021 Percent of Total Operating Expenses"] = expenses21_df["2021 Percent of Total Operating Expenses"].astype(int)
expenses21_df["June 30, 2020 Expenses"] = expenses21_df["June 30, 2020 Expenses"].astype(int)
expenses21_df["2020 Percent of Total Operating Expenses"] = expenses21_df["2020 Percent of Total Operating Expenses"].astype(int)
print("After data type conversion", expenses21_df.dtypes)
print(expenses21_df)
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
expenses21_df_subset = expenses21_df[["June 30, 2021 Expenses", "2021 Percent of Total Operating Expenses", "June 30, 2020 Expenses", "2020 Percent of Total Operating Expenses"]]
#create heatmap of dataframe
sns.heatmap(expenses21_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(expenses21_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=expenses21_df)
""""""
#export cleaned 2021-2020 expenses dataframe in csv file
expenses21_df.to_csv("../../data/processed-data/Budget2021_2020_expenses.csv")
""""""
#extract debt table for 2021 from the 2022 financial statement audit because library was not able to correctly extract debt table from the 2021 financial statement pdf file.
#set pdf file path for the 2022 budget pdf file
pdf_file22 = "../../data/raw-data/fy22-audited-financial-statement.pdf"
#extract table from pdf on page 11 using 'stream" because table data has no border: debt table
table21debts = camelot.read_pdf(pdf_file22, flavor='stream', pages='11')
#if it is a table and table is found, then convert the table into a dataframe
if table21debts:
debts21_df = table21debts[0].df
print(debts21_df)
else:
print("no tables found")
""""""
#drop unneeded columns from the df dataframe. Drop columns with default header 1
#drop 2022 since that was already extracted in the previous part of the code
debts21_df = debts21_df.drop([1,2], axis=1)
#print(debts21_df)
""""""
#rename column headers in dataframe
debts21_df = debts21_df.rename(columns={0:"Sources of Debt", 3:"June 30, 2021 Debts"})
#print(debts21_df)
""""""
#drop rows indexs because they had blank values and other rows were correctly renamed
debts21_df.drop([0,1,2], inplace=True)
#print(debts21_df)
""""""
#rename some values in the "Sources of Debt" column because the table was formatted with too much space for those cells
debts21_df.at[8,"Sources of Debt"] = "Total Debts"
print(debts21_df)
""""""
# strip white spaces and comma characters from values in the dataframe
#debts21_df["June 30, 2021 Debts"] = debts21_df["June 30, 2021 Debts"].str.strip().replace('—',np.nan)
#decided to replace missing value with "0" to convert column to integers and keep row count the same for eaiser joining later on with other dataframes
debts21_df["June 30, 2021 Debts"] = debts21_df["June 30, 2021 Debts"].str.strip().replace('—',np.nan)
#debts21_df["June 30, 2021 Debts"] = debts21_df["June 30, 2021 Debts"].fillna(0)
print(debts21_df)
""""""
#strip white spaces and comma characters from values in the dataframe
debts21_df["June 30, 2021 Debts"] = debts21_df["June 30, 2021 Debts"].str.strip().str.replace(",", "")
print(debts21_df)
print(debts21_df.dtypes) #print the data types of columns in dataframe
debts21_df["Sources of Debt"] = debts21_df["Sources of Debt"].astype(str)
debts21_df["June 30, 2021 Debts"] = debts21_df["June 30, 2021 Debts"].astype(float)
debts21_df["June 30, 2021 Debts"] = debts21_df["June 30, 2021 Debts"].fillna(0).astype(int)
print("After data type conversion", debts21_df.dtypes)
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
debts21_df_subset = debts21_df[[ "June 30, 2021 Debts"]]
#create heatmap of dataframe
sns.heatmap(debts21_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(debts21_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=debts21_df)
""""""
debts21_df.to_csv("../../data/processed-data/Budget2021_debts.csv")
""""""
#extract net position table for 2020 and 2019 from the 2020 financial statement audit
#set pdf file path for the 2022 budget pdf file
pdf_file20 = "../../data/raw-data/fy20-financial-audit.pdf"
#extract table from pdf on page 11 using 'stream" because table data has no border: debt table
table20_net = camelot.read_pdf(pdf_file20, flavor='stream', pages='8')
#if it is a table and table is found, then convert the table into a dataframe
if table20_net:
net20_df = table20_net [0].df
print(net20_df)
else:
print("no tables found")
"""--------------------------------------------------------------------------- DependencyError Traceback (most recent call last) Cell In[183], line 7 4 pdf_file20 = "../../data/raw-data/fy20-financial-audit.pdf" 6 #extract table from pdf on page 11 using 'stream" because table data has no border: debt table ----> 7 table20_net = camelot.read_pdf(pdf_file20, flavor='stream', pages='8') 9 #if it is a table and table is found, then convert the table into a dataframe 10 if table20_net: File /opt/anaconda3/lib/python3.11/site-packages/camelot/io.py:113, in read_pdf(filepath, pages, password, flavor, suppress_stdout, layout_kwargs, **kwargs) 111 p = PDFHandler(filepath, pages=pages, password=password) 112 kwargs = remove_extra(kwargs, flavor=flavor) --> 113 tables = p.parse( 114 flavor=flavor, 115 suppress_stdout=suppress_stdout, 116 layout_kwargs=layout_kwargs, 117 **kwargs 118 ) 119 return tables File /opt/anaconda3/lib/python3.11/site-packages/camelot/handlers.py:169, in PDFHandler.parse(self, flavor, suppress_stdout, layout_kwargs, **kwargs) 167 with TemporaryDirectory() as tempdir: 168 for p in self.pages: --> 169 self._save_page(self.filepath, p, tempdir) 170 pages = [os.path.join(tempdir, f"page-{p}.pdf") for p in self.pages] 171 parser = Lattice(**kwargs) if flavor == "lattice" else Stream(**kwargs) File /opt/anaconda3/lib/python3.11/site-packages/camelot/handlers.py:113, in PDFHandler._save_page(self, filepath, page, temp) 111 fpath = os.path.join(temp, f"page-{page}.pdf") 112 froot, fext = os.path.splitext(fpath) --> 113 p = infile.getPage(page - 1) 114 outfile = PdfFileWriter() 115 outfile.addPage(p) File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_reader.py:477, in PdfReader.getPage(self, pageNumber) 469 """ 470 .. deprecated:: 1.28.0 471 472 Use :code:`reader.pages[page_number]` instead. 473 """ 474 deprecate_with_replacement( 475 "reader.getPage(pageNumber)", "reader.pages[page_number]" 476 ) --> 477 return self._get_page(pageNumber) File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_reader.py:490, in PdfReader._get_page(self, page_number) 487 # ensure that we're not trying to access an encrypted PDF 488 # assert not self.trailer.has_key(TK.ENCRYPT) 489 if self.flattened_pages is None: --> 490 self._flatten() 491 assert self.flattened_pages is not None, "hint for mypy" 492 return self.flattened_pages[page_number] File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_reader.py:1097, in PdfReader._flatten(self, pages, inherit, indirect_reference) 1093 inherit = {} 1094 if pages is None: 1095 # Fix issue 327: set flattened_pages attribute only for 1096 # decrypted file -> 1097 catalog = self.trailer[TK.ROOT].get_object() 1098 pages = catalog["/Pages"].get_object() # type: ignore 1099 self.flattened_pages = [] File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/generic/_data_structures.py:150, in DictionaryObject.__getitem__(self, key) 149 def __getitem__(self, key: Any) -> PdfObject: --> 150 return dict.__getitem__(self, key).get_object() File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/generic/_base.py:164, in IndirectObject.get_object(self) 163 def get_object(self) -> Optional[PdfObject]: --> 164 obj = self.pdf.get_object(self) 165 if obj is None: 166 return None File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_reader.py:1265, in PdfReader.get_object(self, indirect_reference) 1263 # otherwise, decrypt here... 1264 retval = cast(PdfObject, retval) -> 1265 retval = self._encryption.decrypt_object( 1266 retval, indirect_reference.idnum, indirect_reference.generation 1267 ) 1268 else: 1269 if hasattr(self.stream, "getbuffer"): File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_encryption.py:761, in Encryption.decrypt_object(self, obj, idnum, generation) 758 efCrypt = self._get_crypt(self.EFF, rc4_key, aes128_key, aes256_key) 760 cf = CryptFilter(stmCrypt, StrCrypt, efCrypt) --> 761 return cf.decrypt_object(obj) File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_encryption.py:188, in CryptFilter.decrypt_object(self, obj) 186 elif isinstance(obj, DictionaryObject): 187 for dictkey, value in list(obj.items()): --> 188 obj[dictkey] = self.decrypt_object(value) 189 elif isinstance(obj, ArrayObject): 190 for i in range(len(obj)): File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_encryption.py:182, in CryptFilter.decrypt_object(self, obj) 180 def decrypt_object(self, obj: PdfObject) -> PdfObject: 181 if isinstance(obj, (ByteStringObject, TextStringObject)): --> 182 data = self.strCrypt.decrypt(obj.original_bytes) 183 obj = create_string_object(data) 184 elif isinstance(obj, StreamObject): File /opt/anaconda3/lib/python3.11/site-packages/PyPDF2/_encryption.py:147, in CryptAES.decrypt(self, data) 146 def decrypt(self, data: bytes) -> bytes: --> 147 raise DependencyError("PyCryptodome is required for AES algorithm") DependencyError: PyCryptodome is required for AES algorithm
"""
#drop unneeded columns from the df dataframe. Drop columns with default header 1
net20_df = net20_df.drop([1,2], axis=1)
#rename column headers in dataframe
net20_df = net20_df.rename(columns={0:"Statements of Revenue, Expenses and Changes in Net Position", 3:"June 30, 2019 Net Positions"})
#drop rows indexs because they had blank values and other rows were correctly renamed
net20_df = net20_df.drop([0,1,2,6,11])
print(net20_df)
""""""
#rename some values in the "Sources of Operating Revenue" column because the table was formatted with too much space for those cells
net20_df.at[4,"Statements of Revenue, Expenses and Changes in Net Position"] = "Other Revenue"
net20_df.at[12,"Statements of Revenue, Expenses and Changes in Net Position"] = "Total operating expenses, including depreciation and amortization"
print(net20_df)
""""""
#strip white spaces and comma characters from values in the dataframe
net20_df["June 30, 2019 Net Positions"] = net20_df["June 30, 2019 Net Positions"].str.strip().str.replace(",", "").str.replace("(", "").str.replace(")", "")
print(net20_df)
print(net20_df.dtypes) #print the data types of columns in dataframe
net20_df["Statements of Revenue, Expenses and Changes in Net Position"] = net20_df["Statements of Revenue, Expenses and Changes in Net Position"].astype(str)
net20_df["June 30, 2019 Net Positions"] = net20_df["June 30, 2019 Net Positions"].astype(int)
print("After data type conversion", net20_df.dtypes)
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
net20_df_subset = net20_df[["June 30, 2019 Net Positions"]]
#create heatmap of dataframe
sns.heatmap(net20_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(net20_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=net20_df)
""""""
#export cleaned 2019 net positions dataframe to csv file
net20_df.to_csv("../../data/processed-data/Budget2019_net_positions.csv")
""""""
#not able to get revenue table for 2019 because the camelot library was only able to extract the first half of the page which was the text instead of the table.
#set pdf file path for the 2019 budget pdf file
pdf_file19 = "../../data/raw-data/2019-06-30-fy19-financial-audit.pdf"
#extract table from pdf on page 11 using 'stream" because table data has no border: debt table
table19_revenue = camelot.read_pdf(pdf_file19, flavor='stream', pages='9')
#if it is a table and table is found, then convert the table into a dataframe
#if table19_revenue:
#revenue19_df = table19_revenue[1].df
#print(revenue19_df)
#else:
#print("no tables found")
""""""
#extract expenses for 2019
#extract table from pdf on page 10 using 'stream" because table data has no border
table19_expenses = camelot.read_pdf(pdf_file20, flavor='stream', pages='10')
#if it is a table and table is found, then convert the table into a dataframe
if table19_expenses:
expenses19_df = table19_expenses[0].df
print(expenses19_df)
else:
print("no tables found")
""""""
#drop unneeded columns from the df dataframe
expenses19_df = expenses19_df.drop([1,2,3], axis=1)
#rename column headers in dataframe
expenses19_df = expenses19_df.rename(columns={0:"Sources of Operating Expenses", 4:"June 30, 2019 Expenses", 5:"2019 Percent of Total Operating Expenses"})
#drop rows indexs because they had blank values and other rows were correctly renamed
expenses19_df = expenses19_df.drop([0,1,2])
print(expenses19_df)
""""""
Q#rename some values in the "Sources of Operating Revenue" column because the table was formatted with too much space for those cells
expenses19_df.at[8,"Sources of Operating Expenses"] = "Total Operating Expenses"
""""""
#strip white spaces, commas, and % characters from values in the dataframe
expenses19_df["2019 Percent of Total Operating Expenses"] = expenses19_df["2019 Percent of Total Operating Expenses"].str.strip().str.replace("%", "")
expenses19_df["June 30, 2019 Expenses"] = expenses19_df["June 30, 2019 Expenses"].str.strip().str.replace(",", "")
print(expenses19_df)
print(expenses19_df.dtypes) #print the data types of columns in dataframe
expenses19_df["Sources of Operating Expenses"] = expenses19_df["Sources of Operating Expenses"].astype(str)
expenses19_df["2019 Percent of Total Operating Expenses"] = expenses19_df["2019 Percent of Total Operating Expenses"].astype(int)
expenses19_df["June 30, 2019 Expenses"] = expenses19_df["June 30, 2019 Expenses"].astype(int)
print("After data type conversion", expenses19_df.dtypes)
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
expenses19_df_subset = expenses19_df[["2019 Percent of Total Operating Expenses", "June 30, 2019 Expenses"]]
#create heatmap of dataframe
sns.heatmap(expenses19_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(expenses19_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=expenses19_df)
""""""
#export cleaned 2019 expenses dataframe into csv file
expenses19_df.to_csv("../../data/processed-data/Budget2019_expenses.csv")
""""""
#keep both 2020 and 2019 for debts from the 2020 financial file
#extract table from pdf on page 11 using 'stream" because table data has no border: debt table
table20_19_debts = camelot.read_pdf(pdf_file20, flavor='stream', pages='11')
#if it is a table and table is found, then convert the table into a dataframe
if table20_19_debts:
debts20_19_df = table20_19_debts[0].df
print(debts20_19_df)
else:
print("no tables found")
""""""
#drop unneeded columns from the df dataframe
debts20_19_df = debts20_19_df.drop(1, axis=1)
#rename column headers in dataframe
debts20_19_df = debts20_19_df.rename(columns={0:"Sources of Debt", 2:"June 30, 2020 Debt", 3:"June 30, 2019 Debt"})
#drop rows indexs because they had blank values and other rows were correctly renamed
debts20_19_df = debts20_19_df.drop([0,1,2])
print(debts20_19_df)
"""--------------------------------------------------------------------------- NameError Traceback (most recent call last) Cell In[184], line 2 1 #drop unneeded columns from the df dataframe ----> 2 debts20_19_df = debts20_19_df.drop(1, axis=1) 4 #rename column headers in dataframe 5 debts20_19_df = debts20_19_df.rename(columns={0:"Sources of Debt", 2:"June 30, 2020 Debt", 3:"June 30, 2019 Debt"}) NameError: name 'debts20_19_df' is not defined
"""
#rename some values in the "Sources of Operating Revenue" column because the table was formatted with too much space for those cells
debts20_19_df.at[7,"Sources of Debt"] = "Total Debts"
""""""
#strip white spaces and comma characters from values in the dataframe
debts20_19_df["June 30, 2020 Debt"] = debts20_19_df["June 30, 2020 Debt"].str.strip().str.replace(",", "")
debts20_19_df["June 30, 2019 Debt"] = debts20_19_df["June 30, 2019 Debt"].str.strip().str.replace(",", "")
print(debts20_19_df)
print(debts20_19_df.dtypes) #print the data types of columns in dataframe
debts20_19_df["Sources of Debt"] = debts20_19_df["Sources of Debt"].astype(str)
debts20_19_df["June 30, 2020 Debt"] = debts20_19_df["June 30, 2020 Debt"].astype(int)
debts20_19_df["June 30, 2019 Debt"] = debts20_19_df["June 30, 2019 Debt"].astype(int)
print("After data type conversion", debts20_19_df.dtypes)
#subset dataframe to create a heatmap to visualize missing data even though there were no missing data. Keep numeric value columns
debts20_19_df_subset = debts20_19_df[["June 30, 2020 Debt", "June 30, 2019 Debt"]]
#create heatmap of dataframe
sns.heatmap(debts20_19_df_subset)
plt.show() #show heatmap
#find outliers in dataframe using describe()
print(debts20_19_df.describe())
#create boxplot of dataframe without na to visualize outliers
sns.boxplot(data=debts20_19_df)
""""""
#export cleaned 2019 and 2020 debts dataframe into csv file
debts20_19_df.to_csv("../../data/processed-data/Budget2019_2020_debts.csv")
""""""
#join dataframes for net positions from 2023 to 2019
#join on common column "Statements of Revenue, Expenses and Changes in Net Position
net_join = pd.merge(df, combined_net21_df_index, on="Statements of Revenue, Expenses and Changes in Net Position")
#net_join = df.join(combined_net21_df_index, how='left')
#print(net_join) #check first join
net_join2 = pd.merge(net_join, net20_df, on="Statements of Revenue, Expenses and Changes in Net Position")
print(net_join2)
""""""
#rename column headers to prepare for melt function
net_join2_rename = net_join2.rename(columns={"June 30, 2023":"2023-06-30", "June 30, 2022":"2022-06-30", "June 30, 2021":"2021-06-30", "June 30, 2020":"2020-06-30", "June 30, 2019 Net Positions":"2019-06-30"})
""""""
#melt the joined net position dataframe to join with the ride reliability dataset on date column
melt_net_positions = net_join2_rename.melt(id_vars=["Statements of Revenue, Expenses and Changes in Net Position"], var_name='Date', value_name="Net Position Amounts (USD)")
#print(melt_net_positions)
""""""
#load the clean ride reliability dataset to join with the net position dataset
reliability = pd.read_csv("../../data/processed-data/Clean_Ride_Reliability.csv")
#convert csv file into a dataframe
reliability_df = pd.DataFrame(reliability)
#print(reliability_df.head())
#drop unneeded columns from the df dataframe
reliability_df_drop = reliability_df.drop(["Unnamed: 0", "trip_date", "ObjectId"], axis=1)
print(reliability_df_drop.head())
""""""
#join the joined net position dataframe with the ride reliability dataframe on the "Date" column
#net_position_reliabiity_df = pd.merge(melt_net_positions, reliability_df_drop, left_on=None)
net_position_reliabiity_df = melt_net_positions.join(reliability_df_drop, how='inner') #dates and date
#net_position_reliabiity_df = pd.concat([melt_net_positions, reliability_df_drop]) #joined but just stacked
print(net_position_reliabiity_df)
""""""
#export joined dataframe to csv as test file
net_position_reliabiity_df.to_csv("../../data/processed-data/Net_positions_reliability.csv")
""""""
#test attempt convert date to datetime in both datasets
#this attempt merged the two dataframes together correctly!
melt_net_positions["Date"] = pd.to_datetime(melt_net_positions["Date"])
reliability_df_drop["Date"] = pd.to_datetime(reliability_df_drop["Date"])
merged_net = pd.merge(melt_net_positions, reliability_df_drop, on="Date", how="inner")
print(merged_net)
""""""
#export joined dataframe to csv as test file
merged_net.to_csv("../../data/processed-data/attempt2_Net_positions_reliability.csv")
""""""
from datetime import datetime
#convert date observation into binary to better statistical tests and visualizations
#alternative analysis: add a column to the dataframe for binary classification target
merged_net_binary = merged_net
print(merged_net_binary["Date"].dtype) #check if column is datatime type
#we want to observe all dates after 2022-06-30-01 as "1"'s and all dates before 2022-06-30 as "0"'s
#split the data pre and post 2022-01-01 because that was the year the MBTA infrastructure renovation program began. Since this dataset only has the 6/30/2022 date, we will use that data as the benchmark for binary analysis
date_to_string = "2022-06-30" #set variable to 2022-06-30 to find its numeric format
date_convert = datetime.strptime(date_to_string, "%Y-%m-%d") #convert string date to datetime format
print(type(date_convert)) #check if it is datetime format
num_date = int(date_convert.timestamp()) #convert datetime to an integer
#num_date_result = num_date//10**9
#print(num_date_result)
print(num_date) #print the numeric conversion of the 2022-01-01 date which equals 1641013200, easier for if else statement to transform dates into binary values
merged_net_binary["Pre and Post 2021"] = merged_net_binary["Date"].astype('int64')//10**9 #convert dates in the "weekly" column into numeric data type. Divide by 10**9 to convert the nanoseconds
print(merged_net_binary["Pre and Post 2021"].head()) #print first few rows of numeric format of dates in the "Pre and Post 2021" column
merged_net_binary["Pre and Post 2021 Binary"] = np.where(merged_net_binary["Pre and Post 2021"] > num_date, 1, 0) #if value in "Pre and Post 2021" column is greater than numeric value of 2022-01-01 = 1641013200, then value will be 1 if less than the date then value will be 0
print(merged_net_binary.head()) #print first few rows of binary dates
""""""
#export joined dataframe to csv file
merged_net_binary.to_csv("../../data/processed-data/Net_positions_reliability_binary.csv")
""""""
#join dataframes for revenue
merged_revenue = pd.merge(revenue23_df,revenue21_df, on="Sources of Operating Revenue")
print(merged_revenue)
""""""
#rename columns
merged_revenue_rename = merged_revenue.rename(columns={"June 30, 2023 Revenue":"2023-06-30", "June 30, 2022 Revenue":"2022-06-30", "June 30, 2021 Revenue":"2021-06-30", "June 30, 2020 Revenue":"2020-06-30"})
#drop the percent of total operating revenue columns to make melt function easier
merged_revenue_drop = merged_revenue_rename.drop(["2023 Percent of Total Operating Revenue", "2022 Percent of Total Operating Revenue", "2021 Percent of Total Operating Revenue", "2020 Percent of Total Operating Revenue"], axis=1)
#melt the joined revenue dataframe to join with the ride reliability dataset on date column
melt_revenue = merged_revenue_drop.melt(id_vars=["Sources of Operating Revenue"], var_name='Date', value_name="Revenue (USD)")
print(melt_revenue)
""""""
melt_revenue["Date"] = pd.to_datetime(melt_revenue["Date"])
#reliability_df_drop["Date"] = pd.to_datetime(reliability_df_drop["Date"])
merged_rev_rel = pd.merge(melt_revenue, reliability_df_drop, on="Date", how="inner")
print(merged_rev_rel)
""""""
from datetime import datetime
#convert date observation into binary to better statistical tests and visualizations
#alternative analysis: add a column to the dataframe for binary classification target
merged_revenue_binary = merged_rev_rel
print(merged_revenue_binary["Date"].dtype) #check if column is datatime type
#we want to observe all dates after 2022-06-30-01 as "1"'s and all dates before 2022-06-30 as "0"'s
#split the data pre and post 2022-01-01 because that was the year the MBTA infrastructure renovation program began. Since this dataset only has the 6/30/2022 date, we will use that data as the benchmark for binary analysis
#date_to_string = "2022-06-30" #set variable to 2022-06-30 to find its numeric format
#date_convert = datetime.strptime(date_to_string, "%Y-%m-%d") #convert string date to datetime format
#print(type(date_convert)) #check if it is datetime format
#num_date = int(date_convert.timestamp()) #convert datetime to an integer
#num_date_result = num_date//10**9
#print(num_date_result)
#print(num_date) #print the numeric conversion of the 2022-06-30, easier for if else statement to transform dates into binary values
merged_revenue_binary["Pre and Post 2021"] = merged_revenue_binary["Date"].astype('int64')//10**9 #convert dates in the "weekly" column into numeric data type. Divide by 10**9 to convert the nanoseconds
print(merged_revenue_binary["Pre and Post 2021"].head()) #print first few rows of numeric format of dates in the "Pre and Post 2021" column
#num_date variable was set to 2022-06-30 from previous merge of dataframes
merged_revenue_binary["Pre and Post 2021 Binary"] = np.where(merged_revenue_binary["Pre and Post 2021"] > num_date, 1, 0) #if value in "Pre and Post 2021" column is greater than numeric value of 2022-01-01 = 1641013200, then value will be 1 if less than the date then value will be 0
print(merged_revenue_binary.head()) #print first few rows of binary dates
""""""
#export joined dataframe to csv file
merged_revenue_binary.to_csv("../../data/processed-data/Revenue_reliability_binary.csv")
""""""
#merge all the dataframes for expenses
expenses_merged = pd.merge(expenses23_df, expenses21_df, on="Sources of Operating Expenses")
#print(expenses_merged)
expenses_merged2 = pd.merge(expenses_merged , expenses19_df, on="Sources of Operating Expenses")
print(expenses_merged2.head())
""""""
#rename columns
expenses_merged2_rename = expenses_merged2.rename(columns={"June 30, 2023 Expenses":"2023-06-30", "June 30, 2022 Expenses":"2022-06-30", "June 30, 2021 Expenses":"2021-06-30", "June 30, 2020 Expenses":"2020-06-30", "June 30, 2019 Expenses":"2019-06-30"})
#drop the percent of total operating expenses columns to make melt function easier
expenses_merged2_drop = expenses_merged2_rename.drop(["2023 Percent of Total Operating Expenses", "2022 Percent of Total Operating Expenses", "2021 Percent of Total Operating Expenses", "2020 Percent of Total Operating Expenses", "2019 Percent of Total Operating Expenses"], axis=1)
#melt the joined revenue dataframe to join with the ride reliability dataset on date column
melt_expenses = expenses_merged2_drop.melt(id_vars=["Sources of Operating Expenses"], var_name='Date', value_name="Expenses (USD)")
print(melt_expenses.head())
""""""
melt_expenses["Date"] = pd.to_datetime(melt_expenses["Date"])
#reliability_df_drop["Date"] = pd.to_datetime(reliability_df_drop["Date"])
merged_expenses_rel = pd.merge(melt_expenses, reliability_df_drop, on="Date", how="inner")
print(merged_expenses_rel.head())
""""""
from datetime import datetime
#convert date observation into binary to better statistical tests and visualizations
#alternative analysis: add a column to the dataframe for binary classification target
merged_expenses_rel_binary = merged_expenses_rel
print(merged_expenses_rel_binary["Date"].dtype) #check if column is datatime type
merged_expenses_rel_binary["Pre and Post 2021"] = merged_expenses_rel_binary["Date"].astype('int64')//10**9 #convert dates in the "Date" column into numeric data type. Divide by 10**9 to convert the nanoseconds
print(merged_expenses_rel_binary["Pre and Post 2021"].head()) #print first few rows of numeric format of dates in the "Pre and Post 2021" column
#num_date variable was set to 2022-06-30 from previous merge of dataframes
merged_expenses_rel_binary["Pre and Post 2021 Binary"] = np.where(merged_expenses_rel_binary["Pre and Post 2021"] > num_date, 1, 0) #if value in "Pre and Post 2021" column is greater than numeric value of 2022-01-01 = 1641013200, then value will be 1 if less than the date then value will be 0
print(merged_expenses_rel_binary.head()) #print first few rows of binary dates
""""""
#export joined dataframe to csv file
merged_expenses_rel_binary.to_csv("../../data/processed-data/Expenses_reliability_binary.csv")
""""""
#merge all the dataframes for debts
debts_merged = pd.merge(debts23_df, debts21_df, on="Sources of Debt")
#print(debts_merged)
debts_merged2 = pd.merge(debts_merged , debts20_19_df, on="Sources of Debt")
print(debts_merged2.head())
""""""
#rename columns
debts_merged2_rename = debts_merged2.rename(columns={"June 30, 2023 Debt":"2023-06-30", "June 30, 2022 Debt":"2022-06-30", "June 30, 2021 Debts":"2021-06-30", "June 30, 2020 Debt":"2020-06-30", "June 30, 2019 Debt":"2019-06-30"})
#melt the joined revenue dataframe to join with the ride reliability dataset on date column
melt_debts = debts_merged2_rename.melt(id_vars=["Sources of Debt"], var_name='Date', value_name="Debt (USD)")
print(melt_debts.head())
""""""
melt_debts["Date"] = pd.to_datetime(melt_debts["Date"])
#reliability_df_drop["Date"] = pd.to_datetime(reliability_df_drop["Date"])
merged_debts_rel = pd.merge(melt_debts, reliability_df_drop, on="Date", how="inner")
print(merged_debts_rel.head())
""""""
from datetime import datetime
#convert date observation into binary to better statistical tests and visualizations
#alternative analysis: add a column to the dataframe for binary classification target
merged_debts_rel_binary = merged_debts_rel
print(merged_debts_rel_binary["Date"].dtype) #check if column is datatime type
merged_debts_rel_binary["Pre and Post 2021"] = merged_debts_rel_binary["Date"].astype('int64')//10**9 #convert dates in the "Date" column into numeric data type. Divide by 10**9 to convert the nanoseconds
print(merged_debts_rel_binary["Pre and Post 2021"].head()) #print first few rows of numeric format of dates in the "Pre and Post 2021" column
#num_date variable was set to 2022-06-30 from previous merge of dataframes
merged_debts_rel_binary["Pre and Post 2021 Binary"] = np.where(merged_debts_rel_binary["Pre and Post 2021"] > num_date, 1, 0) #if value in "Pre and Post 2021" column is greater than numeric value of 2022-01-01 = 1641013200, then value will be 1 if less than the date then value will be 0
print(merged_debts_rel_binary.head()) #print first few rows of binary dates
""""""
#export joined dataframe to csv file
merged_debts_rel_binary.to_csv("../../data/processed-data/Debts_reliability_binary.csv")
"""Summary
This section will focus on the challenges, benchmarks, and conclusions drawn from our data cleaning portion. It provides a technical overview on obstacles and limiations with our current dataset. Then, it will provide a brief overview on our next step.
Cleaning Challenges**
- Financial data required additional preprocessing as they were available only in PDF format. Converting to structured formats produced difficulties. We initially used pdfplumber library to extract table from pdf file, but failed at integrating it. We then decided on camelot.
- However, camelot recently updated and some of the functions utilized does not work in the newest updated version of camelot.
Conclusion
Next Step
- Our datasets are now prime for preliminary EDA. As we proceed to the exploratory data analysis (EDA) phase, let’s hope to uncover meaningful patterns and relationships within the data.
Potential Improvements
- Optimize the code to create functions for converting pdfs into csvs and for general standarization / relabeling tasks.