-
Notifications
You must be signed in to change notification settings - Fork 0
/
state_local_government_liability.Rmd
136 lines (103 loc) · 4.27 KB
/
state_local_government_liability.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
---
title: "State and Local Government Liabilities Study"
output:
html_document:
toc: true
toc_float: true
toc_depth: 3
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE, warning = FALSE, message = FALSE, results = 'asis')
options(knitr.table.format = "html")
library(readxl)
library(tidyverse)
library(dplyr)
library(purrr)
library(kableExtra)
library(knitr)
library(DT)
```
## Query from database, in-text code
```{r}
gdp_2020 <- readRDS("gdp_2020.RDS")
acfrs <- readRDS("data_from_dbsite.RDS")
d <- acfrs %>%
filter(category != "Non-Profit" & year == 2020) %>%
mutate("Liabilities/Revenues Ratio" = round(total_liabilities/(revenues + 1), 2),
"Bonds Outstanding/Revenues Ratio" = round(bonds_outstanding/revenues, 2),
"Net Pension Liability/Revenues Ratio" = round((net_pension_liability - net_pension_assets)/revenues, 2),
"Net OPEB Liability/Revenues Ratio" = round((net_opeb_liability - net_opeb_assets)/revenues, 2),
"Compensated Absences/Revenues Ratio" = round(compensated_absences/revenues, 2),
"netted_net_pension_liability" = (net_pension_liability - net_pension_assets),
"netted_net_opeb_liability" = (net_opeb_liability - net_opeb_assets)) %>%
#rename for easier human read field names & table display
rename(State = state,
Entity = name,
"Net Pension Liability" = net_pension_liability,
"Net OPEB Assets" = net_opeb_assets,
"Net OPEB Liability" = net_opeb_liability,
"Bonds Outstanding" = bonds_outstanding,
"Notes Outstanding" = notes_outstanding,
"Leases" = leases,
"Loans Outstanding" = loans_outstanding,
"Compensated Absences" = compensated_absences,
"Total Liabilities" = total_liabilities,
"Revenues" = revenues
) %>%
select(State, Entity, category, year, everything())
sum_tot_labilities <- round(sum(d$`Total Liabilities`)/1000000000000,2) #trillion
percent <- round(sum(d$`Total Liabilities`)/ (20893.75*1000000000)*100)
```
US state and local governments reported **$`r sum_tot_labilities` trillion** in liabilities in 2020, an amount that exceeds their annual revenue and is equal to *`r percent`%* of Gross Domestic Product. Reason Foundation computed this total and its subcomponents by reviewing 30,000 audited government financial statements filed by state and local governments for their 2020 fiscal year.
The state and local governments that reported the largest liabilities were as follows:
```{r, results='asis', include=FALSE}
individual_fields <- c("Bonds Outstanding", "Net Pension Liability", "Net OPEB Liability", "Compensated Absences", "Total Liabilities")
get_field_value <- function (field) {
df <- d %>%
select(State, Entity, all_of(field))
df %>%
arrange(desc(df[3])) %>%
head(10) -> d1
knitr::kable(d1,
#caption = (d1 %>% colnames())[3], row.names = FALSE,
align = c('l', 'l', 'r'),
format.args = list(big.mark = ",")) %>%
# kable_paper("striped") %>%
row_spec(row = 0, background = "#FF6C30", color = "white", bold = TRUE) %>%
kable_styling(full_width = TRUE) -> result
print(result)
cat('\n')
}
```
## Plain table
```{r, fig.width= 10, fig.height=10, out.width=50}
get_field_value("Total Liabilities")
```
## Highlight some row(s)/ column(s)
```{r}
d %>%
select(State, Entity, `Total Liabilities`) %>%
arrange(desc(`Total Liabilities`)) %>%
head(10) %>%
kbl(align = c('l', 'l', 'r'),
format.args = list(big.mark = ",")) %>%
column_spec(3, bold = T) %>%
row_spec(row = 0, background = "#69b3a2", color = "white", bold = TRUE) %>%
row_spec(row = 9, background = "#E17D85", color = "white", bold = TRUE) %>%
kable_material(c("striped", "hover"))
```
## Add images
```{r}
entity_img <- tibble( # don't use data.frame
State = c("CA", "NY"),
Entity = c("State of California", "New York"),
`Total Liabilities` = c("408,305,319,000", "305,300,557,000")
)
entity_img %>%
kbl(booktabs = T) %>%
kable_paper(full_width = T) %>%
column_spec(3, bold = T) %>%
row_spec(row = 0, background = "#72A502", color = "white", bold = TRUE) %>%
column_spec(1, image =
spec_image(c("California-State-Flag.jpeg", "Flag_of_New_York_City.png"), 100, 100))
```