Bridgeview Data Replication

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


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)

# 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
## additional information to the pins and class.
class_dict <- read_csv("class_dict.csv")

Bridgeview Replication Numbers

Pulling Muni data tables

Pulling all taxing agencies directly related to Bridgeview. Includes TIFs, SSAs, Library, and Village of Bridgeview (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.

agency_names <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT agency_num, agency_name, minor_type
  FROM agency_info
  WHERE agency_name LIKE '%BRIDGEVIEW%'"
)
## 17 agency names (TIF & non-TIF) that have anything to do with Bridgeview in their names
agency_names

# agency_num = 030120000. So far so good. 
muni_agency_nums<- agency_names %>% 
  filter(minor_type %in% c("MUNI", "TOWNSHIP")) %>%
  select(agency_num)

# 27 tax codes in MUNI taxing agencies for Bridgeview in 2021
muni_tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn, 
  glue_sql("
  SELECT DISTINCT tax_code_num
  FROM tax_code
  WHERE agency_num IN ({muni_agency_nums$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)

# 5302 pins in tax codes within muni taxing agency in 2021
muni_pins <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql(
  "SELECT DISTINCT pin, class
  FROM pin
  WHERE tax_code_num IN ({muni_tax_codes$tax_code_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
))

agency_lookups <- lookup_agency(2021, muni_tax_codes$tax_code_num) 
options(scipen=999)

agency_lookups

agency_lookups %>%
  filter(agency_major_type %in% c("MUNICIPALITY/TOWNSHIP", "SCHOOL")) %>%
  #  agency_minor_type %in% c("MUNI", "TOWNSHIP") ) %>%
           group_by(agency_name) %>%
           summarize(agency_total_eav = first(agency_total_eav),
                     agency_total_ext = first(agency_total_ext)) %>%
           arrange(-agency_total_eav)

There were 27 tax codes for the Village of Bridgeview and 5302 PINs with those tax codes during 2021. There are 397 tax agency-tax code combinations in 2021.

Tax Bill values from tax_bill()

In general, I never use the simply=TRUE default in the tax_bill() function. I want the amount that goes to the tif, the district, and the pre and post exemption bill amounts. These are only returned if simplify=FALSE.

Notes to self:

• Weighted tax rates = agency tax rate X EAV in taxing area

• Composite tax rates is the aggregate of taxing agency ratesXEAV in taxing area

• Taxing agencies revenues = tax rate * frozen EAV

• Frozen EAV = District Tax Revenue / tax rate

• TIF revenue = TIF tax rate * incremental EAV

## Small difference in number of taxbills between methods
# 78,335 taxbills/observations for muni in 2021
# 77832 using muni_pins$pin method from ptaxsim datatable


# method pulls pins from Cook County data portal for properties within property_city = "BRIDGEVIEW"
# method used in old exampes on their GitLab. Probably won't use anymore.
# area_data <- tax_bill(2021, area_pins2, simplify = FALSE)

# 77832 using muni_pins$pin method from ptaxsim datatable
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")) %>% 
  # create variable for if tax revenue went toward TIF.
  mutate(in_tif = ifelse(final_tax_to_tif > 0, 1, 0)) 

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

Composite Tax Rate & Taxing Agencies

## 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_agencies

The 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.

Tax Revenue by Property Class

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.

Reminder: Don’t sum exe_total or eav from area_data because the values are for pins and there are repeated pins in this data frame!

district_revenue <- bills %>% 
  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)

DistrictLevy <- bills %>% summarize(total_tax_to_district = sum(final_tax_to_dist))

TaxableBase <- DistrictLevy/composite_tax_rate

DistrictLevy

TaxableBase

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_pre_exemptions = 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(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_tif

Important values from table above include the amount of exemptions within a TIF… I think.

revenue_by_class <- bills %>% 
  group_by(major_class_code) %>% 
  summarize(
            final_tax_to_dist = sum(final_tax_to_dist), # the district levy
            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),
             dist_tax_share_NOEXEMP = (final_tax_to_dist+tax_amt_exe)/(sum(final_tax_to_dist+tax_amt_exe)),
             # 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
  #  EAV_pre_exe_WRONGCALC = tax_amt_pre_exe/composite_tax_rate, # not correct composite tax rate if 
    "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))

Tax Burden

Related to last two chunks above.

Proportion of district revenue paid by each property class with and without exemptions. Still do not know how to deal with indirect effect of changing exemptions (a change in the tax rate).

Create TIF vectors

# Determining the increment / TIF stuff
# 9 taxing agencies overlap with TIFs
tif_agency_nums <- agency_names %>% 
  filter(minor_type == "TIF") %>% select(agency_num)

# #18 tax_codes for all years
# tif_tax_codes <- DBI::dbGetQuery(
#   ptaxsim_db_conn, 
#   glue_sql("
#   SELECT DISTINCT tax_code_num
#   FROM tax_code
#   WHERE agency_num IN ({tif_agency_nums$agency_num*})
#   ",
#   .con = ptaxsim_db_conn
#   )
# )


# 15 tax_codes in 2021
tif_tax_codes2021 <- DBI::dbGetQuery(
  ptaxsim_db_conn, 
  glue_sql("
  SELECT DISTINCT tax_code_num
  FROM tax_code
  WHERE agency_num IN ({tif_agency_nums$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)


# # 1670 unique pin-class combinations for all years Some pins change classes over time
# # 4978 unique pins
# tif_pins <- DBI::dbGetQuery(
#   ptaxsim_db_conn,
#   glue_sql(
#   "SELECT DISTINCT pin, class
#   FROM pin
#   WHERE tax_code_num IN ({unique_tif_taxcodes$tax_code_num*})
#   ",
#   .con = ptaxsim_db_conn
# ))

# if we want just pins that existed in 2021:
# 415 uniaue pins  in TIFs in 2021
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
))

# 1615 distinct pins that are in TIFs for all years
# 415 in 2021
# tif_pins %>% distinct(pin) %>% count()
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 that involves TIFs
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)

# has EAV values for each pin in the TIF
# 73,256 obs for all years: pin-tax_code-year combinations.

tif_pins_dt <- lookup_pin(2006:2021, pin = tif_pins_vec) %>%
  mutate(tax_code = lookup_tax_code(year, pin))
# 4672 pins in TIFs in 2021

Lookup_pin() command.

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)

# get class info with pin expemption data
#class_dict$class_code <- as.character(class_dict$class_code)
pin_data <- left_join(pin_data, class_dict, by=c("class" = "class_code")) %>% 
  # 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))

EAV by Property Type for Municipality:

eav_by_class_inMUNI <- pin_data %>% 
  group_by(major_class_code, major_class_type) %>% 
  summarise(eav = sum(eav))
eav_by_class_inMUNI

Amount 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(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)

EAV_inandout_TIF

EAV_inandout_TIF <- pin_data %>% 
  group_by(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)

EAV_inandout_TIF

Exemptions by Exemption Type and Property Class

exemptions_by_class <- pin_data %>% 
  group_by(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)) 

exemptions_by_class %>%   
  rename("Major Class Num" = major_class_code,
         "Major Class Name" = major_class_type)

Joining pin_data exemptions & tax_bill revenue data

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

TIF Data tables

Increment:
- Gather all pins within the TIF
- Sum their EAV
- compare to value “frozen” by the TIF

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(major_class_code, major_class_type) %>% 
  summarize(eav_inTIF = sum(eav))

eav_by_class_inTIFS

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


tif_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))
tif_pins_sum

tif_inc_plot <- tif_pins_summ %>% 
#  group_by(year)%>% 
  #summarize(frozen_eav = sum(tax_code_frozen_eav)) %>%
  ggplot() +
  
    geom_col(
    aes(x = year, y = tax_code_eav),
    fill = "red",
    alpha = 0.5
  ) +
      geom_col(
    aes(x = year, y = tax_code_frozen_eav),
    fill = "gray40"
  ) +
  scale_y_continuous(
    labels = scales::label_dollar(scale = 1e-6, suffix = "M"),
    expand = c(0, 0)
  ) +
  scale_x_continuous(n.breaks = 9, expand = c(0, 0.4)) +
  labs(x = "Year", y = "Total EAV of PINs in _____", caption = "Red is amount of EAV taxed where the revenue went to the TIF") +
  theme_minimal() +
  theme(
    axis.title = element_text(size = 13),
    axis.title.x = element_text(margin = margin(t = 6)),
    axis.title.y = element_text(margin = margin(r = 6)),
    axis.text = element_text(size = 11),
    axis.ticks.x = element_line(color = "grey70"),
    strip.text = element_text(size = 16),
    strip.background = element_rect(fill = "#c9c9c9"),
    legend.title = element_text(size = 14),
    legend.key.size = unit(24, "points"),
    legend.text = element_text(size = 12),
    legend.position = "bottom"
  )
tif_inc_plot

Graph of tax revenue by district over time:

tif_bills <- tax_bill(2006:2020, tif_pins_vec)

tif_bills_summ <- tif_bills %>%
  mutate(
    agency_minor_type = recode(
      agency_minor_type,
      "GEN ASST" = "MUNI",
      "PARK" = "MUNI",
      "INFRA" = "MUNI",
      "LIBRARY" = "MUNI",
      "MOSQUITO" = "MISC",
      "WATER" = "MISC"
    ),
    agency_minor_type = factor(
      agency_minor_type,
      levels = c(
        "TIF", "COOK", "MUNI", "TOWNSHIP", "MISC",
        "COMM COLL", "ELEMENTARY", "SECONDARY"
      )
    )
  ) %>%
  group_by(year, agency_minor_type) %>%
  summarize(total_rev = sum(final_tax))


# make a graph! Copied mostly from TIF vingette examples 
tif_dist_plot <- ggplot(data = tif_bills_summ) +
  geom_area(
    aes(x = year, y = total_rev, fill = agency_minor_type),
    alpha = 0.8
  ) +
  scale_y_continuous(
    labels = scales::label_dollar(scale = 1e-6, suffix = "M"),
    expand = c(0, 0)
  ) +
  scale_x_continuous(n.breaks = 9, expand = c(0, 0.4)) +
  scale_fill_manual(
    name = "",
    values = c("#7d26cd", RColorBrewer::brewer.pal(7, "Set2"))
  ) +
  labs(x = "Year", y = "Total Tax Revenue from TIF PINs", caption = "Multiple TIFs are aggregated together in this graph") +
  theme_minimal() +
  theme(
    axis.title = element_text(size = 13),
    axis.title.x = element_text(margin = margin(t = 6)),
    axis.title.y = element_text(margin = margin(r = 6)),
    axis.text = element_text(size = 11),
    axis.ticks.x = element_line(color = "grey70"),
    strip.text = element_text(size = 16),
    strip.background = element_rect(fill = "#c9c9c9"),
    legend.title = element_text(size = 14),
    legend.key.size = unit(24, "points"),
    legend.text = element_text(size = 12)
  )
tif_dist_plot

TIFs

tif_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
tax_codes <- lookup_tax_code(2021, muni_pins$pin) 

# 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


#pindata_in_tifs <- inner_join(tif_taxing_agencies, area_data
      #)
# 25,200 pins in tifs. 
#pindata_in_tifs %>% arrange(tif_share)


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 %>% 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() 

taxcodes_intifs

tif_agency_nums <- all_taxing_agencies %>% 
  filter(in_tif==1) %>% 
  select(agency_num) %>% 
  distinct()

tif_agency_nums

Calculate 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

Exemption Types in 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))

pin_data_summarytable<- tif_pins_dt %>% 
#  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")

pin_data_summarytable

Exemption Total Values match JD’s file.

Exporting data into Excel

library(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')

I pretty much copied and pasted this code from their gitlab examples.

Don’t spend too much time looking at anything below. The focus is how the data is created in all the chunks above.

Counterfactuals

Removing Exemptions

Graphs do not consider the change in tax rates that would occur from changing the taxable base. When removing exemptions, the taxable EAV changes, the levy does not, so then the taxing rates for each agency should change. I do not know how to incorporate the change in tax rates yet (this is the indirect effect of exemptions).

bills_w_exe <- tax_bill(2018:2021, muni_pins$pin, simplify = FALSE)

bills_w_exe_summ <- bills_w_exe %>%
  group_by(year) %>%
  summarize(
    exe = sum(tax_amt_exe),
    bill_total = sum(final_tax_to_tif) + sum(final_tax_to_dist),
    Type = "With exemptions"
  ) %>%
  select(Year = year, Type, "Exemption Amt." = exe, "Bill Amt." = bill_total)

## now remove exemptions
exe_dt <- lookup_pin(2018:2021, muni_pins$pin) %>%
  mutate(across(starts_with("exe_"), ~0)) %>%
  setDT(key = c("year", "pin"))

bills_no_exe <- tax_bill(2018:2021,
  muni_pins$pin,
  pin_dt = exe_dt,
  simplify = FALSE
)

bills_no_exe_summ <- bills_no_exe %>%
  group_by(year) %>%
  summarize(
    exe = sum(tax_amt_exe),
    bill_total = sum(final_tax_to_tif) + sum(final_tax_to_dist),
    Type = "No exemptions"
  ) %>%
  select(Year = year, Type, "Exemption Amt." = exe, "Bill Amt." = bill_total)
bills_no_exe_summ

bills_plot_2 <- rbind(bills_w_exe_summ, bills_no_exe_summ) %>%
  ggplot() +
  geom_line(aes(x = Year, y = `Bill Amt.`, linetype = Type), linewidth = 1.1) +
#  scale_x_continuous(n.breaks = 9) +
  scale_y_continuous(labels = scales::label_dollar()) +
  scale_linetype_manual(
    name = "",
    values = c("With exemptions" = "solid", "No exemptions" = "dashed")
  ) +
  theme_minimal() 
bills_plot_2

t_pins <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "
  SELECT DISTINCT pin
  FROM pin
  WHERE substr(tax_code_num, 1, 2) = '14'
  "
)
t_pins <- muni_pins$pin
t_years <- 2006:2021

t_bills_w_exe <- tax_bill(t_years, t_pins)[, stage := "With exemptions"]

t_pin_dt_no_exe <- lookup_pin(t_years, t_pins)
t_pin_dt_no_exe[, tax_code := lookup_tax_code(year, pin)]

exe_cols <- names(t_pin_dt_no_exe)[startsWith(names(t_pin_dt_no_exe), "exe_")]
t_tc_sum_no_exe <- t_pin_dt_no_exe[,
  .(exe_total = sum(rowSums(.SD))),
  .SDcols = exe_cols,
  by = .(year, tax_code)
]

t_agency_dt_no_exe <- lookup_agency(t_years, t_pin_dt_no_exe$tax_code)
t_agency_dt_no_exe[
  t_tc_sum_no_exe,
  on = .(year, tax_code),
  agency_total_eav := agency_total_eav + exe_total
]

t_pin_dt_no_exe[, (exe_cols) := 0][, c("tax_code") := NULL]

t_bills_no_exe <- tax_bill(
  year_vec = t_years,
  pin_vec = t_pins,
  agency_dt = t_agency_dt_no_exe,
  pin_dt = t_pin_dt_no_exe
)[
  , stage := "No exemptions"
]

# Little function to get the statistical mode
Mode <- function(x) {
  ux <- unique(x)
  ux[which.max(tabulate(match(x, ux)))]
}

t_no_exe_summ <- rbind(t_bills_w_exe, t_bills_no_exe)[
  , class := Mode(substr(class, 1, 1)),
  by = pin
][
  class %in% c("2", "3", "5"),
][
  , class := ifelse(class == "2", "Residential", "Commercial")
][
  , .(total_bill = sum(final_tax)),
  by = .(year, pin, class, stage)
][
  , .(avg_bill = mean(total_bill)),
  by = .(year, class, stage)
][
  , idx_bill := (avg_bill / avg_bill[year == 2006]) * 100,
  by = .(class, stage)
]

t_annot <- tibble(
  class = c("Residential", "Commercial"),
  x = c(2008, 2006.4),
  y = c(105, 115)
)

# Plot the change in indexed values over time
t_no_exe_summ_plot <- ggplot(data = t_no_exe_summ) +
  geom_line(
    aes(x = year, y = idx_bill, color = class, linetype = stage),
    linewidth = 1.1
  ) +
  geom_text(
    data = t_annot,
    aes(x = x, y = y, color = class, label = class),
    hjust = 0
  ) +
  scale_y_continuous(name = "Average Tax Bill, Indexed to 2006") +
  scale_x_continuous(name = "Year", n.breaks = 10, limits = c(2006, 2020.4)) +
  scale_linetype_manual(
    name = "",
    values = c("With exemptions" = "solid", "No exemptions" = "dashed")
  ) +
  scale_color_brewer(name = "", palette = "Set1", direction = -1) +
  guides(color = "none") +
  facet_wrap(vars(class)) +
  theme_minimal() +
  theme(
    axis.title = element_text(size = 13),
    axis.title.x = element_text(margin = margin(t = 6)),
    axis.title.y = element_text(margin = margin(r = 6)),
    axis.text.y = element_text(size = 11),
    strip.text = element_text(size = 16),
    strip.background = element_rect(fill = "#c9c9c9"),
    legend.title = element_text(size = 14),
    legend.key.size = unit(24, "points"),
    legend.text = element_text(size = 12),
    legend.position = "bottom"
  )
t_no_exe_summ_plot

Changing exemptions

PTAXSIM can also answer hypotheticals about large areas. For example, how would the average residential tax bill in ____ change if the Senior Exemption increased by $5,000 and the Senior Freeze Exemption was removed?

To find out, we again create a PIN input with modified exemption amounts, then recalculate the base by taking the difference between the real and hypothetical exemptions.

t_pin_dt_new_exe <- lookup_pin(t_years, t_pins)
t_pin_dt_new_exe[, tax_code := lookup_tax_code(year, pin)]

t_tc_sum_new_exe <- t_pin_dt_new_exe[
  , .(exe_total = sum(exe_freeze - (5000 * (exe_senior != 0)))),
  by = .(year, tax_code)
]

Next, we recalculate the base of each district. This time, the base may lose some EAV, since the Senior Exemption is increasing substantially.

t_agency_dt_new_exe <- lookup_agency(t_years, t_pin_dt_new_exe$tax_code)
t_agency_dt_new_exe[
  t_tc_sum_new_exe,
  on = .(year, tax_code),
  agency_total_eav := agency_total_eav + exe_total
]

Then, we again alter the pin_dt input by setting the Senior Freeze Exemption to zero and adding $5,000 to any Senior Exemption.

t_pin_dt_new_exe <- t_pin_dt_new_exe[
  , exe_freeze := 0
][
  exe_senior != 0, exe_senior := exe_senior + 5000
][
  , c("tax_code") := NULL
]

We again recalculate all Calumet tax bills with our updated exemptions and with an updated tax base for each district.

t_bills_new_exe <- tax_bill(
  year_vec = t_years,
  pin_vec = t_pins,
  agency_dt = t_agency_dt_new_exe,
  pin_dt = t_pin_dt_new_exe
)[
  , stage := "Changed exemptions"
]

Then, do the same aggregation and indexing we did previously, this time using the updated bills.

t_new_exe_summ <- rbind(t_bills_w_exe, t_bills_new_exe)[
  , class := Mode(substr(class, 1, 1)),
  by = pin
][
  class %in% c("2", "3", "5"),
][
  , class := ifelse(class == "2", "Residential", "Commercial")
][
  , .(total_bill = sum(final_tax)),
  by = .(year, pin, class, stage)
][
  , .(avg_bill = mean(total_bill)),
  by = .(year, class, stage)
][
  , idx_bill := (avg_bill / avg_bill[year == 2006]) * 100,
  by = .(class, stage)
]



t_new_exe_summ_plot <- ggplot(data = t_new_exe_summ) +
  geom_line(
    aes(x = year, y = idx_bill, color = class, linetype = stage),
    linewidth = 1.1
  ) +
  geom_text(
    data = t_annot,
    aes(x = x, y = y, color = class, label = class),
    hjust = 0
  ) +
  scale_y_continuous(name = "Average Tax Bill, Indexed to 2006") +
  scale_x_continuous(name = "Year", n.breaks = 10, limits = c(2006, 2020.4)) +
  scale_linetype_manual(
    name = "",
    values = c("With exemptions" = "solid", "Changed exemptions" = "dotted")
  ) +
  scale_color_brewer(name = "", palette = "Set1", direction = -1) +
  guides(color = "none") +
  facet_wrap(vars(class)) +
  theme_minimal() +
  theme(
    axis.title = element_text(size = 13),
    axis.title.x = element_text(margin = margin(t = 6)),
    axis.title.y = element_text(margin = margin(r = 6)),
    axis.text.y = element_text(size = 11),
    strip.text = element_text(size = 16),
    strip.background = element_rect(fill = "#c9c9c9"),
    legend.title = element_text(size = 14),
    legend.key.size = unit(24, "points"),
    legend.text = element_text(size = 12),
    legend.position = "bottom"
  )

t_new_exe_summ_plot

“The net effect of increasing the Senior Exemption while removing the Senior Freeze Exemption is a slight decrease in the average bill. However, this conclusion is ambiguous, complicated by the fact that the Senior Freeze is means-tested, while the Senior Exemption is not. The”real-world effect” of our hypothetical policy change would most likely be an increase in the property tax bills of poorer seniors, even though the average bill decreased.

Ultimately, with some careful coding and assumptions, PTAXSIM (and its included data) can be used to test almost any hypothetical change in exemptions.” - exemption vignette

What if there were no TIFs?

Update output of lookup_tif. tif_share is a variable used within tax_bill(). To remove the share of the bill dedicated to a TIF, make this percentage equal to zero.

Then recalculate Tax Base for all

tif_dt_noTIFS <- lookup_tif(
  2006:2021,
  lookup_tax_code(2006:2021, muni_pins$pin)
) %>%
  # Set TIF shares to 0 for ALL tifs in the municipality, leave non-tif others untouched
  mutate(tif_share = ifelse(agency_num %in% tif_agency_nums, 0, tif_share))

# Recalcuclate Tax base:

# Get the unaltered levy and base for all PINs in geographic area
tif_agency_cntr <- lookup_agency(
  2006:2021,
  lookup_tax_code(2006:2021, muni_pins$pin)
)

# For each agency and year, get the amount recovered from the TIF using the
# summary table we created earlier (tif_pins_summ)
tif_agency_amt_to_add <- tif_agency_cntr %>%
 # filter(tax_code == "38228") %>%
  distinct(year, agency_num) %>%
  left_join(
    tif_pins_summ %>% select(year, amt_to_tif),
    by = "year"
  )

# Update the base by adding the recovered amount to the each district's tax base
tif_agency_cntr_updated <- tif_agency_cntr %>%
  left_join(tif_agency_amt_to_add, by = c("year", "agency_num")) %>%
  rowwise() %>%
  mutate(agency_total_eav = sum(agency_total_eav, amt_to_tif, na.rm = TRUE)) %>%
  select(-amt_to_tif) %>%
  setDT(key = c("year", "tax_code", "agency_num"))

Didn’t finish this. Memory issue and impatient. Code might work? But idk for sure.

## Step 3. Recalculate bills
# Calculate unaltered, original bills for comparison
muni_bills <- tax_bill(2006:2021, muni_pins$pin)

# Calculate counterfactual tax bills where the WTC2 TIF does not exist
tif_bills <- tax_bill(
  year_vec = 2006:2021,
  pin_vec = muni_pins$pin,
  agency_dt = tif_agency_cntr_updated,
  tif_dt = tif_dt_noTIFS
)



## Make a graph
tif_bills_cntr_summ <- muni_bills %>%
  group_by(year, pin) %>%
  summarize(`With TIF` = sum(final_tax)) %>%
  left_join(
    tif_bills %>%
      group_by(year, pin) %>%
      summarize(`No TIF` = sum(final_tax)),
    by = c("year", "pin")
  ) %>%
  tidyr::pivot_longer(ends_with("TIF")) %>%
  group_by(year, name) %>%
  summarize(med_bill = median(value)) %>%
  mutate(
    lt = ifelse(name == "No TIF" & year >= 2015, "s", "d"),
    name = factor(name, levels = c("No TIF", "With TIF")),
  )

tif_bills_cntr_summ


tif_plot_cntr <- ggplot(tif_bills_cntr_summ) +
  geom_line(
    aes(x = year, y = med_bill, color = name, linetype = lt),
    linewidth = 1.1
  ) +
  scale_x_continuous(n.breaks = 9, expand = c(0, 0.4)) +
  scale_y_continuous(n.breaks = 7, labels = scales::label_dollar()) +
  scale_color_manual(
    name = "",
    values = c("With TIF" = "grey50", "No TIF" = "#7d26cd")
  ) +
  labs(x = "Year", y = "Median Tax Bill") +
  guides(linetype = "none") +
  theme_minimal() +
  theme(
    axis.title = element_text(size = 13),
    axis.title.x = element_text(margin = margin(t = 6)),
    axis.title.y = element_text(margin = margin(r = 6)),
    axis.text = element_text(size = 11),
    axis.ticks.x = element_line(color = "grey70"),
    strip.text = element_text(size = 16),
    strip.background = element_rect(fill = "#c9c9c9"),
    legend.title = element_text(size = 14),
    legend.key.size = unit(24, "points"),
    legend.text = element_text(size = 12),
    legend.position = "bottom"
  )
tif_plot_cntr

Other misc notes:

stage A length 1 character vector indicating the assessment stage from which to pull assessed value (column av) and equalized assessed value (column eav). Options include “mailed”, “certified”, “board”, and “clerk”.

eq_version A length 1 character vector indicating the version of the equalizer. The Illinois Department of Revenue calculates two equalizers:

“tentative” - The tentative equalizer based on CCAO certified values

“final” - The final equalizer based on Board of Review certified values

In general, the “tentative” value should be used with the “mailed” and “certified” stage options, while the “final” value should be used with the “board” and “clerk” stage options.

Example from gitlab

Looped for multiple agencies?

Want: List of taxcodes and the municipality they are in

agency_names <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT agency_num, agency_name, minor_type
  FROM agency_info
   WHERE agency_name LIKE '%BRIDGEVIEW%'"
)
# doesn't include schools or items that don't have "Bridgeview in their names
agency_names

muni_agency_nums<- agency_names %>% 
  filter(minor_type %in% c("MUNI", "TOWNSHIP"))



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

agency_lookups <- lookup_agency(2021, muni_tax_codes$tax_code_num) 
options(scipen=999)
agency_lookups

for (i in 1:length(muni_names)) {

  sql_q <- paste0("SELECT '", names(muni_names)[i], "' AS customer, sum(value) AS value FROM db WHERE id IN (", paste(customers_id[[i]], collapse = ", "), ")")

}

lookup_agency() function

agency_lookups <- lookup_agency(2021, muni_tax_codes$tax_code_num) 
options(scipen=999)

agency_lookups

agency_lookups %>%
  filter(agency_major_type %in% c("MUNICIPALITY/TOWNSHIP", "SCHOOL")) %>%
  #  agency_minor_type %in% c("MUNI", "TOWNSHIP") ) %>%
           group_by(agency_name) %>%
           summarize(agency_total_eav = first(agency_total_eav),
                     agency_total_ext = first(agency_total_ext)) %>%
           arrange(-agency_total_eav)

Notes: the lookup_agency() command uses the agency$agency_total_ext variable as the total extension. So for Levies for agencies, use the total_ext variable.

Gitlab method for using lots of items in the WHERE clause for large lookups.

This lookup uses a temp table since it’s faster than putting lots ofvalues into the WHERE clause for large lookups

dt_idx <- data.table::CJ(“year” = year, “pin” = pin, unique = TRUE) DBI::dbWriteTable( conn = conn, name = “lookup_pin”, value = dt_idx, overwrite = TRUE, temporary = TRUE )

game plan:

Get all pins within each MUNI or TOWNSHIP agency. USE PiNS to get all TAXCODES within the MUNI or agency

Then use the taxcodes to lookup_agency()