#import libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as pltExploratory Data Analysis
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: Perform exploratory data analysis to examine cleaned datasets, inching us closer to answering our overarching research. We uncover meaningful patterns, relationships, and trends about MBTA system performance prior and post 2022 renovations.
Motivation:
System Insights: Find relationships in data, uncovering insights that may affirm existing MBTA context. Track project impact.Track performance improvement: Analyze features to find early indicators of potential improvement. This helps gauge renovation success.Understand data for ML: Understand what features might reveal useful for unsupervised and supervised learning (i.e. predicting)
Objectives:
Understand data distributionExamine trends over 2020-2024Find relations between variablesDetect outliersVisualize key metrics
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
- Univariate Analysis: Explore data distributions and identify potential outliers
- Produced histograms for num_predictions and accurate_predictions
- Outputed descriptive stats
- Used boxplots to visualize spread for numerical features
- Categorical Analysis: Analyze frequency and trends for categorical variables.
- Produced bar plot for subway route frequency and time bins
- Created crosstabs between subway and route_id, as well as pre and post-2021 with route_id, bin, and arrival departure
- Bivariate and Multivariate analysis: explore relationship betweeen variables and explore multicollinearity
- Produced correlation matrix, heatmap, and scatterplot (num_predictions and num_accurate predictions)
- Statistical Testing: determine statistical significance of differences in num_predictions and num_accurate predictions
- Performed t-tests
- Logistic Regression
- Created two logistic regressions for fit to predict whether a trip occured pre- or post- 2021 based on features.
- Applied normalization
- Applied z-score to both the prediction and the accurate prediction and compared scatterplot visualization
Reliability Data
- Line Graph: produced a line graph to track the number of on-time subway arrivals over time
News Feed
- Word Cloud:
- Integrated stopwords from ntlk pack to omit commonly used words
- Visualize the most commonly used words in google news headlines
- Sentiment Analysis:
- Used Vader to perform sentiment analysis on the news headlines
Alerts Feed
- Univariate Analysis:
- Created bar graphs on causes and effects of alerts
- Created line graph to track causes (accidents, construction, and maintenance) over the years 2020-2024 (i.e. post- abd pre- renovation)
- Created a line graph to track effects of alerts over time
- Created a stack bar graph to find distribution of alerts by severity level over the years
- Created a heatmap to track severity level by years
- Produced another stacked bar graph and heatmap to track severity levels by routes
- Outputed a list of highly alerted spots
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 explain how we performed EDA for the following datasets:
If you need a refresher on the data sources, visit the link below:
Code
EDA: Ride Accuracy Binary dataset
#load ride accuracy csv file dataset for eda
ride_accuracy = pd.read_csv("../../data/processed-data/Clean_Ride_Accuracy_Binary.csv")
#convert csv file into a dataframe
ride_accuracy_df = pd.DataFrame(ride_accuracy)
print(ride_accuracy_df.head())
#drop unneeded columns
ride_accuracy_drop_df = ride_accuracy_df.drop(["Unnamed: 0"],axis=1) Unnamed: 0 weekly mode route_id bin arrival_departure \
0 780 2020-08-03 subway Blue 0-3 min blended
1 781 2020-08-03 subway Blue 3-6 min blended
2 782 2020-08-03 subway Blue 6-12 min blended
3 783 2020-08-03 subway Blue 12-30 min blended
4 784 2020-08-03 subway Green-B 0-3 min blended
num_predictions num_accurate_predictions Low Range Bin Time (min) \
0 63612 58116 0
1 65346 58809 3
2 124158 112668 6
3 350575 314248 12
4 144067 117550 0
High Range Bin Time (min) Pre and Post 2021 Pre and Post 2021 Binary
0 3 1596412800 0
1 6 1596412800 0
2 12 1596412800 0
3 30 1596412800 0
4 3 1596412800 0
Univariate analysis:
#find outliers and other statistical information using describe()
print(ride_accuracy_drop_df.describe()) num_predictions num_accurate_predictions Low Range Bin Time (min) \
count 7.184000e+03 7184.000000 7184.000000
mean 1.900083e+05 154576.004176 5.250000
std 1.842277e+05 144457.231305 4.437369
min 0.000000e+00 0.000000 0.000000
25% 7.617800e+04 67576.000000 2.250000
50% 1.199255e+05 101258.000000 4.500000
75% 2.237600e+05 187399.250000 7.500000
max 1.158573e+06 904424.000000 12.000000
High Range Bin Time (min) Pre and Post 2021 Pre and Post 2021 Binary
count 7184.000000 7.184000e+03 7184.000000
mean 12.750000 1.662738e+09 0.670379
std 10.473909 3.750476e+07 0.470108
min 3.000000 1.596413e+09 0.000000
25% 5.250000 1.630282e+09 0.000000
50% 9.000000 1.664150e+09 1.000000
75% 16.500000 1.695600e+09 1.000000
max 30.000000 1.724630e+09 1.000000
#create histogram plot for num_predictions column
sns.histplot(data = ride_accuracy_drop_df, x = "num_predictions")
plt.show()
#create histogram plot for num_accurate_predictions column
sns.histplot(data = ride_accuracy_drop_df, x = "num_accurate_predictions")
plt.show()HISTOGRAM ANALYSIS
- Above show the distribution of the “num_predictions” and “num_accurate predictions”. Both histograms have a right skew meaning there were fewer number of predictions and accurate preditictions of subway rides. Not too many outliers.
#bar charts to observe categorical variables: different subway routes
route_count = ride_accuracy_drop_df["route_id"].count()
sns.barplot(data = ride_accuracy_drop_df, x = "route_id", y = route_count)
plt.title("Subway route bar graph")
plt.xticks(rotation=45)
plt.ylabel("Frequency")
plt.show()#bar charts to observe categorical variables: bin column
bin_count = ride_accuracy_drop_df["bin"].count()
sns.barplot(data = ride_accuracy_drop_df, x = "bin", y = bin_count)
plt.title("Subway ride reliability bin time frame bar graph")
plt.xticks(rotation=45)
plt.ylabel("Frequency")
plt.show()BAR PLOT ANALYSIS
- Bar plots above shows the frequency of the categorical variables: route_id and bin. Bar plots show that the number of observations are uniform for all the unique categorical features.
Bivariate and Multivariate Analysis:
#create correlation matrix of the ride reliability dataframe
#first subset dataframe to only include numerical columns
rel_subset = ride_accuracy_drop_df[["num_predictions", "num_accurate_predictions", "Low Range Bin Time (min)", "High Range Bin Time (min)", "Pre and Post 2021 Binary"]]
#create correlation matrix
rel_corr_matrix = rel_subset.corr()
print(rel_corr_matrix)
#create heatmap of dataframe subset
sns.heatmap(rel_subset)
plt.show() #show heatmap num_predictions num_accurate_predictions \
num_predictions 1.000000 0.982956
num_accurate_predictions 0.982956 1.000000
Low Range Bin Time (min) 0.701063 0.678770
High Range Bin Time (min) 0.734128 0.707786
Pre and Post 2021 Binary -0.051032 -0.067263
Low Range Bin Time (min) \
num_predictions 7.010633e-01
num_accurate_predictions 6.787704e-01
Low Range Bin Time (min) 1.000000e+00
High Range Bin Time (min) 9.804686e-01
Pre and Post 2021 Binary 1.068438e-15
High Range Bin Time (min) Pre and Post 2021 Binary
num_predictions 7.341277e-01 -5.103219e-02
num_accurate_predictions 7.077863e-01 -6.726284e-02
Low Range Bin Time (min) 9.804686e-01 1.068438e-15
High Range Bin Time (min) 1.000000e+00 1.094905e-16
Pre and Post 2021 Binary 1.094905e-16 1.000000e+00
CORRELATION MATRIX
- The correlation matrix show majority of features in the ride accuracy dataset have high correlation. But majority of those correalations are due to multicollinearity. The heatmap helps to visualize the relations shown in the correlation matrix. The heatmap shows that “num_prediction” and “num_accurate_predictions” features have higher correlation values, and the rest of the features “Low Range Bin Time (min)” and “High Range Bin Time (min)” and “Pre and Post 2021 Binary” have lower correlation values. Thus, it would be best to fit either “num_prediction” or “num_accurate_predictions” in a model to avoid multicollinearity.
#create crosstab of categorical variables
#crosstab variables mode of transportation and the subway route id
pd.crosstab(ride_accuracy_drop_df["mode"], ride_accuracy_drop_df["route_id"])| route_id | Blue | Green-B | Green-C | Green-D | Green-E | Mattapan | Orange | Red |
|---|---|---|---|---|---|---|---|---|
| mode | ||||||||
| subway | 900 | 900 | 900 | 900 | 900 | 900 | 884 | 900 |
#create crosstab of data between before and after 2022-01-01 and the subway route ids
pd.crosstab(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["route_id"])| route_id | Blue | Green-B | Green-C | Green-D | Green-E | Mattapan | Orange | Red |
|---|---|---|---|---|---|---|---|---|
| Pre and Post 2021 Binary | ||||||||
| 0 | 296 | 296 | 296 | 296 | 296 | 296 | 296 | 296 |
| 1 | 604 | 604 | 604 | 604 | 604 | 604 | 588 | 604 |
#create crosstab of data between before and after 2022-01-01 and the bin of ride arrival time frame
pd.crosstab(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["bin"])| bin | 0-3 min | 12-30 min | 3-6 min | 6-12 min |
|---|---|---|---|---|
| Pre and Post 2021 Binary | ||||
| 0 | 592 | 592 | 592 | 592 |
| 1 | 1204 | 1204 | 1204 | 1204 |
#create crosstab of data between before and after 2022-01-01 and the type of trips made
pd.crosstab(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["arrival_departure"])| arrival_departure | blended |
|---|---|
| Pre and Post 2021 Binary | |
| 0 | 2368 |
| 1 | 4816 |
CROSSTAB ANALYSIS
- The crosstabs above shows the relationship of the categorical variables in the ride accuracy dataset. The first crosstab shows the data only includes data on the subway mode of transportation and includes the subway route ids. The second crosstab shows there were more observations of the subway route-ids post-2021. The third crosstab shows there were more observations of ride accuracy predictions made post 2021. The fourth crosstab shows there were more blended trips (round-trips: both departure and arrival) made post 2021.
#Feature pairings
#create scatter plots between num_accurate_predictions and num_predictions
sns.scatterplot(data = ride_accuracy_drop_df, x= "num_accurate_predictions", y = "num_predictions")
plt.title("Subway Ride Number of Accurate Predictions vs. Number of Predictions")
plt.xticks(rotation=45)
plt.xlabel("Number of Accurate Predictions")
plt.ylabel("Number of Predictions")
plt.show()SCATTERPLOT ANALYSIS * The scatter plot shows the relationship between the number of accurate predictions on subway trips made on time and the number of predictions made of subway trips. Plot shows strong positive correlation between the two features, which may also be due to multicollinearity. More ride predictions implies more of the predictions are accurate.
Data Distribution and Normalization:
#create boxplot of dataframe to visualize dataset distribution
sns.boxplot(data=ride_accuracy_drop_df)
plt.xticks(rotation=45)
plt.show()BOXPLOT ANALYSIS
Boxplot shows distribution of the numerical features of the ride accuracy dataset. Boxplot shows there are no significant outliers in the dataset. The pre and post 2021 feature appears to have the most range.
#apply z-score normalization on subset of dataframe that only includes numerical values
#convert subset dataframe into an array
#Only select num_predictions and num_accurate_predictions
accuracy_subset = ride_accuracy_drop_df[["num_predictions", "num_accurate_predictions"]]
ride_accuracy_array = np.array(accuracy_subset)
#compute z-score normalization transformation on array of dataframe subset (x-mean)/standard deviation
normalized_ride_accuracy = (ride_accuracy_array - np.mean(ride_accuracy_array) / np.std(ride_accuracy_array))
print(normalized_ride_accuracy)[[ 63610.96505666 58114.96505666]
[ 65344.96505666 58807.96505666]
[124156.96505666 112666.96505666]
...
[103571.96505666 82494.96505666]
[203963.96505666 163503.96505666]
[582949.96505666 397087.96505666]]
#scatter plot of original data of the "num_predictions" and "num_accurate_predictions" columns before normalization transformation
plt.scatter(ride_accuracy_drop_df["num_predictions"], ride_accuracy_drop_df["num_accurate_predictions"])
plt.title("Before Normalization Transformation: Number of Ride Predictions vs. Number of Accurate Ride Predictions")
plt.xlabel("Number of Ride Predictions")
plt.ylabel("Number of Accurate Ride Predictions")
plt.show()
#scatter plot of original data of the "num_predictions" and "num_accurate_predictions" columns after normalization transformation
#scatter plot shows normalization of dataset is not necessary
plt.scatter(normalized_ride_accuracy[:,0], normalized_ride_accuracy[:,1])
plt.title("After Normalization Transformation: Number of Ride Predictions vs. Number of Accurate Ride Predictions")
plt.xlabel("Number of Ride Predictions")
plt.ylabel("Number of Accurate Ride Predictions")
plt.show()SCATTERPLOT ANALYSIS
- The first scatterplot shows the relationship between the number of accurate predictions on subway trips made on time and the number of predictions made of subway trips before normalization applied. The second scatter plot shows the same two features after normalization applied. Z-score normalization was applied to the data to address any outliers. There were no signigicant difference in data point distances between the first and second scatter plot, so normalization is not needed for the data.
Stat Insights
#Logistic regression model
#determine relationship between the number of predictions and accurate predictions with binary values of the dates
#import the statsmodel libary
import statsmodels.api as sm
#fit logistic regression model onto the Pre and Post 2021 Binary and num_accurate_predictions column of dataset
log_accuracy_model = sm.Logit(ride_accuracy_drop_df["Pre and Post 2021 Binary"], sm.add_constant(ride_accuracy_drop_df["num_accurate_predictions"])).fit()
#print logistic model summary
print(log_accuracy_model.summary())Optimization terminated successfully.
Current function value: 0.631700
Iterations 5
Logit Regression Results
====================================================================================
Dep. Variable: Pre and Post 2021 Binary No. Observations: 7184
Model: Logit Df Residuals: 7182
Method: MLE Df Model: 1
Date: Mon, 16 Dec 2024 Pseudo R-squ.: 0.003487
Time: 16:11:13 Log-Likelihood: -4538.1
converged: True LL-Null: -4554.0
Covariance Type: nonrobust LLR p-value: 1.744e-08
============================================================================================
coef std err z P>|z| [0.025 0.975]
--------------------------------------------------------------------------------------------
const 0.8611 0.037 23.278 0.000 0.789 0.934
num_accurate_predictions -9.592e-07 1.69e-07 -5.674 0.000 -1.29e-06 -6.28e-07
============================================================================================
ANALYSIS
- Logistic regression models were created to predict whether the features can predict whether the event of the trip occurred pre or post 1/1/2021, where the predictor are the binary values of date - pre and post 2021. Looking at the logistic regression model, the features num_predictions and num_accurate_predictions are likely better predictors of the Pre and Post 2021 Binary feature because the p-values are 0.000.
#create a new subset of dataset, drop the Pre and Post 2021 Binary because that column will be used as the predictor to be fit in the logistic regression model
rel_subset2 = rel_subset.drop("Pre and Post 2021 Binary", axis=1)
print(rel_subset2.columns) #print columns of new subset data as checkpointIndex(['num_predictions', 'num_accurate_predictions',
'Low Range Bin Time (min)', 'High Range Bin Time (min)'],
dtype='object')
#fit logistic regression model onto the Pre and Post 2021 Binary and num_accurate_predictions column of dataset
log_accuracy_model2 = sm.Logit(ride_accuracy_drop_df["Pre and Post 2021 Binary"], sm.add_constant(rel_subset2)).fit()
#print logistic model summary
print(log_accuracy_model2.summary())Optimization terminated successfully.
Current function value: 0.627313
Iterations 5
Logit Regression Results
====================================================================================
Dep. Variable: Pre and Post 2021 Binary No. Observations: 7184
Model: Logit Df Residuals: 7179
Method: MLE Df Model: 4
Date: Mon, 16 Dec 2024 Pseudo R-squ.: 0.01041
Time: 16:11:13 Log-Likelihood: -4506.6
converged: True LL-Null: -4554.0
Covariance Type: nonrobust LLR p-value: 1.261e-19
=============================================================================================
coef std err z P>|z| [0.025 0.975]
---------------------------------------------------------------------------------------------
const 0.8076 0.041 19.704 0.000 0.727 0.888
num_predictions 4.267e-06 8.32e-07 5.128 0.000 2.64e-06 5.9e-06
num_accurate_predictions -7.037e-06 1e-06 -7.025 0.000 -9e-06 -5.07e-06
Low Range Bin Time (min) -0.0071 0.029 -0.241 0.810 -0.064 0.050
High Range Bin Time (min) 0.0177 0.013 1.335 0.182 -0.008 0.044
=============================================================================================
#two-sample t-test
#import the scipy.stats library
import scipy.stats as stats
#perform two-sample t-test on the "Pre and Post 2021 Binary" and "num_accurate_predictions" features
t_stat, p_value = stats.ttest_ind(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["num_accurate_predictions"])
print("T-statistic: ", t_stat) #print t-statistic
print("p-value: ", p_value) #print p-value
#perform two-sample t-test on the "Pre and Post 2021 Binary" and "num_predictions" features
t_stat2, p_value2 = stats.ttest_ind(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["num_predictions"])
print("T-statistic: ", t_stat2) #print t-statistic
print("p-value: ", p_value2) #print p-value
#perform two-sample t-test on the "Pre and Post 2021 Binary" and "Low Range Bin Time (min)" features
t_stat3, p_value3 = stats.ttest_ind(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["Low Range Bin Time (min)"])
print("T-statistic: ", t_stat3) #print t-statistic
print("p-value: ", p_value3) #print p-value
#perform two-sample t-test on the "Pre and Post 2021 Binary" and "High Range Bin Time (min)" features
t_stat4, p_value4 = stats.ttest_ind(ride_accuracy_drop_df["Pre and Post 2021 Binary"], ride_accuracy_drop_df["High Range Bin Time (min)"])
print("T-statistic: ", t_stat4) #print t-statistic
print("p-value: ", p_value4) #print p-valueT-statistic: -90.69515095798823
p-value: 0.0
T-statistic: -87.41768526850058
p-value: 0.0
T-statistic: -86.98884263601893
p-value: 0.0
T-statistic: -97.6541427631758
p-value: 0.0
T-TEST ANALYSIS
- The t-tests compares the means between two group features to see if there are obvious differences between the means of the feature groups compared to within its own group. The t-tests show that the comparision of the “Pre and Post 2021 Binary” feature and “num_accurate_predictions”, “num_predictions”, “Low Range Bin Time (min)”, “High Range Bin Time (min)” have large t-values, meaning there are large differences between those two groups compared to the variation with the feature groups. The p-value shows there are significance between the two feature groups.
Data Visualization and Storytelling:
#create bar plot to show number of accurate prediction of subway rides and their arrival timeframe bins
plt.subplot(1,2,1) #create first plot. First row, 2 columns, 1st subplot
sns.barplot(data = ride_accuracy_drop_df, x = "bin", y = "num_accurate_predictions")
plt.title("Subway Arrival Time Frame Bins \n vs. Number of Accurate Predictions")
plt.ylabel("Number of Accurate Predictions")
plt.xlabel("Time Frame Bin Arrival of Subway")
plt.xticks(rotation=45)
#create bar plot to show number of prediction of subway rides and their arrival timeframe bins
#create second subplot
plt.subplot(1,2,2)
sns.barplot(data = ride_accuracy_drop_df, x = "bin", y = "num_predictions")
plt.title("Subway Arrival Time Frame Bins \n vs. Number of Predictions")
plt.ylabel("Number of Predictions")
plt.xlabel("Time Frame Bin Arrival of Subway")
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()BAR PLOTS ANALYSIS
- Bar plots above shows the time frame (bins) that subway trip arrived in vs. whether the prediction was accurate. Bar plot shows there were fewer number of accurate predictions made compared to the number of predictions. There were more subway trips arriving late in the 12-30 min bin.
plt.subplot(1,2,1) #create first plot. First row, 2 columns, 1st subplot
sns.barplot(data = ride_accuracy_drop_df, hue = "Pre and Post 2021 Binary", y = "num_accurate_predictions", x = "route_id")
plt.title("Accurate Predictions \n by Subway Route ID")
plt.xlabel("Subway route ID")
plt.ylabel("Number of Accurate Predictions")
plt.xticks(rotation=45)
plt.subplot(1,2,2)
sns.barplot(data = ride_accuracy_drop_df, hue = "Pre and Post 2021 Binary", y = "num_predictions", x = "route_id")
plt.title("Predictions by \n Subway Route ID")
plt.xlabel("Weekly (months)")
plt.ylabel("Number of Accurate Predictions")
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()BAR PLOTS ANALYSIS
- The two bar plots show the different subway routes vs. number of accurate trip predictions post and pre 1-1-2021. Bar plots shows no significant frequency trend in number of trip predictions and accurate preditictions pre and post 2021.
plt.figure(figsize=(10,4))
sns.lineplot(data = ride_accuracy_drop_df, x = "weekly", y = "num_predictions", hue = "route_id")
plt.title("Predictions by Subway Route ID")
plt.xlabel("Weekly (months)")
plt.ylabel("Number of Predictions")
plt.xticks(range(0,220,10))
plt.xticks(rotation=45)
plt.show()LINE GRAPH ANALYSIS
- Line graph shows the trend of number of predictions on subway rides made between 8/3/2020 and 5/6/2024. Plot shows steady trend in number of trip predications made throughtout the years, there appeared to be a spike in ride predictions in between 6/12/2023 and 8/7/2023.
plt.figure(figsize=(10,4))
sns.relplot(data = ride_accuracy_drop_df, x = "weekly", y = "num_predictions", col = "route_id", kind="line")
plt.title("Predictions by Subway Route ID")
plt.xlabel("Weekly (months)")
plt.ylabel("Number of Predictions")
plt.xticks(range(0,220,10))
plt.xticks(rotation=45)
plt.show()<Figure size 1000x400 with 0 Axes>
MORE LINE GRAPH ANALYSIS
- The line graph above shows the same information as the previous line graph this time broken down by the subway route ids for close inspection. There no siginificant trends in the number of predictions made throughout the years by subway route-ids.
Ride Reliability
ride_rel = pd.read_csv("../../data/processed-data/Clean_Ride_Reliability.csv")
#convert csv file into a dataframe
ride_rel_df = pd.DataFrame(ride_rel)
print(ride_rel_df.head()) Unnamed: 0 trip_date ontime_trip_count trip_count \
0 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
ObjectId Date Time
0 1 2014-07-01 04:00:00+00
1 2 2014-07-02 04:00:00+00
2 3 2014-07-03 04:00:00+00
3 4 2014-07-04 04:00:00+00
4 5 2014-07-05 04:00:00+00
data_2019_subset = ride_rel_df[ride_rel_df["Date"] > '2019-01-01']max_trip = data_2019_subset.nlargest(5000, "ontime_trip_count")sns.lineplot(data = max_trip, x = "Date", y = "ontime_trip_count")
plt.title("Ontime Subway Trip Counts 2019 to 2023")
plt.xlabel("Date (months)")
plt.ylabel("Number of Ontime Trip Counts")
plt.show()LINE GRAPH ANALYSIS
- Line graph shows trend of number of subway rides that were on time from the ride reliability dataset. Plot shows there was a steady decrease in the number of on time trends as the years continued.
Alerts EDA
Univariate Analysis
file_path = '../../data/processed-data/processed_alerts.csv'
# Load the dataset
merged_alerts_cleaned = pd.read_csv(file_path)
print(merged_alerts_cleaned.head())
cause_counts = merged_alerts_cleaned['cause'].value_counts()
print("Most Frequent Causes of Alerts:")
print(cause_counts)
# Convert cause_counts to a DataFrame for easier filtering
cause_counts_df = cause_counts.reset_index()
cause_counts_df.columns = ['Cause', 'Frequency']
cause_counts_df = cause_counts_df[cause_counts_df['Frequency'] > 0]
# Filter out the dominant category (e.g., 'UNKNOWN_CAUSE') to focus on smaller causes
filtered_cause_counts_df = cause_counts_df[cause_counts_df['Cause'] != 'unknown cause']
# Bar chart for the top 10 causes excluding 'UNKNOWN_CAUSE'
filtered_cause_counts_df.head(10).set_index('Cause').plot(kind='bar', title='Top 10 Causes of Alerts (Excluding UNKNOWN_CAUSE)', color='blue')
plt.xlabel('Cause')
plt.ylabel('Frequency')
plt.xticks(rotation=45)
plt.show()
# Alternatively, apply a logarithmic scale to handle wide-ranging values
cause_counts_df.head(10).set_index('Cause').plot(kind='bar', logy=True, title='Top 10 Causes of Alerts with Logarithmic Scale', color='blue')
plt.xlabel('Cause')
plt.ylabel('Frequency (Log Scale)')
plt.xticks(rotation=45)
plt.show() 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 NaN NaN
1 Unknown 117 Unspecified 2024 NaN NaN
2 Unknown 117 Unspecified 2024 NaN NaN
3 Unknown 117 Unspecified 2024 NaN NaN
4 Unknown 117 Unspecified 2024 NaN NaN
Most Frequent Causes of Alerts:
cause
unknown cause 1173449
construction 760032
maintenance 420486
other cause 220328
technical problem 105486
police activity 22152
accident 13633
weather 12274
medical emergency 6822
demonstration 2145
holiday 21
Name: count, dtype: int64
TOP CAUSES
- found that construction and maintenance were among the most frequent contributors. This aligns with the MBTA’s ongoing renovation efforts. However, accidents and technical issues also play a significant role, adding complexity to the system’s challenges.
Cause: Accidents, Construction, and Maintenance
# Filter rows where 'cause' indicates 'accident'
accidents_data = merged_alerts_cleaned[merged_alerts_cleaned['cause'].str.contains('accident', na=False, case=False)]
# Filter rows where 'cause' indicates 'construction'
construction_data = merged_alerts_cleaned[merged_alerts_cleaned['cause'].str.contains('construction', na=False, case=False)]
# Filter rows where 'cause' indicates 'maintainance'
maintenance_data = merged_alerts_cleaned[merged_alerts_cleaned['cause'].str.contains('maintenance', na=False, case=False)]
# Count accidents by year
accidents_by_year = accidents_data.groupby('year').size()
construction_by_year = construction_data.groupby('year').size()
maintenance_by_year = maintenance_data.groupby('year').size()
# Display the counts
print(accidents_by_year)
# Line graph for accidents by year
plt.plot(accidents_by_year.index, accidents_by_year.values, marker='o', linestyle='-', color='blue')
plt.title('Number of Accidents Alerts by Year')
plt.xlabel('Year')
plt.ylabel('Number of Accidents Alerts')
plt.grid(True)
plt.xticks(rotation=45)
plt.show()
# Line graph for construction by year
plt.plot(construction_by_year.index, construction_by_year.values, marker='o', linestyle='-', color='blue')
plt.title('Number of Construction Alerts by Year')
plt.xlabel('Year')
plt.ylabel('Number of Construction Alerts')
plt.grid(True)
plt.xticks(rotation=45)
plt.show()
# Line graph for maintenance by year
plt.plot(maintenance_by_year.index, maintenance_by_year.values, marker='o', linestyle='-', color='blue')
plt.title('Number of Maintenance Alerts by Year')
plt.xlabel('Year')
plt.ylabel('Number of Maintenance Alerts')
plt.grid(True)
plt.xticks(rotation=45)
plt.show()year
2020 2273
2021 2646
2022 3480
2023 2987
2024 2247
dtype: int64
SUBCATEGORIES OF CAUSES EXPLORED OVER THE YEARS
- notable spike in accidents in 2022, which aligns with major incidents we know occurred that year. Additionally, 2024 shows the highest levels of construction and maintenance alerts, which could indicate progress in addressing infrastructure challenges. This suggests that while disruptions are increasing, they may be a sign of necessary improvements taking place
Effect by year
# Group by 'year' and 'effect' to calculate frequencies by year
effects_by_year = merged_alerts_cleaned.groupby(['year', 'effect']).size().unstack(fill_value=0)
# Get the top 10 most frequent effects from the entire dataset
top_effects = merged_alerts_cleaned['effect'].value_counts().head(10).index.tolist()
# Filter the grouped data to include only the top 10 effects
effects_by_year_top = effects_by_year[top_effects]
# Plot trends for the top 10 effects over time
effects_by_year_top.plot(kind='line', figsize=(12, 6), marker='o', title='Top 10 Effects of Alerts Over Time')
plt.xlabel('Year')
plt.ylabel('Frequency')
plt.legend(title='Effect', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.grid(True)
plt.tight_layout()
plt.show()
EFFECTS OVER TIME
- Detours, followed by accessibility and stop movd are some of the top three common effects from alerts.
Severity by Year
# Group by 'year' and 'severity_level' to calculate frequency of severity levels by year
severity_by_year = merged_alerts_cleaned.groupby(['year', 'severity_level']).size().unstack(fill_value=0)
# Print the severity distribution by year
print("Severity Level Distribution by Year:")
print(severity_by_year)
severity_by_year_percent = severity_by_year.div(severity_by_year.sum(axis=1), axis=0) * 100
severity_by_year_percent.plot(kind='bar', stacked=True, figsize=(12, 6), title='Severity Levels as Percentage of Total Alerts')
plt.xlabel('Year')
plt.ylabel('Percentage (%)')
plt.legend(title='Severity Level', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.tight_layout()
plt.show()Severity Level Distribution by Year:
severity_level INFO SEVERE WARNING
year
2020 60086 169828 85912
2021 96033 202173 113485
2022 141865 195420 212680
2023 93655 278947 239953
2024 247052 270790 328949
plt.figure(figsize=(12, 6))
sns.heatmap(severity_by_year, annot=True, fmt='d', cmap='YlGnBu')
plt.title('Heatmap of Severity Levels by Year')
plt.xlabel('Severity Level')
plt.ylabel('Year')
plt.tight_layout()
plt.show()SEVERITY LEVELS BY YEARS
- examined the severity distribution of alerts. 2020 had the highest percentage of severe incidents, likely due to operational challenges during the early pandemic. While 2022 features a lower percentage of severe alerts, the volume of incidents increased dramatically, reinforcing the need for systemic improvements.
# Most frequent routes
route_counts = merged_alerts_cleaned['route_id'].value_counts()
print("Most Affected Routes:")
print(route_counts.head(10))
# Most frequent stops
stop_counts = merged_alerts_cleaned['stop_id'].value_counts()
print("Most Affected Stops:")
print(stop_counts.head(10))
# Group by 'route_id' and 'severity_level' to calculate frequency
severity_by_route = merged_alerts_cleaned.groupby(['route_id', 'severity_level']).size().unstack(fill_value=0)
# Print the severity distribution for the top 10 routes
top_routes = route_counts.head(10).index.tolist() # Get the top 10 routes
severity_by_route_top = severity_by_route.loc[top_routes]
print(severity_by_route_top)
# Stacked bar chart for severity levels by top 10 routes
severity_by_route_top.plot(kind='bar', stacked=True, figsize=(10, 6), title='Severity by Top 10 Routes')
plt.xlabel('Route ID')
plt.ylabel('Frequency')
plt.legend(title='Severity Level', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.tight_layout()
plt.show()
# Heatmap for severity levels by top 10 routes
plt.figure(figsize=(10, 6))
sns.heatmap(severity_by_route_top, annot=True, fmt='d', cmap='YlGnBu', cbar=True)
plt.title('Heatmap of Severity Levels by Top 10 Routes')
plt.xlabel('Severity Level')
plt.ylabel('Route ID')
plt.tight_layout()
plt.show()Most Affected Routes:
route_id
CR-Worcester 288912
451 149191
CR-Newburyport 137202
Green-B 137060
Green-D 134547
CR-Fitchburg 124749
Red 111782
Unspecified 108719
CR-Lowell 89028
120 60192
Name: count, dtype: int64
Most Affected Stops:
stop_id
Unspecified 594191
141 65187
2936 39036
1060 24367
1123 17604
WML-0442-CS 16573
2444 13482
2755 13402
West Natick 13226
Natick Center 13222
Name: count, dtype: int64
severity_level INFO SEVERE WARNING
route_id
CR-Worcester 120835 81865 86212
451 82361 66568 262
CR-Newburyport 8817 65423 62962
Green-B 77479 35091 24490
Green-D 43099 64898 26550
CR-Fitchburg 11582 23793 89374
Red 26144 57136 28502
Unspecified 2380 157 106182
CR-Lowell 12458 40746 35824
120 349 57048 2795
SEVERITY BY ROUTE ANALYSIS
- Green line, major buses, and commuter rail faces the highest numbers of affect. The Green (D) must also be looked at due to its moderately high number of severe alerts.
News EDA
from wordcloud import WordCloud, STOPWORDS
from nltk.sentiment import SentimentIntensityAnalyzer
import re
import nltk
# Load the dataset
file_path = '../../data/raw-data/google_news_revised.csv'
news_data = pd.read_csv(file_path)
# Function to clean text
def clean_text(text):
if not isinstance(text, str):
return ''
try:
# Remove punctuation and special characters
text = re.sub(r'[^\w\s]', '', text)
# Eliminate extra whitespace
text = re.sub(r'\s+', ' ', text)
# Convert to lowercase
text = text.lower()
except Exception as e:
print(f"Error cleaning text: {e}")
text = ''
return text
# Clean the 'title' column
news_data['cleaned_title'] = news_data['title'].apply(clean_text)
# Add custom stopwords
custom_stopwords = set(STOPWORDS) # default STOPWORDS
custom_words = {'mbta', 'transit', 'boston', 'train', 'line', 'project', 'service'} # Added custom stopwords related to MBTA
custom_stopwords.update(custom_words)
# Function to generate Word Cloud with custom stopwords
def generate_word_cloud(text, title):
wordcloud = WordCloud(
width=3000,
height=2000,
background_color='white',
colormap='viridis',
stopwords=custom_stopwords
).generate(text)
plt.figure(figsize=(12, 8))
plt.imshow(wordcloud, interpolation='bilinear')
plt.axis("off")
plt.title(f"Word Cloud for {title}", fontsize=20)
plt.show()
# Combine all cleaned titles for word cloud
all_cleaned_text = ' '.join(news_data['cleaned_title'])
print("Generating Word Cloud for 2024 News Headlines (with custom stopwords)...")
generate_word_cloud(all_cleaned_text, "2024 News Headlines")
# Perform Sentiment Analysis Using VADER
sia = SentimentIntensityAnalyzer()
def get_vader_sentiment(text):
sentiment = sia.polarity_scores(text)
return sentiment['compound'] # Use the compound score for overall sentiment
# Add sentiment scores
news_data['sentiment_score'] = news_data['cleaned_title'].apply(get_vader_sentiment)
def categorize_sentiment(score):
if score > 0.1:
return 'Positive'
elif score < -0.1:
return 'Negative'
else:
return 'Neutral'
# Apply sentiment categories
news_data['sentiment_category'] = news_data['sentiment_score'].apply(categorize_sentiment)
# Count the number of headlines in each sentiment category
sentiment_counts = news_data['sentiment_category'].value_counts()
# Bar plot for sentiment categories
plt.figure(figsize=(8, 6))
plt.bar(sentiment_counts.index, sentiment_counts.values, color=['green', 'gray', 'red'])
plt.title("Sentiment Categories of 2024 News Headlines")
plt.xlabel("Sentiment Category")
plt.ylabel("Number of Headlines")
plt.grid(axis='y')
plt.tight_layout()
plt.show()
[nltk_data] Downloading package vader_lexicon to
[nltk_data] /Users/mandy.sun/nltk_data...
[nltk_data] Package vader_lexicon is already up-to-date!
Generating Word Cloud for 2024 News Headlines (with custom stopwords)...
WORD CLOUD ANALYSIS
- Words like “new”, “red”, “speed”, “restrictions”, and “network” dominate, suggesting topics surrounding new infrastructure, speed restrictions, and redesigns.
- Challenges Highlighted: Terms like “shutdown”, “restrictions”, and “slow zones” emphasize difficulties and service limitations. There is also a reference to the MBTA works who were fired.
SENTIMENT ANALYSIS
- Neutral Dominance: The high frequency of neutral sentiment indicates that many headlines are likely factual, lacking clear emotional tone. This is common in news reporting where objectivity is emphasized.
- Lower Positive and Negative Sentiments: Fewer headlines exhibit strong positivity or negativity, suggesting that while impactful, stories with clear emotional language are less frequent.
- Potential limitation due to news headline being short and concise. Would need to finetune the sentiment process.
Financial Statements EDA
Summary
This section will focus on the challenges, benchmarks, and conclusions drawn from our exploratory data analysis portion. It provides a technical overview on obstacles and limiations with our EDA. Then, it will provide a brief overview on our next step.
Challenges
EDA Method Difficulties
- Performing EDA required finetuning the cleaning code iteratively
Conclusion
Primary Thoughts
- Histograms showed right-skewed distributions for “num_predictions” and “num_accurate_predictions”, indicating a smaller frequency of very high prediction values.
- Themes around “speed restrictions”, “shutdowns”, and “approvals” emerged, highlighting both challenges and improvements in the MBTA system. This is indication of MBTA staying true to their progress in renovation and improving transparency.
- Positive sentiment correlated with terms like “approves”, “launches”, and “improvement”, while negative sentiment centered around “shutdown” and “restrictions”.
- The logistic regression model, anova models, and majority of t-tests conducted showed no significance. The event of trips made on time happening did not have signigicant relationship between the amounts in the net postion, expenses, revenues, and debts regarding the MBTA system from 2019 to 2023. Only the t-test results of the debts dataset showed potential of a significant relationship, so taking the debts dataset for further analysis in supervised and un-supervised learning would be ideal.
- Data present in alert notifications align with our historical and contextual understanding of the MBTA (i.e. construction rise in 2024 with accidents highest in 2022)
Next Steps
- Following the collection, we will now perform unsupervised learning given the lack of significant relationships from our statistical tests.
Potential Improvements
- Further work on this project will involve finetunng the EDA, improving our existing models and reinterpreting our results.