knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE, error=TRUE)
library(tidyverse)
library(DBI)
library(data.table)
library(ggspatial)
library(gstat)
library(here)
library(httr)
library(jsonlite)
library(ptaxsim)
library(sf)
library(stars)
library(glue)
#remotes::install_gitlab("ccao-data-science---modeling/packages/ptaxsim")
#renv::install("gitlab::ccao-data-science---modeling/packages/ptaxsim")
# Create the DB connection with the default name expected by PTAXSIM functions
ptaxsim_db_conn <- DBI::dbConnect(RSQLite::SQLite(), "./ptaxsim.db/ptaxsim-2021.0.4.db")
# has all potential property classes for pins
# downloaded from CCAO gitlab website
## I used this to merge additional information to the pins and class data later on.
class_dict <- read_csv("class_dict.csv")
#options(digits=4, scipen = 999)
options(scipen = 999)Pulling all taxing agencies directly related to Community1…Community6. Includes TIFs, SSAs, Library, and Village of the municiple agency (what we want).
Then we use the agency number for the the Village to pull all unique pins in the municipality, and plug that into tax_bill() for historic data of tax bills within the municipality.
Also create object for unique tax codes, and unique pins to plug into lookup() commands later.
# pull all relevant taxing agency names for the six communities
agency_names <- DBI::dbGetQuery(
ptaxsim_db_conn,
"SELECT agency_num, agency_name, minor_type
FROM agency_info
WHERE agency_name LIKE '%HOFFMAN ESTATES%'
OR agency_name LIKE '%BRIDGEVIEW%'
OR agency_name LIKE '%DOLTON%'
OR agency_name LIKE '%MIDLOTHIAN%'
OR agency_name LIKE '%OAK PARK%'
OR agency_name LIKE '%TIF - CHICAGO -%'
OR agency_name LIKE '%CITY OF CHICAGO%' AND
agency_name NOT LIKE '%CHICAGO HEIGHTS%'
"
)
muni_agency_nums<- agency_names %>%
filter(minor_type %in% c("MUNI", "TOWNSHIP")) %>%
select(agency_num, agency_name) %>%
mutate(first6 = str_sub(agency_num,1,6),
first5 = str_sub(agency_num,1,5),
muni_name = agency_name)
# 1057 tax codes in these 6 municipalities
muni_tax_codes <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql("
SELECT *
FROM tax_code
WHERE agency_num IN ({muni_agency_nums$agency_num*})
AND year = 2021
",
.con = ptaxsim_db_conn
)
)
# for the 6 communities:
# 4242 tax codes in these 6 municipalities
all_tax_codes <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql("
SELECT *
FROM tax_code
WHERE agency_num IN ({agency_names$agency_num*})
AND year = 2021
",
.con = ptaxsim_db_conn
)
)
all_tax_codes <- all_tax_codes %>% left_join(agency_names)
# 940,072 pins in the six communities
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
))The agency data table in ptaxsim has information for
every taxing agency, for every year. Includes multiple variables for EAV
(cty_cook_eav), Levy (total_levy, total_reduced_levy, total_final_levy),
Extension (total_extension), and agency taxing rate (total_prelim_rate,
total_final_rate).
agency_dt: has eav that is taxable (after exemptions and levies). cty_cook_eav is the taxable base for the agency.
cty_cook_eav should be similar to JD’s values for EAV outside of TIFs after exemptions!
pin_data should have original EAV.
Tax bills would have amount to TIF and district too.
merge tif_distribituion in. Then do something like ifelse(tax_code_frozen_eav > 0, tax_code_frozen_eav, eav) to get the EAV that is actually taxable for the district.
total_levy is what the municipality asked for
sum(total_levy) for all taxing agencies with a common eav in 2021
Unsure what the total_non_cat_ext, and reduction variables represent.
agency_rates <- all_tax_codes %>%
filter(minor_type %in% c("MUNI", "TOWNSHIP")) %>%
group_by(agency_num) %>%
summarize(rate = mean(agency_rate))
all_agency_names <- DBI::dbGetQuery(
ptaxsim_db_conn,
"SELECT agency_num, agency_name, minor_type
FROM agency_info
"
)
agency_dt <- DBI::dbGetQuery(
ptaxsim_db_conn,
"SELECT *
FROM agency
WHERE year = 2021
"
)
# create the variables for first 5 and6 digits to group cities together later
agency_dt <- agency_dt %>%
mutate(first6 = str_sub(agency_num,1,6),
first5 = str_sub(agency_num,1,5))
all_taxing_agencies <- agency_dt %>% left_join(all_agency_names)
all_taxing_agencies <- all_taxing_agencies %>%
left_join(muni_agency_nums, by = c("first5", "first6")) %>%
rename(muni_name2 = agency_name.y,
muni_num = agency_num.y,
agency_num = agency_num.x,
agency_name = agency_name.x)
six_communities <- all_taxing_agencies %>%
filter(muni_name %in% muni_agency_nums$muni_name)
six_communities <- left_join(six_communities, agency_rates,
by = c("muni_num"="agency_num"))
# add taxing agency names and agency type to data table that has eav and extension values
six_communities <- six_communities %>%
mutate(first2 = str_sub(agency_num, 1,2),
last2 = str_sub(agency_num,8,9),
in_SSA = ifelse(minor_type == "SSA", 1,0),
in_chicago = ifelse(str_detect(muni_name, "CHICAGO"),1,0)) %>%
select(-c(cty_dupage_eav:cty_livingston_eav))
#total_levy = cty_cook*total_final_rate
six_communities %>%
mutate(Prelim_weighted_tax_rate = cty_cook_eav*total_prelim_rate,
weighted_tax_rate = cty_cook_eav*total_final_rate) %>%
group_by(muni_name) %>%
summarize(EAV = max(cty_cook_eav),
summed_EAV = sum(cty_cook_eav),
TotalLevy = sum(total_levy),
WeightedTaxRate = sum(weighted_tax_rate, na.rm=TRUE),
prelim_weighted = sum(Prelim_weighted_tax_rate, na.rm=TRUE) ) %>%
mutate(CompositeTaxRate = WeightedTaxRate/summed_EAV)muni_funds <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql(
"SELECT *
FROM agency_fund
WHERE year = 2021
AND agency_num IN ({muni_agency_nums$agency_num*})"
,
.con = ptaxsim_db_conn
))
fund_dt <- DBI::dbGetQuery(
ptaxsim_db_conn,
"SELECT *
FROM agency_fund_info"
)
muni_funds <- left_join(muni_funds, fund_dt)Exemption data is only stored at the individual pin level. Must pull all pins and then aggregate to the level desired.
area_data was results from tax_bill command
which had how much of the taxbill went to each taxing district or the
TIF.
pin_data created below with the lookup_pin() function
has the information on exemptions, eav before exemptions, and the
property type. I also create a dummy variable for if the pin is located
in a tax_code within a TIF.
pin_data <- lookup_pin(2021, muni_pins$pin) %>%
mutate(first6 = str_sub(agency_num,1,6),
first5 = str_sub(agency_num,1,5))## Error in `mutate()`:
## ℹ In argument: `first6 = str_sub(agency_num, 1, 6)`.
## Caused by error in `vctrs::vec_size_common()`:
## ! object 'agency_num' not found# get class info with pin expemption data
class_dict$class_code <- as.character(class_dict$class_code)
pin_data <- pin_data %>% left_join(muni_pins, by = c("pin", "class"))## Error in left_join(., muni_pins, by = c("pin", "class")): object 'pin_data' not found # create a dummy variable for if the pin is located in a tax_code that is within a tif
pin_data <- pin_data %>%
left_join(muni_tax_codes, by = c("year", "tax_code_num") ) %>%
rename(muni_name = agency_name)## Error in left_join(., muni_tax_codes, by = c("year", "tax_code_num")): object 'pin_data' not foundpin_data <- pin_data %>% left_join(class_dict, by = c("class" = "class_code")) # %>%## Error in left_join(., class_dict, by = c(class = "class_code")): object 'pin_data' not foundEAV by Property Type for Municipality:
eav_by_class_inMUNI <- pin_data %>%
group_by(muni_name, major_class_code, major_class_type) %>%
summarise(eav = sum(eav))## Error in group_by(., muni_name, major_class_code, major_class_type): object 'pin_data' not foundeav_by_class_inMUNI## Error in eval(expr, envir, enclos): object 'eav_by_class_inMUNI' not foundexemptions_by_class <- pin_data %>%
group_by(muni_name, major_class_code, major_class_type)%>%
summarize(
exe_homeowner = sum(exe_homeowner),
exe_senior = sum(exe_senior),
exe_freeze = sum(exe_freeze),
exe_longtime_homeowner = sum(exe_longtime_homeowner),
exe_disabled = sum(exe_disabled),
exe_vet_returning = sum(exe_vet_returning),
exe_vet_dis = sum(exe_vet_dis_lt50+exe_vet_dis_50_69+exe_vet_dis_ge70),
exe_abate = sum(exe_abate)
) %>%
mutate("Total Exemptions in Class" = sum(exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner +
exe_disabled + exe_vet_returning + exe_vet_dis + exe_abate)) ## Error in group_by(., muni_name, major_class_code, major_class_type): object 'pin_data' not foundexemptions_by_class %>%
rename("Major Class Num" = major_class_code,
"Major Class Name" = major_class_type)## Error in rename(., `Major Class Num` = major_class_code, `Major Class Name` = major_class_type): object 'exemptions_by_class' not foundpin_data %>%
group_by(muni_name)%>%
summarize(
exe_homeowner = sum(exe_homeowner),
exe_senior = sum(exe_senior),
exe_freeze = sum(exe_freeze),
exe_longtime_homeowner = sum(exe_longtime_homeowner),
exe_disabled = sum(exe_disabled),
exe_vet_returning = sum(exe_vet_returning),
exe_vet_dis = sum(exe_vet_dis_lt50+exe_vet_dis_50_69+exe_vet_dis_ge70),
exe_abate = sum(exe_abate),
"Total Exemptions in Class" = sum(exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner +
exe_disabled + exe_vet_returning + exe_vet_dis + exe_abate)
) ## Error in group_by(., muni_name): object 'pin_data' not foundEAV by Property Type for Municipality:
eav_by_class_inMUNI <- pin_data %>%
group_by(muni_name, major_class_code, major_class_type) %>%
summarise(eav = sum(eav))## Error in group_by(., muni_name, major_class_code, major_class_type): object 'pin_data' not foundeav_by_class_inMUNI## Error in eval(expr, envir, enclos): object 'eav_by_class_inMUNI' not foundpin_data %>%
mutate(exemptions = sum(exe_homeowner:exe_abate)) %>%
group_by(muni_name)%>%
summarize(
exe_homeowner = sum(exe_homeowner),
TotalExemptions = sum(exemptions)
) %>%
left_join(eav_by_class_inMUNI)## Error in mutate(., exemptions = sum(exe_homeowner:exe_abate)): object 'pin_data' not found# Determining the increment / TIF stuff
tif_agency_nums <- agency_names %>%
filter(minor_type == "TIF") %>%
select(agency_num)
tif_agency_nums <- left_join(tif_agency_nums, agency_names)
tif_tax_codes2021 <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql("
SELECT *
FROM tax_code
WHERE agency_num IN ({tif_agency_nums$agency_num*})
AND year = 2021
",
.con = ptaxsim_db_conn
)
)
tif_tax_codes_2021 <- left_join(tif_tax_codes2021, tif_agency_nums)
tif_pins2021 <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql(
"SELECT DISTINCT year, pin, class
FROM pin
WHERE tax_code_num IN ({tif_tax_codes2021$tax_code_num*})
AND year = 2021
",
.con = ptaxsim_db_conn
))
tif_pins2021 %>%distinct(pin) %>% count()# TIF distributions will include all the unique tax codes that make up
# a TIF
# has eav values for each tax code
tif_distrib <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue_sql("
SELECT *
FROM tif_distribution
WHERE agency_num IN ({tif_agency_nums$agency_num*})
AND year = 2021
",
.con = ptaxsim_db_conn
)
)
#tif_distribution
# has same number of pins as method used above but way faster.
tif_pins_vec <- DBI::dbGetQuery(
ptaxsim_db_conn,
glue::glue_sql("
SELECT DISTINCT pin
FROM pin
WHERE tax_code_num IN ({unique(tif_distrib$tax_code_num)*})
AND year = 2021
",
.con = ptaxsim_db_conn
)
) %>%
pull(pin)Exemption data is only stored at the individual pin level. Must pull all pins and then aggregate to the level desired.
area_data was results from tax_bill command
which had how much of the taxbill went to each taxing district or the
TIF.
pin_data created below with the lookup_pin() function
has the information on exemptions, eav before exemptions, and the
property type. I also create a dummy variable for if the pin is located
in a tax_code within a TIF.
pin_data <- pin_data %>%
# create a dummy variable for if the pin is located in a tax_code that is within a tif
mutate(in_tif = ifelse(pin %in% tif_pins2021$pin, 1,0))## Error in mutate(., in_tif = ifelse(pin %in% tif_pins2021$pin, 1, 0)): object 'pin_data' not foundAmount of EAV in and outside of TIF areas:
This is the EAV before exemptions are subtracted. Comes from lookup_pin() command.
Individual properties are summed together within the municipality.
EAV_inandout_TIF <- pin_data %>%
group_by(muni_name, in_tif) %>%
summarise(eav = sum(eav)) %>%
pivot_wider(names_from = in_tif, values_from = eav)%>%
mutate(total = `0`+`1`) %>%
rename(
"EAV outside TIF" = `0`,
"EAV within TIF" = `1`,
"Total EAV before exemptions" = total)## Error in group_by(., muni_name, in_tif): object 'pin_data' not foundEAV_inandout_TIF## Error in eval(expr, envir, enclos): object 'EAV_inandout_TIF' not foundEAV_inandout_TIF <- pin_data %>%
group_by(muni_name, major_class_code, major_class_type, in_tif) %>%
summarise(eav = sum(eav)) %>%
pivot_wider(names_from = in_tif, values_from = eav)%>%
mutate(total = `0`+`1`) %>%
rename("Major Class Num" = major_class_code,
"Major Class Name" = major_class_type,
"EAV outside TIF" = `0`,
"EAV within TIF" = `1`,
"Total EAV before exemptions" = total)## Error in group_by(., muni_name, major_class_code, major_class_type, in_tif): object 'pin_data' not foundEAV_inandout_TIF## Error in eval(expr, envir, enclos): object 'EAV_inandout_TIF' not foundexemptions_by_class <- pin_data %>%
group_by(muni_name, major_class_code, major_class_type)%>%
summarize(
exe_homeowner = sum(exe_homeowner),
exe_senior = sum(exe_senior),
exe_freeze = sum(exe_freeze),
exe_longtime_homeowner = sum(exe_longtime_homeowner),
exe_disabled = sum(exe_disabled),
exe_vet_returning = sum(exe_vet_returning),
exe_vet_dis = sum(exe_vet_dis_lt50+exe_vet_dis_50_69+exe_vet_dis_ge70),
exe_abate = sum(exe_abate)
) %>%
mutate("Total Exemptions in Class" = sum(exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner +
exe_disabled + exe_vet_returning + exe_vet_dis + exe_abate)) ## Error in group_by(., muni_name, major_class_code, major_class_type): object 'pin_data' not foundexemptions_by_class %>%
rename("Major Class Num" = major_class_code,
"Major Class Name" = major_class_type)## Error in rename(., `Major Class Num` = major_class_code, `Major Class Name` = major_class_type): object 'exemptions_by_class' not foundJoin by summed pin_data by property type with summed tax_bill property by type?
joined_table <- revenue_by_class %>%
left_join(exemptions_by_class)%>%
mutate(
"Tax Revenue(District+TIF)" = scales::dollar(tax_amt_post_exe),
TotalEAV = tax_amt_post_exe/composite_tax_rate,
"District Revenue" = scales::dollar(final_tax_to_dist),
"TIF Revenue" = scales::dollar(final_tax_to_tif),
"Lost Revenue from Exempt." = scales::dollar(tax_amt_exe), # same as tax_amt_pre_exe-tax_amt_post_exe
"Tax Share" = scales::percent(dist_tax_share),
"EAV in TIF" = final_tax_to_tif/composite_tax_rate,
# TIF EAV matches JD almost perfectly, Commercial 5A does not match.
"EAV outside TIF" = final_tax_to_dist/composite_tax_rate
) %>%
mutate(EAV = `EAV in TIF`+`EAV outside TIF`)
# select(-c(tax_amt_post_exe:tax_share, final_tax_to_dist:tax_amt_pre_exe,exe_homeowner:TOTAL))
#class_exemptions <- full_join(exemptions_by_class, class_eav, by = "major_class_code") %>%
# select(major_class_code, major_class_type, eav, final_tax_to_dist, tax_share)
#class_exemptions
joined_table %>%
rename("Major Class Num" = major_class_code,
"Major Class Name" = major_class_type
) %>%
select(-c(tax_amt_pre_exe:pre_minus_post, exe_homeowner:exe_abate, final_tax_to_dist:tax_amt_exe))class_dict$class_code <- as.character(class_dict$class_code)
# tif_pins_dt is unique pins and their exemptions.
# can sum EAV from this data frame.
eav_by_class_inTIFS <- tif_pins_dt %>%
left_join(class_dict, by = c("class"="class_code")) %>%
filter(year == 2021) %>%
group_by(muni_name, major_class_code, major_class_type) %>%
summarize(eav_inTIF = sum(eav))## Error in left_join(., class_dict, by = c(class = "class_code")): object 'tif_pins_dt' not foundeav_by_class_inTIFS## Error in eval(expr, envir, enclos): object 'eav_by_class_inTIFS' not foundtif_pins_summ <- tif_pins_dt %>%
group_by(year) %>%
#Summed TIF EAV amounts
summarize(eav_inTIF_pre_exe = sum(eav)) %>%
left_join(tif_distrib, by = "year"
) %>%
# amount of value taxed by tifs = all EAV in tax code - Frozen EAV level
mutate(tif_increment = tax_code_eav - tax_code_frozen_eav,
tifrev2 = tax_code_rate*tif_increment)## Error in group_by(., year): object 'tif_pins_dt' not foundtif_pins_sum <- tif_pins_summ %>% group_by(year) %>%
summarize(tax_code_eav_pre_exe = sum(tax_code_eav),
tif_increment_pre_exe = sum(tif_increment),
frozen_eav_pre_exe = sum(tax_code_frozen_eav))## Error in group_by(., year): object 'tif_pins_summ' not foundtif_pins_sum## Error in eval(expr, envir, enclos): object 'tif_pins_sum' not foundtif_share = the tax_code_distribution_pct / 100 and
comes from tif_distribution table. Comes from lookup_tif() command.
Calculated behind the scenes. Documentation for their process is in
Gitlab R/lookup.R file
Lets find TIF taxing agencies and combine them with the other taxing agencies for the geographic area:
eav_per_taxcode <- bills %>%
group_by(tax_code) %>%
summarize(eav = sum(eav),
final_tax_to_tif=sum(final_tax_to_tif),
final_tax_to_dist=sum(final_tax_to_dist))
# 20 tax_codes with eav inside each one
eav_per_taxcode
eav_perclasstype <- bills %>%
group_by(major_class_code, major_class_type) %>%
summarize(#eav = sum(eav), Don't sum eav at this stage, adds pin eavs multiple times since pins are not unique
final_tax_to_tif=sum(final_tax_to_tif),
final_tax_to_dist=sum(final_tax_to_dist))
## Counts pins multiple times?
## Not unique pins so eav for a pin is added multiple times
eav_perclasstype
bills %>%
group_by(tax_code) %>%
summarize(
# exe_toal = sum(exe_total),
pin_count = n())
bills %>%
group_by(agency_name) %>%
summarize(
pin_count = n())# slightly more tax_codes than pins
# most pins have one tax code
# all tax codes within the 6 communities
tax_codes <- lookup_tax_code(2021, muni_pins$pin)
# now take those tax codes and use them to identify if any tifs are associated with them:
# gives you tax_codes and agency_num and agency_name and the tif_share
# agency_minor_type = TIF
tif_taxing_agencies <- lookup_tif(2021, tax_codes) #all tax codes within a tif
tif_taxing_agencies # tif taxcodes and tif taxing agencies
tif_tax_codes <- as.character(tif_taxing_agencies$tax_code)
#tax_codes <- lookup_tax_code(2021, tif_tax_codes)All non tif taxing agencies:
taxing_agencies <- lookup_agency(2021, tax_codes)
taxing_agencies <- taxing_agencies# %>% left_join(muni_tax_codes)
taxing_agencies %>% arrange(tax_code)taxing_agencies %>%
group_by(agency_name)%>%
summarize(EAV = first(agency_total_eav),
total_levy = first(agency_total_ext)) %>%
arrange(total_levy)# all_taxing_agencies <- full_join(taxing_agencies, tif_taxing_agencies,
# # by = c("year", "tax_code", "agency_name", "agency_num")
# ) %>% mutate(in_tif = ifelse(is.na(tif_share), 0, 1))Combine tif and non-tif taxing agencies:
all_taxing_agencies <- rbind(taxing_agencies, tif_taxing_agencies, fill=TRUE) %>%
mutate(in_tif = ifelse(is.na(tif_share), 0, 1))
all_taxing_agencies %>%
group_by(agency_num, agency_name, in_tif) %>%
summarize(agency_total_eav = mean(agency_total_eav),
tif_share = mean(tif_share),
agency_total_ext=mean(agency_total_ext)
# ,summed_eav = sum(eav)
)Need EAV of each Tax Code. Then we know the TIF eav from TIF tax_codes and other agency EAVs from Tax codes associated with other agencies.
all_taxing_agencies has agency_total_eav but that includes all pin values everywhere that are included in that tax_code (So across all of cook county for Cook County’s total eav)
taxcodes_intifs <- all_taxing_agencies %>%
filter(in_tif==1) %>%
select(tax_code) %>%
distinct() ## Error in `filter()`:
## ℹ In argument: `in_tif == 1`.
## Caused by error:
## ! object 'in_tif' not foundtaxcodes_intifs## Error in eval(expr, envir, enclos): object 'taxcodes_intifs' not foundtif_agency_nums <- all_taxing_agencies %>%
filter(in_tif==1) %>%
select(agency_num) %>%
distinct()## Error in `filter()`:
## ℹ In argument: `in_tif == 1`.
## Caused by error:
## ! object 'in_tif' not foundtif_agency_numsCalculate the increment by comparing the total property value in the TIF (in EAV) to the frozen amount. Anything above the frozen amount is taxed by the TIF
Get all pins within TIF area, then compare total equalized assessed value to the value “frozen” by the TIF.
agency_total_eav The total amount of EAV within the taxing district, otherwise known as the “base”. This is the denominator when calculating tax rates
agency_total_ext The total extension requested by the taxing district, otherwise known as the “levy”. This is the amount the district needs in tax revenue and is the numerator when calculating tax rates
“Changing tax_code_vec”relocates” a PIN by changing the things that are taxing it. This can be useful for counterfactual analysis. For example, if you own property within a school district and want to know what your tax bill would be just outside the district, but otherwise within the same municipality, then you can find the tax code that represents that situation and plug it into tax_bill()”
tifs <- lookup_tif(2021, tax_codes)
tifs# has exemptions for pins within TIFs
tif_pins_dt <- lookup_pin(2021, pin = tif_pins_vec) %>%
mutate(tax_code = lookup_tax_code(year, pin))
tif_pins_dt <- tif_pins_dt %>% left_join(pin_data)## Error in is.data.frame(y): object 'pin_data' not foundpin_data_summarytable<- tif_pins_dt %>%
group_by(muni_name) %>%
# mutate(inTIF = ifelse(pin %in% tif_pins, 1,0)) %>%
summarize(#eav = sum(eav),
exe_homeowner = sum(exe_homeowner),
exe_senior = sum(exe_senior),
exe_freeze = sum(exe_freeze),
exe_longtime_homeowner = sum(exe_longtime_homeowner),
exe_disabled = sum(exe_disabled),
exe_vet_returning = sum(exe_vet_returning),
exe_vet_dis = sum(exe_vet_dis_lt50+exe_vet_dis_50_69+exe_vet_dis_ge70),
exe_abate = sum(exe_abate)
) %>%
mutate(TOTAL = sum(exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner +
exe_disabled + exe_vet_returning + exe_vet_dis + exe_abate)) %>%
pivot_longer(cols = exe_homeowner:TOTAL,
values_to = "exemptions",
names_to = "exemption_type")## Error in `group_by()`:
## ! Must group by variables found in `.data`.
## ✖ Column `muni_name` is not found.pin_data_summarytable## Error in eval(expr, envir, enclos): object 'pin_data_summarytable' not foundlibrary(openxlsx)
dataset_names <- list('Taxing Agencies from Tax Bills' = taxing_agencies, #tifs not listed as agencies
'Agency Levies' = agency_levies,
# comparable to JD taxing agency table
'JD Taxing Agencies Comparison' = nonTIF_taxingagencies_table,
'Total District Revenue' = district_revenue,
'Total Revenue' = total_revenue_table,
'Revenue by District&TIF' = rev_by_district_and_tif,
'Revenue by Class' = revenue_by_class,
# 'Taxcodes in TIFs' == tif_tax_codes2021, creates problem with excel file?
'TIF pins' = tif_pins_dt,
'EAV in and out of TIF' = EAV_inandout_TIF,
'Exemptions by Class' = exemptions_by_class,
'Joined Table' = joined_table,
'EAV in Muni'= eav_by_class_inMUNI, # PRE exemptions!
'eav in tifs'= eav_by_class_inTIFS,
'TIF increment&frozenTotal'= tif_pins_sum,
'tif taxing agencies' = tif_taxing_agencies,
'exemption in tifs'= pin_data_summarytable
)
#write.xlsx(dataset_names, file = 'Bridgeview_2021.xlsx')bills <- tax_bill(2021, muni_pins$pin, simplify = FALSE)
# all taxpbills for PINs in 2021 in the municipality
# does include
bills
# get class info with pin expemption data
class_dict$class_code <- as.character(class_dict$class_code)
bills <- left_join(bills, class_dict,
by=c("class" = "class_code")) %>%
mutate(in_tif = ifelse(final_tax_to_tif > 0, 1, 0)) # creat variable for if tax revenue went toward TIF.Amount collected by each taxing agency within the municipality:
agency_levies <- bills %>%
mutate( weighted_tax_rate = agency_tax_rate*eav) %>%
group_by(agency_num,agency_name) %>%
summarize(
weighted_tax_rate = round(sum(weighted_tax_rate), digits=0), #comparison to JD weighted tax rate
levy_within_muni = round(sum(final_tax_to_dist, na.rm=TRUE), digits=0),
tif_rev = round(sum(final_tax_to_tif, na.rm=FALSE), digits=0),
percent_to_tif = round(tif_rev/ (levy_within_muni+tif_rev), digits = 4))
agency_levies## values are after exemptions have been subtracted!
taxing_agencies <- bills %>%
group_by(agency_name, agency_num, agency_tax_rate) %>%
summarize(eav = sum(eav),
# final_tax = sum(final_tax),
# final tax only exists if simplify = TRUE in that tax_bill() command.
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),
tax_amt_pre_exe = sum(tax_amt_pre_exe),
tax_amt_post_exe = sum(tax_amt_post_exe),
agency_tax_rate = mean(agency_tax_rate)
) %>%
arrange(agency_num) %>%
mutate(weighted_tax_rate = agency_tax_rate*eav) %>%
ungroup() %>%
mutate(composite_tax_rate = sum(weighted_tax_rate)/max(eav)
)
taxing_agenciesThe table above does not include the names of TIF taxing agencies. However it does include the amount of revenue that went to the taxing district compared to the TIF. Sp total amount to TIF we have, but the amount to each TIF we do not have (yet).
Save the composite_tax_rate as its own object because we
will reference it in later calculations.
# saves composite tax rate as its own object because I might plug it in with the TIF stuff later.
composite_tax_rate <- first(taxing_agencies$composite_tax_rate)
nonTIF_taxingagencies_table <- taxing_agencies %>%
mutate(`Tax Rate` = round(agency_tax_rate, digits = 6),
EAV = scales::comma(eav)) %>%
select(agency_name, agency_name, EAV, `Tax Rate`, weighted_tax_rate, composite_tax_rate) %>%
rename("Weighted Tax Rate" = weighted_tax_rate,
"Composite Tax Rate" = composite_tax_rate)
# comparable to JD taxing agency table
nonTIF_taxingagencies_table Taxing Agency Values above very close to JD excel files. Slight difference for this municipality - Not sure why yet - Most likely from difference in number of bills included in data pull.
Remember, the agencies returned in the tax_bill command are all the Non-TIF taxing agencies ! TIFs do not have their own taxing rate, they just use the composite tax rate for the municipality.
Therefore, we need to calculate the eav within each TIF and then multiply it by the composite tax rate. We can also sum the amount paid to TIFs within each tax_code using the municipal tax bill data for pins and then apply the tif_distribution data table to calculate how much of each tif is in each taxcode.
I do both in the code below and have not chosen one method to stick with.
Holds Levy constant.
EAV pre-exemptions in this calculation will be wrong because the composite tax rate is based on the levy/EAV. So if the eav changes, then the composite tax rate will change. For one bill this doesn’t matter. When changing a lot of bills, this matters a lot. New agency rates should be calculated if changing exemptions.
Use the pre- exemption EAV calculated from the pin_lookup() data
frame pin_data$eav if calculating the original EAV before
exemptions.
Is lookup_pin() eav before or after exemptions??? Check their gitlab code for creating the table.
Need levy to calculate new agency rates or district rates.
district_revenue <- bills %>%
group_by(muni_name) %>%
summarize(
# tax bill reduced by this much due to exemptions
tax_amt_exe = sum(tax_amt_exe),
# What individual tax bill would have been without exemptions
tax_amt_pre_exe = sum(tax_amt_pre_exe),
# what tax bill was after exemptions
tax_amt_post_exe = sum(tax_amt_post_exe),
final_tax_to_dist = sum(final_tax_to_dist),
final_tax_to_tif = sum(final_tax_to_tif)) %>%
mutate(total_revenue_amount = final_tax_to_dist + final_tax_to_tif,
# post exemption district tax share! It changes if exemptions are changed
dist_tax_share = final_tax_to_dist/sum(final_tax_to_dist),
# making sure tax_amt_exe is what I think it is next line. Variable not used
pre_minus_post = tax_amt_pre_exe-tax_amt_post_exe,
exempted_EAV = tax_amt_exe/composite_tax_rate)
total_revenue_table <- district_revenue %>%
mutate(
# eav BEFORE exemptions - Will be wrong if calculated this way!
EAV_pre_exemptions = tax_amt_pre_exe/composite_tax_rate,
EAV_post_exemptions = tax_amt_post_exe/composite_tax_rate, # eav after exemptions
"Lost Revenue from Exemptions" = scales::dollar(tax_amt_exe),
"Total Revenue Pre-Exe" = scales::dollar(tax_amt_pre_exe),
"Total Revenue Post-Exe" = scales::dollar(tax_amt_post_exe),
"TIF Increment Post-Exe" = scales::comma(final_tax_to_tif/composite_tax_rate),
"EAV outside TIF Post-Exe" = final_tax_to_dist/composite_tax_rate,
"District Rev Post-Exe" = scales::dollar(final_tax_to_dist),
"TIF Rev Post-Exe" = scales::dollar(final_tax_to_tif),
"District Tax Share Post-Exe" = scales::percent(dist_tax_share),
eav_total = scales::dollar(exempted_EAV+EAV_post_exemptions)
# EAV in TIF pre-exe will be off becase EAV pre-exemptions will be off.
# "EAV in TIF Pre-Exe" = (EAV_pre_exemptions-`EAV outside TIF Post-Exe`),
# "EAV in TIF Post-Exe" = (EAV_post_exemptions-`EAV outside TIF Post-Exe`)
) %>%
select(-c(final_tax_to_dist:pre_minus_post, tax_amt_exe:tax_amt_post_exe))
total_revenue_table
rev_by_district_and_tif <- bills %>%
group_by(muni_name,in_tif) %>%
summarize(tax_amt_exe = sum(tax_amt_exe), # tax bill reduced by this much due to exemptions
tax_amt_pre_exe = sum(tax_amt_pre_exe), # What individual tax bill would have been without exemptions
tax_amt_post_exe = sum(tax_amt_post_exe), # what tax bill was after exemptions
final_tax_to_dist = sum(final_tax_to_dist),
final_tax_to_tif = sum(final_tax_to_tif)) %>%
mutate(total_revenue_amount = final_tax_to_dist+final_tax_to_tif,
# post exemption district tax share! It changes if exemptions are changed
dist_tax_share = final_tax_to_dist/sum(final_tax_to_dist),
# making sure tax_amt_exe is what I think it is next line. Variable not used
pre_minus_post = tax_amt_pre_exe-tax_amt_post_exe) %>%
mutate(
# eav BEFORE exemptions - Will be wrong if calculated this way!
EAV_pre_exemptions = tax_amt_pre_exe/composite_tax_rate,
EAV_post_exemptions = tax_amt_post_exe/composite_tax_rate, # eav after exemptions
"TIF Increment Post-Exe" = scales::comma(final_tax_to_tif/composite_tax_rate),
"EAV outside TIF Post-Exe" = final_tax_to_dist/composite_tax_rate,
"EAV in TIF Post-Exe" = (EAV_post_exemptions-`EAV outside TIF Post-Exe`),
"Total Revenue Pre-Exe" = scales::dollar(tax_amt_pre_exe),
"Total Revenue Post-Exe" = scales::dollar(tax_amt_post_exe),
"District Rev Post-Exe" = scales::dollar(final_tax_to_dist),
"TIF Rev Post-Exe" = scales::dollar(final_tax_to_tif),
"District Tax Share Post-Exe" = scales::percent(dist_tax_share),
# EAV in TIF pre-exe will be off becase EAV pre-exemptions will be off.
# "EAV in TIF Pre-Exe" = (EAV_pre_exemptions-`EAV outside TIF Post-Exe`),
"Lost Revenue from Exemptions" = scales::dollar(tax_amt_exe),
) %>%
select(-c(tax_amt_exe:tax_amt_post_exe, final_tax_to_dist:pre_minus_post))
rev_by_district_and_tifrevenue_by_class <- bills %>%
group_by(muni_name, major_class_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), # tax revenue lost due to exemptions. was individual tax bill decreases for each agency
tax_amt_pre_exe = sum(tax_amt_pre_exe), # What revenue would have been without exemptions
tax_amt_post_exe = sum(tax_amt_post_exe) # what revenue was after exemptions
) %>%
mutate(total_tax_amount = final_tax_to_dist+final_tax_to_tif,
dist_tax_share = final_tax_to_dist/sum(final_tax_to_dist),
# making sure tax_amt_exe is what I think it is next line. Variable not used
pre_minus_post = tax_amt_pre_exe-tax_amt_post_exe)
# ENTIRE TABLE IS ARTER EXEMPTIONS ARE SUBTRACTED
revenue_by_class %>%
mutate(
"Major Class" = major_class_code,
EAV_post_exe = tax_amt_post_exe/composite_tax_rate, # eav after exemptions
"TIF Increment Post-Exe" = scales::comma(final_tax_to_tif/composite_tax_rate),
"EAV outside TIF Post-Exe" = final_tax_to_dist/composite_tax_rate,
"EAV in TIF Post-Exe" = (EAV_post_exe-`EAV outside TIF Post-Exe`),
"Total Revenue Post-Exe" = scales::dollar(tax_amt_post_exe),
"District Rev Post-Exe" = scales::dollar(final_tax_to_dist),
"TIF Rev Post-Exe" = scales::dollar(final_tax_to_tif),
"District Tax Share Post-Exe" = scales::percent(dist_tax_share),
"Lost Revenue from Exemptions" = scales::dollar(tax_amt_exe) # same as tax_amt_pre_exe-tax_amt_post_exe
) %>%
# mutate(EAV = `EAV in TIF Post-Exe`+`EAV outside TIF Post-Exe`) %>%
select(-c(tax_amt_post_exe:dist_tax_share,final_tax_to_dist:tax_amt_pre_exe, pre_minus_post, major_class_code))