census_national_state_debt.Rmd

---
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)
```