Download the Report
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.1.4 ✔ readr 2.1.5
✔ forcats 1.0.0 ✔ stringr 1.5.1
✔ ggplot2 3.5.1 ✔ tibble 3.2.1
✔ lubridate 1.9.4 ✔ tidyr 1.3.1
✔ purrr 1.0.4
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library (ggplot2)library (dplyr)<- read.csv ("C:/Users/nicol/Downloads/fy2019-20 (1).csv" )
# Convert risk columns (columns 13:23) into factors # Standardize levels: ALL CAPS 13 ] <- as.factor (data[,13 ])levels (data[,13 ])<- c ("LOW" , "MODERATE" , "HIGH" )for (i in 0 : 9 ){13 + i] <- factor (data[,13 + i], order= TRUE , levels = c ("LOW" , "MODERATE" , "HIGH" ))
Column num, Items 1: City Names 2-12: Rank 13-23: Risk 24-34: Points 35-44: Ratio 45-66: Other Numeric Data
# Numeric Items <- data[, 24 : 34 ]<- data[, 35 : 44 ]<- data[, 45 : 66 ]
1. Exploratory Data Analysis:
Correlation Analysis:
Consider take out some of the highly correlated variables when modeling
Correlation Analysis Using Ratios:
# Correlation <- cor (ratio, use = "complete.obs" )
General_Fund_Reserves_Ratio Debt_Burden_Ratio
General_Fund_Reserves_Ratio 1.00000000 0.005155050
Debt_Burden_Ratio 0.00515505 1.000000000
Liquidity_Ratio 0.33811237 -0.017499614
Revenue_Trends_Ratio 0.04393613 0.046310907
Pension_Obligations_Ratio -0.15747725 0.045011789
Pension_Funding_Ratio 0.13377685 -0.101198033
Pension_Costs_Ratio -0.20640017 0.018499816
Future_Pension_Costs_Ratio -0.18872225 0.009290444
OPEB_Obligations_Ratio -0.06737789 0.068615098
OPEB_Funding_Ratio 0.19531425 -0.093349356
Liquidity_Ratio Revenue_Trends_Ratio
General_Fund_Reserves_Ratio 0.338112370 0.043936126
Debt_Burden_Ratio -0.017499614 0.046310907
Liquidity_Ratio 1.000000000 -0.001968421
Revenue_Trends_Ratio -0.001968421 1.000000000
Pension_Obligations_Ratio -0.220281682 -0.039903799
Pension_Funding_Ratio 0.157083948 -0.077086569
Pension_Costs_Ratio -0.214179610 -0.019395935
Future_Pension_Costs_Ratio -0.209027927 -0.049089502
OPEB_Obligations_Ratio -0.084776650 -0.046797256
OPEB_Funding_Ratio 0.255285406 -0.107584591
Pension_Obligations_Ratio Pension_Funding_Ratio
General_Fund_Reserves_Ratio -0.15747725 0.13377685
Debt_Burden_Ratio 0.04501179 -0.10119803
Liquidity_Ratio -0.22028168 0.15708395
Revenue_Trends_Ratio -0.03990380 -0.07708657
Pension_Obligations_Ratio 1.00000000 -0.28673182
Pension_Funding_Ratio -0.28673182 1.00000000
Pension_Costs_Ratio 0.81012988 -0.42052344
Future_Pension_Costs_Ratio 0.79928352 -0.57846759
OPEB_Obligations_Ratio 0.38775799 -0.06788278
OPEB_Funding_Ratio -0.25837661 0.15871182
Pension_Costs_Ratio Future_Pension_Costs_Ratio
General_Fund_Reserves_Ratio -0.20640017 -0.188722254
Debt_Burden_Ratio 0.01849982 0.009290444
Liquidity_Ratio -0.21417961 -0.209027927
Revenue_Trends_Ratio -0.01939593 -0.049089502
Pension_Obligations_Ratio 0.81012988 0.799283517
Pension_Funding_Ratio -0.42052344 -0.578467593
Pension_Costs_Ratio 1.00000000 0.851604010
Future_Pension_Costs_Ratio 0.85160401 1.000000000
OPEB_Obligations_Ratio 0.27051688 0.278913672
OPEB_Funding_Ratio -0.20429957 -0.209207470
OPEB_Obligations_Ratio OPEB_Funding_Ratio
General_Fund_Reserves_Ratio -0.06737789 0.19531425
Debt_Burden_Ratio 0.06861510 -0.09334936
Liquidity_Ratio -0.08477665 0.25528541
Revenue_Trends_Ratio -0.04679726 -0.10758459
Pension_Obligations_Ratio 0.38775799 -0.25837661
Pension_Funding_Ratio -0.06788278 0.15871182
Pension_Costs_Ratio 0.27051688 -0.20429957
Future_Pension_Costs_Ratio 0.27891367 -0.20920747
OPEB_Obligations_Ratio 1.00000000 -0.38722256
OPEB_Funding_Ratio -0.38722256 1.00000000
Correlation Analysis Using Other Numeric Variables:
# Correlation <- cor (numeric, use = "complete.obs" )
Population
Population 1.00000000
Unemployment_rate 0.10458595
General_Fund_Cash_and_Investments 0.57453187
General_Fund_Liabilities 0.60112910
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.67300823
Government_Wide_Revenue 0.83252773
Change_GF_Unrestricted_Fund_Balances -0.05122791
GF_Unrestricted_Fund_Balances 0.59262806
GF_Expenditures_Transfers_Out 0.83192423
GF_Unrestricted_FB_PY_PY 0.53903592
GF_Unrestricted_FB_PY 0.60847563
GF_Unrestricted_FB_CY 0.59262806
GF_Rev_PY_PY 0.82733012
GF_Rev_PY 0.82976920
GF_Rev_CY 0.84695900
Net_Pension_Liability_or_Asset 0.81784437
Pension_Related_Debt 0.32108175
Pension_Assets 0.82653615
Accrued_Pension_Liabilities 0.82658650
Actuarially_Determined_Pension_Contributions 0.81326159
Projected_Pension_Contribution 0.81969997
Net_OPEB_Liability_or_Asset 0.54888106
Unemployment_rate
Population 0.1045859539
Unemployment_rate 1.0000000000
General_Fund_Cash_and_Investments -0.0542933134
General_Fund_Liabilities 0.0212190349
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.0386830382
Government_Wide_Revenue 0.0039532113
Change_GF_Unrestricted_Fund_Balances -0.0562638842
GF_Unrestricted_Fund_Balances -0.0648134571
GF_Expenditures_Transfers_Out 0.0098504456
GF_Unrestricted_FB_PY_PY -0.0586534938
GF_Unrestricted_FB_PY -0.0600948138
GF_Unrestricted_FB_CY -0.0648134571
GF_Rev_PY_PY 0.0044085224
GF_Rev_PY 0.0004024498
GF_Rev_CY 0.0031155336
Net_Pension_Liability_or_Asset 0.0220704718
Pension_Related_Debt 0.0969817437
Pension_Assets 0.0436271464
Accrued_Pension_Liabilities 0.0345197300
Actuarially_Determined_Pension_Contributions 0.0183949195
Projected_Pension_Contribution 0.0153072919
Net_OPEB_Liability_or_Asset 0.0708310557
General_Fund_Cash_and_Investments
Population 0.57453187
Unemployment_rate -0.05429331
General_Fund_Cash_and_Investments 1.00000000
General_Fund_Liabilities 0.82028822
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.40020185
Government_Wide_Revenue 0.66020381
Change_GF_Unrestricted_Fund_Balances -0.04356678
GF_Unrestricted_Fund_Balances 0.81081400
GF_Expenditures_Transfers_Out 0.79730538
GF_Unrestricted_FB_PY_PY 0.84175499
GF_Unrestricted_FB_PY 0.88359469
GF_Unrestricted_FB_CY 0.81081400
GF_Rev_PY_PY 0.81259230
GF_Rev_PY 0.81246633
GF_Rev_CY 0.80984833
Net_Pension_Liability_or_Asset 0.70085542
Pension_Related_Debt 0.34136911
Pension_Assets 0.67024018
Accrued_Pension_Liabilities 0.69290692
Actuarially_Determined_Pension_Contributions 0.74376035
Projected_Pension_Contribution 0.69843112
Net_OPEB_Liability_or_Asset 0.62947284
General_Fund_Liabilities
Population 0.60112910
Unemployment_rate 0.02121903
General_Fund_Cash_and_Investments 0.82028822
General_Fund_Liabilities 1.00000000
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.51213626
Government_Wide_Revenue 0.68284422
Change_GF_Unrestricted_Fund_Balances -0.02621429
GF_Unrestricted_Fund_Balances 0.49972955
GF_Expenditures_Transfers_Out 0.82519244
GF_Unrestricted_FB_PY_PY 0.54698719
GF_Unrestricted_FB_PY 0.58521821
GF_Unrestricted_FB_CY 0.49972955
GF_Rev_PY_PY 0.83150876
GF_Rev_PY 0.82761768
GF_Rev_CY 0.82464213
Net_Pension_Liability_or_Asset 0.78867980
Pension_Related_Debt 0.44621468
Pension_Assets 0.75632879
Accrued_Pension_Liabilities 0.78147285
Actuarially_Determined_Pension_Contributions 0.83237643
Projected_Pension_Contribution 0.77097538
Net_OPEB_Liability_or_Asset 0.76640255
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations
Population 0.67300823
Unemployment_rate 0.03868304
General_Fund_Cash_and_Investments 0.40020185
General_Fund_Liabilities 0.51213626
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 1.00000000
Government_Wide_Revenue 0.86977191
Change_GF_Unrestricted_Fund_Balances -0.02268537
GF_Unrestricted_Fund_Balances 0.41945346
GF_Expenditures_Transfers_Out 0.70755784
GF_Unrestricted_FB_PY_PY 0.42409677
GF_Unrestricted_FB_PY 0.45766423
GF_Unrestricted_FB_CY 0.41945346
GF_Rev_PY_PY 0.70618594
GF_Rev_PY 0.70353951
GF_Rev_CY 0.68849011
Net_Pension_Liability_or_Asset 0.72984540
Pension_Related_Debt 0.31398876
Pension_Assets 0.82293903
Accrued_Pension_Liabilities 0.79242509
Actuarially_Determined_Pension_Contributions 0.74042984
Projected_Pension_Contribution 0.75281756
Net_OPEB_Liability_or_Asset 0.37126679
Government_Wide_Revenue
Population 0.832527732
Unemployment_rate 0.003953211
General_Fund_Cash_and_Investments 0.660203806
General_Fund_Liabilities 0.682844220
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.869771907
Government_Wide_Revenue 1.000000000
Change_GF_Unrestricted_Fund_Balances -0.039044249
GF_Unrestricted_Fund_Balances 0.645404929
GF_Expenditures_Transfers_Out 0.920877340
GF_Unrestricted_FB_PY_PY 0.645988710
GF_Unrestricted_FB_PY 0.696345139
GF_Unrestricted_FB_CY 0.645404929
GF_Rev_PY_PY 0.917778297
GF_Rev_PY 0.920687066
GF_Rev_CY 0.916714320
Net_Pension_Liability_or_Asset 0.919065377
Pension_Related_Debt 0.340129269
Pension_Assets 0.947113085
Accrued_Pension_Liabilities 0.943154540
Actuarially_Determined_Pension_Contributions 0.925837620
Projected_Pension_Contribution 0.934939413
Net_OPEB_Liability_or_Asset 0.592023702
Change_GF_Unrestricted_Fund_Balances
Population -0.05122791
Unemployment_rate -0.05626388
General_Fund_Cash_and_Investments -0.04356678
General_Fund_Liabilities -0.02621429
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations -0.02268537
Government_Wide_Revenue -0.03904425
Change_GF_Unrestricted_Fund_Balances 1.00000000
GF_Unrestricted_Fund_Balances -0.01499481
GF_Expenditures_Transfers_Out -0.04651847
GF_Unrestricted_FB_PY_PY -0.05422690
GF_Unrestricted_FB_PY -0.05574819
GF_Unrestricted_FB_CY -0.01499481
GF_Rev_PY_PY -0.04722298
GF_Rev_PY -0.04739149
GF_Rev_CY -0.04687453
Net_Pension_Liability_or_Asset -0.03771912
Pension_Related_Debt -0.01242512
Pension_Assets -0.03674397
Accrued_Pension_Liabilities -0.03705983
Actuarially_Determined_Pension_Contributions -0.03689249
Projected_Pension_Contribution -0.03781699
Net_OPEB_Liability_or_Asset -0.03009563
GF_Unrestricted_Fund_Balances
Population 0.59262806
Unemployment_rate -0.06481346
General_Fund_Cash_and_Investments 0.81081400
General_Fund_Liabilities 0.49972955
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.41945346
Government_Wide_Revenue 0.64540493
Change_GF_Unrestricted_Fund_Balances -0.01499481
GF_Unrestricted_Fund_Balances 1.00000000
GF_Expenditures_Transfers_Out 0.70002638
GF_Unrestricted_FB_PY_PY 0.88375324
GF_Unrestricted_FB_PY 0.91987988
GF_Unrestricted_FB_CY 1.00000000
GF_Rev_PY_PY 0.70933864
GF_Rev_PY 0.71031419
GF_Rev_CY 0.71160927
Net_Pension_Liability_or_Asset 0.58238500
Pension_Related_Debt 0.21226839
Pension_Assets 0.59335052
Accrued_Pension_Liabilities 0.59117154
Actuarially_Determined_Pension_Contributions 0.60444510
Projected_Pension_Contribution 0.59581107
Net_OPEB_Liability_or_Asset 0.39824983
GF_Expenditures_Transfers_Out
Population 0.831924234
Unemployment_rate 0.009850446
General_Fund_Cash_and_Investments 0.797305376
General_Fund_Liabilities 0.825192441
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.707557837
Government_Wide_Revenue 0.920877340
Change_GF_Unrestricted_Fund_Balances -0.046518470
GF_Unrestricted_Fund_Balances 0.700026383
GF_Expenditures_Transfers_Out 1.000000000
GF_Unrestricted_FB_PY_PY 0.739954850
GF_Unrestricted_FB_PY 0.776593988
GF_Unrestricted_FB_CY 0.700026383
GF_Rev_PY_PY 0.993869518
GF_Rev_PY 0.993394697
GF_Rev_CY 0.992036052
Net_Pension_Liability_or_Asset 0.943431022
Pension_Related_Debt 0.408407662
Pension_Assets 0.941921177
Accrued_Pension_Liabilities 0.950516198
Actuarially_Determined_Pension_Contributions 0.962830312
Projected_Pension_Contribution 0.948492219
Net_OPEB_Liability_or_Asset 0.709776285
GF_Unrestricted_FB_PY_PY
Population 0.53903592
Unemployment_rate -0.05865349
General_Fund_Cash_and_Investments 0.84175499
General_Fund_Liabilities 0.54698719
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.42409677
Government_Wide_Revenue 0.64598871
Change_GF_Unrestricted_Fund_Balances -0.05422690
GF_Unrestricted_Fund_Balances 0.88375324
GF_Expenditures_Transfers_Out 0.73995485
GF_Unrestricted_FB_PY_PY 1.00000000
GF_Unrestricted_FB_PY 0.97575550
GF_Unrestricted_FB_CY 0.88375324
GF_Rev_PY_PY 0.74687428
GF_Rev_PY 0.74036509
GF_Rev_CY 0.72794307
Net_Pension_Liability_or_Asset 0.58621253
Pension_Related_Debt 0.16759023
Pension_Assets 0.60243181
Accrued_Pension_Liabilities 0.60181214
Actuarially_Determined_Pension_Contributions 0.61746451
Projected_Pension_Contribution 0.61646760
Net_OPEB_Liability_or_Asset 0.38179034
GF_Unrestricted_FB_PY
Population 0.60847563
Unemployment_rate -0.06009481
General_Fund_Cash_and_Investments 0.88359469
General_Fund_Liabilities 0.58521821
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.45766423
Government_Wide_Revenue 0.69634514
Change_GF_Unrestricted_Fund_Balances -0.05574819
GF_Unrestricted_Fund_Balances 0.91987988
GF_Expenditures_Transfers_Out 0.77659399
GF_Unrestricted_FB_PY_PY 0.97575550
GF_Unrestricted_FB_PY 1.00000000
GF_Unrestricted_FB_CY 0.91987988
GF_Rev_PY_PY 0.78388183
GF_Rev_PY 0.78251955
GF_Rev_CY 0.77501546
Net_Pension_Liability_or_Asset 0.63809415
Pension_Related_Debt 0.22281616
Pension_Assets 0.64363901
Accrued_Pension_Liabilities 0.64556000
Actuarially_Determined_Pension_Contributions 0.66738140
Projected_Pension_Contribution 0.65684089
Net_OPEB_Liability_or_Asset 0.44651167
GF_Unrestricted_FB_CY
Population 0.59262806
Unemployment_rate -0.06481346
General_Fund_Cash_and_Investments 0.81081400
General_Fund_Liabilities 0.49972955
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.41945346
Government_Wide_Revenue 0.64540493
Change_GF_Unrestricted_Fund_Balances -0.01499481
GF_Unrestricted_Fund_Balances 1.00000000
GF_Expenditures_Transfers_Out 0.70002638
GF_Unrestricted_FB_PY_PY 0.88375324
GF_Unrestricted_FB_PY 0.91987988
GF_Unrestricted_FB_CY 1.00000000
GF_Rev_PY_PY 0.70933864
GF_Rev_PY 0.71031419
GF_Rev_CY 0.71160927
Net_Pension_Liability_or_Asset 0.58238500
Pension_Related_Debt 0.21226839
Pension_Assets 0.59335052
Accrued_Pension_Liabilities 0.59117154
Actuarially_Determined_Pension_Contributions 0.60444510
Projected_Pension_Contribution 0.59581107
Net_OPEB_Liability_or_Asset 0.39824983
GF_Rev_PY_PY
Population 0.827330122
Unemployment_rate 0.004408522
General_Fund_Cash_and_Investments 0.812592300
General_Fund_Liabilities 0.831508760
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.706185937
Government_Wide_Revenue 0.917778297
Change_GF_Unrestricted_Fund_Balances -0.047222975
GF_Unrestricted_Fund_Balances 0.709338642
GF_Expenditures_Transfers_Out 0.993869518
GF_Unrestricted_FB_PY_PY 0.746874284
GF_Unrestricted_FB_PY 0.783881834
GF_Unrestricted_FB_CY 0.709338642
GF_Rev_PY_PY 1.000000000
GF_Rev_PY 0.998179565
GF_Rev_CY 0.994215761
Net_Pension_Liability_or_Asset 0.944384484
Pension_Related_Debt 0.396606511
Pension_Assets 0.939698514
Accrued_Pension_Liabilities 0.950509065
Actuarially_Determined_Pension_Contributions 0.964981424
Projected_Pension_Contribution 0.951174292
Net_OPEB_Liability_or_Asset 0.719772928
GF_Rev_PY
Population 0.8297692013
Unemployment_rate 0.0004024498
General_Fund_Cash_and_Investments 0.8124663319
General_Fund_Liabilities 0.8276176842
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.7035395130
Government_Wide_Revenue 0.9206870656
Change_GF_Unrestricted_Fund_Balances -0.0473914872
GF_Unrestricted_Fund_Balances 0.7103141940
GF_Expenditures_Transfers_Out 0.9933946966
GF_Unrestricted_FB_PY_PY 0.7403650909
GF_Unrestricted_FB_PY 0.7825195500
GF_Unrestricted_FB_CY 0.7103141940
GF_Rev_PY_PY 0.9981795653
GF_Rev_PY 1.0000000000
GF_Rev_CY 0.9966048535
Net_Pension_Liability_or_Asset 0.9482628313
Pension_Related_Debt 0.3898758631
Pension_Assets 0.9392259489
Accrued_Pension_Liabilities 0.9517409572
Actuarially_Determined_Pension_Contributions 0.9674875286
Projected_Pension_Contribution 0.9540846675
Net_OPEB_Liability_or_Asset 0.7202161505
GF_Rev_CY
Population 0.846959004
Unemployment_rate 0.003115534
General_Fund_Cash_and_Investments 0.809848335
General_Fund_Liabilities 0.824642127
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.688490112
Government_Wide_Revenue 0.916714320
Change_GF_Unrestricted_Fund_Balances -0.046874527
GF_Unrestricted_Fund_Balances 0.711609268
GF_Expenditures_Transfers_Out 0.992036052
GF_Unrestricted_FB_PY_PY 0.727943067
GF_Unrestricted_FB_PY 0.775015459
GF_Unrestricted_FB_CY 0.711609268
GF_Rev_PY_PY 0.994215761
GF_Rev_PY 0.996604853
GF_Rev_CY 1.000000000
Net_Pension_Liability_or_Asset 0.950682978
Pension_Related_Debt 0.392488992
Pension_Assets 0.937964723
Accrued_Pension_Liabilities 0.951628735
Actuarially_Determined_Pension_Contributions 0.967147219
Projected_Pension_Contribution 0.954143576
Net_OPEB_Liability_or_Asset 0.723539196
Net_Pension_Liability_or_Asset
Population 0.81784437
Unemployment_rate 0.02207047
General_Fund_Cash_and_Investments 0.70085542
General_Fund_Liabilities 0.78867980
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.72984540
Government_Wide_Revenue 0.91906538
Change_GF_Unrestricted_Fund_Balances -0.03771912
GF_Unrestricted_Fund_Balances 0.58238500
GF_Expenditures_Transfers_Out 0.94343102
GF_Unrestricted_FB_PY_PY 0.58621253
GF_Unrestricted_FB_PY 0.63809415
GF_Unrestricted_FB_CY 0.58238500
GF_Rev_PY_PY 0.94438448
GF_Rev_PY 0.94826283
GF_Rev_CY 0.95068298
Net_Pension_Liability_or_Asset 1.00000000
Pension_Related_Debt 0.38653164
Pension_Assets 0.96516600
Accrued_Pension_Liabilities 0.98595204
Actuarially_Determined_Pension_Contributions 0.98763331
Projected_Pension_Contribution 0.98956209
Net_OPEB_Liability_or_Asset 0.72874228
Pension_Related_Debt
Population 0.32108175
Unemployment_rate 0.09698174
General_Fund_Cash_and_Investments 0.34136911
General_Fund_Liabilities 0.44621468
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.31398876
Government_Wide_Revenue 0.34012927
Change_GF_Unrestricted_Fund_Balances -0.01242512
GF_Unrestricted_Fund_Balances 0.21226839
GF_Expenditures_Transfers_Out 0.40840766
GF_Unrestricted_FB_PY_PY 0.16759023
GF_Unrestricted_FB_PY 0.22281616
GF_Unrestricted_FB_CY 0.21226839
GF_Rev_PY_PY 0.39660651
GF_Rev_PY 0.38987586
GF_Rev_CY 0.39248899
Net_Pension_Liability_or_Asset 0.38653164
Pension_Related_Debt 1.00000000
Pension_Assets 0.46475622
Accrued_Pension_Liabilities 0.41668538
Actuarially_Determined_Pension_Contributions 0.42434185
Projected_Pension_Contribution 0.30616455
Net_OPEB_Liability_or_Asset 0.50096801
Pension_Assets
Population 0.82653615
Unemployment_rate 0.04362715
General_Fund_Cash_and_Investments 0.67024018
General_Fund_Liabilities 0.75632879
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.82293903
Government_Wide_Revenue 0.94711309
Change_GF_Unrestricted_Fund_Balances -0.03674397
GF_Unrestricted_Fund_Balances 0.59335052
GF_Expenditures_Transfers_Out 0.94192118
GF_Unrestricted_FB_PY_PY 0.60243181
GF_Unrestricted_FB_PY 0.64363901
GF_Unrestricted_FB_CY 0.59335052
GF_Rev_PY_PY 0.93969851
GF_Rev_PY 0.93922595
GF_Rev_CY 0.93796472
Net_Pension_Liability_or_Asset 0.96516600
Pension_Related_Debt 0.46475622
Pension_Assets 1.00000000
Accrued_Pension_Liabilities 0.99362133
Actuarially_Determined_Pension_Contributions 0.97133418
Projected_Pension_Contribution 0.96610488
Net_OPEB_Liability_or_Asset 0.65275687
Accrued_Pension_Liabilities
Population 0.82658650
Unemployment_rate 0.03451973
General_Fund_Cash_and_Investments 0.69290692
General_Fund_Liabilities 0.78147285
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.79242509
Government_Wide_Revenue 0.94315454
Change_GF_Unrestricted_Fund_Balances -0.03705983
GF_Unrestricted_Fund_Balances 0.59117154
GF_Expenditures_Transfers_Out 0.95051620
GF_Unrestricted_FB_PY_PY 0.60181214
GF_Unrestricted_FB_PY 0.64556000
GF_Unrestricted_FB_CY 0.59117154
GF_Rev_PY_PY 0.95050907
GF_Rev_PY 0.95174096
GF_Rev_CY 0.95162874
Net_Pension_Liability_or_Asset 0.98595204
Pension_Related_Debt 0.41668538
Pension_Assets 0.99362133
Accrued_Pension_Liabilities 1.00000000
Actuarially_Determined_Pension_Contributions 0.98629441
Projected_Pension_Contribution 0.98727071
Net_OPEB_Liability_or_Asset 0.68742391
Actuarially_Determined_Pension_Contributions
Population 0.81326159
Unemployment_rate 0.01839492
General_Fund_Cash_and_Investments 0.74376035
General_Fund_Liabilities 0.83237643
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.74042984
Government_Wide_Revenue 0.92583762
Change_GF_Unrestricted_Fund_Balances -0.03689249
GF_Unrestricted_Fund_Balances 0.60444510
GF_Expenditures_Transfers_Out 0.96283031
GF_Unrestricted_FB_PY_PY 0.61746451
GF_Unrestricted_FB_PY 0.66738140
GF_Unrestricted_FB_CY 0.60444510
GF_Rev_PY_PY 0.96498142
GF_Rev_PY 0.96748753
GF_Rev_CY 0.96714722
Net_Pension_Liability_or_Asset 0.98763331
Pension_Related_Debt 0.42434185
Pension_Assets 0.97133418
Accrued_Pension_Liabilities 0.98629441
Actuarially_Determined_Pension_Contributions 1.00000000
Projected_Pension_Contribution 0.98154011
Net_OPEB_Liability_or_Asset 0.74131614
Projected_Pension_Contribution
Population 0.81969997
Unemployment_rate 0.01530729
General_Fund_Cash_and_Investments 0.69843112
General_Fund_Liabilities 0.77097538
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.75281756
Government_Wide_Revenue 0.93493941
Change_GF_Unrestricted_Fund_Balances -0.03781699
GF_Unrestricted_Fund_Balances 0.59581107
GF_Expenditures_Transfers_Out 0.94849222
GF_Unrestricted_FB_PY_PY 0.61646760
GF_Unrestricted_FB_PY 0.65684089
GF_Unrestricted_FB_CY 0.59581107
GF_Rev_PY_PY 0.95117429
GF_Rev_PY 0.95408467
GF_Rev_CY 0.95414358
Net_Pension_Liability_or_Asset 0.98956209
Pension_Related_Debt 0.30616455
Pension_Assets 0.96610488
Accrued_Pension_Liabilities 0.98727071
Actuarially_Determined_Pension_Contributions 0.98154011
Projected_Pension_Contribution 1.00000000
Net_OPEB_Liability_or_Asset 0.68834578
Net_OPEB_Liability_or_Asset
Population 0.54888106
Unemployment_rate 0.07083106
General_Fund_Cash_and_Investments 0.62947284
General_Fund_Liabilities 0.76640255
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 0.37126679
Government_Wide_Revenue 0.59202370
Change_GF_Unrestricted_Fund_Balances -0.03009563
GF_Unrestricted_Fund_Balances 0.39824983
GF_Expenditures_Transfers_Out 0.70977629
GF_Unrestricted_FB_PY_PY 0.38179034
GF_Unrestricted_FB_PY 0.44651167
GF_Unrestricted_FB_CY 0.39824983
GF_Rev_PY_PY 0.71977293
GF_Rev_PY 0.72021615
GF_Rev_CY 0.72353920
Net_Pension_Liability_or_Asset 0.72874228
Pension_Related_Debt 0.50096801
Pension_Assets 0.65275687
Accrued_Pension_Liabilities 0.68742391
Actuarially_Determined_Pension_Contributions 0.74131614
Projected_Pension_Contribution 0.68834578
Net_OPEB_Liability_or_Asset 1.00000000
Summary Statistics for Other Numeric Variables:
# Summary statistics summary (data[45 : 66 ])
Population Unemployment_rate General_Fund_Cash_and_Investments
Min. : 285 Min. :0.00000 Min. :0.000e+00
1st Qu.: 12513 1st Qu.:0.07300 1st Qu.:5.903e+06
Median : 34934 Median :0.09100 Median :1.579e+07
Mean : 74733 Mean :0.09621 Mean :4.528e+07
3rd Qu.: 78926 3rd Qu.:0.11500 3rd Qu.:3.674e+07
Max. :3970219 Max. :0.34100 Max. :3.492e+09
NA's :9 NA's :1
General_Fund_Liabilities
Min. :1.696e+04
1st Qu.:1.287e+06
Median :3.063e+06
Mean :1.269e+07
3rd Qu.:7.089e+06
Max. :1.331e+09
NA's :1
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations
Min. :0.000e+00
1st Qu.:6.536e+06
Median :2.280e+07
Mean :2.352e+08
3rd Qu.:7.297e+07
Max. :3.599e+10
NA's :1
Government_Wide_Revenue Change_GF_Unrestricted_Fund_Balances
Min. :4.330e+05 Min. : -0.770
1st Qu.:2.232e+07 1st Qu.: -0.010
Median :5.678e+07 Median : 0.070
Mean :2.048e+08 Mean : 1.632
3rd Qu.:1.300e+08 3rd Qu.: 0.190
Max. :1.746e+10 Max. :312.630
NA's :1 NA's :1
GF_Unrestricted_Fund_Balances GF_Expenditures_Transfers_Out
Min. : -8141906 Min. :7.204e+04
1st Qu.: 6068089 1st Qu.:1.312e+07
Median : 15923906 Median :3.234e+07
Mean : 39070057 Mean :9.358e+07
3rd Qu.: 36140557 3rd Qu.:7.498e+07
Max. :2455579000 Max. :6.179e+09
NA's :1 NA's :1
GF_Unrestricted_FB_PY_PY GF_Unrestricted_FB_PY GF_Unrestricted_FB_CY
Min. : -17564262 Min. : -9959177 Min. : -8141906
1st Qu.: 5236011 1st Qu.: 5570489 1st Qu.: 6068089
Median : 14149500 Median : 16072906 Median : 15923906
Mean : 34203098 Mean : 38146357 Mean : 39070057
3rd Qu.: 31310428 3rd Qu.: 35646382 3rd Qu.: 36140557
Max. :2076452000 Max. :2390787000 Max. :2455579000
NA's :2 NA's :1 NA's :1
GF_Rev_PY_PY GF_Rev_PY GF_Rev_CY
Min. :1.530e+05 Min. :1.972e+05 Min. :1.799e+05
1st Qu.:1.205e+07 1st Qu.:1.284e+07 1st Qu.:1.292e+07
Median :2.907e+07 Median :3.113e+07 Median :3.046e+07
Mean :8.223e+07 Mean :9.008e+07 Mean :8.942e+07
3rd Qu.:6.566e+07 3rd Qu.:7.114e+07 3rd Qu.:7.243e+07
Max. :5.250e+09 Max. :5.890e+09 Max. :5.745e+09
NA's :2 NA's :1 NA's :1
Net_Pension_Liability_or_Asset Pension_Related_Debt Pension_Assets
Min. :-244990000 Min. : 0 Min. :5.984e+05
1st Qu.: 8067243 1st Qu.: 0 1st Qu.:2.902e+07
Median : 29415004 Median : 0 Median :9.008e+07
Mean : 134881952 Mean : 7336554 Mean :4.559e+08
3rd Qu.: 102298823 3rd Qu.: 0 3rd Qu.:2.643e+08
Max. :8541000000 Max. :484258000 Max. :4.906e+10
NA's :14 NA's :14 NA's :14
Accrued_Pension_Liabilities Actuarially_Determined_Pension_Contributions
Min. :6.333e+05 Min. :3.087e+04
1st Qu.:3.821e+07 1st Qu.:9.864e+05
Median :1.262e+08 Median :3.367e+06
Mean :5.930e+08 Mean :1.679e+07
3rd Qu.:3.620e+08 3rd Qu.:1.064e+07
Max. :5.761e+10 Max. :1.494e+09
NA's :14 NA's :14
Projected_Pension_Contribution Net_OPEB_Liability_or_Asset
Min. : 38509 Min. :2.917e+04
1st Qu.: 1410980 1st Qu.:2.302e+06
Median : 4653424 Median :6.965e+06
Mean : 13601756 Mean :4.792e+07
3rd Qu.: 14650576 3rd Qu.:2.689e+07
Max. :239393864 Max. :3.903e+09
NA's :25 NA's :100
Missing Values
Consider using only complete objects or use mean and mode imputation when modeling
# Missing values per variable <- data.frame (Missing_Count = colSums (is.na (data))
Missing_Count
City_Name 0
Overall_Rank 0
General_Fund_Reserves_Rank 0
Debt_Burden_Rank 0
Liquidity_Rank 0
Revenue_Trends_Rank 0
Pension_Obligations_Rank 0
Pension_Funding_Rank 0
Pension_Costs_Rank 0
Future_Pension_Costs_Rank 0
OPEB_Obligations_Rank 0
OPEB_Funding_Rank 0
Overall_Risk 0
General_Fund_Reserves_Risk 0
Debt_Burden_Risk 0
Liquidity_Risk 0
Revenue_Trends_Risk 0
Pension_Obligations_Risk 0
Pension_Funding_Risk 0
Pension_Costs_Risk 0
Future_Pension_Costs_Risk 0
OPEB_Obligations_Risk 0
OPEB_Funding_Risk 0
Overall_Points 0
General_Fund_Reserves_Points 0
Debt_Burden_Points 0
Liquidity_Points 0
Revenue_Trends_Points 0
Pension_Obligations_Points 0
Pension_Funding_Points 0
Pension_Costs_Points 0
Future_Pension_Costs_Points 0
OPEB_Obligations_Points 0
OPEB_Funding_Points 0
General_Fund_Reserves_Ratio 1
Debt_Burden_Ratio 1
Liquidity_Ratio 1
Revenue_Trends_Ratio 1
Pension_Obligations_Ratio 14
Pension_Funding_Ratio 14
Pension_Costs_Ratio 14
Future_Pension_Costs_Ratio 25
OPEB_Obligations_Ratio 77
OPEB_Funding_Ratio 77
Population 0
Unemployment_rate 9
General_Fund_Cash_and_Investments 1
General_Fund_Liabilities 1
Primary_Government_Long_Term_Obligations_Excluding_Retirement_Obligations 1
Government_Wide_Revenue 1
Change_GF_Unrestricted_Fund_Balances 1
GF_Unrestricted_Fund_Balances 1
GF_Expenditures_Transfers_Out 1
GF_Unrestricted_FB_PY_PY 2
GF_Unrestricted_FB_PY 1
GF_Unrestricted_FB_CY 1
GF_Rev_PY_PY 2
GF_Rev_PY 1
GF_Rev_CY 1
Net_Pension_Liability_or_Asset 14
Pension_Related_Debt 14
Pension_Assets 14
Accrued_Pension_Liabilities 14
Actuarially_Determined_Pension_Contributions 14
Projected_Pension_Contribution 25
Net_OPEB_Liability_or_Asset 100
Boxplots: Points and Risks
# Points by Risk Boxplots <- 13 <- 24 for (i in 1 : 11 ){# Boxplot <- names (data)[cat] # Categorical variable <- names (data)[num] # Numeric variable <- min (data[[num_col]], na.rm = TRUE )<- max (data[[num_col]], na.rm = TRUE )<- (y_max - y_min) * 0.1 # Add 10% margin for better centering <- ggplot (aes_string (x = cat_col, y = num_col, fill = cat_col)) + geom_boxplot (alpha = 0.7 , outlier.shape = NA ) + # Hide outliers for better centering coord_cartesian (ylim = c (y_min - y_margin, y_max + y_margin)) + # Adjust limits dynamically labs (title = paste ("Boxplot of" , num_col, "by" , cat_col),x = cat_col,y = num_col,fill = cat_col) + theme_minimal () + theme (axis.text.x = element_text (angle = 45 , hjust = 1 ))# Print the plot print (p)<- cat + 1 <- num + 1
Warning: `aes_string()` was deprecated in ggplot2 3.0.0.
ℹ Please use tidy evaluation idioms with `aes()`.
ℹ See also `vignette("ggplot2-in-packages")` for more information.
For each risk category, getting higher points means lower risk in the specific category. However, due to different range of points in different categories overall points are not meaningfully corresponding to overall risk level for the local governments.
Boxplots: Ratio and Risks
# Ratio by Risk <- 14 <- 35 for (i in 1 : 10 ){# Boxplot <- names (data)[cat] # Categorical variable <- names (data)[num] # Numeric variable <- min (data[[num_col]], na.rm = TRUE )<- max (data[[num_col]], na.rm = TRUE )<- (y_max - y_min) * 0.1 # Add 10% margin for better centering <- ggplot (data, aes_string (x = cat_col, y = num_col, fill = cat_col)) + geom_boxplot (alpha = 0.7 , outlier.shape = NA ) + # Hide outliers for better centering coord_cartesian (ylim = c (y_min - y_margin, y_max + y_margin)) + # Adjust limits dynamically labs (title = paste ("Boxplot of" , num_col, "by" , cat_col),x = cat_col,y = num_col,fill = cat_col) + theme_minimal () + theme (axis.text.x = element_text (angle = 45 , hjust = 1 ))# Print the plot print (p)<- cat + 1 <- num + 1
Warning: Removed 1 row containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 1 row containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 1 row containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 1 row containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 14 rows containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 14 rows containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 14 rows containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 25 rows containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 77 rows containing non-finite outside the scale range
(`stat_boxplot()`).
Warning: Removed 77 rows containing non-finite outside the scale range
(`stat_boxplot()`).
Ratio can potentially be either positively or negatively related to their specific Risks.
Mean and mode imputation
library (tidyverse)# Impute missing values with column means <- data %>% mutate (across (where (is.numeric), ~ ifelse (is.na (.), mean (., na.rm = TRUE ), .)))<- colSums (is.na (data_imputed))
# Check Missing values per variable after imputation <- data.frame (Missing_Count = colSums (is.na (data_imputed))sum (missing.imputed)
Histogram of Risk Levels
# Histogram of Risk Levels by Variables <- 13 for (i in 1 : 11 ) {<- names (data)[cat] # Categorical variable <- ggplot (data, aes_string (x = cat_col, fill = cat_col)) + geom_bar () + labs (title = paste ("Histogram of Risk Levels for" , cat_col),x = cat_col,y = "Count" ,fill = "Risk Level" ) + theme_minimal () + theme (axis.text.x = element_text (angle = 45 , hjust = 1 ))print (p)<- cat + 1
2. Statistic Modeling:
Ordinal logistic regression:
Warning: package 'MASS' was built under R version 4.4.3
Attaching package: 'MASS'
The following object is masked from 'package:dplyr':
select
Warning: package 'ordinal' was built under R version 4.4.3
Attaching package: 'ordinal'
The following object is masked from 'package:dplyr':
slice
<- clm (Overall_Risk ~ General_Fund_Reserves_Ratio + Debt_Burden_Ratio + Liquidity_Ratio + Revenue_Trends_Ratio + Pension_Obligations_Ratio + Pension_Funding_Ratio + Pension_Costs_Ratio + Future_Pension_Costs_Ratio + OPEB_Obligations_Ratio + OPEB_Funding_Ratio,data = data_imputed,link = "logit" summary (ordinal_model)
formula:
Overall_Risk ~ General_Fund_Reserves_Ratio + Debt_Burden_Ratio + Liquidity_Ratio + Revenue_Trends_Ratio + Pension_Obligations_Ratio + Pension_Funding_Ratio + Pension_Costs_Ratio + Future_Pension_Costs_Ratio + OPEB_Obligations_Ratio + OPEB_Funding_Ratio
data: data_imputed
link threshold nobs logLik AIC niter max.grad cond.H
logit flexible 423 -267.17 558.34 7(0) 1.50e-09 4.7e+05
Coefficients:
Estimate Std. Error z value Pr(>|z|)
General_Fund_Reserves_Ratio -0.24111 0.08069 -2.988 0.00281 **
Debt_Burden_Ratio 0.58626 0.21870 2.681 0.00735 **
Liquidity_Ratio -0.06083 0.01540 -3.950 7.8e-05 ***
Revenue_Trends_Ratio -2.20938 1.71059 -1.292 0.19650
Pension_Obligations_Ratio 1.16436 0.56056 2.077 0.03779 *
Pension_Funding_Ratio 1.74199 1.57109 1.109 0.26753
Pension_Costs_Ratio -5.52329 6.08241 -0.908 0.36384
Future_Pension_Costs_Ratio 14.62631 5.26400 2.779 0.00546 **
OPEB_Obligations_Ratio -0.02841 0.70131 -0.041 0.96769
OPEB_Funding_Ratio -1.03841 0.36949 -2.810 0.00495 **
---
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
Threshold coefficients:
Estimate Std. Error z value
LOW|MODERATE -2.319 1.425 -1.627
MODERATE|HIGH 2.458 1.380 1.781
3. Predictive Modeling:
Random Forests:
library (tidyverse)library (randomForest)
Warning: package 'randomForest' was built under R version 4.4.3
Type rfNews() to see new features/changes/bug fixes.
Attaching package: 'randomForest'
The following object is masked from 'package:dplyr':
combine
The following object is masked from 'package:ggplot2':
margin
Warning: package 'gbm' was built under R version 4.4.3
This version of gbm is no longer under development. Consider transitioning to gbm3, https://github.com/gbm-developers/gbm3
Warning: package 'caret' was built under R version 4.4.3
Loading required package: lattice
Attaching package: 'caret'
The following object is masked from 'package:purrr':
lift
<- c ("Overall_Risk" , "General_Fund_Reserves_Ratio" , "Debt_Burden_Ratio" , "Liquidity_Ratio" , "Revenue_Trends_Ratio" , "Pension_Obligations_Ratio" , "Pension_Funding_Ratio" , "Pension_Costs_Ratio" , "Future_Pension_Costs_Ratio" , "OPEB_Obligations_Ratio" , "OPEB_Funding_Ratio" )<- data_imputed[, selected_vars]# Split data into training (70%) and testing (30%) sets set.seed (101 )<- createDataPartition (data_selected$ Overall_Risk, p = 0.7 , list = FALSE )<- data_selected[train_index, ]<- data_selected[- train_index, ]set.seed (101 )<- randomForest (Overall_Risk ~ ., data = train_data, mtry = ncol (train_data) - 1 , importance = TRUE )print (rf_model)
Call:
randomForest(formula = Overall_Risk ~ ., data = train_data, mtry = ncol(train_data) - 1, importance = TRUE)
Type of random forest: classification
Number of trees: 500
No. of variables tried at each split: 10
OOB estimate of error rate: 12.42%
Confusion matrix:
LOW MODERATE HIGH class.error
LOW 3 1 5 0.6666667
MODERATE 0 135 16 0.1059603
HIGH 3 12 123 0.1086957
Here we fit a Random Forest Model using 10 splits (all predictors). Looking at the results, we see an OOB error rate of 12.42% which means that the model misclassifies about 12.42% total observations. Furthermore, we can see that the Random Forest model tends to have a harder time predicted for the class “LOW.” While the OOB error rate is relatively low, the class.error for LOW is extremely high.
# Variable Importance Plot importance (rf_model)
LOW MODERATE HIGH MeanDecreaseAccuracy
General_Fund_Reserves_Ratio 17.5817197 60.107365 78.766294 93.194022
Debt_Burden_Ratio 1.0786688 8.128296 9.917937 12.218631
Liquidity_Ratio 11.6225670 1.747423 14.461770 16.173105
Revenue_Trends_Ratio 1.2192035 -1.619935 -1.911778 -2.209593
Pension_Obligations_Ratio 11.2341174 23.078055 24.191498 33.094629
Pension_Funding_Ratio 4.0108439 6.602884 6.661010 9.119146
Pension_Costs_Ratio 1.6470805 9.396006 3.779072 10.103250
Future_Pension_Costs_Ratio 7.5146768 10.120949 7.740986 13.408256
OPEB_Obligations_Ratio 1.8499933 11.447187 1.947810 9.984454
OPEB_Funding_Ratio 0.3245529 8.479432 13.106371 14.777234
MeanDecreaseGini
General_Fund_Reserves_Ratio 96.359249
Debt_Burden_Ratio 7.400525
Liquidity_Ratio 9.142545
Revenue_Trends_Ratio 2.940298
Pension_Obligations_Ratio 17.871268
Pension_Funding_Ratio 4.320126
Pension_Costs_Ratio 2.571313
Future_Pension_Costs_Ratio 5.940656
OPEB_Obligations_Ratio 5.000233
OPEB_Funding_Ratio 5.173426
Variable importance for the Random Forest model was determined through: 1) Mean Decrease in Accuracy, and 2) Mean Decrease in Gini Impurity.
The first evaluates the model’s accuracy by permuting each variable while the second shows how impurity in tree splits are reduced based on a variable’s significance. In both these charts, general fund reserves has the greatest value. In other words, the general fund reserve is a significant contributor to predicting the overall risk.
# Predict on test data <- predict (rf_model, newdata = test_data)# Confusion Matrix and Accuracy <- confusionMatrix (rf_predictions, test_data$ Overall_Risk)print (rf_conf_matrix)
Confusion Matrix and Statistics
Reference
Prediction LOW MODERATE HIGH
LOW 2 0 0
MODERATE 0 59 2
HIGH 1 5 56
Overall Statistics
Accuracy : 0.936
95% CI : (0.8778, 0.972)
No Information Rate : 0.512
P-Value [Acc > NIR] : < 2.2e-16
Kappa : 0.8768
Mcnemar's Test P-Value : NA
Statistics by Class:
Class: LOW Class: MODERATE Class: HIGH
Sensitivity 0.6667 0.9219 0.9655
Specificity 1.0000 0.9672 0.9104
Pos Pred Value 1.0000 0.9672 0.9032
Neg Pred Value 0.9919 0.9219 0.9683
Prevalence 0.0240 0.5120 0.4640
Detection Rate 0.0160 0.4720 0.4480
Detection Prevalence 0.0160 0.4880 0.4960
Balanced Accuracy 0.8333 0.9445 0.9380
Using our Random Forest model based on our training data, the prediction accuracy relatively high at 93.6%.
### XGBOOST MODEL ### # Prepare data for XGBoost <- c ("Overall_Risk" , "General_Fund_Reserves_Ratio" , "Debt_Burden_Ratio" , "Liquidity_Ratio" , "Revenue_Trends_Ratio" , "Pension_Obligations_Ratio" , "Pension_Funding_Ratio" , "Pension_Costs_Ratio" , "Future_Pension_Costs_Ratio" , "OPEB_Obligations_Ratio" , "OPEB_Funding_Ratio" )<- data_imputed[, selected_vars_xg]set.seed (101 )<- createDataPartition (data_selected_xg$ Overall_Risk, p = 0.7 , list = FALSE )<- data_selected[train_index_xg, ]<- data_selected[- train_index_xg, ]
suppressWarnings ({library (tidyverse)library (caret)library (xgboost)<- trainControl (method = "cv" , number = 10 ) # 10-fold CV # Train XGBoost model set.seed (101 )<- train (~ ., data = train_data_xg, method = "xgbTree" , trControl = train_control,verbose = FALSE ,verbosity = 0 # Print model summary print (xgb_model)
eXtreme Gradient Boosting
298 samples
10 predictor
3 classes: 'LOW', 'MODERATE', 'HIGH'
No pre-processing
Resampling: Cross-Validated (10 fold)
Summary of sample sizes: 269, 269, 268, 268, 269, 268, ...
Resampling results across tuning parameters:
eta max_depth colsample_bytree subsample nrounds Accuracy Kappa
0.3 1 0.6 0.50 50 0.8894105 0.7865674
0.3 1 0.6 0.50 100 0.9063070 0.8200302
0.3 1 0.6 0.50 150 0.9028587 0.8130274
0.3 1 0.6 0.75 50 0.8960771 0.7985020
0.3 1 0.6 0.75 100 0.8997479 0.8065433
0.3 1 0.6 0.75 150 0.9028587 0.8142615
0.3 1 0.6 1.00 50 0.8827364 0.7726474
0.3 1 0.6 1.00 100 0.8894030 0.7867612
0.3 1 0.6 1.00 150 0.8896329 0.7884062
0.3 1 0.8 0.50 50 0.8859622 0.7794157
0.3 1 0.8 0.50 100 0.9026437 0.8119677
0.3 1 0.8 0.50 150 0.9095402 0.8259553
0.3 1 0.8 0.75 50 0.8859622 0.7796084
0.3 1 0.8 0.75 100 0.8996403 0.8067785
0.3 1 0.8 0.75 150 0.9096403 0.8266797
0.3 1 0.8 1.00 50 0.8727364 0.7527284
0.3 1 0.8 1.00 100 0.8860697 0.7802539
0.3 1 0.8 1.00 150 0.8795180 0.7695893
0.3 2 0.6 0.50 50 0.9158621 0.8368408
0.3 2 0.6 0.50 100 0.9126363 0.8312833
0.3 2 0.6 0.50 150 0.9159696 0.8377627
0.3 2 0.6 0.75 50 0.9126363 0.8312135
0.3 2 0.6 0.75 100 0.9094179 0.8256865
0.3 2 0.6 0.75 150 0.9129737 0.8334898
0.3 2 0.6 1.00 50 0.8994179 0.8062013
0.3 2 0.6 1.00 100 0.9059770 0.8184353
0.3 2 0.6 1.00 150 0.9125287 0.8312164
0.3 2 0.8 0.50 50 0.9093103 0.8247460
0.3 2 0.8 0.50 100 0.9260920 0.8571951
0.3 2 0.8 0.50 150 0.9227586 0.8510944
0.3 2 0.8 0.75 50 0.9058547 0.8180022
0.3 2 0.8 0.75 100 0.9195328 0.8448847
0.3 2 0.8 0.75 150 0.9094179 0.8255420
0.3 2 0.8 1.00 50 0.8959696 0.7994848
0.3 2 0.8 1.00 100 0.9094253 0.8254197
0.3 2 0.8 1.00 150 0.9125287 0.8314302
0.3 3 0.6 0.50 50 0.9263144 0.8580679
0.3 3 0.6 0.50 100 0.9163070 0.8385977
0.3 3 0.6 0.50 150 0.9128587 0.8325332
0.3 3 0.6 0.75 50 0.9028587 0.8132026
0.3 3 0.6 0.75 100 0.9027438 0.8132045
0.3 3 0.6 0.75 150 0.9094105 0.8261078
0.3 3 0.6 1.00 50 0.9191954 0.8440368
0.3 3 0.6 1.00 100 0.9259770 0.8577046
0.3 3 0.6 1.00 150 0.9226437 0.8515850
0.3 3 0.8 0.50 50 0.9194179 0.8453759
0.3 3 0.8 0.50 100 0.9263144 0.8590132
0.3 3 0.8 0.50 150 0.9296552 0.8649878
0.3 3 0.8 0.75 50 0.9093029 0.8258588
0.3 3 0.8 0.75 100 0.9094179 0.8262194
0.3 3 0.8 0.75 150 0.9128661 0.8331406
0.3 3 0.8 1.00 50 0.8889655 0.7855703
0.3 3 0.8 1.00 100 0.9093103 0.8259123
0.3 3 0.8 1.00 150 0.9059770 0.8198518
0.4 1 0.6 0.50 50 0.8994179 0.8051958
0.4 1 0.6 0.50 100 0.9093029 0.8256242
0.4 1 0.6 0.50 150 0.8927438 0.7944480
0.4 1 0.6 0.75 50 0.8960771 0.7981854
0.4 1 0.6 0.75 100 0.9098628 0.8269722
0.4 1 0.6 0.75 150 0.9097553 0.8270636
0.4 1 0.6 1.00 50 0.8828513 0.7731589
0.4 1 0.6 1.00 100 0.8996329 0.8061801
0.4 1 0.6 1.00 150 0.8962996 0.8010116
0.4 1 0.8 0.50 50 0.9061921 0.8194954
0.4 1 0.8 0.50 100 0.9228661 0.8513439
0.4 1 0.8 0.50 150 0.9227512 0.8505528
0.4 1 0.8 0.75 50 0.8996403 0.8061267
0.4 1 0.8 0.75 100 0.9129737 0.8325301
0.4 1 0.8 0.75 150 0.9097553 0.8261552
0.4 1 0.8 1.00 50 0.8892955 0.7853666
0.4 1 0.8 1.00 100 0.8962996 0.7997773
0.4 1 0.8 1.00 150 0.8896329 0.7893759
0.4 2 0.6 0.50 50 0.9061995 0.8188723
0.4 2 0.6 0.50 100 0.9197627 0.8457372
0.4 2 0.6 0.50 150 0.9230960 0.8521505
0.4 2 0.6 0.75 50 0.8927438 0.7929293
0.4 2 0.6 0.75 100 0.9059696 0.8186881
0.4 2 0.6 0.75 150 0.9195328 0.8454352
0.4 2 0.6 1.00 50 0.9026437 0.8130443
0.4 2 0.6 1.00 100 0.9260920 0.8578545
0.4 2 0.6 1.00 150 0.9058547 0.8201034
0.4 2 0.8 0.50 50 0.9130886 0.8326291
0.4 2 0.8 0.50 100 0.9165369 0.8400269
0.4 2 0.8 0.50 150 0.9130886 0.8331662
0.4 2 0.8 0.75 50 0.9126363 0.8311069
0.4 2 0.8 0.75 100 0.9093029 0.8255653
0.4 2 0.8 0.75 150 0.9126363 0.8319615
0.4 2 0.8 1.00 50 0.9060845 0.8194710
0.4 2 0.8 1.00 100 0.9128661 0.8329472
0.4 2 0.8 1.00 150 0.9094179 0.8265390
0.4 3 0.6 0.50 50 0.9128587 0.8325461
0.4 3 0.6 0.50 100 0.9026288 0.8124190
0.4 3 0.6 0.50 150 0.9060771 0.8191629
0.4 3 0.6 0.75 50 0.9196403 0.8456536
0.4 3 0.6 0.75 100 0.9161921 0.8389578
0.4 3 0.6 0.75 150 0.9127438 0.8320694
0.4 3 0.6 1.00 50 0.9090805 0.8247751
0.4 3 0.6 1.00 100 0.9159770 0.8380401
0.4 3 0.6 1.00 150 0.9127512 0.8320900
0.4 3 0.8 0.50 50 0.9126363 0.8313815
0.4 3 0.8 0.50 100 0.9160845 0.8380326
0.4 3 0.8 0.50 150 0.9194179 0.8444014
0.4 3 0.8 0.75 50 0.9160845 0.8384068
0.4 3 0.8 0.75 100 0.9195328 0.8451096
0.4 3 0.8 0.75 150 0.9195328 0.8453370
0.4 3 0.8 1.00 50 0.8956322 0.7996097
0.4 3 0.8 1.00 100 0.9025287 0.8127853
0.4 3 0.8 1.00 150 0.8991954 0.8067249
Tuning parameter 'gamma' was held constant at a value of 0
Tuning
parameter 'min_child_weight' was held constant at a value of 1
Accuracy was used to select the optimal model using the largest value.
The final values used for the model were nrounds = 150, max_depth = 3, eta
= 0.3, gamma = 0, colsample_bytree = 0.8, min_child_weight = 1 and subsample
= 0.5.
# Predict on test data <- predict (xgb_model, newdata = test_data_xg)# Confusion Matrix and Accuracy <- confusionMatrix (xgb_predictions, test_data_xg$ Overall_Risk)print (xgb_conf_matrix)
Confusion Matrix and Statistics
Reference
Prediction LOW MODERATE HIGH
LOW 2 0 0
MODERATE 0 59 4
HIGH 1 5 54
Overall Statistics
Accuracy : 0.92
95% CI : (0.8578, 0.961)
No Information Rate : 0.512
P-Value [Acc > NIR] : < 2.2e-16
Kappa : 0.8458
Mcnemar's Test P-Value : NA
Statistics by Class:
Class: LOW Class: MODERATE Class: HIGH
Sensitivity 0.6667 0.9219 0.9310
Specificity 1.0000 0.9344 0.9104
Pos Pred Value 1.0000 0.9365 0.9000
Neg Pred Value 0.9919 0.9194 0.9385
Prevalence 0.0240 0.5120 0.4640
Detection Rate 0.0160 0.4720 0.4320
Detection Prevalence 0.0160 0.5040 0.4800
Balanced Accuracy 0.8333 0.9282 0.9207
<- varImp (xgb_model, scale = FALSE )# Plot importance plot (feature_importance, main = "Feature Importance in XGBoost Model" )
We created an eXtreme Gradient Boosting (XGBoost) predictive model. The XGBoost model was trained using 10-fold cross validation and applied to the training dataset. The model was tuned using hyperparameters nrounds = 150, max_depth = 3, eta = 0.3, gamma = 0, colsample_bytree = 0.8, min_child_weight = 1 and subsample = 0.5.
Similar to the Random Forest model, the XGBoost model also performed relatively well achieving a prediction accuracy of 92%. Furthermore, looking at the statistics by class, we see the “LOW” class has the lowest prediction accuracy with only 2 out of 3 cases being correctly classified. On the other hand, levels like “MODERATE” and “HIGH” had a much higher classification rate. These results look very similar to our Random Forest model.
Furthermore, while looking at the feature importance plot, we see similar results of general fund reserves being a key contributor for risk classification.
However, unlike the Random Forest model, the XGBoost model is computationally expensive and even achieved a slightly lower accuracy rate. Due to its complexity and slower learning method of fitting each decision tree sequentially, there is a possibility that our data has been overfit.
