Mapping Residential Property Tax Burden Shift
Alea Wilbur
2023-07-12
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.
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 when including unincorporated land areas.
MuniLevy comes from the final_tax_to_dist
is from tax_bill() output (and ultimately comes from tif_distrib table).
Amount that was billed to all pins within a municipality (based on tax
code level sums that are taxed by each Municipality). the
tif_distrib table had tax_code level EAV values that were
POST exemptions.
district_rev_collected came from exemptions data from
the lookup_pin() output.
Getting Data & Data Prep
Make unincorporated areas clear Add Cicero back in, when did it get dropped?
# 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 %>%
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
"
)
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") %>%
# mutate(tax_code = as.character(tax_code),
# tax_code_frozen_eav = replace_na(tax_code_frozen_eav, 0),
# tax_code_tif_increment = ifelse((tax_code_frozen_eav > tax_code_eav), 0, (tax_code_eav-tax_code_frozen_eav)),
# )
# tax_code_tif_increment = tax_code_eav-tax_code_frozen_eav) %>%
#select(-c(year:tax_code_rate, tax_code_eav, tax_code_revenue, tax_code_distribution_pct))
# 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") %>%
# 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))
## 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)muni_pins <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql(
"SELECT DISTINCT pin, class, tax_code_num
FROM pin
WHERE tax_code_num IN ({muni_tax_codes$tax_code_num*})
AND year = 2021
",
.con = ptaxsim_db_conn
))
#all_muni_pins <-read_csv("all_muni_pins.csv")
# Normal output from lookup_pin() command. Includes all types of exemptions
exe_dt <- lookup_pin(2021, muni_pins$pin) %>%
setDT(key = c("year", "pin"))
taxbills_current <- tax_bill(2021,
muni_pins$pin,
pin_dt = exe_dt, # default option
simplify = FALSE)
head(taxbills_current)
TC_bills_current <- taxbills_current %>%
left_join(class_dict, by = c("class" = "class_code")) %>% # add major property types to pin data
group_by(major_class_code, major_class_type, tax_code) %>%
summarize(
final_tax_to_dist = sum(final_tax_to_dist),
final_tax_to_tif = sum(final_tax_to_tif),
tax_amt_exe = sum(tax_amt_exe), # revenue lost due to exemptions
tax_amt_pre_exe = sum(tax_amt_pre_exe), # total rev before all exemptions
tax_amt_post_exe = sum(tax_amt_post_exe), # total rev after all exemptions
rpm_tif_to_cps = sum(rpm_tif_to_cps), # not used
rpm_tif_to_rpm = sum(rpm_tif_to_rpm), # not used
rpm_tif_to_dist = sum(rpm_tif_to_dist), # not used
tif_share = mean(tif_share), # not used
) %>%
mutate(stage = "With exemptions")
TC_bills_current
write_csv(TC_bills_current, "TC_bills_current.csv")
# Changes all exemptions to 0 for all pins.
# This table will then go INSIDE of the taxbill() comand
# for no exemptions simulation
no_exe_dt <- lookup_pin(2021, muni_pins$pin)# %>%
# mutate(across(starts_with("exe_"), ~0)) %>%
# setDT(key = c("year", "pin"))
no_exe_dt[, tax_code := lookup_tax_code(year, pin)]
exe_cols <- names(no_exe_dt)[startsWith(names(no_exe_dt), "exe_")]
taxcode_sum_no_exe <- no_exe_dt[,
.(exe_total = sum(rowSums(.SD))),
.SDcols = exe_cols,
by = .(year, tax_code)
]
# recalculate the base
# change agency data table and their tax rate
t_agency_dt_no_exe <- lookup_agency(2021, no_exe_dt$tax_code)
t_agency_dt_no_exe[
taxcode_sum_no_exe,
on = .(year, tax_code),
agency_total_eav := agency_total_eav + exe_total
]
no_exe_dt[, (exe_cols) := 0][, c("tax_code") := NULL]
taxbills_no_exemps <- tax_bill(2021,
pin_vec = muni_pins$pin,
agency_dt = t_agency_dt_no_exe,
pin_dt = no_exe_dt,
simplify = FALSE
)[
, stage := "No exemptions"
]
head(taxbills_no_exemps)
class_dict$class_code <- as.character(class_dict$class_code)
TC_bills_noexemps <- taxbills_no_exemps %>%
left_join(class_dict, by = c("class" = "class_code")) %>% # add major property types to pin data
group_by(major_class_code, major_class_type, tax_code) %>%
summarize(
final_tax_to_dist = sum(final_tax_to_dist),
final_tax_to_tif = sum(final_tax_to_tif),
tax_amt_exe = sum(tax_amt_exe), # revenue lost due to exemptions
tax_amt_pre_exe = sum(tax_amt_pre_exe), # total rev before all exemptions
tax_amt_post_exe = sum(tax_amt_post_exe), # total rev after all exemptions
rpm_tif_to_cps = sum(rpm_tif_to_cps), # not used
rpm_tif_to_rpm = sum(rpm_tif_to_rpm), # not used
rpm_tif_to_dist = sum(rpm_tif_to_dist), # not used
tif_share = mean(tif_share), # not used
)
TC_bills_noexemps
write_csv(TC_bills_noexemps, "TC_bills_noexemps.csv")
rm(taxbills_no_exemps)Muni Land Use
Includes all Munis, even if they are largely outside of Cook County. This skews soeme calculations for the border crossing municipalities. A different markdown file has been created where only Munis with 50% of their EAV in Cook County are kept in the analysis.
EAV
using the pin data from ptaxsim, I calculate the eav of all properties, exempt EAV, the current taxbase, and taxbase if there were not exemptios
exempt EAV (summed from all exemption types)
current taxbase (using the
tif_distribtable 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.taxbase 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.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("all_exemptions_by_TC.csv") %>%
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")) %>%
# mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
# 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"))
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()
perc_residential# nicknames %>% filter(!clean_name %in% perc_residential$clean_name)
#
# nicknames %>% filter(!shpfile_name %in% muni_shp$MUNICIPALITY)
# muni_shp %>% filter(!MUNICIPALITY %in% nicknames$shpfile_name)
# nicknames %>% filter(!agency_name %in% muni_shp$AGENCY_DESC)
#muni_shp %>% left_join(muni_agency_names, by = c("AGENCY_DESC" = "agency_name"))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.
Percent Residential is the Residential EAV outside of TIFs / Municipality EAV outside of TIFs.
# #lowest % of eav from residential property types
# perc_residential %>% select(-ResidentialProps) %>%
# arrange(percent_residential) %>% head()
#
# #highest % of eav from residential property types
# perc_residential %>% select(-ResidentialProps) %>%
# arrange(percent_residential) %>% tail()
#
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") + theme_void()+
labs(title = "Residential EAV / Total EAV") +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank()) +#+
scale_fill_stepsn(colors = c("white","darkblue"),
# limits = c(0,.6),
n.breaks = 5, show.limits=TRUE,
name = "% Residential", label = scales::percent)
Homeowner’s Exemptions Exempt EAV / Residential EAV
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_stepsn(colors = c("white", "darkblue"),
# limits = c(0,.6),
n.breaks = 5, show.limits=TRUE,
name = "% Residential EAV \nthat is exempt", label = scales::percent)
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_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_stepsn(colors = c("white", "darkblue"),
# limits = c(0,.6),
n.breaks = 5, show.limits=TRUE,
name = "% Residential EAV \nthat is exempt", label = scales::percent)
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 = "Non-TIF EAV only: Homestead Exemptions / Residential EAV", caption = "Drops Village of Phoenix because it skews map colors
(78% of their residential EAV is tax exempt)") +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_stepsn(colors = c("white", "darkblue"),
# limits = c(0,.6),
n.breaks = 5, show.limits=TRUE,
name = "% Residential EAV \nthat is exempt", label = scales::percent)
Bedford Park has less than 2% of its EAV as residential property. Industrial Suburb, population = 600 people.
Kenilworth has 97% of its EAV is residential property.
Tax Rates
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 (such as the Village of ____).
TC_bills_noexemps <- read_csv("TC_bills_noexemps.csv") %>% mutate(tax_code = as.character(tax_code))
# noexemps_rev<- taxcodes_noexemps %>%
# left_join(muni_agency_names) %>%
# select(tax_code, major_class_code, major_class_type, final_tax_to_dist, agency_name) %>%
# rename(final_tax_to_dist_noexemps = final_tax_to_dist)
taxcodes_noexemps <- left_join(TC_bills_noexemps, muni_tax_codes,
by = c("tax_code" = "tax_code_num")) %>%
left_join(muni_agency_names) %>%
left_join(nicknames) %>%
mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) )
noexemps_munisummary <- taxcodes_noexemps %>%
group_by(clean_name, agency_name) %>%
summarize(MuniLevy = sum(final_tax_to_dist, na.rm = TRUE), #amount billed by muni within cook county
nonTIF_EAV = sum(final_tax_to_dist/(tax_code_rate/100), na.rm = TRUE), # within cook county
final_tax_to_dist = sum(final_tax_to_dist, na.rm=TRUE),
TIF_increment_EAV = sum(final_tax_to_tif/(tax_code_rate/100), na.rm=TRUE), # within cook county
Exempt_EAV = sum(tax_amt_exe/(tax_code_rate/100), na.rm=TRUE), # within cook county
Total_EAV = sum((tax_amt_exe+final_tax_to_dist+final_tax_to_tif)/(tax_code_rate/100), na.rm = TRUE)) %>%
mutate(taxrate_noexemps = MuniLevy/nonTIF_EAV,
# nonTIF_EAV= nonTIF_EAV + Exempt_EAV,
)
# select(agency_name, tax_rate_noexemps, everything()) %>%
# arrange(desc(tax_rate_noexemps))
noexemps_munisummary6 Highest and 6 lowest tax rates. View the extremes on both sides
Current_Taxrates %>% filter(tax_rate_current > 0.25 )Current_Taxrates %>% filter( tax_rate_current < 0.08)Map of Tax Rate Change
burden_shift %>%
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)) +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
# scale_fill_gradientn(colors = c("white", "maroon"),
scale_fill_stepsn(colors = c("white", "#76C3D6", "#1A5E7D"),
limits = c(0,.35),
show.limits=TRUE,
n.breaks = 5,
name = "Tax Rate", label = scales::percent)+
geom_sf(aes(geometry = geometry), color = "black") +
labs(title = "Current composite tax rates with current exemptions in place" ,
caption = "Highest composite tax rate is in Park Forest (41.4%.)
Lowest composite tax rate is in Oak Brook (6.3%) and Chicago (6.7%).")
burden_shift %>%
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 = taxrate_new)) +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_stepsn(colors = c("white", "#76C3D6", "#1A5E7D"),
limits = c(0,.35),
n.breaks = 5, show.limits=TRUE,
name = "Tax Rate", label = scales::percent)+
geom_sf(aes(geometry = geometry), color = "black") +
labs(title = "New composite tax rates if exemptions were eliminated" ,
caption = "Data from CCAO & PTAXSIM. Unincorporated areas are filled with gray.")
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_change*100)) +
geom_sf(aes(geometry = geometry), color = "black") +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_stepsn(colors = c("#ffffcc","#a1dab4" ,"#41b6c4","#2c7fb8", "#253494"),
# low = "#ffffcc", mid = "#41b6c4", high = "#253494",
# space = "Lab",
n.breaks = 6, show.limits=TRUE,
name = "Percentage Point Difference")+
labs(title = "Change in Composite Tax Rate if Exemptions are Removed")
# scale_fill_stepsn(colors = c("white", "#76C3D6", "#1A5E7D"),
# limits = c(0,.35),
Current_Taxrates %>%
mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
# filter(clean_name != "Park Forest") %>%
left_join(muni_shp, by = c("agency_name" = "AGENCY_DESC")) %>%
ggplot(aes(fill = taxrate_change*100)) +
geom_sf(aes(geometry = geometry), color = "black") +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_stepsn(colors = c("#ffffcc","#a1dab4" ,"#41b6c4","#2c7fb8", "#253494"),
# low = "#ffffcc", mid = "#41b6c4", high = "#253494",
# space = "Lab",
n.breaks = 5, show.limits=TRUE,
name = "Percentage Point Difference")+
labs(title = "Change in Composite Tax Rate if Exemptions are Removed")
# 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)
median(order$tax_rate_current)## [1] 0.1212head(order)tail(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") %>%
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_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 = mean(tax_rate_current),
taxrate_new = mean(taxrate_new),
taxrate_change = mean(taxrate_change)) %>%
arrange(taxrate_change)
median(order$taxrate_change) # median rate change is 1.4 percentage points## [1] 0.01361head(order)# this one probably not the best Delete code soon.
# 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 )) %>%
# 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 )+
# 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 Change in Tax Rate: Top and Bottom Observations",
# x = "Composite Tax Rate (%)", y = "" , caption = "For the highest and lowest composite tax rates")
burden_shift %>%
filter(taxrate_change < 0.002 |taxrate_change > 0.055 |
taxrate_change < (median(taxrate_change))+0.00059 & taxrate_change > (median(taxrate_change))-0.00059
) %>%
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 )+
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 Change in Tax Rate: Highest, Median, and Smallest Differences",
x = "Composite Tax Rate (%)", y = "" )
Single-Family (Class 2) Burden Shift
Change in Tax Burden for Class 2 Properties:
burden_shift %>%
mutate(burden_current = ifelse(burden_current>1, 1, burden_current)) %>%
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_gradientn(
scale_fill_stepsn(colors = c("#ffffcc","#a1dab4" ,"#41b6c4","#2c7fb8", "#253494"),
show.limits=TRUE, n.breaks = 6,
name = "Burden with \nExemptions", labels = scales::percent
)+
labs(title = "Current share of property tax burden", subtitle = "for Class = 2 Property Types")
burden_shift %>%
mutate(agency_name = ifelse(agency_name == "TOWN CICERO", "CITY OF CICERO", agency_name) ) %>%
filter(major_class_code == 2) %>%
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_gradientn(
# scale_fill_binned(
#colors = c("#f0f9e8", "#0868ac"),
# colors = c('#a6611a','#018571'),
#limits = c(0,.6),
# n = 6,
# name = "Burden w/o \nExemptions", label = scales::percent)+
scale_fill_stepsn(colors = c("#ffffcc","#a1dab4" ,"#41b6c4","#2c7fb8", "#253494"),
# low = "#ffffcc", mid = "#41b6c4", high = "#253494",
# space = "Lab",
n.breaks = 6, na.value = "grey50",
guide = "coloursteps",
aesthetics = "fill", show.limit = TRUE,
name = "Burden w/o \nExemptions",
label = scales::percent
)+
labs(title = "New share of property tax burden", subtitle = "for Class = 2 Property Types")
burden_shift %>% filter(clean_name != "Phoenix") %>%
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 = (0-burden_change*100)) )+
geom_sf(aes(geometry = geometry), color = "black") + theme_void()+
labs(title = "Change in Residential Share of Tax Burden") +
theme_classic() +
theme(axis.ticks = element_blank(), axis.text = element_blank())+
scale_fill_stepsn(colors = c("#253494", "#2c7fb8", "#41b6c4", "#a1dab4", "#ffffcc"),
# low = "#ffffcc", mid = "#41b6c4", high = "#253494",
# space = "Lab",
n.breaks = 5,
na.value = "grey50",
guide = #"legend",
"coloursteps",
show.limit = TRUE,
name = "Percentage Point \nDifference")
# scale_fill_steps( low= "white", high = "darkblue",
# #colors = c("darkblue","white"),
# show.limits = TRUE,
# n.breaks = 5,
# name = "Difference in Burden")
# geom_sf(data = countyIL, fill=NA, color="dark gray")# as a dot graph ##
order <- burden_shift %>%
ungroup %>% as_tibble() %>%
# filter(ResidentialProps == "Residential") %>%
filter(PropType == "Single-Family") %>%
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(PropType == "Single-Family")%>%
summarize(median_change = median(burden_change),
median_current = median(burden_current),
median_noexemps = median(burden_noexemps)) # 0.0442
burden_shift %>%
filter(PropType == "Single-Family") %>%
filter(burden_current > 0.9 |burden_current < .3 |
( (burden_current < median(burden_current) + 0.01 )& (burden_current > median(burden_current) - 0.01)) )%>%
#filter(ResidentialProps == "Residential") %>%
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_change)))+
# 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("No Exemptions", "Exemptions"), direction = 1)+
labs(title = "Change in Single-family Residential Tax Burden",
subtitle = "Ordered by Current Burden Levels",
x = "Share of Levy (%)", y = "" ,
caption = "Residential Tax Burden is the Share of the property tax collected that was paid for by
single-family home owners in property classes 2.") #+
# scale_x_continuous(label = scales::percent)### Change in Burden
burden_shift %>%
ungroup() %>%
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("No Exemptions", "Exemptions"), direction = 1)+
labs(title = "Change in Single-family Residential Tax Burden",
x = "Share of Levy (%)", y = "" ,
caption = "Residential Tax Burden is the Share of the property tax collected that was paid for by
single-family home owners in property classes 2.")
burden_shift %>%
ungroup() %>%
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("No Exemptions", "Exemptions"), direction = 1)+
labs(title = "Change in Single-family Residential Tax Burden",
x = "Share of Levy (%)", y = "" ,
caption = "Residential Tax Burden is the Share of the property tax collected that was paid for by
single-family home owners in property classes 2.")
Summary table of Burden Shift: 3 Property Categories
Same data that is used above but property types are combined into 3 categories:
Single-family (Class 2),
Multi-Family (Class 3 & 9),
and Commercial-Industrial (all other Classes: 1,4, 5, 6, 7, 8, 9)
# 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, PropType) %>%
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 <- munis_3property_types %>%
mutate(burden_current = ifelse(is.na(burden_current), 0, burden_current)) %>%
pivot_wider( id_cols = clean_name , names_from = "PropType", values_from = "burden_current",names_prefix="Current - ", values_fill = 0 ) %>%
select(clean_name, `Current - Single-Family`, everything()) %>%
arrange(-`Current - Single-Family`)
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 <- munis_3property_types %>%
mutate(burden_noexemps = ifelse(is.na(burden_noexemps), 0, burden_noexemps)) %>%
pivot_wider( id_cols = clean_name , names_from = "PropType", 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_noexempsChange 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 <- munis_3property_types %>%
pivot_wider(id_cols = clean_name ,
names_from = "PropType",
values_from = "burden_change", values_fill = 0) %>% select(clean_name, `Single-Family`, everything()) %>%
arrange(desc(`Single-Family`) )
props_wide[2:4] <- sapply(props_wide[2:4], function(x) scales::percent(x, accuracy=.001))
props_wideproptypes3_comparisontable<- left_join(proptypes3_current, proptypes3_noexemps, by = "clean_name")
proptypes3_comparisontableproptypes3_burdenchange <- munis_3property_types %>%
mutate(burden_noexemps = ifelse(is.na(burden_change), 0, burden_change)) %>%
pivot_wider( id_cols = agency_name , names_from = "PropType", values_from = "burden_change", values_fill = 0) %>%
arrange(desc(`Single-Family`))
proptypes3_burdenchange[2:4] <- sapply(proptypes3_burdenchange[2:4], function(x) scales::percent(x, accuracy=.01))
proptypes3_burdenchangeExporting 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.xlsx')