library(tidyr)
library(readr)
library(dplyr)
library(stringr)
library(magrittr)
library(glptools)
path <- "data-raw/qop/CDBG/"
CDBG <- readxl::read_excel(path %p% "Grantee_Awards_09042019_09301783.xlsx", skip = 3,
col_type = c("numeric", "text", "text", "text", "text", "text", "numeric"))
CoC_crosswalk <- tibble(
city = FIPS_df_two_stl$city,
CoC_name = c("AL-500", "FL-510", "IN-503", "KY-501", "MI-506", "MO-604", "MO-500", "MO-501",
"NE-501", "NC-504", "NC-505", "NC-507", "OH-503", "OH-500", "OH-505",
"OK-502", "OK-501", "SC-501", "TN-504", "TN-502", "TN-501", "VA-500"))
CDBG %<>%
transmute(
org = `Org Name`,
CoC_name = str_sub(`CoC Name`, 1, 6),
year = Year,
program = `Program Name`,
funding = `Award Amount`,
state = State)
CoC <- CDBG %>%
filter(program == "CoC") %>%
left_join(CoC_crosswalk, by = "CoC_name") %>%
select(city, year, program, funding)
CDBG_county <- CDBG %>%
filter(program %in% c("CDBG", "HOME")) %>%
mutate(
geog =
if_else(str_detect(org, ",", negate = T), "state", # If there is no comma, state
if_else(str_detect(org, "County"), "county", # If name contains "County", county
"city")), # else, city
state,
city = if_else(geog == "city", str_extract(org, "^.*(?=,)") %p% "." %p% state,
NA_character_),
county = if_else(geog == "county", str_extract(org, "^.*(?= County)") %p% "." %p% state,
NA_character_)
) %>%
filter(city %in% c(paste0(FIPS_info$city, ".", FIPS_info$state), "High Point.NC") |
county %in% c(paste0(FIPS_info$county, ".", FIPS_info$state),
"Jacksonville-Duval.FL", "St. Louis.MO", "Nashville-Davidson.TN", "Louisville-Jefferson.KY")) %>%
mutate(
city = str_sub(city, 1, -4) %>%
replace(. == "High Point", "Greensboro"),
county = str_sub(county, 1, -4) %>%
replace(. == "Jacksonville-Duval", "Duval") %>%
replace(. == "St. Louis", "St. Louis County") %>%
replace(. == "Nashville-Davidson", "Davidson") %>%
replace(. == "Louisville-Jefferson", "Jefferson")) %>%
{
x <- .
bind_rows(
x %>% filter(geog == "city"),
x %>% filter(geog == "county") %>%
select(-city) %>%
left_join(select(FIPS_info, county, state, city), by = c("state", "county")))
} %>%
select(city, year, program, funding) %>%
bind_rows(CoC) %>%
left_join(FIPS_df_one_stl, by = "city") %>%
group_by(FIPS, year, program) %>%
summarise(funding = sum(funding)) %>%
ungroup() %>%
spread(key = program, value = funding) %>%
left_join(population_df_merged, by = c("FIPS", "year")) %>%
mutate(
CoC = replace(CoC, year %in% 2013:2018 & is.na(CoC), 0),
CDBG_pp = CDBG / population,
CoC_pp = CoC / population,
HOME_pp = HOME / population) %>%
select(-population) %>%
mutate(sex = "total", race = "total") %>%
organize()
CDBG_inflation <- CDBG_county %>%
COLA(CDBG:HOME_pp, base_year = 2019, rpp = FALSE) %>%
rename_at(vars(CDBG:HOME_pp), ~paste0(., "_inflation"))
CDBG_county %<>% bind_df(CDBG_inflation)
update_sysdata(CDBG_county)
rm(CDBG, CDBG_inflation, CoC, CoC_crosswalk, path)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.