01 Main Data Insights
32.59% of the CARES Act federal funding has been spent
77.17% of the Coronavirus Response and Relief Supplemental
Appropriations Act has been spent
Maps in section 03 show that recipients of grant funding are
concentrated on few zip codes, there is a to expand the grant
access.
Only 50.0% of CARES Act funding allocated to contracts has been
spent
Recipients that receive funding for Contracts across all
industries struggle the most to disburse these funds.
Assistance listings of interest with less than 50% of their
allocated funding spent:
- 0% Connecting Minority Communities Pilot Program
- 1.6% Community Health Workers for Public Health Response and
Resilient
- 15.7% Public Health Training Centers Program
- 21.3% Programs to improve Community Mental Health Services
- 40.45% Computer and Information Science and Engineering
- 42.3% Prevention of Disease, Disability, and Death through
immunization and control of respiratory and related diseases
- 43.0% Food insecurity nutrition incentive grants program
Last updated: May 2024
02 Data Cleaning
02.1 Importing data
Shapefiles: Boundary data by Zip Code
# Download zip code multipolygons from Census 2020
# zip_codes <- zctas(cb = TRUE, class = "sf", year = 2020)
# save(file = "zip_code_data.R", zip_codes)
# Load multipolygons by zip code
load("zip_code_data.R")
zips_sp <- zip_codes %>%
select(zip = ZCTA5CE20, geometry) %>%
mutate(zip = as.character(zip))
# Load chicago boundaries shapefile
chicago_bound <- st_read("Boundaries - City")
# Transform to same CRS format
chicago_bound <- st_transform(chicago_bound, 4326)
zipShp <- st_transform(zips_sp, 4326)
# Join census tracts with chicago boundaries
chiPolygons <- st_intersection(zipShp, chicago_bound)
Awards data: COVID-19 relief Awards for the City of Chicago by Zip
Code
awards <- read.csv("Awards Chicago.csv") %>%
mutate(zip = as.character(Recipient.zip),
spending_abs = Award.amount - Amount.spent, # absolute spending difference
spending_pct = (Amount.spent/Award.amount)*100
) %>% # Spending variables
filter(Award.amount >= 0) # Omitting savings
02.2 Data Manipulation
Legislation by Zip code: Percentage of funding spent
awards_leg <- awards %>%
group_by(zip, Funding.by.legislation) %>%
summarize(
award_amount = sum(Award.amount),
amount_spent = sum(Amount.spent)) %>%
mutate(spending_pct = (amount_spent/award_amount)*100) %>% # Percentage spent
select(-award_amount, -amount_spent) %>%
pivot_wider(names_from = Funding.by.legislation,
values_from = spending_pct)
Assistance Listing by Zip Code: Percentage of funding spent
awards_assist <- awards %>%
filter(Assistance.listing != "") %>%
group_by(zip, Assistance.listing) %>%
summarize(
award_amount = sum(Award.amount),
amount_spent = sum(Amount.spent)) %>%
mutate(spending_pct = (amount_spent/award_amount)*100) %>% # Percentage spent
select(-award_amount, -amount_spent) %>%
pivot_wider(names_from = Assistance.listing,
values_from = spending_pct)
Assistance Listing: Fununding spent
awards_assist2 <- awards %>%
filter(Assistance.listing != "") %>%
group_by(Assistance.listing) %>%
summarize(
`Award amount` = sum(Award.amount),
`Amount spent` = sum(Amount.spent)) %>%
mutate(`Left to spend` = `Award amount` - `Amount spent`,
`Spent (%)` = (`Amount spent`/`Award amount`)*100)
datatable(awards_assist2, options = list(scrollX=TRUE, scrollY=TRUE))
Industry by Zip code: Percentage of funding spent
awards_ind <- awards %>%
group_by(zip, Industry) %>%
summarize(
award_amount = sum(Award.amount),
amount_spent = sum(Amount.spent)) %>%
mutate(spending_pct = (amount_spent/award_amount)*100) %>% # Percentage spent
select(-award_amount, -amount_spent) %>%
pivot_wider(names_from = Industry,
values_from = spending_pct)
03 Mapping
*Note: Loans are not considered in the spending.
03.1 COVID-19 Award Spent by Legislation:
Merging polygons with Award data by Legislation:
award_leg_poly <- left_join(chiPolygons, awards_leg, by = "zip")
Paycheck Protection Program and Health care Enhancement Act
Coronavirus Response and Relief Supplemental Appropriations Act
03.2 COVID-19 Award Spent by Assistance Listing of interest:
Merging polygons with Award data by Assistance Listing:
Grants for capital development in Health Centers
Grants for new and expanded services under the health center
program
Health Center Program
Includes: Community Health Centers, Migrant Health Centers, Health
Care for the Homeless, and Public Housing Primary Care
04 Descriptive Statistics
Funding by Assistance listing in CARES Act
Recipients that have spent less than 50% of their award
Funding by Award Type and Legislation
Funding by Industry and Award Type