-
Notifications
You must be signed in to change notification settings - Fork 0
/
census_national_state_debt.Rmd
104 lines (80 loc) · 2.63 KB
/
census_national_state_debt.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
---
title: "Census National and State debt"
date: June 11, 2022
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)
library(tidyverse)
library(dplyr)
library(purrr)
library(kableExtra)
options(scipen=999)
```
# ACFRS Data
# Census Data
The Census Bureau has national and state debt totals here:
https://www2.census.gov/programs-surveys/gov-finances/tables/2019/19slsstab1a.xlsx
and here:
https://www2.census.gov/programs-surveys/gov-finances/tables/2019/19slsstab1b.xlsx.
The excel files has 5 components for each state. The column used below is "state & local government amount"
```{r}
# first half
census_debta <- rio::import(here::here("data", "19slsstab1a.xlsx"), skip = 9)
state_local_amount1 <- census_debta %>% filter(`...1` == "125") %>%
pivot_longer(3:132,
names_to = "State",
values_to = "debt_outstanding_census") %>%
select(3:4) %>%
filter(!grepl("\\...", State))
#second half
census_debtb <- rio::import(here::here("data", "19slsstab1b.xlsx"), skip = 9)
state_local_amount2 <- census_debtb %>%
filter(`...1` == "125") %>%
pivot_longer(3:132,
names_to = "State",
values_to = "debt_outstanding_census") %>%
select(3:4) %>%
filter(!grepl("\\...", State))
# joining
state_local_amount_census <- rbind(state_local_amount1, state_local_amount2)
state_local_amount_census
```
Getting state.abb column
```{r}
left_join(state_local_amount_census, state_abb)
state_name_abb <- data.frame(state.abb, state.name) %>%
rename("Name" = state.name)
dc <- c("DC", "District of Columbia")
rbind(dc, state_name_abb) %>%
arrange(Name) -> foo1
state_local_amount_census %>%
filter(State != "United States Total") %>%
arrange(State) -> foo2
debt_outstanding_census <- cbind(foo1, foo2) %>% select(-State) %>%
rename("State" = state.abb)
```
Compute sum of `Bonds Outstanding`, `Notes Outstanding`, `Leases` by state
```{r}
d %>%
select(State, `Bonds Outstanding`, `Notes Outstanding`, Leases) %>%
mutate(sum_bonds_notes_leases = `Bonds Outstanding` + `Notes Outstanding` + Leases) %>%
select(State, sum_bonds_notes_leases) %>%
group_by(State) %>%
mutate(state_sum_bonds_notes_leases = sum(sum_bonds_notes_leases)) %>%
select(-sum_bonds_notes_leases) %>% distinct() -> acfrs_debt
acfrs_debt
```
```{r}
acfrs_debt %>%
left_join(debt_outstanding_census) %>%
mutate(ratio = state_sum_bonds_notes_leases - debt_outstanding_census)
debt_outstanding_census %>%
as_tibble() %>%
as.double(debt_outstanding_census)
map_dbl(sum)
```