01 Main Findings

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)
  
datatable(awards_leg, options = list(scrollX = TRUE, scrollY = "200px"))

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)

datatable(awards_assist, options = list(scrollX = TRUE, scrollY = "200px", scrollX = "200px"))

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 = "200px", scrollX = "200px"))

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)

datatable(awards_ind, options = list(scrollX = TRUE, scrollY = "200px", scrollX = "200px"))

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

American Rescue Plan Act

CARES Act

Paycheck Protection Program and Health care Enhancement Act

Coronavirus Response and Relief Supplemental Appropriations Act

Multiple legislations

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

Education stabilization fund

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 Legislation

Funding by Award Type

Funding by Assistance listing in CARES Act

Recipients that have spent less than 50% of their award

Funding by Industry

Sources