EDA: Financial Statement Datasets and Ride Reliability Binary Dataset

#import libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
#load merged net position financial csv file dataset for eda
netposition_dataset = pd.read_csv("../../data/processed-data/Net_positions_reliability_binary.csv")
#convert csv file into a dataframe
netposition_df = pd.DataFrame(netposition_dataset)
#print first few rows of dataframe
print(netposition_df.head())
#print columns of dataframe
print(netposition_df.columns)


#load merged revenue financial csv file dataset for eda
revenue_dataset = pd.read_csv("../../data/processed-data/Revenue_reliability_binary.csv")
#convert csv file into a dataframe
revenue_df = pd.DataFrame(revenue_dataset)
#print first few rows of dataframe
print(revenue_df.head())
#print columns of dataframe
print(revenue_df.columns)


#load merged expenses financial csv file dataset for eda
expenses_dataset = pd.read_csv("../../data/processed-data/Expenses_reliability_binary.csv")
#convert csv file into a dataframe
expenses_df = pd.DataFrame(expenses_dataset)
#print first few rows of dataframe
print(expenses_df.head())
#print columns of dataframe
print(expenses_df.columns)


#load merged debts financial csv file dataset for eda
debts_dataset = pd.read_csv("../../data/processed-data/Debts_reliability_binary.csv")
#convert csv file into a dataframe
debts_df = pd.DataFrame(debts_dataset)
#print first few rows of dataframe
print(debts_df.head())
#print columns of dataframe
print(debts_df.columns)
   Unnamed: 0 Statements of Revenue, Expenses and Changes in Net Position  \
0           0                        Revenue from transportation            
1           1                           Total operating revenues            
2           2                            Transportation services            
3           3                           Other operating expenses            
4           4   Total operating expenses, excluding depreciation            

         Date  Net Position Amounts (USD)  ontime_trip_count  trip_count  \
0  2023-06-30                      376357               2925        3297   
1  2023-06-30                      452973               2925        3297   
2  2023-06-30                      822671               2925        3297   
3  2023-06-30                      968312               2925        3297   
4  2023-06-30                     1790983               2925        3297   

          Time  Pre and Post 2021  Pre and Post 2021 Binary  
0  04:00:00+00         1688083200                         1  
1  04:00:00+00         1688083200                         1  
2  04:00:00+00         1688083200                         1  
3  04:00:00+00         1688083200                         1  
4  04:00:00+00         1688083200                         1  
Index(['Unnamed: 0',
       'Statements of Revenue, Expenses and Changes in Net Position', 'Date',
       'Net Position Amounts (USD)', 'ontime_trip_count', 'trip_count', 'Time',
       'Pre and Post 2021', 'Pre and Post 2021 Binary'],
      dtype='object')
   Unnamed: 0 Sources of Operating Revenue        Date  Revenue (USD)  \
0           0                          Bus  2023-06-30          76549   
1           1                       Subway  2023-06-30         176521   
2           2                Commuter rail  2023-06-30         112801   
3           3              Other passenger  2023-06-30          10486   
4           4              Other operating  2023-06-30          76616   

   ontime_trip_count  trip_count         Time  Pre and Post 2021  \
0               2925        3297  04:00:00+00         1688083200   
1               2925        3297  04:00:00+00         1688083200   
2               2925        3297  04:00:00+00         1688083200   
3               2925        3297  04:00:00+00         1688083200   
4               2925        3297  04:00:00+00         1688083200   

   Pre and Post 2021 Binary  
0                         1  
1                         1  
2                         1  
3                         1  
4                         1  
Index(['Unnamed: 0', 'Sources of Operating Revenue', 'Date', 'Revenue (USD)',
       'ontime_trip_count', 'trip_count', 'Time', 'Pre and Post 2021',
       'Pre and Post 2021 Binary'],
      dtype='object')
   Unnamed: 0  Sources of Operating Expenses        Date  Expenses (USD)  \
0           0             Wages and benefits  2023-06-30          822671   
1           1                  Commuter rail  2023-06-30          634244   
2           2  Depreciation and amortization  2023-06-30          562247   
3           3          Material and supplies  2023-06-30          304646   
4           4                Other operating  2023-06-30           29422   

   ontime_trip_count  trip_count         Time  Pre and Post 2021  \
0               2925        3297  04:00:00+00         1688083200   
1               2925        3297  04:00:00+00         1688083200   
2               2925        3297  04:00:00+00         1688083200   
3               2925        3297  04:00:00+00         1688083200   
4               2925        3297  04:00:00+00         1688083200   

   Pre and Post 2021 Binary  
0                         1  
1                         1  
2                         1  
3                         1  
4                         1  
Index(['Unnamed: 0', 'Sources of Operating Expenses', 'Date', 'Expenses (USD)',
       'ontime_trip_count', 'trip_count', 'Time', 'Pre and Post 2021',
       'Pre and Post 2021 Binary'],
      dtype='object')
   Unnamed: 0   Sources of Debt        Date  Debt (USD)  ontime_trip_count  \
0           0  Commercial paper  2023-06-30      125000               2925   
1           1  Commercial paper  2022-06-30       40000               3104   
2           2  Commercial paper  2021-06-30      200000               2329   
3           3  Commercial paper  2020-06-30       35000               1825   
4           4  Commercial paper  2019-06-30       50000               1914   

   trip_count         Time  Pre and Post 2021  Pre and Post 2021 Binary  
0        3297  04:00:00+00         1688083200                         1  
1        3404  04:00:00+00         1656547200                         0  
2        2727  04:00:00+00         1625011200                         0  
3        1894  04:00:00+00         1593475200                         0  
4        1999  04:00:00+00         1561852800                         0  
Index(['Unnamed: 0', 'Sources of Debt', 'Date', 'Debt (USD)',
       'ontime_trip_count', 'trip_count', 'Time', 'Pre and Post 2021',
       'Pre and Post 2021 Binary'],
      dtype='object')
#drop unneeded columns
netposition_df_drop = netposition_df.drop(["Unnamed: 0"],axis=1)
revenue_df_drop = revenue_df.drop(["Unnamed: 0"],axis=1)
expenses_df_drop = expenses_df.drop(["Unnamed: 0"],axis=1)
debts_df_drop = debts_df.drop(["Unnamed: 0"],axis=1)

Univariate Analysis:

#find outliers and other statistical information using describe() for the net position, revenue, expenses, and debts financial files
print(netposition_df_drop.describe())
print(revenue_df_drop.describe())
print(expenses_df_drop.describe())
print(debts_df_drop.describe())
       Net Position Amounts (USD)  ontime_trip_count   trip_count  \
count                6.500000e+01          65.000000    65.000000   
mean                 1.690963e+06        2419.400000  2664.200000   
std                  1.554446e+06         521.953596   635.363779   
min                  1.671140e+05        1825.000000  1894.000000   
25%                  7.771910e+05        1914.000000  1999.000000   
50%                  1.213708e+06        2329.000000  2727.000000   
75%                  2.013488e+06        2925.000000  3297.000000   
max                  7.603147e+06        3104.000000  3404.000000   

       Pre and Post 2021  Pre and Post 2021 Binary  
count       6.500000e+01                 65.000000  
mean        1.624994e+09                  0.200000  
std         4.497035e+07                  0.403113  
min         1.561853e+09                  0.000000  
25%         1.593475e+09                  0.000000  
50%         1.625011e+09                  0.000000  
75%         1.656547e+09                  0.000000  
max         1.688083e+09                  1.000000  
       Revenue (USD)  ontime_trip_count   trip_count  Pre and Post 2021  \
count      24.000000          24.000000    24.000000       2.400000e+01   
mean   139883.083333        2545.750000  2830.500000       1.640779e+09   
std    155446.313300         516.325163   611.683848       3.601665e+07   
min      3657.000000        1825.000000  1894.000000       1.593475e+09   
25%     38484.500000        2203.000000  2518.750000       1.617127e+09   
50%     83540.000000        2627.000000  3012.000000       1.640779e+09   
75%    183182.000000        2969.750000  3323.750000       1.664431e+09   
max    632578.000000        3104.000000  3404.000000       1.688083e+09   

       Pre and Post 2021 Binary  
count                 24.000000  
mean                   0.250000  
std                    0.442326  
min                    0.000000  
25%                    0.000000  
50%                    0.000000  
75%                    0.250000  
max                    1.000000  
       Expenses (USD)  ontime_trip_count   trip_count  Pre and Post 2021  \
count    3.000000e+01          30.000000    30.000000       3.000000e+01   
mean     7.466914e+05        2419.400000  2664.200000       1.624994e+09   
std      7.328224e+05         526.777032   641.235252       4.538593e+07   
min      2.254800e+04        1825.000000  1894.000000       1.561853e+09   
25%      2.928102e+05        1914.000000  1999.000000       1.593475e+09   
50%      5.444250e+05        2329.000000  2727.000000       1.625011e+09   
75%      8.536322e+05        2925.000000  3297.000000       1.656547e+09   
max      2.353230e+06        3104.000000  3404.000000       1.688083e+09   

       Pre and Post 2021 Binary  
count                 30.000000  
mean                   0.200000  
std                    0.406838  
min                    0.000000  
25%                    0.000000  
50%                    0.000000  
75%                    0.000000  
max                    1.000000  
         Debt (USD)  ontime_trip_count   trip_count  Pre and Post 2021  \
count       5.00000           5.000000     5.000000       5.000000e+00   
mean    90000.00000        2419.400000  2664.200000       1.624994e+09   
std     71501.74823         579.055524   704.872825       4.989012e+07   
min     35000.00000        1825.000000  1894.000000       1.561853e+09   
25%     40000.00000        1914.000000  1999.000000       1.593475e+09   
50%     50000.00000        2329.000000  2727.000000       1.625011e+09   
75%    125000.00000        2925.000000  3297.000000       1.656547e+09   
max    200000.00000        3104.000000  3404.000000       1.688083e+09   

       Pre and Post 2021 Binary  
count                  5.000000  
mean                   0.200000  
std                    0.447214  
min                    0.000000  
25%                    0.000000  
50%                    0.000000  
75%                    0.000000  
max                    1.000000  

Stastistical information of the features in the ride reliability dataset shows some fields have outliers due to the differences in the mean and std of the feature values.

#create histogram plot from the net position dataframe
#numerical columns to make historgram for: 'Net Position Amounts (USD)', 'ontime_trip_count', 'trip_count'
sns.histplot(data = netposition_df_drop, x = "Net Position Amounts (USD)")
plt.show()

sns.histplot(data = netposition_df_drop, x = "ontime_trip_count")
plt.show()

sns.histplot(data = netposition_df_drop, x = "trip_count")
plt.show()

HISTOGRAM ANALYSIS

  • Histogram above shows the distribution of the amounts, trip counts, and number of on time trip counts regarding the net position amounts of the MBTA system from 2019 to 2023. Plots show no significant skewness or trends.
#create historgrams from the revenue dataframe
#numerical columns to use 'Revenue (USD)','ontime_trip_count', 'trip_count'

sns.histplot(data = revenue_df_drop, x = "Revenue (USD)")
plt.show()

sns.histplot(data = revenue_df_drop, x = "ontime_trip_count")
plt.show()

sns.histplot(data = revenue_df_drop, x = "trip_count")
plt.show()

HISTOGRAM ANALYSIS

  • Histogram above shows the distribution of the amounts, trip counts, and number of on time trip counts regarding the revenue amounts of the MBTA system from 2019 to 2023. Plots show no significant skewness or trends.
#create histogram plot from the expenses position dataframe
sns.histplot(data = expenses_df_drop, x = "Expenses (USD)")
plt.show()

sns.histplot(data = expenses_df_drop, x = "ontime_trip_count")
plt.show()

sns.histplot(data = expenses_df_drop, x = "trip_count")
plt.show()

HISTOGRAM ANALYSIS

  • Histogram above shows the distribution of the amounts, trip counts, and number of on time trip counts regarding the expenses amounts of the MBTA system from 2019 to 2023. Plots show no significant skewness or trends.
#create histogram plot from the debts position dataframe
sns.histplot(data = debts_df_drop, x = "Debt (USD)")
plt.show()

sns.histplot(data = debts_df_drop, x = "ontime_trip_count")
plt.show()

sns.histplot(data = debts_df_drop, x = "trip_count")
plt.show()

HISTOGRAM ANALYSIS

  • Histogram above shows the distribution of the amounts, trip counts, and number of on time trip counts regarding the debt amounts of the MBTA system from 2019 to 2023. Plots show no significant skewness or trends.

Bivariate and Multivariate Analysis:

#create correlation matrix of the net position dataframe
#first subset dataframe to only include numerical columns
netposition_subset = netposition_df_drop[['Net Position Amounts (USD)', 'ontime_trip_count', 'trip_count', 'Pre and Post 2021 Binary']]
#create correlation matrix
netposition_corr_matrix = netposition_subset.corr()
print(netposition_corr_matrix)

#create heatmap of dataframe subset
sns.heatmap(netposition_subset)
plt.show() #show heatmap
                            Net Position Amounts (USD)  ontime_trip_count  \
Net Position Amounts (USD)                    1.000000           0.193556   
ontime_trip_count                             0.193556           1.000000   
trip_count                                    0.196662           0.989030   
Pre and Post 2021 Binary                      0.131448           0.488103   

                            trip_count  Pre and Post 2021 Binary  
Net Position Amounts (USD)    0.196662                  0.131448  
ontime_trip_count             0.989030                  0.488103  
trip_count                    1.000000                  0.501858  
Pre and Post 2021 Binary      0.501858                  1.000000  

CORRELATION ANALYSIS

  • The correlation matrix and heatmap of the net position amount dataset shows majority have positive correlation. The ontime_trip_count and trip_count and pre and post 2021 binary features have strong high correalation, so they can be used as predictors for models.
#create correlation matrix of the operating revenues dataframe
#first subset dataframe to only include numerical columns
rev_subset = revenue_df_drop[['Revenue (USD)', 'ontime_trip_count', 'trip_count', 'Pre and Post 2021 Binary']]
#create correlation matrix
rev_corr_matrix = rev_subset.corr()
print(rev_corr_matrix)

#create heatmap of dataframe subset
sns.heatmap(rev_subset)
plt.show() #show heatmap
                          Revenue (USD)  ontime_trip_count  trip_count  \
Revenue (USD)                  1.000000          -0.122285   -0.165140   
ontime_trip_count             -0.122285           1.000000    0.986635   
trip_count                    -0.165140           0.986635    1.000000   
Pre and Post 2021 Binary       0.042144           0.433195    0.449786   

                          Pre and Post 2021 Binary  
Revenue (USD)                             0.042144  
ontime_trip_count                         0.433195  
trip_count                                0.449786  
Pre and Post 2021 Binary                  1.000000  

CORRELATION ANALYSIS

  • The correlation matrix and heatmap of the revenue amount dataset shows majority have positive correlation. The ontime_trip_count and trip_count and pre and post 2021 binary features have strong high correalation, so they can be used as predictors for models.
#create correlation matrix of the operating expenses dataframe
#first subset dataframe to only include numerical columns
exp_subset = expenses_df_drop[['Expenses (USD)', 'ontime_trip_count', 'trip_count', 'Pre and Post 2021 Binary']]
#create correlation matrix
exp_corr_matrix = exp_subset.corr()
print(exp_corr_matrix)

#create heatmap of dataframe subset
sns.heatmap(exp_subset)
plt.show() #show heatmap
                          Expenses (USD)  ontime_trip_count  trip_count  \
Expenses (USD)                  1.000000           0.020716    0.018052   
ontime_trip_count               0.020716           1.000000    0.989030   
trip_count                      0.018052           0.989030    1.000000   
Pre and Post 2021 Binary        0.026175           0.488103    0.501858   

                          Pre and Post 2021 Binary  
Expenses (USD)                            0.026175  
ontime_trip_count                         0.488103  
trip_count                                0.501858  
Pre and Post 2021 Binary                  1.000000  

CORRELATION ANALYSIS

  • The correlation matrix and heatmap of the expenses amount dataset shows majority have positive correlation, but there are more significant weak correlation between ht features. The ontime_trip_count and trip_count and pre and post 2021 binary features have strong high correalation, so they can be used as predictors for models.
#create correlation matrix of the operating expenses dataframe
#first subset dataframe to only include numerical columns
debts_subset = debts_df_drop[['Debt (USD)', 'ontime_trip_count', 'trip_count', 'Pre and Post 2021 Binary']]
#create correlation matrix
debts_corr_matrix = debts_subset.corr()
print(debts_corr_matrix)

#create heatmap of dataframe subset
sns.heatmap(debts_subset)
plt.show() #show heatmap
                          Debt (USD)  ontime_trip_count  trip_count  \
Debt (USD)                  1.000000           0.159588    0.302755   
ontime_trip_count           0.159588           1.000000    0.989030   
trip_count                  0.302755           0.989030    1.000000   
Pre and Post 2021 Binary    0.273638           0.488103    0.501858   

                          Pre and Post 2021 Binary  
Debt (USD)                                0.273638  
ontime_trip_count                         0.488103  
trip_count                                0.501858  
Pre and Post 2021 Binary                  1.000000  

CORRELATION ANALYSIS

  • The correlation matrix and heatmap of the debts amount dataset shows majority have positive correlation. The ontime_trip_count and trip_count and pre and post 2021 binary features have strong high correalation, so they can be used as predictors for models.
#create crosstab of categorical variables
#crosstab variables Net position sources and Dates features
pd.crosstab(netposition_df_drop["Statements of Revenue, Expenses and Changes in Net Position"], netposition_df_drop["Date"])
Date 2019-06-30 2020-06-30 2021-06-30 2022-06-30 2023-06-30
Statements of Revenue, Expenses and Changes in Net Position
Beginning of year, net position 1 1 1 1 1
Capital grants and contributions 1 1 1 1 1
Depreciation and amortization 1 1 1 1 1
End of year, net position 1 1 1 1 1
Increase in net position 1 1 1 1 1
Nonoperating revenue, net 1 1 1 1 1
Operating loss 1 1 1 1 1
Other operating expenses 1 1 1 1 1
Revenue from transportation 1 1 1 1 1
Total operating expenses, excluding depreciation 1 1 1 1 1
Total operating expenses, including depreciation and amortization 1 1 1 1 1
Total operating revenues 1 1 1 1 1
Transportation services 1 1 1 1 1
#crosstab variables Net position amounts and number of subway trips that were on-time
pd.crosstab(netposition_df_drop["Net Position Amounts (USD)"], netposition_df_drop["ontime_trip_count"])
ontime_trip_count 1825 1914 2329 2925 3104
Net Position Amounts (USD)
167114 0 0 1 0 0
206189 0 0 1 0 0
322292 0 0 0 0 1
364229 0 1 0 0 0
376357 0 0 0 1 0
... ... ... ... ... ...
2473146 1 1 0 0 0
3578852 1 0 1 0 0
5146804 0 0 1 0 1
6682694 0 0 0 1 1
7603147 0 0 0 1 0

61 rows × 5 columns

#crosstab variables number of subway trips that were on-time before and after 2022-01-01
pd.crosstab(netposition_df_drop["ontime_trip_count"], netposition_df_drop["Pre and Post 2021 Binary"])
Pre and Post 2021 Binary 0 1
ontime_trip_count
1825 13 0
1914 13 0
2329 13 0
2925 0 13
3104 13 0
#crosstab variables number of subway trips before and after 2022-01-01
pd.crosstab(netposition_df_drop["trip_count"], netposition_df_drop["Pre and Post 2021 Binary"])
Pre and Post 2021 Binary 0 1
trip_count
1894 13 0
1999 13 0
2727 13 0
3297 0 13
3404 13 0

CROSSTAB ANALYSIS

  • Crosstabs were created to analyze if there were patterns between the categorical variables. The crosstabs do not show significant patterns in regards to the trip counts, the number of trips made on time and whether the trip was pre or post 1/1/2021.
#Feature pairings
#create scatter plots between "ontime_trip_count" and Net Position Amounts (USD)
sns.scatterplot(data = netposition_subset, x= "ontime_trip_count", y = "Net Position Amounts (USD)")
plt.title("Subway Ride Number of Trips On Time vs. Net Position Amounts (USD)")
plt.xticks(rotation=45)
plt.xlabel("Number of Trips On Time")
plt.ylabel("Net Position Amounts (USD)")
plt.show()

SCATTER PLOT ANALYSIS

  • Scatter plot shows number of on time trips vs net position amounts. Plot shows no significant relationship between the number of trips made on time and the net position amounts of the MBTA system from 2019 to 20023.
#Feature pairings
#create scatter plots between "ontime_trip_count" and Expenses (USD)
sns.scatterplot(data = exp_subset, x= "ontime_trip_count", y = "Expenses (USD)")
plt.title("Subway Ride Number of Trips On Time vs. Expenses (USD)")
plt.xticks(rotation=45)
plt.xlabel("Number of Trips On Time")
plt.ylabel("Expenses (USD)")
plt.show()

SCATTER PLOT ANALYSIS

  • Scatter plot shows number of on time trips vs expenses amounts. Plot shows no significant relationship between the number of trips made on time and the expenses amounts of the MBTA system from 2019 to 20023.
#Feature pairings
#create scatter plots between "ontime_trip_count" and Revenues (USD)
sns.scatterplot(data = rev_subset, x= "ontime_trip_count", y = "Revenue (USD)")
plt.title("Subway Ride Number of Trips On Time vs. Revenues (USD)")
plt.xticks(rotation=45)
plt.xlabel("Number of Trips On Time")
plt.ylabel("Revenues (USD)")
plt.show()

SCATTER PLOT ANALYSIS

  • Scatter plot shows number of on time trips vs revenue amounts. Plot shows no significant relationship between the number of trips made on time and the revenue amounts of the MBTA system from 2019 to 20023.
#Feature pairings
#create scatter plots between "ontime_trip_count" and Debts (USD)
sns.scatterplot(data = debts_subset, x= "ontime_trip_count", y = "Debt (USD)")
plt.title("Subway Ride Number of Trips On Time vs. Debts (USD)")
plt.xticks(rotation=45)
plt.xlabel("Number of Trips On Time")
plt.ylabel("Debts (USD)")
plt.show()

SCATTER PLOT ANALYSIS

  • Scatter plot shows number of on time trips vs debts amounts. Plot shows no significant relationship between the number of trips made on time and the debt amounts of the MBTA system from 2019 to 20023.

Data Distribution and Normalization:

#create new subset of the netposition_df_drop without the 'Pre and Post 2021' column because it was redundant to the 'Pre and Post 2021 Binary'

#create boxplot of dataframe to visualize dataset distribution
sns.boxplot(data= netposition_subset)
plt.title("Distribution of Numerical Features in the Net Position Financial Dataset")
plt.xlabel("Numerical Features")
plt.ylabel("Dataset Quartiles")
plt.xticks(rotation=90)
plt.show()

BOX PLOT ANALYSIS

  • Boxplot shows the distribution of the numerical features in the net position amount datasets. Plot shows some outliers in the Net postiion (usd) feature.
#create new subset of the expenses_df_drop without the 'Pre and Post 2021' column because it was redundant to the 'Pre and Post 2021 Binary'

#create boxplot of dataframe to visualize dataset distribution
sns.boxplot(data= exp_subset)
plt.title("Distribution of Numerical Features in the Expenses Financial Dataset")
plt.xlabel("Numerical Features")
plt.ylabel("Dataset Quartiles")
plt.xticks(rotation=90)
plt.show()

BOX PLOT ANALYSIS

  • Boxplot shows the distribution of the numerical features in the expenses amount datasets. Plot shows some outliers in the expenses (usd) feature.
#create new subset of the expenses_df_drop without the 'Pre and Post 2021' column because it was redundant to the 'Pre and Post 2021 Binary'

#create boxplot of dataframe to visualize dataset distribution
sns.boxplot(data= rev_subset)
plt.title("Distribution of Numerical Features in the Revenue Financial Dataset")
plt.xlabel("Numerical Features")
plt.ylabel("Dataset Quartiles")
plt.xticks(rotation=90)
plt.show()

BOX PLOT ANALYSIS

  • Boxplot shows the distribution of the numerical features in the revenue amount datasets. Plot shows some outliers in the revenue (usd) feature.
#create new subset of the expenses_df_drop without the 'Pre and Post 2021' column because it was redundant to the 'Pre and Post 2021 Binary'

#create boxplot of dataframe to visualize dataset distribution
sns.boxplot(data= debts_subset)
plt.title("Distribution of Numerical Features in the Debts Financial Dataset")
plt.xlabel("Numerical Features")
plt.ylabel("Dataset Quartiles")
plt.xticks(rotation=90)
plt.show()

BOX PLOT ANALYSIS

  • Boxplot shows the distribution of the numerical features in the debt amount datasets. Plot shows some outliers in the debt (usd) feature.
#apply z-score normalization on subset of dataframe that only includes numerical values
#create subset of the netposition_subset dataframe to only keep the "ontime_trip_count" and the "Net Position Amounts (USD)" columns
netposition_subset2 = netposition_subset[["ontime_trip_count", "Net Position Amounts (USD)"]]

#convert subset dataframe into an array
netposition_array = np.array(netposition_subset2)

#compute z-score normalization transformation on array of dataframe subset (x-mean)/standard deviation
normalized_netposition = (netposition_array - np.mean(netposition_array) / np.std(netposition_array))
#print(normalized_netposition)
#scatter plot of original data of the "ontime_trip_count" and the "Net Position Amounts (USD)" columns before normalization transformation
plt.scatter(netposition_subset["ontime_trip_count"], netposition_subset["Net Position Amounts (USD)"])
plt.title("Before Normalization Transformation: Number of On Time Trips vs. Net Position Amounts (USD)")
plt.xlabel("Number of On Time Trips")
plt.ylabel("Net Position Amounts (USD)")
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_netposition[:,0], normalized_netposition[:,1])
plt.title("After Normalization Transformation: Number of On Time Trips vs. Net Position Amounts (USD)")
plt.xlabel("Number of On Time Trips")
plt.ylabel("Net Position Amounts (USD)")
plt.show()

NORMALIZATION ANALYSIS

  • z-score normalization was applied to the net position dataset due to the outliers. The scatter plot shows the data points of the number of trips vs net position amounts before and after the normalization. Plot showed no siginficat difference in layout of the datapoints, so we decided to not continue to use the normalization of the data.

Statistical Insights:

#create new subset of the net position subset data
#drop the "Pre and Post 2021 Binary" column so when fitting to the logistic regression model, it does not cause a perfect prediction
netposition_subset2 = netposition_subset.drop("Pre and Post 2021 Binary", axis=1)
#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  column of dataset
log_netposition_model = sm.Logit(netposition_subset["Pre and Post 2021 Binary"], sm.add_constant(netposition_subset2)).fit()
#print logistic model summary
print(log_netposition_model.summary())

#Due to perfect prediction warning message, we decided to not create further logistic regression models
Warning: Maximum number of iterations has been exceeded.
         Current function value: 0.000000
         Iterations: 35
                              Logit Regression Results                              
====================================================================================
Dep. Variable:     Pre and Post 2021 Binary   No. Observations:                   65
Model:                                Logit   Df Residuals:                       61
Method:                                 MLE   Df Model:                            3
Date:                      Fri, 13 Dec 2024   Pseudo R-squ.:                   1.000
Time:                              20:39:17   Log-Likelihood:            -1.9287e-08
converged:                            False   LL-Null:                       -32.526
Covariance Type:                  nonrobust   LLR p-value:                 4.888e-14
==============================================================================================
                                 coef    std err          z      P>|z|      [0.025      0.975]
----------------------------------------------------------------------------------------------
const                       -621.1993   2.09e+05     -0.003      0.998   -4.11e+05    4.09e+05
Net Position Amounts (USD)  1.615e-08      0.004    4.3e-06      1.000      -0.007       0.007
ontime_trip_count             -0.7558    279.695     -0.003      0.998    -548.947     547.436
trip_count                     0.8653    309.222      0.003      0.998    -605.199     606.929
==============================================================================================

Complete Separation: The results show that there iscomplete separation or perfect prediction.
In this case the Maximum Likelihood Estimator does not exist and the parameters
are not identified.
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/discrete/discrete_model.py:227: PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified
  warnings.warn(msg, category=PerfectSeparationWarning)
/opt/anaconda3/envs/finalportfolio_dsan5000/lib/python3.13/site-packages/statsmodels/base/model.py:607: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals
  warnings.warn("Maximum Likelihood optimization failed to "
#create new subset of the expenses subset data
#drop the "Pre and Post 2021 Binary" column so when fitting to the logistic regression model, it does not cause a perfect prediction
expenses_subset2 = exp_subset.drop("Pre and Post 2021 Binary", axis=1)

#Logistic regression model
#determine relationship between the number of predictions and accurate predictions with binary values of the dates

#fit logistic regression model onto the Pre and Post 2021 Binary and  column of dataset
#log_expenses_model = sm.Logit(exp_subset["Pre and Post 2021 Binary"], sm.add_constant(expenses_subset2)).fit()
#print logistic model summary
#print(log_expenses_model.summary())

#Decided to not create a logistic regression model of the expenses financial dataset with "Pre and Post 2021 Binary" as predictor due to perfect preditiction warning
#create new subset of the revenue subset data
#drop the "Pre and Post 2021 Binary" column so when fitting to the logistic regression model, it does not cause a perfect prediction
revenue_subset2 = rev_subset.drop("Pre and Post 2021 Binary", axis=1)

#Logistic regression model
#determine relationship between the number of predictions and accurate predictions with binary values of the dates
#fit logistic regression model onto the Pre and Post 2021 Binary and  column of dataset
#log_revenue_model = sm.Logit(rev_subset["Pre and Post 2021 Binary"], sm.add_constant(revenue_subset2)).fit()
#print logistic model summary
#print(log_revenue_model.summary())

#Decided to not create a logistic regression model of the revenue financial dataset with "Pre and Post 2021 Binary" as predictor due to perfect preditiction warning
#create new subset of the debt subset data
#drop the "Pre and Post 2021 Binary" column so when fitting to the logistic regression model, it does not cause a perfect prediction
debt_subset2 = debts_subset.drop("Pre and Post 2021 Binary", axis=1)

#Logistic regression model
#determine relationship between the number of predictions and accurate predictions with binary values of the dates
#fit logistic regression model onto the Pre and Post 2021 Binary and  column of dataset
#log_debt_model = sm.Logit(debts_subset["Pre and Post 2021 Binary"], sm.add_constant(debt_subset2)).fit()
#print logistic model summary
#print(log_debt_model.summary())

#Decided to not create a logistic regression model of the Debts financial dataset with "Pre and Post 2021 Binary" as predictor due to perfect preditiction warning

NOTE: We attempted to fit logistic regression model onto the net position, expenses, revenue, and debts dataset to predict the event of the trip occurring pre or post 1/1/2021. However, we got “PerfectSeparationWarning: Perfect separation or prediction detected, parameter may not be identified” warnings from the model output, so the logistic regression model was not the best fit for these datasets.

#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 "Net Position Amounts (USD)" features
t_stat, p_value = stats.ttest_ind(netposition_subset["Pre and Post 2021 Binary"], netposition_subset["Net Position Amounts (USD)"])
print("T-statistic: ", t_stat) #print t-statistic
print("p-value: ", p_value) #print p-value
T-statistic:  -8.770311565076161
p-value:  9.443422513225784e-15
#two-sample t-test

#perform two-sample t-test on the "ontime_trip_count" and "Net Position Amounts (USD)" features
t_stat2, p_value2 = stats.ttest_ind(netposition_subset["ontime_trip_count"], netposition_subset["Net Position Amounts (USD)"])
print("T-statistic: ", t_stat2) #print t-statistic
print("p-value: ", p_value2) #print p-value
T-statistic:  -8.75776369811067
p-value:  1.0123982472342289e-14
#two-sample t-test
#perform two-sample t-test on the "ontime_trip_count" and "Expenses (USD)" features
t_stat3, p_value3 = stats.ttest_ind(exp_subset["ontime_trip_count"], exp_subset["Expenses (USD)"])
print("T-statistic: ", t_stat3) #print t-statistic
print("p-value: ", p_value3) #print p-value
T-statistic:  -5.562800200509888
p-value:  7.077394346859824e-07
#two-sample t-test
#perform two-sample t-test on the "ontime_trip_count" and "Revenues (USD)" features
t_stat4, p_value4 = stats.ttest_ind(rev_subset["ontime_trip_count"], rev_subset["Revenue (USD)"])
print("T-statistic: ", t_stat4) #print t-statistic
print("p-value: ", p_value4) #print p-value
T-statistic:  -4.328240746564412
p-value:  8.03339895840829e-05
#two-sample t-test
#perform two-sample t-test on the "ontime_trip_count" and "Debts (USD)" features
t_stat5, p_value5 = stats.ttest_ind(debts_subset["ontime_trip_count"], debts_subset["Debt (USD)"])
print("T-statistic: ", t_stat5) #print t-statistic
print("p-value: ", p_value5) #print p-value
T-statistic:  -2.7388107043103282
p-value:  0.025496067137814025

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 “ontime_trip_count” feature and the “Net Position Amounts (USD)”, “Expenses (USD)”, “Revenue (USD)”, and “Debt (USD)” have negative t-values and low p-values. Which implyies that the null hypothesis can be rejected there are some differences in the means of the feature groups as compared to other groups.

  • In addition, the p-value for the t-test result of the “ontime_trip_count” feature with the “Debt (USD)” is rather siginificant. So the Debts dataset can be taken for further analysis.

#Create an ANOVA Analysis of Variance model
#compare means of three or more groups in the net position dataset

import scipy.stats as stats #import library

netposition = netposition_df_drop["Net Position Amounts (USD)"]
ontime = netposition_df_drop["ontime_trip_count"]
trip = netposition_df_drop["trip_count"]
binary_date = netposition_df_drop["Pre and Post 2021 Binary"]

f_stat, p_value = stats.f_oneway(netposition , ontime, trip, binary_date)

print(f_stat, p_value)
76.76442677882271 1.8994029522412697e-35
#Create an ANOVA Analysis of Variance model
#compare means of three or more groups in the revenue dataset

revenue = revenue_df_drop["Revenue (USD)"]
rev_ontime = revenue_df_drop["ontime_trip_count"]
rev_trip = revenue_df_drop["trip_count"]
rev_binary_date = revenue_df_drop["Pre and Post 2021 Binary"]

f_stat_rev, p_value_rev = stats.f_oneway(revenue , rev_ontime, rev_trip, rev_binary_date)

print(f_stat_rev, p_value_rev)
18.945957639590752 1.1885808140875508e-09
#Create an ANOVA Analysis of Variance model
#compare means of three or more groups in the expenses dataset

expenses = expenses_df_drop["Expenses (USD)"]
exp_ontime = expenses_df_drop["ontime_trip_count"]
exp_trip = expenses_df_drop["trip_count"]
exp_binary_date = expenses_df_drop["Pre and Post 2021 Binary"]

f_stat_exp, p_value_exp = stats.f_oneway(expenses, exp_ontime, exp_trip, exp_binary_date)

print(f_stat_exp, p_value_exp)
31.005344983232142 8.586503361300924e-15
#Create an ANOVA Analysis of Variance model
#compare means of three or more groups in the debts dataset

debts = debts_df_drop["Debt (USD)"]
debt_ontime = debts_df_drop["ontime_trip_count"]
debt_trip = debts_df_drop["trip_count"]
debt_binary_date = debts_df_drop["Pre and Post 2021 Binary"]

f_stat_debt, p_value_debt = stats.f_oneway(debts, debt_ontime, debt_trip, debt_binary_date)

print(f_stat_debt, p_value_debt)
7.6306656525419 0.002180526274083107

ANOVA ANALYSIS

  • ANOVA models were created to compare the means of the features in the net positions, expenses, revenues, and debts datasets to see if there were significant differences between the features. The f-statistics from the ANOVA model are majority large, which implies that the feature means are more different from each than to their own group.

Data Visualization and Storytelling:

print(netposition_df_drop.columns)
print(revenue_df_drop.columns)
Index(['Statements of Revenue, Expenses and Changes in Net Position', 'Date',
       'Net Position Amounts (USD)', 'ontime_trip_count', 'trip_count', 'Time',
       'Pre and Post 2021', 'Pre and Post 2021 Binary'],
      dtype='object')
Index(['Sources of Operating Revenue', 'Date', 'Revenue (USD)',
       'ontime_trip_count', 'trip_count', 'Time', 'Pre and Post 2021',
       'Pre and Post 2021 Binary'],
      dtype='object')
#Create bar graphs of net position dataset
sns.barplot(data = netposition_df_drop, y = 'Net Position Amounts (USD)', x = 'Statements of Revenue, Expenses and Changes in Net Position', hue = 'Pre and Post 2021 Binary')
plt.title("Net Position Amounts Before and After 2022")
plt.xticks(rotation=90)
plt.xlabel("Sources of Net Position")
plt.ylabel("Net Position Amounts (USD)")
plt.show()

#Create bar graphs of revenue dataset
sns.barplot(data = revenue_df_drop, y = 'Revenue (USD)', x = 'Sources of Operating Revenue', hue = 'Pre and Post 2021 Binary')
plt.title("Net Position Amounts Before and After 2022")
plt.xticks(rotation=90)
plt.xlabel("Sources of Revenue")
plt.ylabel("Revenue Amounts (USD)")
plt.show()

#Create bar graphs of expenses dataset
sns.barplot(data = expenses_df_drop, y = 'Expenses (USD)', x = 'Sources of Operating Expenses', hue = 'Pre and Post 2021 Binary')
plt.title("Expenses Amounts Before and After 2022")
plt.xticks(rotation=90)
plt.xlabel("Sources of Expenses")
plt.ylabel("Expenses Amounts (USD)")
plt.show()


#Create bar graphs of debt dataset
sns.barplot(data = debts_df_drop, y = 'Debt (USD)', x = 'Sources of Debt', hue = 'Pre and Post 2021 Binary')
plt.title("Debt Amounts Before and After 2022")
plt.xticks(rotation=90)
plt.xlabel("Sources of Debt")
plt.ylabel("Debt Amounts (USD)")
plt.show()

BAR PLOT ANALYSIS

  • Bar plots show the trend of net position, expenses, revenues, and debt amounts from 2019 to 2023 and grouped by pre and post 1/1/2021. Majority of the bar plots show that post 2021 had higher amounts of net position, expenses, revenues, and debts regarding the MBTA system.
#Create scatter plot of net position dataset
sns.scatterplot(data = netposition_df_drop, y = 'Net Position Amounts (USD)', x = 'ontime_trip_count', hue = 'Pre and Post 2021 Binary')
plt.title("Net Position Amounts vs. Number of Subway Rides on Time Before and After 2022")
plt.xlabel("Number of On Time Trips")
plt.ylabel("Net Position Amounts (USD)")
plt.show()

#Create scatter plot of revenue dataset
sns.scatterplot(data = revenue_df_drop, y = 'Revenue (USD)', x = 'ontime_trip_count', hue = 'Pre and Post 2021 Binary')
plt.title("Revenue Amounts vs. Number of Subway Rides on Time Before and After 2022")
plt.xlabel("Number of On Time Trips")
plt.ylabel("Revenue Amounts (USD)")
plt.show()

#Create scatter plot of expenses dataset
sns.scatterplot(data = expenses_df_drop, y = 'Expenses (USD)', x = 'ontime_trip_count', hue = 'Pre and Post 2021 Binary')
plt.title("Expenses Amounts vs. Number of Subway Rides on Time Before and After 2022")
plt.xlabel("Number of On Time Trips")
plt.ylabel("Expenses Amounts (USD)")
plt.show()

#Create scatter plot of debts dataset
sns.scatterplot(data = debts_df_drop, y = 'Debt (USD)', x = 'ontime_trip_count', hue = 'Pre and Post 2021 Binary')
plt.title("Debt Amounts vs. Number of Subway Rides on Time Before and After 2022")
plt.xlabel("Number of On Time Trips")
plt.ylabel("Debt Amounts (USD)")
plt.show()

SCATTER PLOT ANALYSIS

  • Scatter plots above were created to try to find relationshup between the number of trips made on time vs. amounts in the net postion, expenses, revenues, and debt datasets and grouped by pre and post 2021. Scatter plots showed no significant correlation or relationship between the two features.