incentive_table_calculations.qmd

---
title: "Summary Table Calculations - All stats"
format: 
  html:
    code-fold: true
    toc: true
    toc-location: left
    tbl-cap-location: margin
    fig-cap-location: margin
    df-print: paged
---

# Data Preparation

```{r setup, warning = FALSE, output = FALSE}

library(tidyverse)
library(DT)        # for interactive html tables on website
library(flextable) # best for exporting to word or PDF files.


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

set_flextable_defaults(theme_fun = theme_vanilla, 
                       padding = 2,
                       line_spacing = 1,
                       big.mark = ",",
                       )

options(DT.options = list())

FitFlextableToPage <- function(ft, pgwidth = 6){

  ft_out <- ft %>% autofit()

  ft_out <- width(ft_out, width = dim(ft_out)$widths*pgwidth /(flextable_dim(ft_out)$widths))
  return(ft_out)
}
```

```{r message = FALSE}
# all pins for 2022
ptax_pins <- read_csv("Output/Dont_Upload/0_joined_PIN_data_2022.csv") %>%
  mutate(class = as.numeric(class)) %>%         # Allows for joining later
    select(-c(propclass_1dig:av.y))



# Workaround for identifying more project IDs. 
# Used Appeal ID to create unique identifier to group PINs.
bor <- read_csv("Output/borappeals.csv") %>%
  mutate(project_appellant = paste(project_id, sep = "-", appellant))

# Cleaned PIN-Project list after cleaning the commercial valuatoin dataset found online. 
# Another temporary work-around until we (maybe) have full keypin list:
proj_xwalk <- read_csv("Output/all_keypins.csv")               
# all commercial valuation properties but made with not-quite-clean data from commercial valuation dataset on Cook County Data Portal (which was made from combining the Methodology worksheets) 
# Values are also only the FIRST PASS assessments and do not include appeals or changes in values

# Join project IDs to PINs:
ptax_pins <- ptax_pins %>% left_join(proj_xwalk)

#  create tc_muninames from helper file:
source("scripts/helper_tc_muninames_2022.R")
tc_muninames <- tc_muninames %>%  select(-year)

# add muni names by joining tax code info:
ptax_pins <- ptax_pins %>% 
  mutate(tax_code_num = as.character(tax_code_num)) %>%
  left_join(tc_muninames)



# original class_dict variables already in 0_joined data
# but I do want the new-ish variables I created to be brought in:
class_dict <- read_csv("./Necessary_Files/class_dict_expanded.csv") %>%
  select(class_code, comparable_props, Alea_cat, incent_prop)

ptax_pins <- ptax_pins %>% 
  left_join(class_dict, by =  c("class" = "class_code")) %>%
  mutate(clean_name = ifelse(is.na(clean_name), "Unincorporated", clean_name)) 

# BOR data source shortfall: We only have the data if they appeal!

bor_pins <- bor %>% 
  group_by(pin) %>% 
  arrange(desc(tax_year)) %>%
  summarize(pin = first(pin),              # grabs first occurrence of unique PIN
            class_bor = list(unique(class)),
            appellant = first(appellant),
            project_id = first(project_id), 
            timesappealed = n() ) %>%
  mutate(proj_appellant = paste(project_id, "-", appellant))

ptax_pins <- ptax_pins %>% left_join(bor_pins, by = "pin")

# now do it the other way and compare
# MVH: I THINK THIS NEEDS SOME ELABORATION

ptax_pins <- ptax_pins %>% 
  mutate( both_ids = project_id,
          both_ids = ifelse(is.na(both_ids), keypin, both_ids),
          both_ids = ifelse(is.na(both_ids) & between(class, 300, 899), pin, both_ids))

```

<!--- Don't use tax_amount_exe for calculations below because it is the "naive" calculation of the current tax rate * exempt eav discussed in the exemptions research. --->

```{r}
eq2022 <- 2.9237 #example of eq factor proliferation


commercial_classes <- c(401:435, 490, 491, 492, 496:499,
                        500:535,590, 591, 592, 597:599, 
                        700:799,
                        800:835, 891, 892, 897, 899)  

industrial_classes <- c(480:489,493, 
                        550:589, 593,
                        600:699,
                        850:890, 893
                        )

ptax_pins <- ptax_pins %>% 
  mutate(class_group = str_sub(class, 1,1),
         class_group = case_when(
          (class_group == 5 & class %in% commercial_classes) ~ "5A",
          (class_group == 5 & class %in% industrial_classes) ~ "5B",
          class_group == 7 &  class < 742 ~ "7A",
          class_group == 7 &  class >= 742 ~ "7B",
          (class_group == 8 & class %in% commercial_classes ) ~ "8A",
          (class_group == 8 & class %in% industrial_classes ) ~ "8B",
          TRUE ~ as.character(class_group))) %>%
  mutate(
    # taxing district revenue = taxable eav * tax rate so rearrange the formula:
    taxed_eav = final_tax_to_dist / tax_code_rate*100,
    total_value_eav = (final_tax_to_dist + final_tax_to_tif)/ tax_code_rate * 100 + all_exemptions + abatements,
    
    taxed_av =  taxed_eav / eq2022, # current value that taxing agencies can tax for their levies
    
    ## taxable AV = equalized assessed value net TIF increments, gross exemptions. 
    ## Used for calculating untaxable value further below
    taxable_av = (final_tax_to_dist / tax_code_rate *100 + all_exemptions + abatements)/ eq2022, 
    
    ## FMV * assessment rate = AV
    taxed_fmv = taxed_av / loa, 

      ## untaxable value = exempt EAV from abatements and exemptions
    untaxable_value_eav = all_exemptions + abatements + 
      
      ## TIF increment EAV above frozen EAV, which becomes TIF revenue
      (final_tax_to_tif /  tax_code_rate*100) +
      
      ## difference between 25% and reduced level of assessment for incentive class properties. Excludes TIF increment when calculating the difference! 
      ifelse(between(class, 600, 900), 
             (taxable_av - taxed_av)*eq2022, 0),
    
    #  manually adjust untaxable value of class 239 properties
    untaxable_value_eav = ifelse(class == 239, 
                                 equalized_av-taxed_eav, untaxable_value_eav), 
    
    untaxable_value_av = untaxable_value_eav / eq2022,
    untaxable_value_fmv = untaxable_value_av / loa,
    exempt_eav_inTIF = ifelse(in_tif == 1, 
                              all_exemptions, 0),
    exempt_eav= all_exemptions + abatements,
    exempt_fmv = exempt_eav / eq2022 / loa, 
    
    fmv_inTIF = ifelse(in_tif==1, 
                       av/loa, 0),
    fmv_tif_increment = ifelse(final_tax_to_tif > 0, 
                               ((final_tax_to_tif / (tax_code_rate/100)) / eq2022 ) / loa, 0),
    fmv_incents_inTIF = ifelse(between(class, 600, 900) & in_tif == 1, 
                               fmv, 0),
    fmv_incents_tif_increment = ifelse(between(class, 600, 900) & final_tax_to_tif > 0 , 
                               ((final_tax_to_tif / (tax_code_rate/100)) / eq2022 ) / loa, 0),

 
    eav_incents_inTIF = fmv * loa * eq2022
    ) %>%
  select(tax_code, class, pin, taxed_fmv,
         untaxable_value_fmv, fmv_inTIF, fmv_tif_increment, fmv, total_billed, final_tax_to_dist, final_tax_to_tif, tax_code_rate, eav, equalized_av, av, everything())


```

> Total Value should equal Current Taxable Value + non-Taxable Value where non-Taxable Value = Value in TIF Increment + Reduced Value from Policy Choices where Reduced Value = Tax Exempt Value from Homeowners exemptions or abatements + Reduced Taxable Value from lower levels of assessments due to incentive classifications.

$$\mbox{Total Value = Taxed Value + Untaxable Value}$$

*where*

$$\mbox{Untaxable Value = TIF Increment + Exemptions + Abatements    
+ Reduced Taxable Value from Lower Incentive Class Assessment Ratios}$$

*where*

$$\mbox{Reduced Taxable Value from Incentive Classification Levels of Assessments}$$
$$\mbox{which then equals }  {0.25 \ast EAV - \approx0.10 \ast EAV}$$


```{r include = FALSE, eval=FALSE}
#ptax_pins %>% filter(equalized_av - (taxed_eav + untaxable_value_eav) > 100)

#ptax_pins %>% filter(equalized_av - eav > 10) 





## What is class 239? That is the class that is consistently not equaling 0 in the test above.

### 239 is some form of subsidized farm land. Using the assessment ratio to back out the value does not work. Could add something like: `untaxable_value = ifelse(class == 239, equalized_av-taxed_value, untaxable_value)` - COMPLETED AWM

## "The Farmland Assessment Law establishes capitalized net income as the basis for the EAV of farmland. Each year, thenet income is determined for each PI of cropland. The net income is then capitalized by the five-year Federal Land Bank rate to determine an agricultural economic value (AEV) for each PI. The AEV for each PI is then multiplied by 33 1/3 percent (.3333), the product of which is the EAV. A listing of the 2024 EAVs of cropland by PI is given in Table 1. By law, the EAV of permanent pasture should be at one-third and the EAV of other farmland should be at one-sixth of these values."

ptax_pins %>% filter(class == 239) %>% mutate(difference = round(equalized_av - (taxed_eav + untaxable_value_eav)), .before = taxed_fmv ) %>% arrange(desc(difference)) %>% head()

```

```{r include = FALSE}
# Are there any properties where the taxed_eav is greater than the original eav value? Nope! yay.
ptax_pins %>% filter(equalized_av - taxed_eav < -1)
```

```{r eval=FALSE, include=FALSE}
stopifnot(
  (ptax_pins$taxed_eav - ptax_pins$equalized_av <= 10)
)

```

# Cook County Total Value

> Numbers will be slightly different than Sankey graph due to using more accurate levels of assessment when calculating the Fair Market Value of properties. There is a typo in the sankey graphs: says 665 billion for cook county and it should be 606 billion.

$$
\mbox{AV = Fair Market Value * Level of Assessment}
$$

$$
\mbox{Tax Rate} = \frac{\mbox{Amount Levied by Taxing Districts}}{\mbox{Taxable Value}}
$$

> Taxed Value refers to what taxing agencies did tax to pay for their levies. We use the portion of the tax bill that does NOT go to TIFs to calculate the portion of the composite levy paid by each PIN and then sum up from there.

$$
\mbox{Final Tax to District} = {\mbox{Tax Code Rate}}*{\mbox{Taxable Value of PIN}}
$$

$$\mbox{Fair Market Value} = {\frac{\mbox{final tax to dist + final tax to TIF}}{\mbox{tax code rate}} + \mbox{Exemptions + Abatements}}$$

```{r ptax2022-codebook, eval=FALSE}
library(dataReporter)
check(ptax_pins$fmv)  # Individual check of events
check(ptax_pins$both_ids)  # Individual check of events
reporter_summary <- summarize(ptax_pins)

check(ptax_pins) # Check all variables at once


ptax_pins <- ptax_pins  %>% 
  mutate(across(c(has_HO_exemp:has_AB_exemp, in_tif, year.y, zero_bill, Triad, Township, needs_keypin, incent_prop, Alea_cat), as_factor)) 


codebook_data <- ptax_pins %>% 
  select(tax_code, class, fmv, taxed_fmv, has_HO_exemp, has_SF_exemp, has_AB_exemp, has_FR_exemp, has_LTHO_exemp, in_tif, year.y, zero_bill, Triad, Township, needs_keypin, incent_prop, Alea_cat) 

 codebook_data %>% 
  makeDataReport(replace=TRUE )
```

```{r}
#| label: tbl-cooktotals
#| tbl-cap: "**FMV of PINs in Cook County**       Taxed FMV represents the property value that was actually taxed by local taxing jurisdictions and is equal to the amount levied. We use the the portion of an individuals tax bill that does NOT go to a TIF to calculate the composite levy for taxing jursidictions."


table_cook <- ptax_pins %>%
  summarize(
    cty_PC = n(),
    cty_PC_industrial = sum(ifelse(class %in% industrial_classes, 1, 0), na.rm = TRUE),
    cty_PC_commercial = sum(ifelse(class %in% commercial_classes, 1, 0), na.rm = TRUE),
    cty_PC_com_incent = sum(ifelse(class %in% commercial_classes & incent_prop == "Incentive", 1, 0), na.rm = TRUE),
    cty_PC_com_incent_inTIF = sum(ifelse(class %in% commercial_classes & incent_prop == "Incentive" & in_tif == 1, 1, 0), na.rm = TRUE),
    cty_PC_ind_incent = sum(ifelse(class %in% industrial_classes & incent_prop == "Incentive", 1, 0), na.rm = TRUE),
    cty_PC_ind_incent_inTIF = sum(ifelse(class %in% industrial_classes & incent_prop == "Incentive" & in_tif == 1, 1, 0), na.rm = TRUE),
    
    cty_PC_incent = sum(ifelse(incent_prop == "Incentive", 1, 0)),
    
    cty_PC_comandind =  sum(ifelse((class %in% commercial_classes | class %in% industrial_classes), 1, 0)),
    
    cty_PC_comandind_inTIF =  sum(ifelse((class %in% commercial_classes | class %in% industrial_classes) & in_tif == 1, 1, 0)),
    cty_PC_incent_inTIF =  sum(ifelse(between(class, 600, 900) & in_tif == 1, 1, 0)),
    
    cty_projects = n_distinct(both_ids), # mostly for industrial and commercial properties. Purposely not assigning keypins to residential properties. 
    
    cty_fmv_incentive = sum(ifelse(between(class, 600, 900), fmv, 0), na.rm = TRUE),
    cty_fmv_taxed =  sum(taxed_fmv, na.rm=TRUE),
    cty_fmv_incent_inTIF = sum(ifelse(between(class, 600, 900) & in_tif == 1, fmv, 0), na.rm = TRUE),
    cty_fmv_inTIF = sum(fmv_inTIF, na.rm=TRUE),
    cty_fmv_incents_tif_increment = sum(fmv_incents_tif_increment, na.rm=TRUE),
    cty_fmv_tif_increment = sum(fmv_tif_increment, na.rm=TRUE),
    
    cty_fmv_untaxable_value = sum(untaxable_value_fmv , na.rm=TRUE),
    cty_fmv = sum(fmv, na.rm=TRUE),
    cty_fmv_exemptions = sum(all_exemptions/eq2022/loa, na.rm=TRUE),
    cty_fmv_abatements = sum((abatements/eq2022)/loa, na.rm=TRUE),
    cty_fmv_residential = sum(ifelse(class %in% c(200:399), fmv, 0), na.rm = TRUE),
    cty_fmv_industrial = sum(ifelse(class %in% industrial_classes, fmv, 0), na.rm = TRUE),
    cty_fmv_indwithincent = sum(ifelse(class %in% industrial_classes & incent_prop == "Incentive", fmv, 0), na.rm = TRUE),
    cty_fmv_ind_incent_inTIF= sum(ifelse(class %in% industrial_classes & incent_prop == "Incentive" & in_tif == 1, fmv, 0), na.rm = TRUE),
    
    cty_fmv_commercial = sum(ifelse(class %in% commercial_classes, fmv, 0), na.rm = TRUE),
    cty_fmv_comwithincent = sum(ifelse(class %in% commercial_classes & incent_prop == "Incentive", fmv, 0), na.rm = TRUE),
    cty_fmv_com_incent_inTIF= sum(ifelse(class %in% commercial_classes & incent_prop == "Incentive" & in_tif == 1, fmv, 0), na.rm = TRUE),
    
    cty_fmv_comandind = sum(ifelse(class %in% c(commercial_classes, industrial_classes) , fmv, 0), na.rm = TRUE),
    
    
    cty_levy = sum(final_tax_to_dist),
    cty_current_rate_avg = mean(tax_code_rate),
    cty_avg_C2_bill_noexe = mean(ifelse(between(class,200,299) & all_exemptions == 0, (final_tax_to_dist+ final_tax_to_tif), NA), na.rm=TRUE),
    cty_avg_C2_bill_withexe = mean(ifelse(between(class,200,299) & all_exemptions > 0, (final_tax_to_dist+ final_tax_to_tif), NA), na.rm=TRUE),
    cty_av_taxed = sum(taxed_av, na.rm = TRUE),
    cty_untaxable_value_av = sum(untaxable_value_av, na.rm=TRUE),
    cty_av = sum(av),
    cty_zero_bill = sum(zero_bill, na.rm=TRUE),
  ) %>% 
  mutate(
    cty_pct_fmv_untaxable = cty_fmv_untaxable_value / cty_fmv,
    cty_pct_fmv_taxed = cty_fmv_taxed / cty_fmv,
    cty_pct_fmv_incentinTIF = cty_fmv_incent_inTIF / cty_fmv_incentive,
    cty_pct_fmv_incents_tif_increment = cty_fmv_incents_tif_increment / cty_fmv_incentive,
    
    cty_pct_av_taxed = cty_av_taxed / cty_av,
    cty_pct_av_untaxable = cty_untaxable_value_av/cty_av,
    
    cty_pct_incent_oftotalPC = cty_PC_incent / cty_PC,  # incentive pins / all PINs
    cty_pct_incent_ofcomPC = cty_PC_com_incent / cty_PC_commercial,  # incentive pins / commercial PINs
    cty_pct_incent_ofindPC = cty_PC_ind_incent / cty_PC_industrial,  # incentive pins / commercial PINs
    cty_pct_PC_incent_inTIF = cty_PC_incent_inTIF / cty_PC_incent        # count of incentive classes in TIFs / count of incentive Class PINs
    
  ) %>%  mutate(cty_pct_fmv_both_incent = cty_fmv_comwithincent / cty_fmv_comandind,
         cty_pct_PC_both_incent = cty_PC_incent / cty_PC_comandind,
         cty_pct_PC_both_incent_inTIF = cty_PC_incent_inTIF / cty_PC_incent,
         cty_pct_fmv_both_incent_inTIF = cty_fmv_incent_inTIF / cty_fmv_incentive,
  ) 

table_cook %>% 
  select(cty_PC, cty_fmv_taxed, cty_fmv_untaxable_value, cty_fmv) %>%
  flextable() %>% 
  set_header_labels(cty_PC = 'PINs', cty_projects = "Project IDs", 
                    cty_fmv = 'Total FMV', 
                    cty_fmv_taxed = 'Taxed FMV', cty_fmv_untaxable_value = 'FMV not Taxed\nfor Levy',
                    cty_pct_fmv_untaxable = 'Value not Taxed (%)'
  ) %>%   FitFlextableToPage()
```

```{r add-variable-labels, eval=FALSE, include =FALSE}
library(labelled)

table_cook <- table_cook %>% 
  set_variable_labels(
  cty_PC = 'PINs', 
  cty_projects = "Project IDs", 
  cty_fmv = 'Total FMV', 
  cty_fmv_taxed = 'Taxed FMV', cty_fmv_untaxable_value = 'FMV not Taxed\nfor Levy',
  cty_pct_fmv_untaxable = 'Value not Taxed (%)',
  cty_fmv_comandind = 'Com. & Ind. FMV', 
  #  cty_fmv = 'Total FMV in Cook',
  cty_pct_fmv_both_incent = '% of Com. & Ind. FMV  w/ Incent.',
  cty_pct_fmv_incentinTIF = '% of Com. & Ind. FMV  w/ Incent. in TIF', 
  cty_pct_fmv_incents_tif_increment = '% of Com. & Ind. FMV in TIF Increment',
  
  cty_PC_comandind = 'PIN Count', 
  cty_PC_incent_inTIF = "Incent. PINs in TIF", 
  cty_pct_PC_both_incent = '% of Com. & Ind. PINs w/ Incent.',
  cty_pct_PC_both_incent_inTIF = '% of Incent. PINs in TIF'
  )
```

```{r}
#| label: tbl-cooktotals-comandind
#| tbl-cap: "**Commercial and Industrial PINs in Cook County**   3.2% of industrial and commercial PINs (aka \"revenue producing PINs\") FMV has an incentive classification (4.55% when using PIN counts). Of the PINs that have incentive classification, 41.5% of the FMV is located within a TIF (43.9% when using PIN counts)."

table_cook %>% 
  select(cty_fmv_comandind, #cty_fmv, 
         cty_pct_fmv_both_incent, cty_pct_fmv_incentinTIF,
         cty_pct_PC_both_incent, cty_PC_comandind, cty_PC_incent_inTIF, cty_pct_PC_both_incent_inTIF, cty_pct_fmv_incents_tif_increment) %>%
  flextable() %>% 
    set_header_labels(
                          cty_fmv_comandind = 'Com. & Ind. FMV',
                        #  cty_fmv = 'Total FMV in Cook',
                    cty_pct_fmv_both_incent = '% of Com. & Ind. FMV  w/ Incent.',
                    cty_pct_fmv_incentinTIF = '% of Com. & Ind. FMV  w/ Incent. in TIF',
  cty_pct_fmv_incents_tif_increment = '% of Com. & Ind. FMV in TIF Increment',

                          cty_PC_comandind = 'PIN Count',
                      cty_PC_incent_inTIF = "Incent. PINs in TIF",
                    cty_pct_PC_both_incent = '% of Com. & Ind. PINs w/ Incent.',
                    cty_pct_PC_both_incent_inTIF = '% of Incent. PINs in TIF'
  ) %>%
  FitFlextableToPage()
```

```{r}
#| label: tbl-cooktotals-commercial
#| tbl-cap: "**Commercial PINs in Cook County**  4.1% of commercial PINs FMV has an incentive classification (1.2% when using PIN counts). Of the commercial PINs that have incentive classification, 55.9% of the FMV is located within a TIF (40.5% when using PIN counts). "

table_cook %>% 
  mutate(cty_pct_fmv_com_incent = cty_fmv_comwithincent / cty_fmv_commercial,
         cty_pct_fmv_com_incent_inTIF = cty_fmv_com_incent_inTIF / cty_fmv_comwithincent,
         
         cty_pct_PC_com_incent_inTIF = cty_PC_com_incent_inTIF / cty_PC_com_incent) %>%
  
  select(cty_PC_commercial, cty_PC_com_incent, cty_pct_incent_ofcomPC, cty_pct_PC_com_incent_inTIF,
         cty_fmv_commercial, cty_pct_incent_ofcomPC, cty_pct_fmv_com_incent, cty_pct_fmv_com_incent_inTIF) %>%
  flextable() %>% 
    set_header_labels(cty_fmv_commercial = 'Commercial FMV',
                          cty_PC_commercial = 'Commercial Pin Count', 
                        cty_PC_com_incent = 'Com. PIN Count w/ Incent.',
                    cty_pct_incent_ofcomPC = 'Com. PINs w/ Incent.',
                    cty_pct_fmv_com_incent = '% of Com. FMV  w/ Incent.', 

                    cty_pct_fmv_com_incent_inTIF = '% of Com. FMV  w/ Incent. in TIF', 
                      cty_PC_com_incent_inTIF = "Com. Incent. PINs in TIF", 
                    cty_pct_PC_com_incent = '% of Com. PINs w/ Incent.',
                    cty_pct_PC_com_incent_inTIF = '% of Incent. PINs in TIF'
  ) %>%   FitFlextableToPage()

```

```{r}
#| label: tbl-cooktotals-industrial
#| tbl-cap: "**Industrial PINs in Cook County**  36.7% of industrial PINs FMV has an incentive classification (13.7% when using PIN counts). Of the Industrial PINs that have incentive classification, 35.7% of the FMV is located within a TIF (44% when using PIN counts). "

table_cook %>% 
  mutate(cty_pct_fmv_ind_incent = cty_fmv_indwithincent / cty_fmv_industrial,
       cty_pct_fmv_ind_incent_inTIF = cty_fmv_ind_incent_inTIF / cty_fmv_indwithincent,
       
       cty_pct_PC_ind_incent_inTIF = cty_PC_ind_incent_inTIF / cty_PC_ind_incent) %>%
  
  select(cty_PC_industrial, cty_PC_ind_incent, cty_PC_ind_incent_inTIF, 
         cty_pct_incent_ofindPC, cty_pct_fmv_ind_incent_inTIF, cty_pct_PC_ind_incent_inTIF,
         
         cty_fmv_industrial, cty_pct_fmv_ind_incent,  
  ) %>%
  flextable() %>% 
     set_header_labels(cty_fmv_industrial = 'Industrial FMV',
                          cty_PC_industrial = 'Industrial Pin Count', 
                        cty_PC_ind_incent = 'Ind. PIN Count w/ Incent.',
                    cty_pct_incent_ofindPC = 'Ind. PINs w/ Incent.',
                    cty_pct_fmv_ind_incent = '% of Ind. FMV  w/ Incent.', 

                    cty_pct_fmv_ind_incent_inTIF = '% of Ind. FMV  w/ Incent. in TIF', 
                      cty_PC_ind_incent_inTIF = "Ind. Incent. PINs in TIF", 
                    cty_pct_PC_ind_incent = '% of Com. PINs w/ Incent.',
                    cty_pct_PC_ind_incent_inTIF = '% of Incent. PINs in TIF'
  ) %>% 
  FitFlextableToPage()

```

```{r}
#| label: tbl-untaxableFMV-Cook
#| tbl-cap: "**Untaxable FMV in Cook County.**"

table_cook %>% 
  select(cty_fmv, cty_fmv_tif_increment, cty_fmv_exemptions, cty_fmv_abatements, 
         cty_pct_fmv_untaxable, ) %>%
  flextable() %>% 
  set_header_labels(
                   cty_fmv = 'Total FMV', 
                    cty_fmv_tif_increment = 'TIF Increment FMV' ,
                    cty_fmv_exemptions = 'Exempt Value: Exemptions',
                    cty_fmv_abatements = 'Exempt Value: Abatements',
                    cty_fmv_untaxable_value = 'Value not Taxable \nfor Levy',
                    cty_pct_fmv_untaxable = 'County FMV not Taxed (%)'
) %>%   FitFlextableToPage()
```

```{r}
#| label: tbl-untaxableAV-Cook
#| tbl-cap: "**Untaxable AV in Cook County.**        Taxed AV represents the property value that was actually taxed by local taxing jurisdictions."

table_cook %>% 
  select(cty_av, cty_av_taxed, cty_untaxable_value_av, cty_pct_av_taxed, cty_pct_av_untaxable ) %>%
  flextable() %>% 
  set_header_labels(
                   cty_av = 'Total AV', 
                    cty_av_taxed = 'Taxed AV' ,
                    cty_untaxable_value_av = 'AV Not Taxed',
                    cty_pct_av_taxed = '% Taxed',
                    cty_pct_av_untaxable = '% Not Taxed') %>%
   FitFlextableToPage()
```

```{r}
#| label: tbl-fmv-intif-withincentive-Cook
#| tbl-cap: "**FMV of properties with incentive classifications and TIF increment.**      Value in TIFs, value within the TIF that can be taxed by local taxing jurisdictions, value of properties that have reduced levels of assessments from incentive classifications, and the value that is both in a TIF and has a reduced LOA."

table_cook %>% 
  select(cty_fmv, cty_fmv_inTIF, cty_fmv_tif_increment, 
         cty_fmv_incentive, cty_fmv_incent_inTIF, cty_fmv_incents_tif_increment) %>%
  flextable() %>% 
  set_header_labels(
                    cty_fmv = 'Total FMV', 
                    cty_fmv_inTIF = 'FMV in TIFs',
                    cty_fmv_tif_increment = 'TIF Increment FMV' ,
                    cty_fmv_incentive = "FMV with Incent.Class.", 
                    cty_fmv_incent_inTIF = 'FMV with Incent. Class. in TIFs'
)%>%   FitFlextableToPage()

```

**Taxed value** is the amount of value that was actually taxed in order to pay for taxing agencies levies. It includes frozen EAV within an area + taxable EAV for residential properties net exemptions and abatements. It also includes the equalized assessed value of incentive properties at their current, lower assessment ratios. `final_tax_to_dist` is used to calculate the amount that was collected by local government agencies and then divided by the tax rate to calculate the amount of value that was taxed, or the **taxable equalized assessed value (TEAV)**.

The Taxed Value, when converted to the Fair Market Value (FMV) represents the amount of value that was taxed out of the full FMV available in Cook County.

::: aside
The Fair Market Value (FMV) is also called the "Market Value for Assessment Purposes" and can be calculated from the `av` / `loa`, or the Assessed Value divided by the Level of Assessment. However, the values used for the level of assessment are an approximation for incentive properties since we do not have the PIN level assessment ratios.
:::

## Cook County by Major Class

```{r}
#| label: tbl-cooktotals-majorclass
#| tbl-cap: "**Summed FMV of PINs in Cook County by Property Class.**      Untaxable EAV includes homeowner exemptions for 200 level properties, abatements for other property class types, EAV in the TIF increment, and EAV that has been reduced due to incentive classifications"

cty_MC_table <- ptax_pins %>%
  group_by(class_group) %>%
  summarize(
    cty_MC_PC = n(),
    cty_MC_projects = n_distinct(both_ids), # mostly for industrial and commercial properties
    cty_MC_fmv_incentive = sum(ifelse(between(class, 600, 900), fmv, 0), na.rm = TRUE),
    cty_MC_fmv_taxed =  sum(taxed_fmv, na.rm=TRUE),
    cty_MC_av_taxed  = sum(taxed_av, na.rm=TRUE),
    cty_MC_fmv_incent_inTIF = sum(ifelse(between(class, 600, 900) & in_tif == 1, fmv, 0), na.rm = TRUE),
    cty_MC_fmv_inTIF = sum(fmv_inTIF, na.rm=TRUE),
    cty_MC_fmv_tif_increment = sum(fmv_tif_increment, na.rm=TRUE),
    cty_MC_fmv_untaxable_value = sum(untaxable_value_fmv , na.rm=TRUE),
    cty_MC_fmv = sum(fmv, na.rm=TRUE),
    cty_MC_fmv_exemptions = sum(all_exemptions/eq2022/loa, na.rm=TRUE),
    cty_MC_fmv_abatements = sum(abatements/eq2022/loa, na.rm=TRUE),
    cty_MC_cty_zero_bill = sum(zero_bill, na.rm=TRUE),
    cty_MC_fmv_residential = sum(ifelse(class %in% c(200:399), fmv, 0), na.rm = TRUE),
    cty_MC_fmv_industrial = sum(ifelse(class %in% industrial_classes, fmv, 0), na.rm = TRUE),
    cty_MC_fmv_commercial = sum(ifelse(class %in% commercial_classes, fmv, 0), na.rm = TRUE),
    cty_MC_levy_paid = sum(final_tax_to_dist),
    cty_MC_current_rate_avg = mean(tax_code_rate),
    
    cty_MC_avg_C2_bill_noexe = mean(ifelse(between(class, 200, 299) & all_exemptions == 0, (final_tax_to_dist+ final_tax_to_tif), NA), na.rm=TRUE),
     cty_MC_avg_C2_bill_withexe = mean(ifelse(between(class,200, 299) & all_exemptions > 0, (final_tax_to_dist+ final_tax_to_tif), NA), na.rm=TRUE),
  ) %>%
  mutate( cty_fmv = sum(cty_MC_fmv),
          pct_cty_MC_fmv_untaxable = cty_MC_fmv_untaxable_value / cty_MC_fmv,
          pct_fmv_taxed = cty_MC_fmv_taxed / cty_fmv,
          pct_MC_levy_paid = cty_MC_levy_paid / sum(cty_MC_levy_paid) )


cty_MC_table %>%
  select(-c(cty_MC_avg_C2_bill_noexe, cty_MC_avg_C2_bill_withexe, cty_MC_fmv_residential, cty_MC_fmv_industrial, cty_MC_fmv_commercial, cty_fmv)) %>%
  flextable() %>% 
  set_header_labels(
    class_group = "Property Type", 
    cty_MC_PC = 'PINs',# projects = "Project IDs", 
    cty_MC_fmv_taxed = 'Taxed FMV', 
    cty_MC_fmv_untaxable_value = 'Value not Taxable for Levy', 
    cty_MC_fmv_incentive = "FMV with Incent.Class.", 
    cty_MC_fmv_inTIF = 'FMV in TIFs', 
    cty_MC_fmv_tif_increment = 'TIF Increment FMV',
    pct_cty_MC_fmv_untaxable = 'Value not Taxed (%)', 
    cty_MC_incentive_fmv = 'FMV with Incent. Classification',
    cty_MC_incents_inTIFs = 'FMV with Incent. Class. in TIFs' )

```

# Municipality Level Stats

*Ignore stats for these Municipalities. Simple rounding errors may cause bizarre results for rate changes & other calculations. These municipalities are dropped from summary tables in this website but are included in exported files.*

-   Frankfort has 1 PIN in Cook County
-   East Dundee has 2
-   Homer Glen has 3
-   University Park has 4
-   Oak Brook, Deer Park, Deerfield, & Bensenville each have less than 75 PINs in Cook County, IL

```{r}
munilevel <- ptax_pins %>% 
  group_by(clean_name) %>%
  summarize(
    muni_PC_total = n(),
    muni_PC_residential = sum(ifelse(class %in% c(200:399), 1, 0), na.rm = TRUE),
    muni_PC_industrial  = sum(ifelse(class %in% industrial_classes, 1, 0), na.rm = TRUE),
    muni_PC_commercial = sum(ifelse(class %in% commercial_classes, 1, 0), na.rm = TRUE),
    muni_PC_inTIF = sum(in_tif),
    muni_PC_withincents = sum(ifelse(between(class, 600, 900), 1, 0), na.rm = TRUE),
    muni_PC_incents_inTIFs = sum(ifelse(between(class, 600, 900) & in_tif == 1, 1, 0), na.rm = TRUE),
    muni_PC_claimed_exe = sum(ifelse(all_exemptions > 0, 1, 0)),
  
  # Projects primarily apply to commercial and industrial property.
    muni_projects = n_distinct(both_ids),
    muni_fmv_incentive = sum(ifelse(class >=600 & class <=900, fmv, 0), na.rm = TRUE),
    muni_fmv_taxed = sum(taxed_fmv, na.rm=TRUE),
    muni_fmv_inTIF = sum(fmv_inTIF, na.rm=TRUE),
    muni_fmv_exempt = sum(all_exemptions/eq2022/loa, na.rm=TRUE),
    muni_fmv_abated = sum(abatements/eq2022/loa, na.rm = TRUE),
    muni_fmv_tif_increment = sum(fmv_tif_increment, na.rm=TRUE),
    muni_fmv_abates_inTIF = sum(ifelse(between(class, 600, 900) & in_tif == 1 & abatements >0 , fmv, 0), na.rm = TRUE),
    muni_fmv_incents_inTIF = sum(ifelse(between(class, 600, 900) & in_tif == 1, fmv, 0), na.rm = TRUE),
    muni_fmv_untaxable_value = sum(untaxable_value_fmv , na.rm=TRUE),

    muni_fmv = sum(fmv, na.rm=TRUE), 
    muni_fmv_residential = sum(ifelse(class %in% c(200:399), fmv, 0), na.rm = TRUE),
    muni_fmv_industrial = sum(ifelse(class %in% industrial_classes, fmv, 0), na.rm = TRUE),
    muni_fmv_commercial = sum(ifelse(class %in% commercial_classes, fmv, 0), na.rm = TRUE),
    muni_zero_bill = sum(zero_bill, na.rm=TRUE),
    muni_levy = sum(final_tax_to_dist),
    muni_current_rate_avg = mean(tax_code_rate),
    muni_eav_taxed = sum(taxed_av*eq2022), 

    muni_min_TC_rate = min(tax_code_rate),
    muni_max_TC_rate = max(tax_code_rate),
    muni_avg_C2_bill_noexe = mean(ifelse(between(class,200,299) & all_exemptions == 0, (final_tax_to_dist + final_tax_to_tif), NA), na.rm=TRUE),
    muni_avg_C2_bill_withexe = mean(ifelse(between(class,200,299) & all_exemptions > 0, (final_tax_to_dist+ final_tax_to_tif), NA), na.rm=TRUE),
muni_eav = sum(eav)) %>%

  mutate(
    muni_range_TC_rate = muni_max_TC_rate - muni_min_TC_rate,
    muni_effective_rate =  muni_levy / muni_fmv * 100, 
muni_pct_eav_taxed = muni_levy / muni_eav_taxed,

         pct_fmv_taxed = muni_fmv_taxed / muni_fmv,
         pct_fmv_w_incentclass = muni_fmv_incentive / muni_fmv,
         pct_fmv_inTIF = muni_fmv_inTIF / muni_fmv,
         pct_fmv_in_tif_increment = muni_fmv_tif_increment / muni_fmv,
         pct_fmv_untaxable_value = muni_fmv_untaxable_value / muni_fmv,
pct_fmv_incents_inTIFs = muni_fmv_incents_inTIF / muni_fmv ) %>%
  mutate(across(starts_with("muni_fmv_"), round, digits = 0)) %>%
  
  mutate(across(contains(c("rate", "pct","bill")), round, digits = 3) ) %>%

  ungroup() %>%
  select(clean_name, everything())

```

```{r}
#| label: tbl-muni-levelstats
#| tbl-cap: "Municipality Level Summary Statistics"


munilevel %>% 
filter(!clean_name  %in% c("Frankfort", "Homer Glen",  "Oak Brook", "East Dundee", "University Park",  "Bensenville", "Hinsdale", "Roselle", "Deer Park", "Deerfield"))  %>%
datatable(rownames=FALSE)
```

## Municipality Percentages

```{r}
#| label: tbl-muni-percentages
#| tbl-cap: "Municipality Percentages Statistics"


table_muni_percentages <- munilevel %>%   
filter(!clean_name  %in% c("Frankfort", "Homer Glen",  "Oak Brook", "East Dundee", "University Park",  "Bensenville", "Hinsdale", "Roselle", "Deer Park", "Deerfield"))  %>%
  select(clean_name, starts_with("pct"))

table_muni_percentages%>% 
  datatable(rownames = FALSE) %>%
  formatPercentage(columns = 2:7, digits = 2)
```

# Municipality by Major Class Type

## Muni-major class level percentages

```{r}
ccao_loa <- read_csv("./inputs/ccao_loa.csv") %>% 
  filter(year== 2022) %>% 
  select(-year)

muni_rate <- read_csv("./Output/ptaxsim_muni_level_2006-2022.csv") %>%
  filter(year == 2022) %>%
  left_join(nicknames) %>% 
  select(clean_name, muni_current_rate_avg, muni_pct_eav_taxed,# cur_comp_muni_rate
) %>%
  rename(cur_munilevel_rate = muni_current_rate_avg)
```

```{r create-muni-mc-table}
incentive_majorclasses <- c("6", "7A", "7B", "8A", "8B")

muni_mc_table <- ptax_pins %>%
  left_join(muni_rate) %>%
  group_by(clean_name, 
           class_group) %>%
  summarize(
    classgroup_PC = n(),
    class_group_projects = n_distinct(both_ids), # mostly for industrial and commercial properties
    pins_withincents = sum(ifelse(class_group %in% incentive_majorclasses, 1,0)),
    fmv_incentive = sum(ifelse(class >=600 & class <=900, fmv, 0), na.rm = TRUE),
    fmv_taxed =  sum(taxed_fmv, na.rm=TRUE),
    fmv_incents_inTIFs = sum(ifelse(class >=600 & class <=900 & final_tax_to_tif > 0, fmv, 0), na.rm = TRUE),
    fmv_inTIF = sum(fmv_inTIF, na.rm=TRUE),
    fmv_tif_increment = sum(fmv_tif_increment, na.rm=TRUE),
    fmv_untaxable_value = sum(untaxable_value_fmv , na.rm=TRUE),
    fmv_exemptions = sum(all_exemptions/eq2022/loa, na.rm=TRUE),
    fmv_abatements = sum(exe_abate/eq2022/loa, na.rm=TRUE),
    zero_bill = sum(zero_bill, na.rm=TRUE),
    fmv_residential = sum(ifelse(class %in% c(200:399), fmv, 0), na.rm = TRUE),
    fmv_C2 = sum(ifelse(class %in% c(200:299), fmv, 0), na.rm = TRUE),
    
    fmv_industrial = sum(ifelse(class %in% industrial_classes, fmv, 0), na.rm = TRUE),
    fmv_commercial = sum(ifelse(class %in% commercial_classes, fmv, 0), na.rm = TRUE),
    
    current_rate_avg = mean(tax_code_rate),
    avg_C2_bill_noexe = mean(ifelse(between(class,200,299) & all_exemptions == 0, (final_tax_to_dist + final_tax_to_tif), NA), na.rm=TRUE),
    avg_C2_bill_withexe = mean(ifelse(between(class,200,299) & all_exemptions > 0, (final_tax_to_dist + final_tax_to_tif), NA), na.rm=TRUE),
    av_taxed = sum(taxed_av, na.rm = TRUE),
    untaxable_value_av = sum(untaxable_value_av, na.rm=TRUE),
    av = sum(av),
    eav_taxed = sum(taxed_av*eq2022), 
    eav_untaxable = sum(untaxable_value_eav),
    eav_max = sum(fmv*loa*eq2022),
    fmv = sum(fmv, na.rm=TRUE),
    pins_in_class = n(),
    all_exemptions = sum(all_exemptions),   # in EAV
    abatements = sum(exe_abate),            # in EAV
    eav_incents_inTIFs = sum(ifelse(class >=600 & class <=900 & in_tif == 1, eav, 0), na.rm = TRUE),
    loa = mean((loa*classgroup_PC ) / sum(classgroup_PC), na.rm=TRUE),
    final_tax_to_dist = sum(final_tax_to_dist),
    final_tax_to_tif = sum(final_tax_to_tif),
    eav = sum(eav)) %>%
  mutate(
    new_TEAV_noIncents = ifelse(
      class_group %in% incentive_majorclasses,
      (eav_taxed/loa)*0.25, eav_taxed),
    new_TEAV_noC6 = ifelse(class_group == "6", (eav_taxed/loa)*0.25 , eav_taxed),
    new_TEAV_noC7 = ifelse(class_group %in% c("7B", "7A"), (eav_taxed/loa)*0.25, eav_taxed),
    new_TEAV_noC8 = ifelse(class_group %in% c("8A", "8B"), (eav_taxed/loa)*0.25, eav_taxed),
    
    new_TEAV_vacant_noIncents = ifelse(class_group %in% incentive_majorclasses,
                                       0, eav_taxed),
    
    new_TEAV_noExemps = eav_taxed + all_exemptions, # does not include abatements
    new_TEAV_noAbates = eav_taxed + abatements, # include only abatements, not other exemption types
    
    # amount of EAV from taxing an additional 15% of the AV if incentive properties didn't exist
    forgone_EAV_incent = ifelse(class_group %in% incentive_majorclasses,
                                #incent_prop == "Incentive", 
                                new_TEAV_noIncents - eav_taxed, 0),
    
    # TIF increment above the frozen EAV
    forgone_TIF_EAV = fmv_tif_increment * loa * eq2022,
    
    forgone_EAV_overlap = ifelse(fmv_incents_inTIFs > 0, 
                                 fmv_incents_inTIFs * loa * eq2022, 0) ) %>%
  
  left_join(munilevel) %>%
  
  mutate(pct_PC_proptype = classgroup_PC/muni_PC_total,
         pct_projects = class_group_projects / muni_projects,
         pct_fmv_incentive = fmv_incentive / muni_fmv_incentive,
         pct_fmv_taxed = fmv_taxed / muni_fmv_taxed,
         pct_fmv_inTIF = fmv_inTIF / muni_fmv_inTIF,
         pct_fmv_tif_increment = fmv_tif_increment / muni_fmv_tif_increment,
         pct_fmv_untaxable = fmv_untaxable_value / muni_fmv_untaxable_value,
         pct_fmv = fmv / muni_fmv,
         pct_zero_bill = zero_bill / muni_zero_bill,
         pct_PC_withincents = pins_withincents / muni_PC_withincents,
         pct_fmv_incents_inTIF = fmv_incents_inTIFs / sum(fmv_incents_inTIFs),
         pct_eav_incents_inTIF = eav_incents_inTIFs / sum(eav),
pct_eav_taxed = eav_taxed / muni_eav_taxed
  )
```

```{r eval=FALSE, include=FALSE}
rename(
  'Municipality' = 'clean_name', 
  'Major Class' = 'class_group', 
  'Projects' = 'class_group_projects', 
  'FMV w/ Incent. Class. (%)' = 'pct_fmv_incentive',
  'Class FMV' = 'fmv', 'Taxed FMV' = 'fmv_taxed',  
  'Value not Taxable for Levy' = 'fmv_untaxable_value' , 
  'FMV with Incent.Class.' = 'fmv_incentive',  
  'FMV in TIFs' = 'fmv_inTIF',  
  'TIF Increment FMV' = 'fmv_tif_increment', 
  'Value not Taxed (%)' = 'pct_fmv_untaxable' , 
  'FMV with Incent. Class. in TIFs' = 'pct_fmv_incents_inTIF',
  
  'PIN Count' = 'muni_PC', 
  'Current Composite Rate' = 'muni_current_rate_avg', 
  'Rate if No Exemptions' = 'muni_rate_noExe', 
  'Rate if No Incentive Classes' = 'muni_rate_noInc', 
  'Rate if No Inc. or Exe.' = 'muni_rate_neither',
  'Rate if No TIF Increments' = 'muni_rate_noTIFs',
  'Rate: No Abatements' = 'muni_rate_noAbate',
  'Rate: Incent. Prop. Lose All Value' = 'muni_rate_vacant',
  'Lowest Rate Possible' = 'muni_rate_lowest'
  )
```

```{r}
#| label: tbl-municlassgroup-totals
#| tbl-cap: "Major class types in Municipalities"


muni_mc_table %>% 
  select(-c(starts_with("muni")) ) %>%
  
  datatable(rownames = FALSE,
            colnames = c('Municipality' = 'clean_name', 'Major Class' = 'class_group', 
                         'Projects' = 'class_group_projects', 
                         'FMV w/ Incent. Class. (%)' = 'pct_fmv_incentive',
                         
                         'Class FMV' = 'fmv', 'Taxed FMV' = 'fmv_taxed',  
                         'Value not Taxable for Levy' = 'fmv_untaxable_value' , 'FMV with Incent.Class.' = 'fmv_incentive',  
                         'FMV in TIFs' = 'fmv_inTIF',  'TIF Increment FMV' = 'fmv_tif_increment', 
                         'Value not Taxed (%)' = 'pct_fmv_untaxable' , 'FMV with Incent. Class. in TIFs' = 'pct_fmv_incents_inTIF')) %>%
  
  formatPercentage( c(  'Value not Taxed (%)', 'FMV w/ Incent. Class. (%)'  ), digits = 1) %>%
  formatCurrency(c('Class FMV', 'Taxed FMV', 'Value not Taxable for Levy', 'TIF Increment FMV', 'FMV with Incent.Class.', 'FMV with Incent. Class. in TIFs', 'FMV in TIFs'), digits = 0)
```

```{r}
table_muni_mc_percentages <- muni_mc_table %>%
  select(clean_name, class_group, starts_with("pct"))

table_muni_mc_percentages %>% 
  datatable(rownames = FALSE) %>%
  formatPercentage(columns = 3:14, digits = 2)
```

# Tax Rates

```{r}
alt_rates <- muni_mc_table %>% 
ungroup() %>%
  left_join(muni_rate) %>%
  group_by(clean_name) %>% 
  summarise(
    muni_PC = sum(classgroup_PC),
    muni_levy = sum(final_tax_to_dist),
    muni_eav_taxed = sum(eav_taxed),

    muni_exemptions = sum(all_exemptions), # all exe_ variables except exe_abate
    muni_abatements = sum(abatements),
    muni_forgoneTIF = sum(forgone_TIF_EAV),
    muni_forgone_EAV_incent = sum(forgone_EAV_incent),
    muni_TEAV_noC6 = sum(new_TEAV_noC6),
    muni_TEAV_noC7 = sum(new_TEAV_noC7),
    muni_TEAV_noC8 = sum(new_TEAV_noC8),
    muni_current_TEAV = sum(eav_taxed),
    muni_TEAV_noExe = sum(new_TEAV_noExemps),
    muni_TEAV_noAbate= sum(new_TEAV_noAbates),
    muni_TEAV_noInc = sum(new_TEAV_noIncents),
    muni_TEAV_vacant_noInc = sum(new_TEAV_vacant_noIncents),
    muni_forgone_eav_overlap_incents_inTIFs = sum(forgone_EAV_overlap),
    cur_munilevel_rate = mean(muni_current_rate_avg), # Was same value for each municpality. Took mean to keep variable in dataframe. (Could have included it in group() command) 
    muni_eav_max = sum(eav_max)  
    ) %>%
  mutate(muni_current_rate = cur_munilevel_rate) %>%
  mutate(
    # Absolute maximum TEAV: No Exemptions, no abatements, no TIFS, no Incentive properties
    # Commercial and industrial assessed at 25%
    muni_TEAV_max = muni_current_TEAV + muni_exemptions + muni_abatements + muni_forgoneTIF + muni_forgone_EAV_incent,

    # no exemptions or incentive classifications:
    muni_TEAV_neither = muni_current_TEAV + muni_exemptions + muni_forgone_EAV_incent,
    
    muni_rate_noExe = muni_levy / muni_TEAV_noExe * 100,
    muni_rate_noAbate = muni_levy / muni_TEAV_noAbate * 100,
    muni_rate_noInc = muni_levy / muni_TEAV_noInc * 100,
    muni_rate_neither = muni_levy / muni_TEAV_neither * 100, 
    muni_rate_noTIFs = muni_levy / (muni_current_TEAV + muni_forgoneTIF) * 100,
    muni_rate_vacant = muni_levy / muni_TEAV_vacant_noInc* 100,
    muni_rate_lowest = muni_levy / muni_TEAV_max * 100,
    muni_rate_noC6 = muni_levy / muni_TEAV_noC6 * 100,
    muni_rate_noC7 = muni_levy / muni_TEAV_noC7 * 100,
    muni_rate_noC8 = muni_levy / muni_TEAV_noC8 * 100,
    muni_rate_avgerror = muni_levy / muni_eav_taxed * 100
  ) 



alt_rates <- alt_rates %>% 
  mutate(change_error_dif = muni_rate_avgerror - muni_current_rate,
    change_noExe =  muni_rate_noExe - muni_current_rate,
         change_noInc = muni_rate_noInc - muni_current_rate,
         change_noC6 = muni_rate_noC6 - muni_current_rate,
         change_noC7 = muni_rate_noC7 - muni_current_rate,
         change_noC8 = muni_rate_noC8 - muni_current_rate,
         
         change_noTIFs = muni_rate_noTIFs - muni_current_rate,
         change_neither =  muni_rate_neither - muni_current_rate,
         change_max = muni_rate_lowest - muni_current_rate,
         change_noAbate = muni_rate_noAbate- muni_current_rate,
         change_vacants = muni_rate_vacant - muni_current_rate)  #%>%
#mutate(across(.cols = change_noExe:change_vacants), .func = .x-change_error_dif)

# roundfuntion <- function(x){
# ifelse(x<0.1 & x> -0.1, 0, return(x))
# }
# 
# alt_rates <- alt_rates %>% mutate(across(change_noExe:change_vacant, ifelse, (x<0.1 & x> -0.1, 0, return(x)) ))


  ## drop munis with only a few PINs in them. They get weird results from rounding issues (I think)
  # Frankfort, Homer Glenn, Oak Brook are below 1% in Cook County
  # East Dundee, University Park, Bensenville, Hinsdale, Roeselle, Deerfield are below 15% in Cook County
alt_rates <- alt_rates %>%   
  filter(!clean_name  %in% c("Frankfort", "Homer Glen",  "Oak Brook", "East Dundee", "University Park",  "Bensenville", "Hinsdale", "Roselle", "Deer Park", "Deerfield")) 

```

```{r}
tbl1 <- alt_rates %>% 
  select(clean_name, muni_PC, muni_current_rate, muni_rate_noExe, muni_rate_noInc, muni_rate_neither,  muni_rate_noTIFs, muni_rate_noAbate,
                             muni_rate_lowest, muni_rate_vacant) %>% 
  distinct() %>% 
  mutate(across(muni_current_rate:muni_rate_vacant, round, digits = 2)) %>% 
  as.data.frame()

datatable(tbl1,
          rownames= FALSE,
          colnames = c('Municipality' = 'clean_name', 
                       'PIN Count' = 'muni_PC', 
'Current Composite Rate' = 'muni_current_rate', 
                       'Rate if No Exemptions' = 'muni_rate_noExe', 
                       'Rate if No Incentive Classes' = 'muni_rate_noInc', 
                         'Rate if No Inc. or Exe.' = 'muni_rate_neither',
                       'Rate if No TIF Increments' = 'muni_rate_noTIFs',
                        'Rate: No Abatements' = 'muni_rate_noAbate',
                       'Rate: Incent. Prop. Lose All Value' = 'muni_rate_vacant',
                      'Lowest Rate Possible' = 'muni_rate_lowest'
                      ))
```

```{r include = FALSE, eval=FALSE}

tbl2 <- alt_rates %>% 
  select(clean_name, 
         change_noExe, change_noInc, 
         change_noAbate, change_noTIFs, 
         change_neither, change_max, 
         change_vacants) %>%
  distinct() %>%
  mutate(across(change_noExe:change_vacants, round, digits = 2)) %>% 
  as.data.frame()


datatable(tbl2,
          rownames= FALSE,
          colnames = c('Municipality' = 'clean_name',   
                       'Rate Change from Taxing GHE Exempt EAV' = 'change_noExe', 
                       'Rate Change: No Abatements' = 'change_noAbate',
                       'Rate Change from Additional Commerc & Indust. Value' = 'change_noInc', 
                       'Rate Change: Incent. Prop. Lose All Value' = 'change_vacants',
                       'Rate if No Incentive Classes & No Exemptions' = 'change_neither',
                       'Rate if No TIF Increments' = 'change_noTIFs',
                       'Maxiumum Rate Change' = 'change_max'
                       
                    ))
```

```{r}
#| label: tbl-adjustedratechange
#| 

# tbl3 <- alt_rates %>%
#   select(clean_name, 
#          change_noExe, change_noInc, 
#          change_noC6, change_noC7, change_noC8, 
#          change_noAbate,
#          change_noTIFs, 
#          change_neither, 
#          change_max, change_vacants, 
#          change_error_dif) %>%
# #mutate(across(change_noExe:change_noTIFs, mean, na.rm=TRUE) ) %>%
#   distinct() %>%
#   mutate(across(change_noExe:change_error_dif, round, digits = 2)) %>% as.data.frame()
# 
# datatable(tbl3,
#           rownames= FALSE,
#           colnames = c('Municipality' = 'clean_name',   
#                        'Rate Change from Taxing GHE Exempt EAV' = 'change_noExe', 
#                        'Rate Change: No Abatements' = 'change_noAbate',
#                        'Rate Change from Additional Commerc & Indust. Value' = 'change_noInc', 
#                        'Rate Change: Incent. Prop. Lose All Value' = 'change_vacants',
#                        'Rate Change: No C6 AV Reduction' = 'change_noC6',
#                        'Rate Change: No C7 AV Reduction' = 'change_noC7',
#                        'Rate Change: No C8 AV Reduction' = 'change_noC8',
# 
#                        'Rate if No Incentive Classes & No Exemptions' = 'change_neither',
#                        'Rate if No TIF Increments' = 'change_noTIFs',
#                        'Maxiumum Rate Change' = 'change_max',
#                        'Error from Tax Code Rate Aggregation' = 'change_error_dif'
#                        
#                     ))
# 



table4_adjusted <- alt_rates %>% 
mutate(across(change_noExe:change_vacants, ~ . - change_error_dif))  %>%
mutate(across(starts_with("change"), round, digits = 2)) %>% 

select(clean_name, starts_with("change")) %>%
select(-change_error_dif)

datatable(table4_adjusted,
          rownames= FALSE,
          colnames = c('Municipality' = 'clean_name',   
                       'Rate Change from Taxing GHE Exempt EAV' = 'change_noExe', 
                       'Rate Change: No Abatements' = 'change_noAbate',
                       'Rate Change from Additional Commerc & Indust. Value' = 'change_noInc', 
                       'Rate Change: Incent. Prop. Lose All Value' = 'change_vacants',
                       'Rate Change: No C6 AV Reduction' = 'change_noC6',
                       'Rate Change: No C7 AV Reduction' = 'change_noC7',
                       'Rate Change: No C8 AV Reduction' = 'change_noC8',

                       'Rate if No Incentive Classes & No Exemptions' = 'change_neither',
                       'Rate if No TIF Increments' = 'change_noTIFs',
                       'Maxiumum Rate Change' = 'change_max')
)


```

> CODE CHANGES July 10th 2024: No longer filter by commercial and incentive property types because it was dropping PINs that had changed property classes if they were not commercial or incentive. Not what was intended! - AWM

# Yearly Trends

The file `comm_ind_inmunis_timeseries_2006to2022.csv` contains all PINs that had an incentive property class for at least 1 year. It includes all observations for a property during the years that it existed, even if it is not an incentive class property in that year.

## 2006 to 2022 Growth

```{r uses-comind-file}
ptax_pins <- read_csv("./Output/comm_ind_inmunis_timeseries_2006to2022.csv") %>% 
  mutate(class = as.numeric(class))

ptax_pins <- ptax_pins %>% 
  group_by(pin) %>%
  mutate(incentive_years = sum(incent_prop == "Incentive"),
         landuse_change = ifelse(
           sum(land_use == "Commercial") == 17, "Always Commercial",
           ifelse(sum(land_use == "Industrial") == 17, "Always Industrial",
                  "Changes Land Use"
                  ))) %>%
  ungroup() %>%
  mutate(incent_change = case_when(
    incentive_years == 17 ~ "Always Incentive",
    incentive_years == 0 ~ "Never Incentive",
    TRUE ~ "Changes Sometime")
  )

```

```{r}
table(ptax_pins$incent_change)
table(ptax_pins$incentive_years)
table(ptax_pins$landuse_change)

ptax_pins %>% filter(year == 2022) %>% reframe(count = n(), .by = landuse_change)

ptax_pins %>% filter(year == 2022) %>% reframe(count = n(), .by = incentive_years) %>% arrange(incentive_years) %>% filter(incentive_years > 0) %>% ggplot() + geom_col(aes(x=incentive_years, y = count)) + scale_x_continuous(limits = c(0,18) )

ptax_pins %>% filter(year == 2022) %>% reframe(count = n(), .by = incent_change) 

table(ptax_pins$land_use)
```

```{r}
#| label: fig-FMVgraphs
#| fig-cap: ""

ptax_pins %>%
  group_by(year, incent_change) %>%
  summarize(fmv = sum(fmv),
            av = sum(av_clerk)) %>% 
  ggplot() + 
  geom_line(aes(x=year, y=fmv, color = incent_change))+
  theme_classic() + 
  labs(title = "FMV Over Time",
       subtitle = "Includes all years of observations for PINs that had an Incentive Class for at least 1 year", caption = "Uses com_ind_PINS... file.") + scale_y_continuous(labels = scales::dollar)


ptax_pins %>%
  group_by(year, landuse_change) %>%
  summarize(fmv = sum(fmv),
            av = sum(av_clerk)) %>% 
  ggplot() + 
  geom_line(aes(x=year, y=fmv, color = landuse_change))+
  theme_classic() + 
  labs(title = "FMV Over Time",
       subtitle = "Includes all years of observations for PINs that had an Incentive Class for at least 1 year", caption = "Uses com_ind_PINS... file.") + scale_y_continuous(labels = scales::dollar)

ptax_pins %>%
  group_by(year) %>%
  summarize(fmv = sum(fmv),
            av = sum(av_clerk)) %>% 
  ggplot() + 
  geom_line(aes(x=year, y=fmv#, color = Alea_cat
  ))+
  geom_line(aes(x=year, y=av), color = "red") + 
  theme_classic() + 
  labs(title = "FMV Over Time",
       subtitle = "Includes all years of obsservations for PINs that had an Incentive Class for at least 1 year", caption = "Uses com_ind_PINS... file.  Red line is AV")

ptax_pins %>%
  group_by(year, land_use) %>%
  summarize(fmv = sum(fmv),
            av = sum(av_clerk)) %>% 
  ggplot() + 
  geom_line(aes(x=year, y=fmv, color = land_use))+
  theme_classic() + 
  labs(title = "FMV Over Time by Land Use", subtitle = "Uses com_ind_PINS... file.")


## Fixed NAs in incent_props
# ptax_pins %>% filter(is.na(incent_prop))
# 16261070080000 was class 319 in 2007 and 2008 and is currently 592. 
# 13223190070000 was class 319 in 2008 and is 318 currently
# 09244100010000 doesn't exist any more
# 19271000680000 was class 180 and is now 190. 

ptax_pins %>% 
  group_by(year, incent_prop) %>%
  summarize(fmv = sum(fmv),
            av_clerk = sum(av_clerk)) %>%
  ggplot() +
  geom_line(aes(x=year, y = fmv, color = incent_prop)) + theme_classic()  + 
  labs(title = "FMV from Incentive and non-Incentive Class Properties", subtitle = "Uses com_ind_PINS... file.")




```

```{r}
#| label: tbl-growthcalculations5
#| tbl-cap: "**Growth from 2006 to 2022.** Removes PINs if they did not exist during all years in the data. Uses com_ind_PINS... file. Grouped by land_use, incent_prop, year. "

df_2006 <- ptax_pins %>%
  group_by(pin) %>%
  mutate(years_existed = n()) %>%
  ungroup() %>%
  filter(years_existed ==17 ) %>%
  mutate(fmv = av_clerk/loa,
         fmv = ifelse(is.na(fmv), 0 , fmv)) |>
  arrange(year) |>
  group_by(year, land_use, incent_prop) |>
  summarize(pins = n(), 
            group_year_fmv = sum(fmv),
  ) |>
  ungroup()|>
group_by(land_use, incent_prop) |>
  mutate(            
    base_year_fmv = group_year_fmv[year == min(year)],
    base_year_n = pins[year == min(year)],
    fmv_growth = group_year_fmv/base_year_fmv,
    n_growth = pins/base_year_n
  )  |> ungroup()


df_2006 %>% select(year, land_use,  incent_prop, pins, fmv_growth, n_growth) %>% filter(year == 2022)  %>% select(-year)


```

```{r}
#| label: tbl-growthcalculations-commindfile
#| tbl-cap: "**Growth from 2011 to 2022.** Grouped by land_use, incent_prop, year."

# grouped by land_use, incent_prop, and then year
df_2011 <- ptax_pins %>%
  filter(year > 2010) %>%
  group_by(pin) %>%
  mutate(years_existed = n()) %>%
  ungroup() %>%
  filter(years_existed == 12 ) %>%
  mutate(fmv = av_clerk/loa,
         fmv = ifelse(is.na(fmv), 0 , fmv)) |>
  group_by(land_use, incent_prop, year) |>
  summarize(pins = n(), 
            group_year_fmv = sum(fmv),
  ) |>
  
  mutate(            
    base_year_fmv = group_year_fmv[year == min(year)],
    base_year_n = pins[year == min(year)],
    fmv_group_growth = group_year_fmv/base_year_fmv,
    n_group_growth = pins/base_year_n
  ) 


df_2011 %>% select(year, land_use, incent_prop, n_group_growth, fmv_group_growth, everything()) %>% 
filter(year == 2022) %>% select(-year)
```

### Share of Commercial and Industrial Over Time

```{r}
#| label: tbl-cooktotals-allyears
#| tbl-cap: "Commercial and Industrial PINs in Cook County (2006 - 2022). Uses comm_ind_PINs file."


table <- ptax_pins %>%
  group_by(year, land_use) %>%
  summarize(
    proptype_pincount = n(),
            n_incent = sum(ifelse(incent_prop == "Incentive", 1, 0)),
            n_nonincent = sum(ifelse(incent_prop == "Non-Incentive", 1, 0))) %>%
  mutate(pct_incent = n_incent / proptype_pincount) %>%
  arrange(desc(year)) %>%
  mutate(year = as.character(year)) %>%
filter(!land_use %in% c("Exempt", "Other Residential", "Owner Occupied", "Rental") )




flextable(table) %>% 
  set_header_labels(year = "Year", land_use = "Land Use", proptype_pincount = 'PIN Count', n_incent = "Incent. Class", n_nonincent = "No Incent. Class",
                    pct_incent = "% Incent. Class") %>%
  set_table_properties( layout = "autofit", width = .75)
```

```{r}
#| label: tbl-cooktotals-allyears-fmv
#| tbl-cap: "Commercial and Industrial FMV in Cook County (2006 - 2022)"


table <- ptax_pins %>%
  group_by(year, land_use) %>%
  summarize(
    proptype_fmv = sum(fmv),
            n_incent = sum(ifelse(incent_prop == "Incentive", 1, 0)),
            n_nonincent = sum(ifelse(incent_prop == "Non-Incentive", 1, 0)),
            fmv_incent = sum(ifelse(incent_prop == "Incentive", fmv, 0)),
            fmv_nonincent = sum(ifelse(incent_prop == "Non-Incentive", fmv, 0))) %>%
  mutate(pct_incent = fmv_incent / proptype_fmv) %>%
  arrange(desc(year)) %>%
  mutate(year = as.character(year)) %>%
filter(!land_use %in% c("Exempt", "Other Residential", "Owner Occupied", "Rental") )


table %>% select(-c(n_incent, n_nonincent)) %>% flextable() %>% 
  set_header_labels(year = "Year", land_use = "Land Use", proptype_fmv = "Group\'s FMV", fmv_incent = "Incent. Class FMV", fmv_nonincent = "No Incent. Class",
                    pct_incent = "% FMV from Incent. Class") %>%
  set_table_properties( layout = "autofit", width = .75)
```

```{r}
#| label: fig-cooktotals-allyears-fmv2
#| fig-cap: "**Percent of Commercial and Industrial FMV in Cook County that had Incentive Property Classification (2006 - 2022).**  In 2006, around 20% of industrial FMV came from properties with incentive classes. In 2022, over 35% of Industrial FMV came from incentive class properties. Commercial properties have gone from making up nearly 0% Commercial FMV to close to 5% of Commercial FMV."
 
table %>% ggplot() + 
geom_line(aes(x=year, y = pct_incent, group = land_use, color = land_use)) + labs(title = "Percent of Commercial and Industrial FMV with Incent. Classes", y = "Share of FMV", x="")

```

```{r}
#| label: fig-growth-facetby-incentprop
#| fig-cap: "Growth from 2006 to 2022. Faceted by binary indicator for if a property had incentive classification or not. Indexed to FMV of properties during 2011"

df_2006 %>% filter(!is.na(incent_prop) ) %>% 
ggplot() + geom_line(aes(x=year, y=fmv_growth, group = land_use, color = land_use)) + facet_wrap(~incent_prop) + labs(title= "Growth from 2006 to 2022")


```

## 2011 to 2022

```{r}
#| label: tbl-growthcalculations-commindfile3
#| tbl-cap: "Growth from 2011 to 2022. Grouped in different order of same variables: year, land_use, incent_prop. The way I would think it should be done - AWM."


# grouped byincent_prop, land_use, and then year

df_2011 <- ptax_pins %>%
  filter(year >= 2011) %>%
  mutate(fmv = av_clerk/loa,
         fmv = ifelse(is.na(fmv), 0 , fmv)) |>
  group_by(pin) %>%
  mutate(
    years_existed = n() ) |>
  filter(years_existed >=11 ) %>%
ungroup() %>%
  
  group_by(year, land_use, incent_prop) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
    #avg_group_baseyear_fmv = mean(base_year_fmv, na.rm=TRUE),
    #avg_group_fmv = mean(fmv, na.rm=TRUE),   # average FMV for PINs in the group for each year
 ) |> ungroup() |>
group_by(land_use,incent_prop) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2011 %>% select(year, land_use, incent_prop, n_group_growth, fmv_group_growth, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growthcalculations2
#| tbl-cap: "Growth from 2011 to 2022. Includes all commercial and industrial PINs even if they did not exist for all years."

df_2011 <- ptax_pins %>%
filter(year >= 2011) %>%
#  group_by(land_use, incent_prop, year) |>
 group_by(year, land_use, incent_prop) |>
  summarize(n = n(), 
            group_year_fmv = sum(fmv, na.rm=TRUE),
  ) |>

  mutate(            
        base_year_fmv = group_year_fmv[year == min(year)],
            base_year_n = n[year == min(year)],
            fmv_growth = group_year_fmv/base_year_fmv,
         n_growth = n/base_year_n
       ) 

df_2011 %>% select(year, land_use, incent_prop, fmv_growth, n_growth) %>% 
filter(year == 2022) 

```

```{r}
#| label: tbl-growthcalculations3
#| tbl-cap: "Growth from 2011 to 2022. grouped by new categorical variables. Includes all commercial and industrial PINs even if they did not exist for all years."

df_2011_v2 <- ptax_pins %>%
filter(year >= 2011) %>%

 group_by(year, landuse_change, incent_change ) |>
  summarize(n = n(), 
            group_year_fmv = sum(fmv, na.rm=TRUE),
  ) |>
ungroup() |>
group_by(landuse_change, incent_change, ) |>
  mutate(            
        base_year_fmv = group_year_fmv[year == min(year)],
            base_year_n = n[year == min(year)],
            fmv_growth = group_year_fmv/base_year_fmv,
         n_growth = n/base_year_n
       ) 

df_2011_v2 %>% select(year, landuse_change, incent_change, fmv_growth, n_growth) %>% 
filter(year == 2022) 

```

```{r}
#| label: fig-growth-facetby-incentprop4
#| fig-cap: "**Growth from 2011 to 2022.** Faceted by if a PIN changed landuse during the sample period. Indexed to fair market value during 2011."


df_2011_v2 %>%  
ggplot() + geom_line(aes(x=year, y=fmv_growth, group = landuse_change, color = landuse_change)) + facet_wrap(~incent_change) + labs(title= "Growth from 2011 to 2022")

```

```{r}
#| label: fig-growth-facetby-incentprop2
#| fig-cap: "**Growth from 2011 to 2022.** Faceted by binary indicator for if a property had incentive classification or not. Indexed to fair market value during 2011."


df_2011 %>% filter(!is.na(incent_prop)) %>% 
ggplot() + geom_line(aes(x=year, y=fmv_growth, group = land_use, color = land_use)) + facet_wrap(~incent_prop) + labs(title= "Growth from 2011 to 2022")

```

```{r}
#| label: fig-growth-facetby-Aleacat
#| fig-cap: "**Growth from 2006 to 2022.** Faceted by land use category."

df_2006 %>% 
ggplot() + geom_line(aes(x=year, y=fmv_growth, group = incent_prop, color = incent_prop))  + theme_classic() + facet_wrap(~land_use)

```

```{r}
#| label: fig-growth-facetby-Aleacat-2011
#| fig-cap: "**Growth from 2011 to 2022.** Faceted by land use category."

df_2011 %>% 
ggplot() + geom_line(aes(x=year, y=fmv_growth, group = incent_prop, color = incent_prop)) + theme_classic() + facet_wrap(~land_use)

```

```{r}
df_2011 %>% 
filter(land_use == "Commercial" | land_use == "Industrial") %>% 
ggplot() + geom_line(aes(x=year, y=n_growth, group = incent_prop, color = incent_prop)) + scale_y_continuous(limits = c(0.8,2.5)) + labs( title = "Commercial v Industrial PIN Count Growth") + facet_wrap(~land_use)


```

# Balanced 2011-2022 Panel Descriptive Tables - FMV Growth

Uses `comm_ind_PINs_2011to2022_existallyears.csv` made from the `helper_pull_comm_ind_allyears.R` script.

```{r}
#| label: tbl-growthcalculations-commindfile-balanced2011
#| tbl-cap: "Growth from 2011 to 2022. Uses pins that exist all years between 2011 and 2022."

#ptax_pins <- read_csv("./Output/comm_ind_PINs_2011to2022_existallyears.csv")

ptax_pins <- read_csv("./Output/comm_ind_PINs_2011-2022_balanced.csv")

df_2011 <- ptax_pins |>
  
  group_by(year, land_use, incent_prop) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
    #avg_group_baseyear_fmv = mean(base_year_fmv, na.rm=TRUE),
    #avg_group_fmv = mean(fmv, na.rm=TRUE),   # average FMV for PINs in the group for each year
 ) |> ungroup() |>
group_by(land_use,incent_prop) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2011 %>% select(year, land_use, incent_prop, fmv_group_growth, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growth-incentchange-balanced2011
#| tbl-cap: "Growth from 2011 to 2022. Uses pins that exist all years between 2011 and 2022."


df_2011_incentchange <- ptax_pins |>
  
  group_by(year, incent_change) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
 ) |> ungroup() |>
group_by( incent_change) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2011_incentchange %>% select(year, incent_change, fmv_group_growth, base_year_fmv, group_year_fmv, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growth-landusechange-balanced2011
#| tbl-cap: "Growth from 2011 to 2022. Uses pins that exist all years between 2011 and 2022."


df_2011_landusechange <- ptax_pins |>
  
  group_by(year, landuse_change) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
 ) |> ungroup() |>
group_by( landuse_change) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2011_landusechange %>% select(year, landuse_change, fmv_group_growth, base_year_fmv, group_year_fmv, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growth-landuseandincentchange-balanced2011
#| tbl-cap: "Growth from 2011 to 2022. Uses pins that exist all years between 2011 and 2022."


df_2011_incentchange <- ptax_pins |>
  
  group_by(year, landuse_change, incent_change) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
 ) |> ungroup() |>
group_by( landuse_change, incent_change) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2011_incentchange %>% select(year, landuse_change, incent_change, fmv_group_growth, base_year_fmv, group_year_fmv, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: fig-growth-facetby-incentprop-balanced
#| fig-cap: "**Growth from 2011 to 2022.** Faceted by if a PIN changed landuse during the sample period. Indexed to fair market value during 2011."


#df_2011_v2 %>%  
df_2011_incentchange %>%  
ggplot() + geom_line(aes(x=year, y=fmv_group_growth, group = landuse_change, color = landuse_change)) + facet_wrap(~incent_change) +
scale_x_continuous(#limits = c(2011, 2022), 
breaks = c(2011, 2018, 2022)) + 
labs(title= "Growth from 2011 to 2022", subtitle = "Change in Incentive Use by Land Use Change", caption = "Indexed to 2011 FMV", x = "", y = "FMV Growth from 2011") 

```

```{r}
#| label: fig-growth-facetby-incentprop-balanced2
#| fig-cap: "**Growth from 2011 to 2022.** Faceted by binary indicator for if a property had incentive classification or not. Indexed to fair market value during 2011."


df_2011 %>% filter(!is.na(incent_prop)) %>% 
ggplot() + geom_line(aes(x=year, y=fmv_group_growth, group = land_use, color = land_use)) + facet_wrap(~incent_prop) + labs(title= "Growth from 2011 to 2022")

```

```{r}
#| label: fig-growth-facetby-landusebalanced-2011
#| fig-cap: "**Growth from 2011 to 2022.** Faceted by land use category."

df_2011 %>% 
ggplot() + geom_line(aes(x=year, y=fmv_group_growth, group = incent_prop, color = incent_prop)) + theme_classic() + facet_wrap(~land_use)

```

# Balanced 2006-2022 Panel Descriptive Tables - FMV Growth

Uses `comm_ind_PINs_2006to2022_existallyears.csv`

```{r}
#| label: tbl-growthcalculations-commindfile-balanced2006
#| tbl-cap: "Growth from 2006 to 2022. Uses pins that exist all years between 2011 and 2022."

ptax_pins <- read_csv("./Output/comm_ind_PINs_2006to2022_balanced.csv")

df_2006 <- ptax_pins |>
  
  group_by(year, land_use, incent_prop) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),

 ) |> ungroup() |>
group_by(land_use,incent_prop) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2006 %>% select(year, land_use, incent_prop, fmv_group_growth, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growth-incentchange-balanced2006
#| tbl-cap: "Growth from 2006 to 2022. Uses pins that exist all years between 2006 and 2022."


df_2006_incentchange <- ptax_pins |>
  
  group_by(year, incent_change) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
 ) |> ungroup() |>
group_by( incent_change) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2006_incentchange %>% select(year, incent_change, fmv_group_growth, base_year_fmv, group_year_fmv, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growth-landusechange-balanced2006
#| tbl-cap: "Growth from 2006 to 2022. Uses pins that exist all years between 2006 and 2022."


df_2006_incentchange <- ptax_pins |>
  
  group_by(year, landuse_change) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
 ) |> ungroup() |>
group_by( landuse_change) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2006_incentchange %>% select(year, landuse_change, fmv_group_growth, base_year_fmv, group_year_fmv, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: tbl-growth-landuseandincentchange-balanced2006
#| tbl-cap: "Growth from 2006 to 2022. Uses pins that exist all years between 2006 and 2022."


df_2006_incentchange <- ptax_pins |>
  
  group_by(year, landuse_change, incent_change) |>
  summarize(
    group_pin_count = n(),
    group_year_fmv = sum(fmv, na.rm=TRUE),
 ) |> ungroup() |>
group_by( landuse_change, incent_change) %>%
  mutate(
    base_year_fmv = group_year_fmv[year == min(year)],
    
    base_year_n = group_pin_count[year == min(year)],
    n_group_growth = group_pin_count/base_year_n,
    fmv_group_growth = group_year_fmv / base_year_fmv) 


df_2006_incentchange %>% select(year, landuse_change, incent_change, fmv_group_growth, base_year_fmv, group_year_fmv, everything()) %>% 
filter(year == 2022) %>% select(-year)

```

```{r}
#| label: fig-growth-facetby-incentprop-balanced-2006
#| fig-cap: "**Growth from 2006 to 2022.** Faceted by if a PIN changed landuse during the sample period. Indexed to fair market value during 2006."


#df_2011_v2 %>%  
df_2006_incentchange %>%  
ggplot() + geom_line(aes(x=year, y=fmv_group_growth, group = landuse_change, color = landuse_change)) + facet_wrap(~incent_change) +
scale_x_continuous(#limits = c(2011, 2022), 
breaks = c(2006, 2016, 2022)) + 
labs(title= "Growth from 2006 to 2022", 
subtitle = "Change in Incentive Use by Land Use Change", 
caption = "Indexed to 2006 FMV", 
x = "", y = "FMV Growth from 2006") 
```

# Export Tables

```{r}
library(readxl)

tablelist <- list(
  "Muni Sums" = munilevel,
  "Muni Class Summaries" = muni_mc_table, 
  "Muni Class Percentages" = table_muni_mc_percentages, 
  "Muni Tax Rate Hypotheticals" = alt_rates,
  "Muni Adjusted Tax Rates" = table4_adjusted,
  "Cook Sums" = table_cook,
  "Cook Class Sums" = cty_MC_table
)


writexl::write_xlsx(tablelist, "Output/calculation_output_preliminary.xlsx")
```