00 Main Findings

01 Introduction

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)

Spending 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 data

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 data

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)

Industry data

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

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

COVID-19 Award Spent by Industry of interest

awards_ind <- awards %>%
  filter(is.na(zip)) %>%
  select(Industry, Award.amount, Amount.spent, spending_abs, spending_pct) %>%
  group_by(Industry) %>%
  summarize(
    `Award amount` = sum(Award.amount),
    `Amount spent` = sum(Amount.spent),
    `Left to spend` = sum(spending_abs),
    `Spent (%)` = sum(spending_pct)
  )

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

04 Conclusions

Sources