Incentive Properties in Cook County, IL

library(tidyverse)
library(ptaxsim)
library(DBI)
library(httr)
library(jsonlite)
library(glue)
library(sf)

knitr::opts_chunk$set(warning = FALSE, message = FALSE)



ptaxsim_db_conn <- DBI::dbConnect(RSQLite::SQLite(), "./ptaxsim.db/ptaxsim-2021.0.4.db")

# link to the API output as a JSON file
muni_shp <- read_sf("https://gis.cookcountyil.gov/traditional/rest/services/politicalBoundary/MapServer/2/query?outFields=*&where=1%3D1&f=geojson")

#muni_shp <- read_json("muni_shp.json")
nicknames <- readxl::read_excel("./Necessary_Files/muni_shortnames.xlsx")

class_dict <- read_csv("./Necessary_Files/class_dict_expanded.csv") %>% 
  mutate(class_code = as.character(class_code))

NBHs <- read_sf("https://datacatalog.cookcountyil.gov/resource/pcdw-pxtg.geojson")



# has EAV values, extensions by agency_num
agency_dt <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT *
  FROM agency
  WHERE year = 2021
  "
)

cook_agency_names <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT DISTINCT agency_num, agency_name
  FROM agency_info
  "
)

 


# has all tax codes and the taxing agency that taxes them. Tax code rates and agency rates. 
cook_tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql("
  SELECT DISTINCT tax_code_num, tax_code_rate
  FROM tax_code
  WHERE agency_num IN ({cook_agency_names$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)


muni_agency_names <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT DISTINCT agency_num, agency_name, minor_type
  FROM agency_info
  WHERE minor_type = 'MUNI'
  OR agency_num = '020060000'  

  "
)

muni_tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql("
  SELECT agency_num, tax_code_num, tax_code_rate
  FROM tax_code
  WHERE agency_num IN ({muni_agency_names$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)

muni_tax_codes <- muni_tax_codes %>% left_join(muni_agency_names)

Maps!

######
base_url <- "https://datacatalog.cookcountyil.gov/resource/tx2p-k2g9.json"



### Pulls from parcel universe online ### 
nbh_pins <- GET(
  base_url,
  query = list(
    tax_year = 2021,
 #   ward_num = 49,
   # property_city = "ROGERS PARK",
    `$select` = paste0(c("pin", "pin10", 
                         "class", 
                         "township_code", "township_name",
                         "nbhd_code", "census_puma_geoid",
                         "triad_name" 
                         ),
   collapse = ","),
   `$limit` = 500000000L
  )
)

nbh_pins2 <- fromJSON(rawToChar(nbh_pins$content))
head(nbh_pins2)




######
### Pulls from PTAXSIM database downloaded on your computer ### 

# There are 1,864,594 pins taxed by Cook County in 2021.
cook_pins <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql(
  "SELECT DISTINCT pin, class, tax_code_num
  FROM pin
  WHERE tax_code_num IN ({cook_tax_codes$tax_code_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
))

# finds all pins within a municipality
pin_data <- lookup_pin(2021, cook_pins$pin) %>%
  left_join(cook_pins, by = c("pin", "class")) %>% 
  mutate(class_1dig = str_sub(class, 1, 1))

### Joins the neighborhood codes to the PINs with tax codes (matches by PIN and property class) ###
pin_data <- left_join(pin_data, nbh_pins2, by = c("pin", "class"))

pin_data <- pin_data %>% 
  # first 2 digits of the 5-digit neighborhood are for the township
  mutate(township = str_sub(nbhd_code,1,2),
         nbhd_3 = str_sub(nbhd_code, 3,5)) %>% 
  left_join(class_dict, by = c("class" = "class_code")) # add property class descriptions

pin_data %>% head()

# TC_MC_PC = tax code major class PIN count
TC_MC_sums <- pin_data %>% 
  group_by(tax_code_num, major_class_code) %>%
  summarize(TC_MC_PC = n(),
            TC_MC_AV = sum(av),
            TC_MC_EAV = sum(eav),
            Total_Exemptions = sum(exe_homeowner+exe_senior +exe_freeze + exe_longtime_homeowner +exe_disabled + exe_vet_returning + exe_vet_dis_lt50+ exe_vet_dis_50_69 + exe_vet_dis_ge70 + exe_abate, na.rm = TRUE),
    GHE_only = sum(exe_homeowner, na.rm = TRUE),
            )
TC_MC_sums <- right_join(cook_tax_codes, TC_MC_sums)
TC_MC_sums

TC_MC_sums %>% write_csv("./Output/7_TC_MC_summaries.csv")

incentive_pins <- parcel_data %>% 
  filter(class>599 & class < 900) %>%  
  mutate(pin10 = str_sub(pin,1, 10))  %>% 
  left_join(muni_tax_codes)

residential_pins <- parcel_data %>% 
  filter(class>199 & class < 300) %>%  
  mutate(pin10 = str_sub(pin,1, 10))  %>% 
  left_join(muni_tax_codes)


# PINs that have incentive property classes
# Have muni name, taxcodes, neighborhoods, etc. with PIN data
write_csv(incentive_pins, "./Output/7_output_incentive_pins.csv")
write_csv(residential_pins, "./Output/7_output_residential_pins.csv")

incentive_pins <- read_csv("./Output/7_output_incentive_classes.csv")

residential_pins <- read_csv("./Output/7_output_residential_pins.csv")

TC_MC_sums <- read_csv("./Output/7_TC_MC_summaries.csv")
# 
# ### Class 8 Only ### 
# table1 <- incentive_classes %>% 
#   filter(class > 799 & class < 900) %>% 
#   group_by(township_name, agency_name, major_class_code) %>%
#   summarize(av = sum(av)) %>%
#   pivot_wider(id_cols = c(township_name, agency_name), 
#               names_from = "major_class_code", 
#               values_from = "av") %>%
#   rename(Class8_AV = `8`)
# 
# table2 <- incentive_classes %>% 
#   filter(class > 799 & class < 900) %>% 
#   group_by(township_name, agency_name, major_class_code) %>%
#   summarize(pincount = n()) %>%
#   pivot_wider(id_cols = c(township_name, agency_name), 
#               names_from = "major_class_code", 
#               values_from = "pincount") %>%
#   rename(Class8_pincount = `8`)
# 
# ### Table of Incentive Class AV and PIN count for each Muni ### 
# left_join(table1, table2)

#### Makes the Same thing as line 184-203!!!:
incentive_pins %>% 
  left_join(nicknames, by = "agency_name") %>% 
  reframe(Class8_PINcount = n(),
          Class8_MuniAV = sum(av),
          .by=c(clean_name)) %>% 
  rename(
         Municipality = clean_name) %>%
    mutate(Municipality = ifelse(is.na(Municipality), "Unincorporated", Municipality)) %>%
  arrange(Municipality)

# incentive_classes %>% 
#   left_join(nicknames) %>%
#   filter(class > 599 & class < 900) %>% 
#   group_by(township_name, clean_name, major_class_code) %>%
#   summarize(pincount = n()) %>%
#   pivot_wider(id_cols = c(township_name, clean_name), 
#               names_from = "major_class_code", 
#               values_from = "pincount") %>%
#   select( Municipality = clean_name,
#           Township = township_name,
#           "6A", "6B", "6C", "7A", "7B", "8") %>% 
#   mutate(Municipality = ifelse(is.na(Municipality), "Unincorporated", Municipality)) %>%
#   arrange(Municipality)

incentive_pins %>% 
  left_join(nicknames) %>%
  filter(class > 599 & class < 900) %>% 
  group_by(clean_name, major_class_code) %>%
  summarize(pincount = n()) %>%
  pivot_wider(id_cols = clean_name, 
              names_from = "major_class_code", 
              values_from = "pincount") %>%
  select( Municipality = clean_name,
          "6A", "6B", "6C", "7A", "7B", "8") %>% 
  mutate(Municipality = ifelse(is.na(Municipality), "Unincorporated", Municipality)) %>%
  arrange(Municipality)

residential_pins %>% 
  left_join(nicknames) %>% 
  reframe(
    Class2_PINcount = n(),
          Class2_MuniAV = sum(av),
    Total_Exemptions = sum(exe_homeowner+exe_senior +exe_freeze + exe_longtime_homeowner +exe_disabled + exe_vet_returning + exe_vet_dis_lt50+ exe_vet_dis_50_69 + exe_vet_dis_ge70 + exe_abate, na.rm = TRUE),
    GHE = sum(exe_homeowner, na.rm = TRUE),
          .by=c(clean_name)) %>% 
  rename(
         Municipality = clean_name) %>%
    mutate(Municipality = ifelse(is.na(Municipality), "Unincorporated", Municipality)) %>%
  arrange(Municipality)

nbh_pincounts <- incentive_pins %>% 
  group_by(nbhd_code, class) %>%
  dplyr::summarize(pin_count = n(),
            av = sum(av)) %>%
  mutate(class_1dig = str_sub(class, 1, 1))

nbh_pincounts_majorclass <- incentive_pins %>% 
  group_by(major_class_code, major_class_type, nbhd_code) %>%
  dplyr::summarize(pin_count = n(),
            av = sum(av))

# table(nbh_pincounts_majorclass$major_class_code)


incentives_props <- nbh_pincounts_majorclass %>% 
  filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8", "9")) %>%
  mutate(nbhd_code = as.character(nbhd_code))


fig_nbhd_pincount <- incentives_props %>% 
  group_by(nbhd_code) %>%
  summarize(pin_count = sum(pin_count)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Number of PINs with Incentives in Assessor Neighborhoods", 
  caption = "Count of 14 digit PINs") +
  theme_void() + 
   theme(axis.ticks = element_blank(), axis.text = element_blank()) +
scale_fill_steps2(
  high = "darkblue", low = "orange",
  show.limits=TRUE,
  nice.breaks = TRUE,
na.value = NA,
  name = "Pin Count")


fig_nbhd_AV <- incentives_props %>% 
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  filter(triad_name != "City") %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Assessed Value of Properties in 
       Assessor Neighborhoods", 
       caption = "Different classes of properties have different assessment levels. 
  Some change over time.") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    show.limits=TRUE,
    nice.breaks = FALSE,
   # limits = c(0, 90000000),
    na.value = NA,
    name = "AV",
    label = scales::dollar)



fig_nbhd_Chicago_AV <- incentives_props %>% 
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  filter(triad_name == "City") %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Assessed Value of Properties in 
       Assessor Neighborhoods", 
       caption = "Different classes of properties have different assessment levels. 
  Some change over time.") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    show.limits=TRUE,
    nice.breaks = FALSE,
   # limits = c(0, 90000000),
    na.value = NA,
    name = "AV",
    label = scales::dollar)

fig_nbhd_Chicago_AV

nbh_pincounts_class <- incentive_pins %>% 
  group_by(nbhd_code, class) %>%
  dplyr::summarize(pin_count = n(),
            av = sum(av)) %>%
  mutate(class_1dig = str_sub(class, 1, 1))

nbh_pincounts_majorclass <- incentive_pins %>% 
  group_by(major_class_code, major_class_type, nbhd_code) %>%
  dplyr::summarize(pin_count = n(),
            av = sum(av))

# table(nbh_pincounts_majorclass$major_class_code)


incentives_props <- nbh_pincounts_majorclass %>% 
  filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8")) %>%
  mutate(nbhd_code = as.character(nbhd_code))

fig_nbhd_C7AV <- incentives_props %>% 
  filter(major_class_code %in% c("7A", "7B", "7C") ) %>% 
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  filter(triad_name != "City") %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 7 Properties - Assessed Value",
       subtitle = "AV of Buildings with Incentives in Assessor Neighborhoods",
       caption = "Over $90 million in Chicago") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
    label = scales::dollar)

fig_nbhd_C7_Chicago_AV <- incentives_props %>% 
  filter(major_class_code %in% c("7A", "7B", "7C") ) %>% 
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  filter(triad_name == "City") %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 7 Properties - Assessed Value",
       subtitle = "AV of Buildings with Incentives in Assessor Neighborhoods",
       caption = "Over $90 million in Chicago") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
    label = scales::dollar)



fig_nbhd_C7_PC <- incentives_props %>% 
  filter(major_class_code %in% c("7A", "7B", "7C") ) %>%
  group_by(nbhd_code) %>%
  summarize(pincount = sum(pin_count)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
    filter(triad_name != "City") %>%

  ggplot(aes(fill = pincount)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 7 Properties",
       subtitle = "# of Buildings with Class 7 Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    show.limits=TRUE,
    na.value = NA,
    name = "Pin Count")

fig_nbhd_C7_Chicago_PC <- incentives_props %>% 
  filter(major_class_code %in% c("7A", "7B", "7C") ) %>%
  group_by(nbhd_code) %>%
  summarize(pincount = sum(pin_count)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
    filter(triad_name == "City") %>%

  ggplot(aes(fill = pincount)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 7 Properties in Chicago",
       subtitle = "# of Buildings with Class 7 Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    show.limits=TRUE,
    na.value = NA,
    name = "Pin Count")

fig_nbhd_C6_AV <- incentives_props %>% 
  filter(major_class_code %in% c("6A", "6B", "6C") ) %>%
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 6 Properties",
       subtitle = "AV of Buildings with Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
    label = scales::dollar)

fig_nbhd_C6_Chicago_AV <- incentives_props %>% 
  filter(major_class_code %in% c("6A", "6B", "6C") ) %>%
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  filter(triad_name == "City") %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 6 Properties",
       subtitle = "AV of Buildings with Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
    label = scales::dollar)
  
fig_nbhd_C6_PC <- incentives_props %>% 
  filter(major_class_code %in% c("6A", "6B", "6C") ) %>%
  group_by(nbhd_code) %>%
  summarize(pin_count= round(sum(pin_count)), digits = 0) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 6 Properties",
       subtitle = "# of Buildings with Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "Pin Count")

fig_nbhd_C6_Chicago_PC <- incentives_props %>% 
  filter(major_class_code %in% c("6A", "6B", "6C") ) %>%
  group_by(nbhd_code) %>%
  summarize(pin_count= round(sum(pin_count)), digits = 0) %>%
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  filter(triad_name == "City") %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Class 6 Properties",
       subtitle = "# of Buildings with Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "Pin Count")

All Incentive Types Together

# Add in names that merge with Muni shapefile names
muni_pincounts_majorclass <- incentive_pins %>% 
  left_join(nicknames) %>%
  group_by(clean_name, major_class_code, major_class_type, agency_name, shpfile_name) %>%
  dplyr::summarize(pin_count = n(),
            av = sum(av))

# table(muni_pincounts_majorclass$major_class_code) # Number of Munis with these major classes


fig_muni_AV <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8")) %>%
  filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(av = sum(av)) %>% 
  filter(!is.na(shpfile_name) ) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Any Type of Incentive Class",
       subtitle = "AV of Buildings with Incentives in Municipalities (Except Chicago).
       Chicago has around $500 million in AV from properties with incentives.") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
  #  nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
  limits = c(0, 90000000),
    name = "AV ($)",
    label = scales::dollar)

fig_muni_AV
fig_nbhd_AV

All Incentive Property Classes: Municipality level and Assessor Neighborhood level

fig_muni_pincount <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8")) %>%
  filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(pin_count = round(sum(pin_count)) ) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Incentive Clases",
       subtitle = "# of Buildings with Incentives in Municipalities (Except Chicago)") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
  #  nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "# PINs")


fig_muni_pincount
fig_nbhd_pincount

Class 6 Incentives

Class 6A: Industrial Incentive

Class 6B: Industrial Incentive - Designed to encourage industrial development throughout Cook County by offering a real estate tax incentive for the development of new industrial facilities, the rehabilitation of existing industrial structures, and the industrial reutilization of abandoned buildings.

Class 6C: Industrial Brownfield

fig_muni_C6_AV <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("6A", "6B", "6C")) %>%
  filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(av = sum(av)) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Class 6 Incentives",
       subtitle = "Assessed Value in Municipalities (Except Chicago)") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
   # nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
  label = scales::dollar)



fig_muni_C6_PC <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("6A", "6B", "6C")) %>%
 # filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(pin_count = sum(pin_count)) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Class 6 Incentives",
       subtitle = "# of Buildings with Incentives in Municipalities") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "# PINs")

fig_muni_C6_AV
fig_nbhd_C6_AV

fig_muni_C6_PC
fig_nbhd_C6_PC

Class 7 Incentives

Class 7A(B): Intended to encourage commercial projects in areas determined to be “in need of commercial development.These projects have total development costs, exclusive of land, that DO NOT EXCEED(DO EXCEED) two million and would not be economically feasible without the incentive.

fig_muni_C7_AV <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("7A", "7B", "7C")) %>%
  filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(av = sum(av)) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Assessed Value of Properties with Class 7 Incentives",
       subtitle = "in Municipalities (Except Chicago)") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
  label = scales::dollar)


fig_muni_C7_PC <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("7A", "7B", "7C")) %>%
  filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(pin_count = sum(pin_count)) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Class 7 Incentives",
       subtitle = "# of Buildings with Incentives in Municipalities (Except Chicago)") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
  #  nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "# PINs")

fig_muni_C7_AV
fig_nbhd_C7AV



fig_muni_C7_PC
fig_nbhd_C7_PC

Class 8 Incentives

Class 8: Designed to encourage industrial and commercial development in areas of the county which are experiencing severe economic stagnation.

Class 8 properties exist in places besides the 5 townships:

Bloom, Bremen, Calumet, Rich, Thornton

fig_nbhd_C8_PC <- incentives_props %>% 
  filter(major_class_code == "8") %>%
  group_by(nbhd_code)%>%
  summarize(pin_count = sum(pin_count)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Class 8 Properties",
       subtitle = "Number of PINs with Incentives in Assessor Neighborhoods", 
       caption = "Count of 14 digit PINs") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "Count")


fig_nbhd_C8 <- incentives_props %>% 
  filter(major_class_code == "8") %>%
  group_by(nbhd_code) %>%
  summarize(av = sum(av)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
         # color = "black",
          lwd = .1)  +
  labs(title = "Class 8 Properties",
       subtitle = "AV of Buildings with Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    limits = c(0, 13000000),
    na.value = NA,
    name = "AV ($)",
    label = scales::dollar)

fig_muni_C8_AV <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("8")) %>%
  filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(av = sum(av)) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Class 8 Incentives",
       subtitle = "# of Buildings with Incentives in Municipalities (Except Chicago)") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "AV ($)",
  label = scales::dollar)



fig_muni_C8_PC <- muni_pincounts_majorclass %>% 
  filter(major_class_code %in% c("8")) %>%
 # filter(clean_name != "Chicago") %>% 
  group_by(shpfile_name)%>%
  summarize(pin_count = sum(pin_count)) %>% 
  filter(!is.na(shpfile_name)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = pin_count)) + 
  geom_sf(aes(geometry = geometry),
          color = "black",
          lwd = .05)+ 
  labs(title = "Properties with Class 8 Incentives",
       subtitle = "# of Buildings with Incentives in Municipalities") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
  #  nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "# PINs")

fig_muni_C8_PC
fig_nbhd_C8_PC

There are some Class 8 properties located in townships other than the Class 8 five chosen for the incentives.

fig_muni_C8 <- muni_pincounts_majorclass %>% 
   filter(!is.na(shpfile_name)) %>% 

  filter(major_class_code %in% c("8")) %>%
  group_by(shpfile_name)%>%
  summarize(av = sum(av)) %>% 
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot(aes(fill = av)) + 
  geom_sf(aes(geometry = geometry),color = "black", lwd = .05)+ 
  labs(title = "Class 8 Properties",
       subtitle = "AV of Buildings with Incentives in Assessor Neighborhoods") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "orange",
    nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    limits = c(0, 13000000),
    name = "AV ($)",
    label = scales::dollar)

fig_muni_C8
fig_nbhd_C8

class8Townships = read_csv("./Output/3_Exemptions_Details_output-ClassTaxcodeExemps.csv") %>% 
  mutate(class_code = as.character(class_code)) %>%
  filter(class_code >=800 & class_code <900) %>%
  left_join(class_dict) %>%
  mutate(tax_code_num = as.character(tax_code_num)) %>%
  left_join(township_tax_codes) %>%
  left_join(township_agency_names)%>% 
  distinct(agency_name) 

class8Townships

Chicago Specific Graphs

# fig_nbhd_Chicago_AV All incentives

fig_nbhd_C6_Chicago_AV
fig_nbhd_C6_Chicago_PC
fig_nbhd_C7_Chicago_AV
fig_nbhd_C7_Chicago_PC

Tables

incentive_pins %>% 
  group_by(agency_name, major_class_type) %>%
  summarize(av = sum(av),
           # pins = n()
           ) %>%
  pivot_wider(id_cols = agency_name, names_from = "major_class_type", values_from = "av")

incentive_pins %>% 
  group_by(agency_name, major_class_type) %>%
  summarize(av = sum(av),
            pins = n()) %>%
  pivot_wider(id_cols = agency_name, names_from = "major_class_type", values_from = "pins")

incentive_pins %>% 
  group_by(agency_name, major_class_type, major_class_code) %>%
  summarize(av = sum(av),
            pins = n()) %>%
  pivot_wider(id_cols = agency_name, names_from = "major_class_code", values_from = "av")

incentive_pins %>% 
  group_by(agency_name, major_class_type, major_class_code) %>%
  summarize(av = sum(av),
            pins = n()) %>%
  pivot_wider(id_cols = agency_name, names_from = "major_class_code", values_from = "pins")

# # By Muni and Property Class
# incentive_classes %>% 
#     mutate(tax_code_num = as.character(tax_code_num)) %>% 
#   left_join(muni_tax_codes) %>%
#   mutate(tax_code_num = as.character(tax_code_num)) %>% 
#   left_join(muni_agency_names) %>%
#   group_by(agency_num, class) %>%
#   dplyr::summarize(pin_count = n(),
#             av = sum(av)) %>%
#   mutate(class_1dig = str_sub(class, 1, 1))


# By Muni and Major Property Class
incentive_pins %>% 
    mutate(tax_code_num = as.character(tax_code_num)) %>% 
  left_join(muni_tax_codes) %>%
    mutate(tax_code_num = as.character(tax_code_num)) %>% 
  left_join(muni_agency_names) %>%
  group_by(agency_num, major_class_code) %>%
  dplyr::summarize(pin_count = n(),
            av = sum(av))

Areas with lots of exemptions and incentives

aka a reduced tax base

Municpality Level

pin_data <- read_csv("./Output/4C_joined_PINs_bills_and_exemptions.csv")

muni_taxrates <- pin_data %>% 
  group_by(agency_name) %>%
  summarize(
    muni_av = sum(av, na.rm = TRUE),
    muni_eav = sum(eav, na.rm = TRUE),
    muni_equalized_AV = sum(equalized_AV, na.rm = TRUE),
    pins_in_muni = n(),
    muni_current_exemptions = sum(all_exemptions, na.rm = TRUE),
    muni_HO_exemps = sum(exe_homeowner, na.rm = TRUE),
    muni_comp_rate = mean(tax_code_rate, na.rm = TRUE), # Changed from first() to mean() on Nov 1
    final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE), # used as LEVY amount!! 
    final_tax_to_tif = sum(final_tax_to_tif, na.rm = TRUE),
    tax_amt_exe = sum(tax_amt_exe, na.rm = TRUE), 
    tax_amt_pre_exe = sum(tax_amt_pre_exe, na.rm = TRUE), 
    tax_amt_post_exe = sum(tax_amt_post_exe, na.rm = TRUE),
    # rpm_tif_to_cps = sum(rpm_tif_to_cps, na.rm = TRUE), # not used
    # rpm_tif_to_rpm = sum(rpm_tif_to_rpm, na.rm=TRUE), # not used
    # rpm_tif_to_dist = sum(rpm_tif_to_dist, na.rm=TRUE), # not used
    # tif_share = mean(tif_share, na.rm=TRUE), # not used
  ) %>%
  
  rename(cur_comp_TC_rate = muni_comp_rate) %>%
  mutate(current_muni_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100),
         new_muni_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100) + muni_HO_exemps) %>%
  mutate(new_comp_TC_rate = (final_tax_to_dist / new_muni_taxable_eav)*100) %>%
  mutate(new_comp_TC_rate = ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>%
  select(agency_name, cur_comp_TC_rate, new_comp_TC_rate, current_muni_taxable_eav, new_muni_taxable_eav, everything())

muni_taxrates 

prop_class_sums <- pin_data %>% 
  group_by(agency_name, major_class_code, major_class_type )  %>%
  
  summarize(
    av = sum(av, na.rm = TRUE),
    eav = sum(eav, na.rm = TRUE),
    equalized_AV = sum(equalized_AV, na.rm = TRUE),
    pins_in_class = n(),
    current_exemptions = sum(all_exemptions, na.rm = TRUE),
    HO_exemps = sum(exe_homeowner, na.rm = TRUE),
    tax_code_rate = mean(tax_code_rate, na.rm = TRUE), # Changed from first() to mean() on Nov 1
    final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE), # used as LEVY amount!! 
    final_tax_to_tif = sum(final_tax_to_tif, na.rm = TRUE),
    tax_amt_exe = sum(tax_amt_exe, na.rm = TRUE), 
    tax_amt_pre_exe = sum(tax_amt_pre_exe, na.rm = TRUE), 
    tax_amt_post_exe = sum(tax_amt_post_exe, na.rm = TRUE),
    rpm_tif_to_cps = sum(rpm_tif_to_cps, na.rm = TRUE), # not used
    rpm_tif_to_rpm = sum(rpm_tif_to_rpm, na.rm=TRUE), # not used
    rpm_tif_to_dist = sum(rpm_tif_to_dist, na.rm=TRUE), # not used
    tif_share = mean(tif_share, na.rm=TRUE), # not used
  ) %>%
  
  mutate(total_bill_current = final_tax_to_dist + final_tax_to_tif) %>%
  rename(cur_comp_TC_rate = tax_code_rate) %>%
  mutate(current_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100),
         new_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100) + HO_exemps) %>%
  mutate(new_comp_TC_rate = (final_tax_to_dist / new_taxable_eav)*100) %>%
  mutate(new_comp_TC_rate = ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>%
  select(agency_name, major_class_code, HO_exemps, current_exemptions, pins_in_class, current_taxable_eav, new_taxable_eav,  everything())

# muni level by major_class summary
prop_class_sums 

Neighborhood level join

nbh_major_class <- read_csv("./Output/8_Current_Taxrates_per_nbh_by_majorclass.csv")
nbh_major_class

# neighborhoods with incentive properties
incentives_props <- nbh_major_class %>% 
  filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8")) %>%
  mutate(nbhd_code = as.character(nbhd_code))
# 
# nbh_pincounts_class <- incentive_classes %>% 
#   group_by(nbhd_code, class) %>%
#   dplyr::summarize(pin_count = n(),
#             av = sum(av)) %>%
#   mutate(class_1dig = str_sub(class, 1, 1))
# 
# nbh_pincounts_majorclass <- incentive_classes %>% 
#   group_by(major_class_code, major_class_type, nbhd_code) %>%
#   dplyr::summarize(pin_count = n(),
#             av = sum(av))

# nbh_res_exemptions <- read_csv("./Output/8_nbh_sum_new_exe.csv") 
nbh_taxrates <- read_csv( "./Output/8_Current_Taxrates_per_nbh.csv")


nbh_data <- left_join(nbh_pincounts_majorclass, nbh_taxrates, by = "nbhd_code")


nbh_data <- nbh_data %>% 
  rename(all_PINs = pin_count.x,
         nbh_pins = pin_count.y)  %>%
  mutate(exempt_over_total_EAV = Exempt_EAV / Total_EAV,
         nbhd_code = as.character(nbhd_code),
         exempts_perPIN = Exempt_EAV / nbh_PINs,
         incentive_projects_pct = incentive_PINs / nbh_PINs,
         incentives_EAV = av * 3.0027 ,
         incentives_over_total_EAV = incentives_EAV / Total_EAV) %>%
  select(nbhd_code, exempt_over_total_EAV, nbh_PINs, major_class_PINs, exempts_perPIN, incentive_projects_pct, incentives_EAV, incentives_over_total_EAV, major_class_code, everything()) %>% arrange(nbhd_code)

nbh_data

# nbh_res_exemptions <- read_csv("./Output/8_nbh_sum_new_exe.csv") 
nbh_taxrates <- read_csv( "./Output/8_Current_Taxrates_per_nbh.csv") %>% select(nbhd_code, pin_count, Total_EAV)


nbh_data <- left_join(nbh_major_class, nbh_taxrates, by = "nbhd_code")


nbh_data <- nbh_data %>% 
  rename(major_class_PINs = pin_count.x,
         nbh_PINs = pin_count.y)  %>%
  mutate(exempt_over_total_EAV = round(Exempt_EAV / Total_EAV.y, digits = 4),
         nbhd_code = as.character(nbhd_code),
         exempts_perPIN = round(Exempt_EAV / nbh_PINs, digits = 0),
         class_PIN_pct = major_class_PINs / nbh_PINs,
         #class_EAV = round(av * 3.0027, digits = 0),
         class_over_total_EAV = Total_EAV.x / Total_EAV.y) %>%
  select(nbhd_code, major_class_code, exempt_over_total_EAV, nbh_PINs, major_class_PINs, exempts_perPIN, class_PIN_pct, Total_EAV.x, class_over_total_EAV, everything()) %>% 
  arrange(nbhd_code)

nbh_data

nbh_res <- nbh_data %>% 
  filter(major_class_code == 2) %>% 
  select(nbhd_code, major_class_code, 
         residential_PC = major_class_PINs, 
         res_EAV = Total_EAV.x, 
         res_exemptions = Exempt_EAV) %>%
  mutate(nbhd_code = as.character(nbhd_code),
         res_exe_per_res_pin = round(res_exemptions / residential_PC)
  )
  
  
#nbh_data2 <- nbh_data %>% left_join(nbh_res, by = "nbhd_code") %>% select(nbhd_code, all_PINs, residential_PC, incentive_PINs, everything()) %>% mutate(exemps_per_resPIN = res_exemptions / residential_PC) %>% arrange(nbhd_code)


fig4 <- nbh_res %>% 
  filter(major_class_code  == 2) %>%
  filter(residential_PC > 4) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = res_exe_per_res_pin)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Average exemptions per Residential Class 2 PIN") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "white",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "Exemptions per PIN")

fig4

Maps

fig1 <- nbh_data %>% 
  filter(major_class_code == 2) %>%
  #filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8")) %>%
  #group_by(nbhd_code)%>%
  #summarize(pin_count = sum(pin_count)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = exempt_over_total_EAV)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Exempt EAV (from Residential Exemptons) / Total EAV",
      # caption = "Total EAV includes TIF and all EAV before exemptions are deducted"
       ) +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "forestgreen", low = "white",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "% Exempt")

fig1

fig3 <- nbh_data %>% 
  filter(major_class_code %in% c("6A", "6B", "6C", "7A", "7B", "8")) %>%
  group_by(nbhd_code)%>%
  summarize(incentives_over_total_EAV = sum(Total_EAV.x/Total_EAV.y) ) %>%
  #summarize(pin_count = sum(pin_count)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = incentives_over_total_EAV)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "EAV of incentive properties / Total EAV") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "orange", low = "yellow",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "%")

fig3

fig2 <- nbh_data %>% 
  filter(major_class_code == "2") %>%
  #group_by(nbhd_code)%>%
  #summarize(pin_count = sum(pin_count)) %>% 
  full_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot(aes(fill = exempts_perPIN)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Average exemptions per PIN") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
  scale_fill_steps2(
    high = "darkblue", low = "white",
    #nice.breaks = FALSE,
    show.limits=TRUE,
    na.value = NA,
    name = "Exemptions per PIN")

fig2