Mapping Property Tax Burden Shift due to Exemptions

Cook County Municipalities (with >50% EAV within Cook County)

Getting Data

Data Prep

Pull all agency names that exist, then use agency numbers associated with MUNI types to pull only the muni_agency_names object.

There are 946 unique taxing agencies that existed in 2021.

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

# grabs all unique muni names. Would be needed if creating a loop for calculating all munis
# municipality names and their agency number
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_agency_names <- muni_agency_names %>% 
    mutate(first6 = str_sub(agency_num,1,6),
         first5 = str_sub(agency_num,1,5)) %>% 
  select(-minor_type)



#Makes a list of ALL taxing agencies, including TIFs, SSAs, etc.

# all agency names, numbers, and types
# includes TIF and non-TIF agencies
all_taxing_agencies <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT agency_num, agency_name, major_type, minor_type
  FROM agency_info
  "
) %>%
  mutate(first6 = str_sub(agency_num,1,6),
         first5 = str_sub(agency_num,1,5))


muni_agency_nums<- all_taxing_agencies %>% 
  filter(minor_type %in% c("MUNI") | 
           agency_num == "020060000") %>%
   select(agency_num)

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

# Agency number and agency name for all TIFs
TIF_agencies <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT DISTINCT agency_num, agency_name, major_type, minor_type
  FROM agency_info
  WHERE minor_type = 'TIF'
  "
)

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



## Read in summarized tax code level data for exemptions and taxbills.

taxbills_by_Class_per_TC <- #read_csv("taxbills_inMunis_perTC.csv")  %>% 
  read_csv("2_Summed_Bills_by_Taxcode_and_Class.csv") %>%
  mutate(tax_code = as.character(tax_code)) 


# get rid of rpm variables, tax_amt_exe,pre and post exemption variables,
exemptions_by_class_per_TC <- #read_csv("all_exemptions_by_TC.csv") %>%
  read_csv("3_Exemptions_Details_output-ClassTaxcodeExemps.csv") %>%
    mutate(tax_code_num = as.character(tax_code_num))

tif_distrib <- DBI::dbGetQuery(
  ptaxsim_db_conn, 
  glue_sql("
  SELECT *
  FROM tif_distribution
  WHERE tax_code_num IN ({muni_tax_codes$tax_code_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
) %>% mutate(tax_code_num = as.character(tax_code_num))



all_taxing_agencies <- all_taxing_agencies %>% 
  left_join(muni_agency_names, by = c("first5", "first6")) %>% 
  rename(muni_name =  agency_name.y,
        muni_num = agency_num.y,
        agency_name = agency_name.x,
        agency_num = agency_num.x)






## Add agency names since those weren't included originally in the table:

# all_taxing_agencies <- all_taxing_agencies %>% 
#   left_join(muni_agency_names, by = c("first5", "first6")) %>% 
#   rename(muni_name =  agency_name.y,
#         muni_num = agency_num.y,
#         agency_name = agency_name.x,
#         agency_num = agency_num.x)
# 


# combine taxing agency names and agency type to data table that has eav and extension values
agency_data <- right_join(agency_dt, all_taxing_agencies) %>% 
  # get rid of unneeded columns to make table outputs smaller
  select(-c(cty_dupage_eav:cty_livingston_eav, lim_numerator, lim_denominator)) %>% # drop some of the unused variables
  arrange(agency_num)

This table is used for the municipality levy in later calculations.

agency_dt has all taxing agencies (but not TIFs) that existed each year and includes their total taxable base (cty_cook_eav), their levy, taxing rate, binary variables for if a municipality is home rule or not, as well as many other variables. Also currently doesn’t have agency_names to go with the agency numbers. That will be merged in shortly.

There are 1,878 taxing agencies. When grouped by minor_type, there are 133 muni agencies, 639 tif agencies, 30 townships, etc.

Everything depends on the tax codes.

Using the agency numbers for each municipality, I pull all tax codes that have an agency_num included in the muni_agency_names object. By narrowing the agencies down to just Municipality types, this prevents duplicate tax_codes from being pulled.

There are 3774 tax codes within Cook County that are taxed by Municipalities. There are 4228 unique tax codes in Cook County in 2021.

Drop municipalities where most of the EAV is outside of Cook County:

cross_county_lines <- c("030440000",
"030585000", "030890000", "030320000", "031280000","030080000", "030560000", "031120000", "030280000", "030340000","030150000","030050000", "030180000","030500000","031210000"
)

# Frankfort, Homer Glenn, Oak Brook are below 1% in Cook County
# East Dundee, University Park, Bensenville, Hinsdale, Roeelle, Deerfield are below 15% in Cook County

# Above 50%:  buffalo grove, bertlette, burr ridge, hanover park, steger, 
# tinley park, barrington hills and barrington, and park forest 

# ALL border crossers:
#cross_county_lines <- c("030030000", "030040000", "030050000", "030080000","030150000","030180000", "030280000", "030320000","030340000", "030440000","030500000", "030560000", "030585000", "030890000",  "031000000","031120000","031210000","031270000", "031280000")


# Municipalities | Triad | agency_num 
# 
# CITY OF ELGIN North   30340000  
# VILLAGE OF BARRINGTON North   30030000  
# VILLAGE OF BARRINGTON HILLS   North   30040000  
# VILLAGE OF BARTLETT   North   30050000  
# VILLAGE OF BENSENVILLE    NA  30080000  
# VILLAGE OF BUFFALO GROVE  North   30150000  
# VILLAGE OF BURR RIDGE South   30180000  
# VILLAGE OF DEERFIELD  NA  30280000  
# VILLAGE OF EAST DUNDEE    NA  30320000  
# VILLAGE OF FRANKFORT  NA  30440000  
# VILLAGE OF HANOVER PARK   North   30500000  
# VILLAGE OF HINSDALE   South   30560000  
# VILLAGE OF HOMER GLEN NA  30585000  
# VILLAGE OF OAK BROOK  NA  30890000  
# VILLAGE OF PARK FOREST    South   31000000  
# VILLAGE OF ROSELLE    North   31120000  
# VILLAGE OF STEGER South   31210000  
# VILLAGE OF TINLEY PARK    South   31270000  
# VILLAGE OF UNIVERSITY PARK    NA  31280000  

Proportion of Residential Land in Municipalities

using the pin data from ptaxsim, I calculate the eav of all properties, exempt EAV, the current taxbase, and taxbase if there were not exemptions

• exempt EAV (summed from all exemption types)

• current taxbase (using the tif_distrib table and percent of taxcode rev that goes to the tif). If a taxcode is a TIF taxcode, then do (EAV-exempt EAV) * (1-%rev that goes to TIF). If the tax code is not a TIF tax code, then use the EAV-exempt EAV.

• tax base without exemptions: If a taxcode is a TIF taxcode, then do EAV * (1-%rev that goes to TIF). If the tax code is not a TIF tax code, then use the eav

Joining problem: in muni_agency_names, Cicero is called “TOWN CICERO”, in map shapefile it is called “CITY OF CICERO”.

class_dict <- read_csv("class_dict_expanded.csv")

# use exemptions in tax codes to summarize EAV. 
# More accurate that calculating it from revenue collected.tax rate in tax bill data
exemptions_by_class_per_TC <- read_csv("3_Exemptions_Details_output-ClassTaxcodeExemps.csv") %>% 
  filter(class_code != "0") %>%
  left_join(class_dict) %>%
  mutate(tax_code_num = as.character(tax_code_num)) %>%
  left_join(muni_tax_codes) %>%
  full_join(muni_agency_names) %>%
  left_join(nicknames, by = c("agency_name" = "agency_name")) %>%
  filter(!agency_num %in% cross_county_lines) %>%
  
  # merge with TIF distrib table to calculate the percent of the EAV that goes to the TIF vs the district
  left_join(tif_distrib, by=c("tax_code_num", "year", "tax_code_rate")) %>%
  mutate(exempt_EAV = (exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner + 
         exe_disabled + exe_vet_returning + exe_vet_dis + exe_abate) ,
         in_TIF = ifelse(tax_code_num %in% unique_tif_taxcodes$tax_code_num, 1, 0),
         tax_base_current = ifelse(in_TIF==1, (eav-exempt_EAV)*(1-tax_code_distribution_pct/100), eav-exempt_EAV),
         tax_base_noexemps = ifelse(in_TIF==1, (eav)*(1-tax_code_distribution_pct/100), eav),
         ResidentialProps = ifelse(major_class_code %in% c("2", "3", "9"), "Residential", "Commercial")) 
#,
         # 
         # PropType = case_when(
         #   major_class_code %in% c("3","9") ~ "Multi-Family",
         #   major_class_code == "2" ~ "Single-Family",
         #   TRUE~ "Commercial-Industrial")

Exemptions within each municipality:

Percent Residential is the Residential EAV outside of TIFs / Municipality EAV outside of TIFs.

grouped_exemptions <- exemptions_by_class_per_TC %>% 
   # group_by(agency_name, major_class_code, major_class_type, ResidentialProps, PropType) %>%
  group_by(clean_name, ResidentialProps, agency_name, agency_num.x) %>%
  summarize(eav = sum(eav),
           exempt_EAV = sum(exempt_EAV, exe_abate, na.rm=TRUE),
         tax_base_current = sum(tax_base_current, na.rm=TRUE),
         tax_base_noexemps = sum(tax_base_noexemps, na.rm=TRUE)) %>% ungroup() %>% select(clean_name, eav, exempt_EAV, everything())
  
grouped_exemptions 

# calculate totals with ONLY EAV outside of TIFs
muni_eav <- grouped_exemptions %>%  
  group_by(clean_name, agency_name, agency_num.x) %>% 
  summarize(muni_EAV_includesTIF = sum(eav), # all EAV in the municipality that exists
            muni_tax_base_current=sum(tax_base_current), # taxable EAV based on current exemptions
            muni_tax_base_noexemps = sum(tax_base_noexemps)) %>%  # taxable EAV pre-exemptions
  ungroup() 

perc_residential <- full_join(grouped_exemptions, muni_eav) %>% 
  filter(ResidentialProps == "Residential") %>% 
  
  mutate(percent_residential = eav / muni_EAV_includesTIF)# %>% select()


# in this stage, Bensenville, East Dundee, and Homer Glen were dropped. This is fine because I was going to drop them later anyways. They don't have residential EAV within Cook County.

perc_residential

library(ggpattern)

Residential (Only Class 2)

In Cook County there is $118,936,418,533 in Class 2 Residential Property EAV. Note: Class 2 properties include single-family detached homes, condos, and some rental buildings.

# Class 2 only
class2_perc <- exemptions_by_class_per_TC %>% 
    filter(major_class_code == 2) %>%
  group_by(clean_name, major_class_code, agency_name, agency_num.x) %>%
  summarize(eav = sum(eav),
           exempt_EAV = sum(exempt_EAV, exe_abate, na.rm=TRUE),
         tax_base_current = sum(tax_base_current, na.rm=TRUE),
         tax_base_noexemps = sum(tax_base_noexemps, na.rm=TRUE)) %>% 
  left_join(muni_eav) %>%
  mutate(perc_class2_totalEAV = eav / muni_EAV_includesTIF) %>%    
  mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) )



class2_perc %>%
  full_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = perc_class2_totalEAV)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Class 2 Property EAV /  Total EAV in Municipality", 
  caption = "") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+# +#+
  scale_fill_steps2(
    high = "darkblue", low = "black",
    midpoint = median(class2_perc$perc_class2_totalEAV),
  #  breaks = breaks_sd,  # tried to do breaks based on standard deviation but map does that in an easier way by setting the midpoint.
  na.value = "white",
    nice.breaks = FALSE,
    show.limits=TRUE,
    name = "% Residential",
    labels = scales::percent)

The median municipality has 66.84% of its EAV from Residential Class 2 Properties. Total EAV includes frozen EAV & TIF increments, and all exempt EAV.

Cook_layer <- ggplot(data = cook_shp,aes(geometry = geometry)) + 
  geom_sf_pattern() + theme_void()


class2_perc %>%
  full_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot() + 
  geom_sf_pattern(data = cook_shp, aes(geometry = geometry),  pattern_alpha = 0.1, 
                 pattern_scale = .5, 
                  pattern_angle = 45, pattern_fill = NA, 
              #pattern_density = .5, 
              pattern_spacing = .02
                  ) + 
  geom_sf(aes(geometry = geometry, fill = perc_class2_totalEAV), color = "black") + 
  labs(title = "Class 2 Property EAV /  Total EAV in Municipality", 
  caption = "") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+# +#+
  scale_fill_steps2(
    high = "darkblue", low = "maroon",
    midpoint = median(class2_perc$perc_class2_totalEAV),
  #  breaks = breaks_sd,  # tried to do breaks based on standard deviation but map does that in an easier way by setting the midpoint.
  na.value = NA,
    nice.breaks = FALSE,
    show.limits=TRUE,
    name = "% Residential",
    labels = scales::percent)

Percent of Class 2 EAV that is tax exempt: Exempt EAV / Residential EAV

exemptions_to_class2EAV_ratios <- class2_perc %>% 
  mutate(exemptEAV_pctof_resEAV = exempt_EAV/eav,
         nontif_ratio = exempt_EAV / tax_base_noexemps) %>% 
  select(agency_name, clean_name, exemptEAV_pctof_resEAV, nontif_ratio) %>% 
  arrange(nontif_ratio)

exemptions_to_class2EAV_ratios %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
 left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = exemptEAV_pctof_resEAV)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Exemptions / Residential EAV (in and out of TIFs)") +
    theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "darkblue", low = "black", 
                      # limits = c(0,.8),
                       n.breaks = 7, show.limits=TRUE,
                    nice.breaks = FALSE,
                    midpoint = median(exemptions_to_class2EAV_ratios$exemptEAV_pctof_resEAV),
                        name = "% Residential EAV \nthat is exempt", label = scales::percent)


exemptions_to_class2EAV_ratios %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = nontif_ratio)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Non-TIF EAV only: Homestead Exemptions / Residential EAV", caption = "Village of Phoenix skews graph. Dropped in map below") +
    theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "darkblue", low = "black",
     #colors = c("white", "darkblue"), 
                      # limits = c(0),
                    midpoint = median(exemptions_to_class2EAV_ratios$nontif_ratio),
                       n.breaks = 7, 
     show.limits=TRUE,
                        name = "% Residential EAV \nthat is exempt", label = scales::percent) + 
  labs( caption = "Median value is 20.85% ")

Median value for single-family EAV is 20.76%.

exemptions_to_class2EAV_ratios %>%  filter(nontif_ratio<.6) %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = nontif_ratio)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Percent of Residential EAV that is Tax Exempt", 
  subtitle = "% of Non-TIF Residential EAV only: \nHomestead Exemptions / Residential EAV", 
  caption = "") +
    theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "darkblue", low = "black",
   #  colors = c("white", "darkblue"), 
                       limits = c(0,.4),
   midpoint = median(exemptions_to_class2EAV_ratios$nontif_ratio),
                       n.breaks = 7, show.limits=TRUE,
                        name = "% Residential EAV \nthat is tax exempt", label = scales::percent)

Drops Village of Phoenix because it skews map colors (78% of their residential EAV is tax exempt). Median municipality can not get tax revenue from 1/5th of their residential EAV due to exemptions. 20.85% of Single-family home EAV is not taxed and transferred to other tax payers.

Residential (Class 2, 3, & 9)

The median amount of EAV from residentical parcels is 70%.

variable <- perc_residential$percent_residential

breaks_sd = c(
  (median(variable)-2.5*sd(variable)),
  (median(variable)-1.5*sd(variable)),
  median(variable)-.5*sd(variable),
  median(variable),
  median(variable)+.5*sd(variable),
  median(variable)+1.5*sd(variable),
  median(variable)+2.5*sd(variable))



# class 2, 3, 9 combined
perc_residential %>% 
  group_by(ResidentialProps)%>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = percent_residential)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Residential EAV /  Total EAV", caption = "The median municipality has 70.4% of its EAV from Residential Class 2, 3, & 9 Properties") +
    theme_classic() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+# +#+
    scale_fill_steps2(
    high = "darkblue", low = "black",  #  guide = "legend",
      midpoint = median(perc_residential$percent_residential),
                      breaks = breaks_sd,
  #                      n.breaks = 6,
  show.limits=TRUE,
                        name = "% Residential",
  labels = scales::percent)

exemptions_to_resEAV_ratios <- grouped_exemptions %>% 
  filter(ResidentialProps == "Residential") %>% 
  mutate(exemptEAV_pctof_resEAV = exempt_EAV/eav,
         nontif_ratio = exempt_EAV / tax_base_noexemps) %>% 
  select(agency_name, clean_name, exemptEAV_pctof_resEAV, nontif_ratio) %>% 
  arrange(nontif_ratio)

exemptions_to_resEAV_ratios %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
 left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = exemptEAV_pctof_resEAV)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Exemptions / Residential EAV (in and out of TIFs)") +
    theme_classic() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "maroon", low = "black", 
                      # limits = c(0,.8),
                       n.breaks = 7, show.limits=TRUE,
                    nice.breaks = FALSE,
                    midpoint = median(exemptions_to_resEAV_ratios$exemptEAV_pctof_resEAV),
                        name = "% Residential EAV \nthat is exempt", label = scales::percent) + labs(caption = "Median value is 19.5%. 
                                                                                                     Nearly 20% of Residential EAV is tax exempt in the median municipality. ")

exemptions_to_resEAV_ratios %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = nontif_ratio)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Non-TIF EAV only: Homestead Exemptions / Residential EAV", caption = "Village of Phoenix skews graph. Dropped in map below") +
    theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "maroon", low = "black",
     #colors = c("white", "darkblue"), 
                       limits = c(0,.8),
                    midpoint = median(exemptions_to_resEAV_ratios$nontif_ratio),
                       n.breaks = 7, show.limits=TRUE,
                        name = "% Residential EAV \nthat is exempt", label = scales::percent) + labs( caption = "Median value is 19.77% ")

exemptions_to_resEAV_ratios %>% 
  filter(nontif_ratio<.5) %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = nontif_ratio)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Percent of Residential EAV that is Tax Exempt", 
  subtitle = "% of Non-TIF Residential EAV only: \nHomestead Exemptions / Residential EAV", 
  caption = "Residential includes Class 2, 3, and 9.
  Drops Village of Phoenix because it skews map colors (78% of their residential EAV is tax exempt). 
  Median municipality can not get tax revenue from 19.77% of their residential EAV due to exemptions.") +
    theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "maroon", low = "black",
   #  colors = c("white", "darkblue"), 
                      # limits = c(0,.4),
   midpoint = median(exemptions_to_resEAV_ratios$nontif_ratio),
                     #  n.breaks = 7, 
   show.limits=TRUE,
                        name = "% Residential EAV \nthat is exempt", label = scales::percent)

exemptions_to_resEAV_ratios %>% arrange(desc(nontif_ratio))

Residential (Owner Occupied)

Municipality total EAV excludes TIF increment EAV. Frozen base EAV is included.

grouped_exemptions <- exemptions_by_class_per_TC %>% 
left_join(class_dict) %>%
  group_by(clean_name, Alea_cat, agency_name, agency_num.x) %>%
  summarize(eav = sum(eav),
           exempt_EAV = sum(exempt_EAV, exe_abate, na.rm=TRUE),
         tax_base_current = sum(tax_base_current, na.rm=TRUE),
         tax_base_noexemps = sum(tax_base_noexemps, na.rm=TRUE)) %>% ungroup() %>% 
  select(clean_name, eav, exempt_EAV, everything())
  
grouped_exemptions 

# calculate totals with ONLY EAV outside of TIFs
muni_eav <- grouped_exemptions %>%  
  group_by(clean_name, agency_name, agency_num.x) %>% 
  summarize(muni_EAV_includesTIF = sum(eav), # all EAV in the municipality that exists
            muni_tax_base_current=sum(tax_base_current), # taxable EAV based on current exemptions
            muni_tax_base_noexemps = sum(tax_base_noexemps)) %>%  # taxable EAV pre-exemptions
  ungroup() 

perc_residential <- full_join(grouped_exemptions, muni_eav) %>% 
 # filter(ResidentialProps == "Residential") %>% 
  filter(Alea_cat == "Owner Occupied") %>% 
  mutate(percent_owned = eav / muni_EAV_includesTIF)# %>% select()


# in this stage, Bensenville, East Dundee, and Homer Glen were dropped. This is fine because I was going to drop them later anyways. They don't have residential EAV within Cook County.

perc_residential

The median municipality has 63% of their EAV in the form of owner occupied properties.

While this is very similar to the percentage calculated using only Class 2 residential properties. There are some municipalities that experience large changes due to the change in categorization of Owner occupied vs rental compared to Class 2 or multi-family vs single-family properties.

variable <- perc_residential$percent_owned

breaks_sd = c(
  (median(variable)-2.5*sd(variable)),
  (median(variable)-1.5*sd(variable)),
  median(variable)-.5*sd(variable),
  median(variable),
  median(variable)+.5*sd(variable),
  median(variable)+1.5*sd(variable),
  median(variable)+2.5*sd(variable))



perc_residential %>% 
  filter(Alea_cat == "Owner Occupied")%>%
  mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  full_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = percent_owned)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  labs(title = "Owner Occupied EAV /  Total EAV", 
      # caption = "The median municipality has 70.4% of its EAV from Residential Class 2, 3, & 9 Properties"
       ) +
    theme_classic() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank()) +
    scale_fill_steps2(
    high = "darkblue", low = "black",  #  guide = "legend",
    midpoint = median(perc_residential$percent_owned, na.rm = TRUE),
                      #breaks = breaks_sd,
  #                      n.breaks = 6,
  show.limits=TRUE,
                        name = "% Owner Occupied",
  na.value = "beige",
  labels = scales::percent
  )

exemptions_to_resEAV_ratios <- grouped_exemptions %>% 
  mutate(ResidentialProps = ifelse(Alea_cat %in% c("Rental", "Owner Occupied", "Other Residential"), "Residential", "Non-residential")) %>%
  filter(ResidentialProps == "Residential") %>% 
  mutate(exemptEAV_pctof_resEAV = exempt_EAV/eav,
         nontif_ratio = exempt_EAV / tax_base_noexemps) %>% 
  select(agency_name, clean_name, exemptEAV_pctof_resEAV, nontif_ratio) %>% 
  arrange(nontif_ratio)

exemptions_to_resEAV_ratios %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
 left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = exemptEAV_pctof_resEAV)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Exemptions / Residential EAV (in and out of TIFs)") +
    theme_classic() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "maroon", low = "black", 
                       n.breaks = 7, show.limits=TRUE,
                    nice.breaks = FALSE,
                    midpoint = median(exemptions_to_resEAV_ratios$exemptEAV_pctof_resEAV),
                        name = "% Residential EAV \nthat is exempt", label = scales::percent) + labs(caption = "Median value is 19.18%. 
                                                                                                     Nearly 19.2% of Owner Occuppied EAV is tax exempt in the median municipality. ")

exemptions_to_resEAV_ratios %>% 
  filter(nontif_ratio<.5) %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = nontif_ratio)) + 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Percent of Owner Occupied EAV that is Tax Exempt", 
  subtitle = "% of Non-TIF Owner Occupied EAV only: \nHomestead Exemptions / Residential EAV", 
  caption = "Drops Village of Phoenix because it skews map colors (78% of their residential EAV is tax exempt). 
  Median municipality can not get tax revenue from 19.29% of their residential EAV due to exemptions.") +
    theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(high = "maroon", low = "black",
   midpoint = median(exemptions_to_resEAV_ratios$nontif_ratio),
   show.limits=TRUE,
                        name = "% Owner Occupied EAV \nthat is exempt", label = scales::percent)

exemptions_to_resEAV_ratios %>% arrange(desc(nontif_ratio))

Taxable Base and Muni Land Use

Tax base in the tables above are the total EAV outside of TIF areas.

TC_bills_current <- read_csv("2_Summed_Bills_by_Taxcode_and_Class.csv") %>% 
  filter(class != "0") %>% 
  mutate(tax_code = as.character(tax_code),
         class = as.character(class))

class_dict$class_code <- as.character(class_dict$class_code)
 

taxcodes_current <- full_join(TC_bills_current, muni_tax_codes, 
                      by = c("tax_code" = "tax_code_num")) 

Current_Taxrates <- taxcodes_current %>% 
  left_join(muni_agency_names) %>%
  left_join(nicknames, by = c("agency_name" = "agency_name")) %>%
  filter(!agency_num %in% cross_county_lines) %>%
  group_by(clean_name, agency_name) %>%
  summarize(MuniLevy = sum(final_tax_to_dist, na.rm = TRUE), # amount billed by munis with current exemptions in place
            nonTIF_EAV_post_exemps = sum(final_tax_to_dist/(tax_code_rate/100), na.rm = TRUE),
            TIF_increment_EAV = sum(final_tax_to_tif/(tax_code_rate/100), na.rm=TRUE),  
            Exempt_EAV = sum(tax_amt_exe/(tax_code_rate/100), na.rm=TRUE), 
            Total_EAV = sum((tax_amt_exe+final_tax_to_dist+final_tax_to_tif)/(tax_code_rate/100), na.rm = TRUE)) %>%

  mutate(tax_rate_current = MuniLevy/nonTIF_EAV_post_exemps,
         nonTIF_EAV_pre_exemps = nonTIF_EAV_post_exemps + Exempt_EAV,
         taxrate_new = MuniLevy/nonTIF_EAV_pre_exemps,
         taxrate_change = tax_rate_current-taxrate_new) %>% 
  select(clean_name, taxrate_change, tax_rate_current, taxrate_new, everything()) %>% 
  arrange(desc(tax_rate_current))

land_use <- taxcodes_current %>% 
  left_join(muni_agency_names) %>% 
  left_join(Current_Taxrates) %>%
    left_join(class_dict, by = c("class" = "class_code")) %>%

  mutate(class_1dig = str_sub(class, 1, 1),
    ResidentialProps = ifelse(class_1dig %in% c("2", "3", "9"), "Residential", "Non-Residential")) %>%
  #        PropType = case_when(
  #          major_class_code %in% c("3","9") ~ "Multi-Family",
  #          major_class_code == "2" ~ "Single-Family",
  #          TRUE ~ "Commercial-Industrial")) %>%
  group_by(clean_name, Alea_cat, agency_num, tax_rate_current, taxrate_new, taxrate_change, agency_name) %>% 
  
  # All of the values calculated below are AFTER exemptions have been removed
  summarize(taxrev_from_proptype = sum(final_tax_to_dist, na.rm = TRUE),
            nonTIF_EAV = sum(final_tax_to_dist/(tax_code_rate/100), na.rm = TRUE),
            TIF_increment_EAV = sum(final_tax_to_tif/(tax_code_rate/100), na.rm=TRUE),
            Exempt_EAV = sum(tax_amt_exe/(tax_code_rate/100), na.rm=TRUE),
            Total_EAV = sum((tax_amt_exe+final_tax_to_dist+final_tax_to_tif)/(tax_code_rate/100), na.rm = TRUE) ) %>% ungroup()

Current_Taxrates

Current composite tax rates for each municipality are above. Table also includes the new tax rate if there were no exemptions, the levy (aka amount collected by the municipality from final_tax_to_dist variable), EAV outside of TIFs, amount of exempt EAV, and additional variables.

Below: Take EAV values within each property type, join it with muni level EAV values, and calculate …

grouped_exemptions <- exemptions_by_class_per_TC %>% 
   # group_by(agency_name, major_class_code, major_class_type, ResidentialProps, PropType) %>%
  group_by(clean_name, Alea_cat, agency_name) %>%
  summarize(eav = sum(eav),
           exempt_EAV = sum(exempt_EAV, exe_abate, na.rm=TRUE),
         tax_base_current = sum(tax_base_current, na.rm=TRUE),
         tax_base_noexemps = sum(tax_base_noexemps, na.rm=TRUE)) %>% ungroup()
  

# calculate totals with ONLY EAV outside of TIFs
muni_eav <- grouped_exemptions %>%  
  group_by(clean_name, agency_name) %>% 
  summarize(muni_EAV_includesTIF = sum(eav), # all EAV in the municipality that exists
            muni_tax_base_current=sum(tax_base_current), # taxable EAV based on current exemptions
            muni_tax_base_noexemps = sum(tax_base_noexemps)) %>%  # taxable EAV pre-exemptions
  ungroup() 

pct_property_types <- left_join(grouped_exemptions, muni_eav) %>% 
 # filter(ResidentialProps == "Alea_cat") %>% 
  mutate(pct_PropType = eav / muni_EAV_includesTIF)# %>% select()

pct_property_types

burden_table <- pct_property_types %>% 
  left_join(Current_Taxrates, by = c("agency_name", "clean_name")) %>%
  mutate(rev_collected_current = tax_base_current * tax_rate_current,
         rev_collected_new = tax_base_noexemps*taxrate_new,
         burden_current = rev_collected_current/MuniLevy,
         burden_noexemps = rev_collected_new/MuniLevy, 
         burden_change = burden_noexemps- burden_current,
         burden_noexemps = ifelse(burden_noexemps >1, 1, burden_noexemps)) %>%
  mutate(burden_current = ifelse(is.na(burden_current), 0, burden_current),
         burden_noexemps = ifelse(is.na(burden_noexemps), 0, burden_noexemps)) %>%
  mutate(burden_noexemps = ifelse(burden_noexemps>1, 1, burden_noexemps),
         burden_current = ifelse(burden_current>1, 1, burden_current)) %>%
  mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) )


burden_shift <- burden_table # only to not change code below in graphs. 

Composite Tax Rate Change

The tax rate shown is the composite tax rate for the Municipality. The composite tax rate is the aggregate of the tax rate for each taxing agency X the EAV within the taxing jurisdiction. This was calculated at a tax code level first and then added together for all taxcodes taxed by a municipal taxing agency.

6 Highest and 6 lowest tax rates. View the extremes on both sides

library(kableExtra)
Current_Taxrates %>%  filter(tax_rate_current > 0.25 ) %>% select( -agency_name) %>%
  kbl(caption = "Highest Composite Tax Rates" , row.names=FALSE) %>% 
     kable_classic()

Highest Composite Tax Rates

clean_name	taxrate_change	tax_rate_current	taxrate_new	MuniLevy	nonTIF_EAV_post_exemps	TIF_increment_EAV	Exempt_EAV	Total_EAV	nonTIF_EAV_pre_exemps
Park Forest	0.1445	0.4136	0.2691	37786878	91359693	7860014	49058933	148278640	140418626
Riverdale	0.0677	0.3065	0.2389	29466293	96122768	0	27234709	123357478	123357478
Phoenix	0.1362	0.2981	0.1619	1611282	5405017	3879516	4548727	13833260	9953744
Harvey	0.0507	0.2726	0.2220	54729654	200734513	9158437	45848694	255741644	246583207
Markham	0.0619	0.2652	0.2032	31891600	120271847	15224108	36651935	172147890	156923782
Calumet City	0.0583	0.2606	0.2023	92759714	355895328	9193252	102654888	467743468	458550216

Current_Taxrates %>%  filter( tax_rate_current < 0.08)  %>%   select( -agency_name) %>%
  kbl(caption = "Lowest Composite Tax Rates" , row.names=FALSE) %>% 
     kable_classic()

Lowest Composite Tax Rates

clean_name	taxrate_change	tax_rate_current	taxrate_new	MuniLevy	nonTIF_EAV_post_exemps	TIF_increment_EAV	Exempt_EAV	Total_EAV	nonTIF_EAV_pre_exemps
Countryside	0.0054	0.0790	0.0737	26921653	340570898	8300179	24803814	373674890	365374711
Lemont	0.0072	0.0784	0.0712	56597990	722097359	11370587	72740257	806208203	794837616
Chicago	0.0044	0.0673	0.0629	6536952735	97124833788	17702266790	6839135241	121666235819	103963969029

#quantile(Current_Taxrates$tax_rate_current )

#sd(Current_Taxrates$tax_rate_current)

The current composite tax rate for the median municipality is 12%

Current_Taxrates %>% 
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
    ) %>%
  #  filter(major_class_code == 2) %>% # all property types have same composite tax rate
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  
  ggplot(aes(fill = tax_rate_current)) +
  geom_sf(aes(geometry = geometry), color = "black") + 
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(#colors = colors,
    high = "#420420", low = "black",
    midpoint = median(Current_Taxrates$tax_rate_current), #  midpoint = 0.123,
    #breaks = breaks_sd,
    limits = c(0,.45),
    show.limits=TRUE,
    nice.breaks=FALSE,
    n =6,
    name = "Tax Rate",
    label = scales::percent )+
  
  labs(title = "Current composite tax rates" ,    
       caption = "The current median composite tax rate is 12.3%. 
         Highest composite tax rate is in Park Forest (41.4%.)
       Lowest composite tax rate is in Chicago (6.7%).")

# median 
burden_shift %>% 
  filter(Alea_cat == "Owner Occupied") %>%
  #filter(major_class_code == 2) %>%
  summarize(median_currentburden = median(burden_current),
            median_new_burden = median(burden_noexemps),
            median_burdenchange = median(burden_change),
            median_currenttaxrate = median(tax_rate_current),
            median_taxratechange = median(taxrate_change),
            median_taxratenew = median(taxrate_new)) %>% 
  pivot_longer(cols = everything(), names_to = "Stat", values_to = "Value")

burden_shift %>% 
  filter(Alea_cat == "Rental") %>%
  #filter(major_class_code == 2) %>%
  summarize(median_currentburden = median(burden_current),
            median_new_burden = median(burden_noexemps),
            median_burdenchange = median(burden_change),
            median_currenttaxrate = median(tax_rate_current),
            median_taxratechange = median(taxrate_change),
            median_taxratenew = median(taxrate_new)) %>% 
  pivot_longer(cols = everything(), names_to = "Stat", values_to = "Value")

Current_Taxrates %>% 
      mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
    ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = taxrate_new)) + 
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
    high = "#420420", low = "#008080",
    limits = c(0,.45),
    midpoint = median(Current_Taxrates$taxrate_new),  # midpoint = .1052,
    nice.breaks=FALSE,
    show.limits=TRUE,
    n=6,
    name = "Tax Rate", label = scales::percent)+
  geom_sf(aes(geometry = geometry), color = "black") +  
  labs(title = "New composite tax rates if exemptions were eliminated" ,    
       caption = "The new median composite tax rate would be approximately 10.5% 
       if exemptions were removed.")

Current_Taxrates %>% 
  mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  # filter(clean_name != "Park Forest") %>%
  full_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = taxrate_change)) + 
  geom_sf(aes(geometry = geometry), color = "black") +  
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  
  scale_fill_steps2(high = "blue", low = "black",
    n=6, midpoint = 0.01388, 
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value = "lightgray",
    labels = scales::percent,
    name = "Percentage Point \nDifference")+
  labs(title = "Change in Composite Tax Rate if Exemptions are Removed",
       caption = "The median change in composite tax rate is 1.43 percentage points")

# as a dot graph ## 

order <- burden_shift %>%  
  as_tibble() %>% 
  summarize(agency_name = unique(agency_name), 
            clean_name = unique(clean_name), 
            tax_rate_current = unique(tax_rate_current), 
            taxrate_new = unique(taxrate_new)) %>% 
  arrange(tax_rate_current) %>%
  select(agency_name, clean_name, tax_rate_current)

head(order)

# look at ones that changed the most
burden_shift %>% 
  # filter(tax_rate_current < (median(tax_rate_current))+0.002 & tax_rate_current > (median(tax_rate_current))-0.002 |
  #  (tax_rate_current > 0.25 | tax_rate_current < 0.08 )) %>%
  #          (tax_rate_current < (median(tax_rate_current))+0.005 & tax_rate_current > (median(tax_rate_current))-0.005 )) %>% 
  #filter(PropType == "Single-Family") %>%
  filter(Alea_cat == "Owner Occupied") %>%
  ungroup() %>% 
  select(clean_name, tax_rate_current, taxrate_new, agency_name) %>% 
  pivot_longer(c("tax_rate_current", "taxrate_new"), 
               names_to = "type", values_to = "tax_rate") %>% 
  left_join(order) %>%
  ggplot(aes(x = tax_rate*100, y= reorder(clean_name, tax_rate_current)))+

  geom_line(aes(group = clean_name))+ 
      geom_hline(yintercept = 3.5, linetype = 2)+
    geom_hline(yintercept = 13.5, linetype = 2) +
   geom_point(aes(color=type), size=3 )+
  theme_minimal() + 
  theme( 
    legend.title = element_blank(),
               plot.title.position = "plot",
     #   panel.background = element_rect(fill='transparent'), #transparent panel bg
    plot.background = element_rect(fill='transparent', color=NA) #transparent plot bg
   )+
        scale_color_brewer(palette="Paired", labels = c("Exemptions", "No Exemptions"), direction = 1)+

  labs(title = "Difference in Composite Tax Rate if there were No Exemptions",
       subtitle = "Ordered by Current Composite Tax Rate", x = "Composite Tax Rate (%)", y = "" , 
       caption = "For the highest, median, and lowest municipality composite tax rates ")

# ordered by change in tax rate if exemptions were removed.
# as a dot graph ## 

order <- burden_shift %>% 
  as_tibble() %>%
  group_by(agency_name, clean_name) %>%
    summarize(tax_rate_current = median(tax_rate_current), 
            taxrate_new = median(taxrate_new),
            taxrate_change = median(taxrate_change)) %>%
  arrange(taxrate_change)

median(order$taxrate_change) # median rate change is 1.388 percentage points

## [1] 0.01388

#head(order)
#tail(order)

burden_shift %>%  
  filter(taxrate_change < 0.0027 |taxrate_change > 0.06  |
           taxrate_change < (median(taxrate_change))+0.0007 & taxrate_change > (median(taxrate_change))-0.00075
         ) %>% 
    filter(Alea_cat == "Owner Occupied") %>%

 # filter(PropType == "Single-Family") %>%
  ungroup() %>% 
  select(clean_name, tax_rate_current, taxrate_new, agency_name) %>% 
  pivot_longer(c("tax_rate_current", "taxrate_new"), 
               names_to = "type", values_to = "tax_rate") %>% 
  left_join(order) %>%
  ggplot(aes(x = tax_rate*100, y= reorder(clean_name, taxrate_change)))+
  geom_line(aes(group = clean_name))+ 
   geom_point(aes(color=type), size=3 )+
  geom_hline(yintercept = 10.5, linetype =2)+
    geom_hline(yintercept = 5.5, linetype =2)+
  theme_minimal() + 
  theme( 
    legend.title = element_blank(),
               plot.title.position = "plot",
    legend.position = "none",
     #   panel.background = element_rect(fill='transparent'), #transparent panel bg
    plot.background = element_rect(fill='transparent', color=NA) #transparent plot bg
   )+
        scale_color_brewer(palette="Paired", labels = c("Exemptions", "No Exemptions"), direction = 1)+

  labs(title = "Difference in Composite Tax Rate if there were No Exemptions", 
       subtitle = "Ordered by Change in Tax Rate: Highest, Median, and Smallest Differences",
       caption = "Median change is 1.38 percentage points",
       x = "Composite Tax Rate (%)", y = "" )

ggsave("Composite_Rate_Change.png", width = 6, height = 4, units = "in")

Owner Occupied Properties Burden Shift

Change in Tax Burden for Owner Occupied Properties. Includes class 2 properties that are not rental properties:

burden_shift %>%
      mutate(burden_current = ifelse(burden_current>1, 1, burden_current)) %>%
  filter(Alea_cat == "Owner Occupied") %>%
      #filter(major_class_code == 2) %>%
  mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%

  ggplot(aes(fill = burden_current)) + 
  geom_sf(aes(geometry = geometry), color = "black") + 
  theme_classic() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(
   # high = "#00e304" , low = "#00cfd4",
     high = "#420420", low = "#008080",
   #  high = "#00a8ff" , low = "#ff4800",
     nice.breaks=FALSE,
                        show.limits=TRUE, 
   n.breaks = 6,
                     midpoint = 0.597,
  # guide = "legend",
                        name = "Current Burden \n(with Exemptions)", labels = scales::percent
                    )+
  labs(title = "Current share of property tax burden", 
       subtitle = "for  Owner Occupied Property Types", 
       caption = "The median municipality gets 59.7% of its property tax revenue 
       from Class 2 properties that are owner occupied in the current tax system.")

burden_shift %>% 
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
    #  filter(major_class_code == 2) %>%
  filter(Alea_cat == "Owner Occupied") %>%

  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%

  ggplot(aes(fill = burden_noexemps)) + 
  geom_sf(aes(geometry = geometry), color = "black") +     
  theme_classic( ) + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+

     scale_fill_steps2(
      #  high = "#00e304" , low = "#00cfd4",
     # colors = c("#ffffcc","#a1dab4" ,"#41b6c4","#2c7fb8", "#253494"),
      high = "#420420", low = "#008080",
  #   high = "#253494" , low = "#3b3c36" ,
     nice.breaks=FALSE,
                        show.limits=TRUE, 
   n.breaks = 6,
                     midpoint = 0.65,
                        name = "Burden without \nExemptions", labels = scales::percent) +
 labs(title = "New share of property tax burden", subtitle = "for Owner occupied properties", caption =
      "Without exemptions, municipalities would get a larger share of 
      their revenue from owner occupied properties.
      Owner occupied properties includes condos and single-family homes.")

burden_shift %>% #filter(clean_name != "Phoenix") %>%
    filter(Alea_cat == "Owner Occupied") %>%

 # filter(major_class_code == 2) %>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
  mutate(burden_change = ifelse(burden_change<0,0, burden_change))%>%
     #    burden_change = ifelse(burden_change>1,1, burden_change))%>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  ggplot(aes(fill = (burden_change*100)) )+ 
  geom_sf(aes(geometry = geometry), color = "black") + theme_void()+ 
  labs(title = "Change in Tax Burden: Owner occupied properties", 
       caption = "The median shift from owner occupied properties to other property types
       due to current exemptions is worth approximately 4.5% of a municipality's levy.") +
    theme_classic() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
   scale_fill_steps2(
     high = "#420420", low = "#008080",
     nice.breaks=FALSE,
     show.limits=TRUE,# n.breaks = 5,
                     midpoint = 4.7, # median(burden_shift$burden_change*100),
                        name = "Change in Burden \n Pct Points")

Change median vertical line and the horizontal lines. Refilter the top, medium, and lowest values

burden_shift %>% #filter(PropType == "Single-Family")%>%
    filter(Alea_cat == "Owner Occupied") %>%

summarize(median_change = median(burden_change), # 4.74 percentage points.
          median_current = median(burden_current), #60%
          median_noexemps = median(burden_noexemps)) # 66%

# as a dot graph ## 


order <- burden_shift %>% 
  ungroup %>% as_tibble() %>%
  #  filter(ResidentialProps == "Residential") %>%
#  filter(PropType == "Single-Family") %>%
    filter(Alea_cat == "Owner Occupied") %>%

  select(agency_name, clean_name, burden_current, burden_change)



# burder_shift_ordered <-  burden_shift %>% 
#   ungroup() %>% 
#   select(agency_name, current_burden, no_exemptions_burden) %>%    
#   pivot_longer(c("current_burden", "no_exemptions_burden"), 
#                names_to = "type", values_to = "pct_burden") %>% 
#   left_join(order)

# look at ones that changed the most

burden_shift %>%     filter(Alea_cat == "Owner Occupied") %>%
 arrange(burden_current)

burden_shift %>% 
    filter(Alea_cat == "Owner Occupied") %>%
  filter(clean_name != "Hoffman Estates") %>% # only because 6 were between the median range and I only wanted 5 in the graph
   filter(burden_current > 0.931 |burden_current < .17 |
            ( (burden_current < median(burden_current) + 0.01 )& (burden_current > median(burden_current) - 0.01)) )%>% 
  ungroup() %>% 
  select(agency_name, clean_name, burden_current, burden_noexemps,  burden_change) %>% 
  arrange(burden_change) %>%
  mutate( 
    burden_noexemps = ifelse(burden_noexemps > 1, 1, burden_noexemps)) %>%
  pivot_longer(c("burden_current", "burden_noexemps"), 
               names_to = "type", values_to = "pct_burden") %>% 
  inner_join(order) %>%
  ggplot(aes(x = pct_burden*100, 
             y= reorder(clean_name, -burden_current)))+
  # y= reorder(clean_name, burden_current)))+
  geom_vline(xintercept = 59.7, linetype = 3)+
  geom_line(aes(group = clean_name))+ 
  geom_hline(yintercept = 5.5, linetype = 2)+
  geom_hline(yintercept = 10.5, linetype = 2)+
  geom_point(aes(color=type), size=3 )+

  theme_minimal() + 
  theme(#legend.position = "none", 
    legend.title = element_blank(),
    plot.title.position = "plot",
    #   panel.background = element_rect(fill='transparent'), #transparent panel bg
    plot.background = element_rect(fill='transparent', color=NA) #transparent plot bg
  )+
  scale_color_brewer(palette="Paired", labels = c("Current Burden", "Burden if \nNo Exemptions" ), direction = 1)+

  
  labs(title = "Change in Owner Occupied Tax Burden", 
       subtitle = "Ordered by Current Tax Burden",
  x = "Share of Levy (%)", y = "" , 
  caption = "Dotted line represents current tax burden (59.7% of the municpality's levy) in the median municipality. 
  Residential Tax Burden is the share of the property tax collected that was paid for by residential 
  property owners. Includes condos and detached homes. Does not include classes described as 'rentals'.") +
    geom_label(label = "Home owners pay small share of \nlevy; very little residential", x=35, y = 12.5, label.size = 1, size = 3)+
    geom_label(label = "Home owners pay median share of \nlevy (59.7%), mix of land use", x=30, y = 7.5, label.size = 1, size = 3) +
    geom_label(label = "Home owners pay nearly all of levy, \nhighly residential", x=70, y = 3, label.size = 1,size = 3)

### Change in Burden
burden_shift %>%    
  ungroup() %>% 
    filter(Alea_cat == "Owner Occupied") %>%

   # filter(PropType == "Single-Family") %>%
   # filter(burden_change > 0.3 | burden_change < .01) %>% 

  select(agency_name, clean_name, burden_current, burden_noexemps,
         burden_change) %>% 
  mutate(
         burden_current = ifelse(burden_current > 1, 1, burden_current)) %>%
  pivot_longer(c("burden_current", "burden_noexemps"),
               names_to = "type", values_to = "pct_burden") %>%
  inner_join(order) %>%
  ggplot(aes(x = pct_burden, y= reorder(clean_name, -burden_current)))+
  geom_line(aes(group = clean_name))+ 
   geom_point(aes(color=type), size=3 )+
  theme_minimal() + 
  theme(#legend.position = "none", 
    legend.title = element_blank(),
               plot.title.position = "plot",
     #   panel.background = element_rect(fill='transparent'), #transparent panel bg
    plot.background = element_rect(fill='transparent', color=NA) #transparent plot bg
   )+
        scale_color_brewer(palette="Paired", labels = c("Current Burden","Burden if \nNo Exemptions"), direction = 1)+

  labs(title = "Change in Owner Occupied Property Tax Burden", 
  x = "Share of Levy (%)", y = "" , 
  caption = "Tax Burden is the Share of the property tax collected that was paid for by 
     home owners in property classes 2.")

burden_shift %>%    
  ungroup() %>% 
    filter(Alea_cat == "Owner Occupied") %>%

  #  filter(PropType == "Single-Family") %>%
    filter(burden_noexemps > 0.2 | burden_noexemps < .001) %>% 

  select(agency_name, clean_name, burden_current, burden_noexemps,
         burden_change) %>% 
  mutate(
         burden_current = ifelse(burden_current > 1, 1, burden_current)) %>%
  pivot_longer(c("burden_current", "burden_noexemps"),
               names_to = "type", values_to = "pct_burden") %>%
  inner_join(order) %>%
  ggplot(aes(x = pct_burden, y= reorder(clean_name, burden_change)))+
  geom_line(aes(group = clean_name))+ 
   geom_point(aes(color=type), size=3 )+
  theme_minimal() + 
  theme(#legend.position = "none", 
    legend.title = element_blank(),
               plot.title.position = "plot",
     #   panel.background = element_rect(fill='transparent'), #transparent panel bg
    plot.background = element_rect(fill='transparent', color=NA) #transparent plot bg
   )+
        scale_color_brewer(palette="Paired", labels = c( "Current Burden","Burden if \nNo Exemptions"), direction = 1)+

  labs(title = "Change in Owner Occupied Class 2 Residential Tax Burden", 
  x = "Share of Levy (%)", y = "" , 
  caption = "Tax Burden is the Share of the property tax collected that was paid for by 
    home owners in property classes 2.")

burden_shift %>% ungroup() %>% #filter(PropType == "Single-Family") %>%
    filter(Alea_cat == "Owner Occupied") %>%

arrange(burden_change) %>% head()

burden_shift %>%    
  ungroup() %>% 
    filter(Alea_cat == "Owner Occupied") %>%

   # filter(PropType == "Single-Family") %>%
  filter(clean_name != "Phoenix") %>%
  filter(clean_name %in% c("Schaumburg", "Elk Grove Village", "Westchester", "Palos Hills", "Orland Hills") |
 # filter(clean_name %in% c("Hometown", "Burbank", "Schaumburg", "Elk Grove Village", "Westchester") | 
           burden_change > 0.11 |burden_change < .003 ) %>%
         #|  ( (burden_change < median(burden_change) + 0.001 )& (burden_change > median(burden_change) - 0.001)) ) %>% 
  select(agency_name, clean_name, burden_current, burden_noexemps,
         burden_change) %>% 
  pivot_longer(c("burden_current", "burden_noexemps"),
               names_to = "type", values_to = "pct_burden") %>%
  inner_join(order) %>%
  ggplot(aes(x = pct_burden*100, y= reorder(clean_name, burden_change*100)))+
  geom_line(aes(group = clean_name))+ 
    geom_hline(yintercept = 5.5, linetype = 2)+
  geom_hline(yintercept = 10.5, linetype = 2)+
   geom_point(aes(color=type), size=3 )+
  theme_minimal() + 
  theme(legend.position = "none", 
    legend.title = element_blank(),
               plot.title.position = "plot",
     #   panel.background = element_rect(fill='transparent'), #transparent panel bg
    plot.background = element_rect(fill='transparent', color=NA) #transparent plot bg
   )+
        scale_color_brewer(palette="Paired", labels = c( "Current Burden","Burden if \nNo Exemptions"), direction = 1)+

  labs(title = "Change in Residential Tax Burden (Owner Occupied only)", 
  x = "Share of Levy (%)", y = "" )

ggsave("residential_tax_burden_change.png", width = 6, height = 4, units = "in")

Summary table of Burden Shift: Grouped Property Categories

Same data that is used above but property types are combined into 4 categories:

• Owner Occupied (Most of Class 2 Properties. Classes with “rental” in the description were included with Renter properties.)
  
• Renters (Class 3 & 9 and some of Class 2)
  
• Commercial (splits class 5, 6, 7, and 8. Commercial/Industrial, incentive and nonprofit classes based on property class descriptions)
• Industrial (splits class 5, 6, 7, and 8. Commercial/Industrial, incentive and nonprofit classes based on property class descriptions)
• Other?: Vacant Land.
  • Railroad land is dropped from calculations and tables. Not taxed.

# Current Burden:

munis_3property_types <- burden_shift %>%# mutate(
     #       burden_noexemps = ifelse(is.na(burden_noexemps), 0, burden_noexemps)) %>%
  #    mutate(burden_current = ifelse(burden_current>1, 1, burden_current)) %>%
  ungroup() %>% 
  group_by(agency_name, clean_name, Alea_cat) %>%
  summarize(#final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE),
            burden_current = sum(burden_current, na.rm = TRUE),
            burden_noexemps = sum(burden_noexemps, na.rm = TRUE),
            burden_change = sum(burden_change, na.rm = TRUE)) 

#write.csv(munis_3property_types, "1_usemetable.csv")

proptypes3_current <- burden_shift %>% 
    mutate(burden_current = ifelse(is.na(burden_current), 0, burden_current)) %>%

  pivot_wider( id_cols = clean_name , names_from = "Alea_cat", values_from = "burden_current",names_prefix="Current - ", values_fill = 0 ) %>% 
  select(clean_name,  everything()) 

proptypes3_current[2:4] <- sapply(proptypes3_current[2:4], function(x) scales::percent(x, accuracy=.01) )
                                   
proptypes3_current

# __Tax Burden if there were no exemptions:__

proptypes3_noexemps <- burden_shift %>% 
  mutate(burden_noexemps = ifelse(is.na(burden_noexemps), 0, burden_noexemps)) %>%
  pivot_wider( id_cols = clean_name , names_from = "Alea_cat", values_from = "burden_noexemps", names_prefix = "W/O Exemptions - ", values_fill = 0)  
proptypes3_noexemps[2:4] <- sapply(proptypes3_noexemps[2:4], function(x) scales::percent(x, accuracy=.01))
proptypes3_noexemps

Change in Share of Burden if there were no exemptions:

# burden_shift %>% ungroup() %>% 
#   group_by(agency_name, PropType) %>%
#   summarize(district_rev_collected = sum(district_rev_collected),
#             current_burden = sum(current_burden),
#             no_exemptions_burden = sum(no_exemptions_burden)) 

props_wide <- burden_shift %>%
  filter(!is.na(Alea_cat))%>%
  pivot_wider(id_cols = clean_name , 
               names_from = "Alea_cat", 
               values_from = "burden_change", values_fill = 0) %>% 
  select(clean_name, everything()) %>%
  arrange(desc(`Owner Occupied`) )

props_wide[2:7] <- sapply(props_wide[2:7], function(x) scales::percent(x, accuracy=.001))
props_wide

proptypes3_comparisontable<- left_join(proptypes3_current, proptypes3_noexemps, by = "clean_name") 
proptypes3_comparisontable

write_csv(proptypes3_comparisontable, "OwnerOccupied_ComparisonTable.csv")

proptypes3_burdenchange <- burden_shift %>% 
  filter(!is.na(Alea_cat))%>%
  mutate(burden_noexemps = ifelse(is.na(burden_change), 0, burden_change)) %>%
  pivot_wider( id_cols = clean_name , names_from = "Alea_cat", values_from = "burden_change", values_fill = 0) %>%
  arrange(desc(`Owner Occupied`))

proptypes3_burdenchange[2:4] <- sapply(proptypes3_burdenchange[2:4], function(x) scales::percent(x, accuracy=.01))
proptypes3_burdenchange

#proptypes3_burdenchange %>% write_csv("burden_change_august6.csv")

Cook County “Cost” Values

“Multiplies the EAV exempted for all Cook County municipalities by their consolidated tax rates in each municipality”

Do this by taxcode and actual tax code tax rate. Much more accurate.

taxcodes_current %>% 
  summarize(forgone_rev = sum(tax_amt_exe, na.rm = TRUE),
            exempt_EAV = sum(tax_amt_exe/(tax_code_rate/100), na.rm = TRUE)
  )

• There is $15,770,910,176 of EAV that is exempt from property taxes.

• $1.5 Billion of potential revenue is transferred to other taxpayers due to current exemptions.

Current_Taxrates %>% 
  mutate(transfered_taxes = tax_rate_current*Exempt_EAV) %>% 
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
    ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%

  ggplot(aes(fill = transfered_taxes)) +
  geom_sf(aes(geometry = geometry), color = "black") + 
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
    high = "#420420", low = "black",
    # midpoint = median(transfered_taxes),
    show.limits=TRUE,
    nice.breaks=FALSE,
    n =6,
    name = "",
    labels = scales::dollar
  )+
  
  labs(title = "Dollars passed from Class 2 properties to others \ndue to current exemptions")

Current_Taxrates %>% 
  filter(clean_name != "Chicago") %>%
  mutate(transfered_taxes = tax_rate_current*Exempt_EAV) %>% 
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
    ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%

    ggplot(aes(fill = transfered_taxes)) +
    geom_sf(aes(geometry = geometry), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_steps2(
    high = "#420420", low = "black",
  # midpoint = median(transfered_taxes),
                   show.limits=TRUE,
  nice.breaks=FALSE,
                    n =6,
                       name = "",
         labels = scales::dollar
)+
 
  labs(title = "Dollars passed from Class 2 Residential properties to others \ndue to current exemptions",    
         caption = "Ignores Chicago (over $460K) to highlight other municipalities")
ggsave("cook_nochicago.png")

class2_perc <- class2_perc %>%  mutate(percent_exempt = exempt_EAV/muni_EAV_includesTIF) 

class2_perc%>%
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
    ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%

    ggplot(aes(fill = percent_exempt)) +
    geom_sf(aes(geometry = geometry), color = "black") + 
      theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_steps2(
    high = "#420420", low = "black",
 midpoint = median(class2_perc$percent_exempt),
                   show.limits=TRUE,
  nice.breaks=FALSE,
                    n =6,
                       name = "Percent Exempt",
         labels = scales::percent
)+
 
  labs(title = "Percent Exempt:  Exempt EAV / All EAV in Municipality")





class2_perc <- class2_perc %>%   mutate(percent_exempt = exempt_EAV/muni_tax_base_noexemps) 

class2_perc %>% 
    mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
    ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%

    ggplot(aes(fill = percent_exempt)) +
    geom_sf(aes(geometry = geometry), color = "black") + 
      theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_steps2(
    high = "#420420", low = "black",
   midpoint = median(class2_perc$percent_exempt),
                   show.limits=TRUE,
  nice.breaks=FALSE,
                    n =6,
                       name = "Percent Exempt",
         labels = scales::percent
)+
 
  labs(title = "Percent of pre-exemption taxable base that is tax exempt",    
         caption = "Total EAV includes the pre-exemption Taxable Base. 
       TIF increment EAV is not included in the total EAV in this image.
       Median value is 12.43%")

Current_Taxrates <- Current_Taxrates %>% 
  mutate(Exempt_percent = Exempt_EAV/Total_EAV) 

median(Current_Taxrates$Exempt_percent)

## [1] 0.1245

Current_Taxrates%>% 
  mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) 
  ) %>%
  left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
  
  ggplot(aes(fill = Exempt_percent)) +
  geom_sf(aes(geometry = geometry), color = "black") + 
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
    high = "#420420", low = "black",
    midpoint = median(Current_Taxrates$Exempt_percent),
    show.limits=TRUE,
    nice.breaks=FALSE,
    n =6,
    name = "Percent Exempt",
    labels = scales::percent)+

  labs(title = "Percent of Total EAV that is Tax Exempt",    
       caption = "Total EAV includes all EAV in and out of TIFs.
       Median value is 13.1%. ")

“Takes the median tax rate increase as a result of exemptions (1.4%) and multiplies that by the total residential EAV in Cook County”

taxcodes_current %>%
  filter(year == 2021) %>%
  group_by(major_class_type, major_class_code) %>%
  summarize(eav = sum(tax_amt_pre_exe/(tax_code_rate/100), na.rm=TRUE)) %>% arrange(desc(eav))

120944941944 * 0.01388 # 1693229187

• There is $120,944,941,944 of Residential EAV in Cook County.

• $1,678,715,794 is Residential EAV * average increase in municipal taxrate.

“And then we should [show] how/why if levy is fixed, exemptions increase the tax rate and/or shift the tax burden.”

• Once the amount of money that is needed is determined, then the tax rate is determined based off of that.
  • Levy doesn’t change, so if you are decreasing the amount of taxable EAV, then the tax rate must go up to collect the same amount of money.
    
  • maybe draw a diagram for a municipality as an example on a slide?

“We might also want to take a closer look at either our 6 case study municipalities or those municipalities most affected by exemptions (Park Forest, Phoenix, Calumet Park, Riverdale) to get some effects for the owner of the average or median value home.”

Locations with the highest percent of their potentially taxable EAV (non-TIF increment EAV) that is tax-exempt:

Phoenix Park Forest Markham Calumet Park
Thornton
Harvey
Burnham Dolton

Almost exact same list of locations as largest tax rate change.

Exporting data to Excel file

library(openxlsx)

dataset_names <- list(
  'Land Use' = perc_residential,
  'Burden Table' = burden_table,
  # 'EAVoutsideTIFs' = EAV_outside_TIFS_byClass,
  
  'Composite Tax Rates' = Current_Taxrates,
  'MuniEAV' = muni_eav, #included as columns in Burden Table too
 #  'ResidentialEAV' = table_ResidentialEAV,  # doesn't exist?
  # 'Burden with Exemps' = burden_with_exemptions,
  # 'Burden without Exemps' = burden_noexemps,
  'Burden Shift-3PropTypes' = proptypes3_burdenchange,
  '3 Property Types'= munis_3property_types,
#  'TaxcodeData-NoExemptions'=taxcodes_noexemps,
  'TaxcodeData-Current' = taxcodes_current)

write.xlsx(dataset_names, file = 'data_for_slides_filteredMunis.xlsx')