Incentives_in_TIFs.qmd

---
title: "Incentive Projects in TIFs"
format: 
  html:
    code-fold: true
    toc: true
    toc-location: left
    tbl-cap-location: margin
    fig-cap-location: top
    df-print: paged
---

**Data Preparation**

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

library(tidyverse)
library(DT)
library(flextable) # for exporting to word or PDF files. Kable does not do that

library(sf)
library(httr)
library(jsonlite)

library(ggpattern)



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

set_flextable_defaults(theme_fun = theme_vanilla, 
                       padding = 2,
                       line_spacing = 1,
                       big.mark = ",",
                       )
# keypins <- read_csv("Output/all_keypins.csv")

options(DT.options = list())

####Customize features and interactivity of table
#create a list of options used to format the DT table
my.options <- list(autoWidth = FALSE, #smart width handling
searching = TRUE, #search box above table
ordering = TRUE, #whether columns can be sorted
#lengthChange = FALSE, #ability to change number rows shown on page in table
#lengthMenu = FALSE, #options lengthChange can be changed to
pageLength = FALSE, #initial number of rows per page of table
paging = TRUE, #whether to do pagination
info = TRUE) #notes whether or not table is filtered

# link to the API output as a JSON file
muni_shp <- read_sf("https://gis.cookcountyil.gov/traditional/rest/services/politicalBoundary/MapServer/2/query?outFields=*&where=1%3D1&f=geojson")

forests <- read_sf("https://services2.arcgis.com/I5Or36sMcO7Y9vQ3/arcgis/rest/services/Forest_Preserve_Nature_Preserves/FeatureServer/0/query?outFields=*&where=1%3D1&f=geojson")




```

## Create Project ID

### Board of Review Appeals Data

Pulls appeal data from the Cook County Data Portal. CSV file was created using data from May 2024. Property classes had extra zeros padding the right side of the class so an additional data cleaning step was done after pulling the data.

After the second filter step to fix the class variable and filter the remaining residential properties out, there are 780K observations.

```{r getBORappeals, eval=FALSE}
base_url <- "https://datacatalog.cookcountyil.gov/resource/7pny-nedm.json?$where=class > 399"


bor <- GET(
  base_url,
  query = list(

    `$select` = paste0(c("pin", "township_code", "tax_year",
                         "class", "majorclass", "changereason", 
                         "changereasondescription",
                         "appealid", "appellant"
                       ),
   collapse = ","),

    `$limit` = 6000000L
  )
)

bor <- fromJSON(rawToChar(bor$content))


## class had extra zeros padding the right side so keep first 3 digits that are the actual property class
bor <- bor %>% 
  mutate(
  class_bor = str_sub(class, 1,3)) %>% 
  filter(class > 399)    ## filters out any property class below 400

bor <- bor %>% 
  mutate(project_id = str_extract(appealid, "[^.]+"))

write_csv(bor, "Output/borappeals.csv")
```


```{r}
#| label: tbl-getBORappeals2
#| tbl-cap: "Board of Review Appeal Data - Grouped by appellant and project ID. Project ID is created from the `appealid` variable used by the BoR. The `appealid` appears to group PINs by the digits before the decimal point."

bor <- read_csv("Output/borappeals.csv") %>%
  mutate(project_appellant = paste(project_id, sep = "-", appellant))


# bor %>% 
#   group_by(tax_year, project_id, appellant) %>% 
#   summarize(pincount = n(),
#              pins = list(pin)) %>% 
#   filter(pincount > 1)  %>% 
#   arrange(desc(tax_year))  %>% 
#   head() %>%
#   datatable(rownames = FALSE)
# 
# bor %>% 
#   group_by(tax_year, project_id) %>% 
#   summarize(pincount = n(),
#             appellant = list(unique(appellant)),
#              pins = list(pin)) %>% 
#   filter(pincount > 1)  %>% 
#   arrange(desc(tax_year))  %>% 
#   head() %>%
#   datatable(rownames = FALSE)
```

For the Archived Parcel Universe:

-   13,000+ KeyPINs have more than 1 PIN associated with them.

-   11,849 when grouping by keypin and mailing name. But that is probably due to a bunch of keypins having mailing names that do not match completely so when grouped this way, they now have only 1 PIN associated with the keypin, which was filter out.

```{r}
parcuniverse_keypins <- readxl::read_xlsx("./Inputs/parceluniverse_keypins_20240725.xlsx", 
                                          sheet = "keypins_20240725") %>%
  mutate(pin14 = str_pad(as.character(pin), width = 14, side = "left", pad = "0"),
         keypin = str_pad(as.character(proration_key_pin), width = 14, side = "left", pad = "0"),
         pin10 = str_sub(pin14,1,10),
         pin7 = str_sub(pin14,1,7), .before = "pin",) %>%
  select(-c(pin_7dig, pin, Column1)) %>%
  filter(class != "EX")

puni_keypins <- parcuniverse_keypins %>% 
select(pin14, 
puni_keypin = keypin, 
proration_rate2022 = proration_rate, 
mailing_name2022 = mailing_name)

parcuniverse_keypins %>% 
  group_by(keypin
#, mailing_name
           ) %>% 
  summarize(pincount = n(), 
            class = mean(as.numeric(class), na.rm=TRUE),
            has_incent = sum(ifelse(as.numeric(between(class, 600, 899)), 1, 0))) %>%
  filter(pincount > 1) %>% 
  arrange(desc(has_incent), desc(pincount))

# parcuniverse_keypins %>% filter(is.na(mailing_name))  # 21,612 keypins
# parcuniverse_keypins %>% distinct(keypin)  # 21,612 keypins
```

```{r}
#| label: tbl-borprojectpins
#| tbl-cap: "Uses first occurance of the an appeal id and the appealant name to create project IDs for each PIN. This deal with PINS that appealed multiple time and makes each PIN a unique occurance. Takes numbers for athe decimal point in `appealid to create project ID. Uses Board of Review Appeals data from the Cook County Data Portal. Counts number of times each PIN appealed its valuation. 102,619 PINs from BoR data (downloaded May 2024)."

bor_pins <- bor %>% 
  group_by(pin) %>% 
  arrange(desc(tax_year)) %>%
  summarize(pin = first(pin),
            class_bor = list(class),
taxyear_appealed = list(tax_year),
            appellant = first(appellant),
            project_id = first(project_id), 
            timesappealed = n() ) %>%
  mutate(proj_appeallant = paste(project_id, "-", appellant))


#46,250 distinct projects based on IDs
# projects <- bor_pins %>% distinct(project_id)

#38,808 distinct appellant names
# appellants <- bor_pins %>% distinct(appellant)

# 48,993 distinct project id-appellant combinations
projects_appellant <- bor_pins %>% 
  distinct(project_id, appellant)
```

### Joined Pin Data

Pull in the joined pin and exemption data for 2022. Extracted from PTAXSIM. See `0_joined_PIN_data_2022.qmd` for code.

```{r}
#| echo: true
#| code-fold: false


loa <- read_csv("inputs/ccao_loa.csv") %>% 
filter(year == 2022)

# all pins for 2022
ptax_pins <- read_csv("Output/Dont_Upload/0_joined_PIN_data_2022.csv") %>% 
  mutate(class = as.numeric(class)) %>%
  #        # keep 500-899 class PINs
  filter(#(class >= 400 & class < 900) |
between(class, 0, 199) | between(class, 400, 899)
  #          # and keep land PINs if we want land property owned by them too.
  #        (class >= 100 & class < 200)  
          )  %>%
   select(-c(propclass_1dig:av.y)) %>%
mutate(fmv = ifelse(is.na(fmv), 0, fmv)) %>%
select(-c(exe_homeowner:exe_vet_dis_ge70))

ptax_pins <- ptax_pins %>% 
left_join(bor_pins) %>%
left_join(puni_keypins, by = c("pin" = "pin14")) %>%
select(proj_appeallant, puni_keypin, pin, tax_code, class, class_bor, tax_code_rate, fmv, everything()) %>%
select(-c(has_HO_exemp:has_DV_exemp))


ptax_pins %>% 
     filter(is.na(proj_appeallant) & is.na(puni_keypin) & class < 200)

ptax_pins <- ptax_pins %>% 
     filter((!is.na(proj_appeallant) & !is.na(puni_keypin)) | class > 200) 


```

### Methodology Worksheet Project IDs (from Data Portal: commercial valuation dataset)

82,957 distinct keypins from `all_keypins` file. (Made from commercial valuation dataset on the Data Portal.)

46,213 distinct project ids using the Board of Review appeal ids

```{r}
#| echo: true
#| code-fold: false


# temporary work-around until we have full keypin list:
proj_xwalk <- read_csv("Output/all_keypins.csv")               
# all commercial valuation properties but made with very unreliable data from commercial valuation dataset. 

# 82K+ distinct keypins from all_keypins file
# proj_xwalk %>% distinct(keypin) 

# combine project IDs to PINs:
ptax_pins <- ptax_pins %>% left_join(proj_xwalk, by = "pin")

project_sums <- ptax_pins %>% 
mutate(actual_keypin = ifelse(is.na(proj_appeallant), puni_keypin, proj_appeallant))%>%
mutate(actual_keypin = ifelse(is.na(actual_keypin), keypin, actual_keypin))%>%

group_by(actual_keypin) %>%
summarize(
fmv = sum(fmv),
tax_code = mean(tax_code),
final_tax_to_dist = sum(final_tax_to_dist),
pin_classes = list(unique(class)), 
incent_pins = sum(ifelse(class >= 600 & class < 900, 1, 0)),
project_pins = n()
)
project_sums


#  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), "0_Unincorporated", clean_name))


```

```{r}
#| label: tbl-createbothids2
#| tbl-cap: "Incentive Properties only. Count of PINs in project ID (using `both_ids`). both_ids is created from using the BoR appeal ids as the default group ID. Then if a PIN in ptaxsim doesn't match to a Board of Review appeal PIN, project IDs from the commercial valuation data set are used."


newproject_pinlist <- ptax_pins %>% 
mutate(actual_keypin = ifelse(is.na(proj_appeallant), puni_keypin, proj_appeallant)) %>%
mutate(actual_keypin = ifelse(is.na(actual_keypin), keypin, actual_keypin)) %>%
select(pin, actual_keypin)
  
  
ptax_pins <- ptax_pins %>% 
mutate(actual_keypin = ifelse(is.na(proj_appeallant), puni_keypin, proj_appeallant)) %>%
mutate(actual_keypin = ifelse(is.na(actual_keypin), keypin, actual_keypin)) %>%
mutate(both_ids = actual_keypin)


ptax_pins %>%
  group_by(both_ids) %>%
  summarize(pincount = n()) %>% arrange(desc(pincount))

ptax_pins %>%
distinct(both_ids)

# table_list <- ptax_pins %>% 
#   filter(class > 599) %>% 
#   group_by(both_ids) %>% 
#   summarize(pincount = n(),
#             pin_list = (list(unique(pin))),
#             class_list = (list(unique(class))),
#             appellant = ((list(unique(appellant)))) ) %>%
#   arrange(desc(pincount))
# 
# table_list %>% datatable(rownames = FALSE)
```

```{r}
#| label: tbl-Aleacats-inTIFs
#| tbl-cap: "All nonResidential PINs Types. Count of PINs in project ID (using `both_ids`). both_ids is created from using the BoR appeal ids as the default group ID. Then if a PIN in ptaxsim doesn't match to a Board of Review appeal PIN, project IDs from the commercial valuation data set are used."

table <- ptax_pins %>% 
  group_by(Alea_cat) %>%
  summarize(proptype_pincount = n(),
            projects = n_distinct(both_ids),
            av_incent = sum(ifelse(incent_prop == "Incentive", av, 0)),
            av_nonincent = sum(ifelse(incent_prop == "Non-Incentive", av, 0)),
            av=sum(av),
incentpins_inTIF = sum(ifelse(in_tif == 1 & incent_prop == "Incentive", 1, 0)),
pins_inTIF = sum(in_tif, na.rm=TRUE),
fmv = sum(fmv, na.rm=TRUE)

          ) %>% 
  mutate(
         incent_av_adj = 2.5*av_incent) %>%
  mutate(
         total_av_adjusted = av_incent*2.5 + av_nonincent,
         pct_incent = incent_av_adj / total_av_adjusted) %>%
    select(-c(av_incent, av_nonincent, incent_av_adj))

flextable(table) %>% 
 # set_caption("Totals for Cook County") %>% 
  set_header_labels(Alea_cat = "Property Type", proptype_pincount = 'PINs', projects = "Project IDs", av = ' Taxable AV') %>%
  set_table_properties( layout = "autofit", width = .75)
```

```{r}
#| label: tbl-summarytable-commercialandindustrialPINs
#| tbl-cap: "Commercial and Industrial PINs only (500 and higher, excludes nonprofit properties). Count of PINs in project ID (using `both_ids`). both_ids is created from using the BoR appeal ids as the default group ID. Then if a PIN in ptaxsim doesn't match to a Board of Review appeal PIN, project IDs from the commercial valuation data set are used."



com_ind_projects <- ptax_pins %>% 
  group_by(both_ids) %>%
  arrange(both_ids, class) %>%
  mutate(min_class = first(class),
         max_class = last(class)) %>% 
  filter(max_class > 499) %>%
  ungroup()

table <- com_ind_projects %>% 
  group_by(Alea_cat) %>%
  summarize(proptype_pincount = n(),
            projects = n_distinct(both_ids),
            av_incent = sum(if_else(incent_prop == "Incentive", av, 0)),
            av_nonincent = sum(ifelse(incent_prop == "Non-Incentive", av, 0)),
       #     av_adjusted=sum(ifelse(between(class, 600, 899), av*2.5, av)),
            av=sum(av),
fmv =sum(fmv, na.rm=TRUE),
pins_inTIF = sum(in_tif, na.rm=TRUE)

) %>%
  mutate(
         incent_av_adj = 2.5*av_incent ) %>%
  mutate(
         total_av_adjusted = incent_av_adj + av_nonincent,
         pct_incent = incent_av_adj / total_av_adjusted) %>%
  select(-c(av_incent, av_nonincent))

  


flextable(table) %>% 
 # set_caption("Totals for Cook County") %>% 
  set_header_labels(Alea_cat = "Property Type", proptype_pincount = 'PINs', projects = "Project IDs", av = ' Current Taxable AV') %>%
  set_table_properties( layout = "autofit", width = .75)
```

```{r}
#| label: tbl-summarytable-commercialandindustrial-incentivePINs
#| tbl-cap: "Commercial and Industrial Incentive PINs only (600 and higher, excludes nonprofit properties). Count of PINs in project ID (using `both_ids`). both_ids is created from using the BoR appeal ids as the default group ID. Then if a PIN in ptaxsim doesn't match to a Board of Review appeal PIN, project IDs from the commercial valuation data set are used."


incentive_pins2022 <- ptax_pins %>% 
  group_by(both_ids) %>%
  arrange(both_ids, class) %>%
  mutate(min_class = first(class),
         max_class = last(class)) %>% 
  filter(max_class > 599 & max_class < 900) %>%
  ungroup()

table <- incentive_pins2022 %>% 
  group_by(Alea_cat) %>%
  summarize(proptype_pincount = n(),
            projects = n_distinct(both_ids),
            av_incent = sum(if_else(incent_prop == "Incentive", av, 0)),
            av_nonincent = sum(ifelse(incent_prop == "Non-Incentive", av, 0)),
       #     av_adjusted=sum(ifelse(between(class, 600, 899), av*2.5, av)),
            av=sum(av),
fmv =sum(fmv, na.rm=TRUE),
pins_inTIF = sum(in_tif, na.rm=TRUE)

) %>%
  mutate(
         incent_av_adj = 2.5*av_incent ) %>%
  mutate(
         total_av_adjusted = incent_av_adj + av_nonincent,
         pct_incent = incent_av_adj / total_av_adjusted) %>%
  select(-c(av_incent, av_nonincent))

  


flextable(table) %>% 
 # set_caption("Totals for Cook County") %>% 
  set_header_labels(Alea_cat = "Property Type", proptype_pincount = 'PINs', projects = "Project IDs", av = ' Current Taxable AV') %>%
  set_table_properties( layout = "autofit", width = .75)
```

> renaming the both_ids variable to keypin to avoid changing all the code below.

```{r}
#| code-fold: false

ptax_pins <- ptax_pins %>% 
  mutate(keypin = both_ids)
```

## Create TIF vectors

```{r}
# TIF distributions will include all the unique tax codes that make up
# a TIF

# has eav values for each tax code
tif_distrib <- DBI::dbGetQuery(
  ptaxsim_db_conn, 
  glue_sql("
  SELECT *
  FROM tif_distribution
  WHERE year = 2022
  ",
  .con = ptaxsim_db_conn
  )
)


# has same number of pins as method used above but way faster. 
tif_pins <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue::glue_sql("
    SELECT DISTINCT *
    FROM pin
    WHERE tax_code_num IN ({unique(tif_distrib$tax_code_num)*})
    AND year = 2022
  ",
    .con = ptaxsim_db_conn
  )
) 

```

```{r}
bound_tif <- st_read(paste0(
  "https://opendata.arcgis.com/api/v3/datasets/",
  "8aeb00520c544aafb9a22510465c679d_18/downloads/",
  "data?format=geojson&spatialRefId=4326&where=1%3D1"
), quiet = TRUE) 


  
# bound_tif %>%
#   ggplot() +
#   geom_sf(data = bound_tif, aes(geometry = geometry#, fill = project_count
#               ), color = "black") +
#   theme_void() 
```

### TIF pins and Lookup_pin() command.

```{r}
ptax_pins <- ptax_pins %>%
  # 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_pins$pin, 1,0))

incentive_pins2022 <- incentive_pins2022 %>%
  # 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_pins$pin, 1,0))

```

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.

```{r}
#| label: tbl-allprops-eavinandoutTIF
#| tbl-cap: "All non-residential properties in and out of TIFs in Cook County. Sums EAV."


EAV_inandout_TIF <- ptax_pins %>% 
  group_by(clean_name,
           in_tif) %>% 
  summarise(eav = sum(eav, na.rm=TRUE)) %>% 
  pivot_wider(names_from = in_tif, values_from = eav 
              )%>% 
  mutate(#total = `0`+`1`
         total = ifelse(is.na(`1`), `0`, `0`+`1`)) %>%
  rename(
         "Taxable EAV" = `0`,
         "TIF Increment" = `1`,
         "Total EAV before exemptions" = total)

EAV_inandout_TIF %>% datatable(rownames = FALSE)
```

```{r}
#| label: tbl-incentiveprops-eavinandoutTIF
#| tbl-cap: "Incentive non-residential properties in and out of TIFs in Cook County. Sums EAV."


EAV_inandout_TIF <- incentive_pins2022 %>% 
  group_by(clean_name,
           in_tif) %>% 
  summarise(eav = sum(eav, na.rm=TRUE)) %>% 
  pivot_wider(names_from = in_tif, values_from = eav 
              )%>% 
  mutate(#total = `0`+`1`
         total = ifelse(is.na(`1`), `0`, `0`+`1`)) %>%
  rename(
         "Taxable EAV" = `0`,
         "TIF Increment" = `1`,
         "Total EAV before exemptions" = total)

EAV_inandout_TIF %>% datatable(rownames = FALSE)
```

```{r}
#| label: tbl-allprops-FMVinandoutTIF
#| tbl-cap: "All non-residential properties in and out of TIFs in Cook County. Sums FMV."

FMV_inandout_TIF <- ptax_pins %>% 
  group_by(clean_name,
           in_tif) %>% 
  summarise(fmv = sum(fmv, na.rm=TRUE)) %>% 
  pivot_wider(names_from = in_tif, values_from = fmv 
              )%>% 
  mutate(#total = `0`+`1`
         total = ifelse(is.na(`1`), `0`, `0`+`1`)) %>%
  rename(
         "Taxable FMV (Before Exemptions)" = `0`,
         "TIF Increment" = `1`,
         "Total FMV before exemptions" = total)

FMV_inandout_TIF %>% datatable(rownames = FALSE)  %>%
formatCurrency(c('Taxable FMV (Before Exemptions)', 'TIF Increment', 'Total FMV before exemptions'), digits = 0)
```

```{r}
#| label: tbl-incentiveprops-FMVinandoutTIF
#| tbl-cap: "All incentive non-residential properties in and out of TIFs in Cook County. Sums FMV. FMV is the AV / level of assessment."

FMV_inandout_TIF <- incentive_pins2022 %>% 
  group_by(clean_name,
           in_tif) %>% 
  summarise(fmv = sum(fmv, na.rm=TRUE)) %>% 
  pivot_wider(names_from = in_tif, values_from = fmv 
              )%>% 
  mutate(#total = `0`+`1`
         total = ifelse(is.na(`1`), `0`, `0`+`1`)) %>%
  rename(
         "Taxable FMV (Before Exemptions)" = `0`,
         "TIF Increment" = `1`,
         "Total FMV before exemptions" = total)

FMV_inandout_TIF %>% datatable(rownames = FALSE)  %>%
formatCurrency(c('Taxable FMV (Before Exemptions)', 'TIF Increment', 'Total FMV before exemptions'), digits = 0) 

```

```{r}
#| label: tbl-cooklevel-eavinandoutTIF
#| tbl-cap: "Cook County non-residential EAV totals in and out of TIF"

ptax_pins %>% 
  group_by( 
           in_tif) %>% 
  summarise(eav = sum(eav, na.rm=TRUE)) %>% 
  pivot_wider(names_from = in_tif, values_from = eav 
              )%>% 
  mutate(
         total = ifelse(is.na(`1`), `0`, `0`+`1`)) %>%
  rename(
         "Taxable EAV" = `0`,
         "TIF Increment" = `1`,
         "Total EAV before exemptions" = total)

```

```{r}
ptax_pins %>% group_by(clean_name, incent_prop, in_tif) %>% 
  summarize(pins = n(),
            projects = n_distinct(keypin),
                      #  av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
            av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
                        av = sum(av)
) %>% datatable(rownames = FALSE)
```

```{r}
#| label: tbl-percentofcommercialindustrialAV
#| tbl-cap: "Percentages are out of the commercial and industrial pins with and without incentive property classifications in a Muni"

table3 <- ptax_pins %>% 
  group_by(clean_name) %>% 
  mutate(muni_pins = n(),
            muni_projects = n_distinct(keypin),
                      #  av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
            muni_av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
                        muni_av = sum(av)
) %>% 
  ungroup() %>%
  group_by(clean_name, incent_prop, in_tif, muni_pins, muni_projects, muni_av, muni_av_adjusted) %>% 
  summarize(pins = n(),
            projects = n_distinct(keypin),
                      #  av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
            av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
                        av = sum(av),
fmv = sum(fmv, na.rm=TRUE)
) %>%
  mutate(pct_munipins = pins / muni_pins,
         pct_muniprojects = projects / muni_projects,
         pct_muniav = round(av / muni_av, digits =3),
         pct_adjustedmuniav = round(av_adjusted / muni_av_adjusted, digits = 3)) %>% 
  select(clean_name, incent_prop, in_tif, pct_munipins, pct_muniprojects, pct_muniav, pct_adjustedmuniav, everything())

table3

table3 %>%  filter(in_tif == 1) %>%
  datatable(          rownames= FALSE
) %>%
 # set_caption("Totals for Cook County") %>% 
 # set_header_labels(Alea_cat = "Property Type", proptype_pincount = 'PINs', projects = "Project IDs", av = ' Taxable AV') %>%
  set_table_properties( layout = "autofit", width = .75) %>%
    formatPercentage(c('pct_munipins', 'pct_muniprojects', 'pct_muniav', 'pct_adjustedmuniav'), digits = 2)

```

# Incentive Users - Muni level

34 Municipalities do not use Incentive Classifications.

15 cross into other counties. All 15 that cross county lines do use incentive classifications.

Alsip, Bedford Park, Blue Island,Bridgeview, Riverdale, South Holland, Summit, and Worth also use "Abatements" (based on the exe_abate variable in PTAXSIM)

```{r}
#| label: tbl-countycrossers
#| column: margin


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

cross_county_lines <- tc_muninames %>% filter(agency_num %in% cross_county_lines) %>% select(clean_name, agency_num) %>% distinct() %>% arrange(clean_name)

cross_county_lines <- cross_county_lines %>% left_join(muni_shp, by = c("clean_name" = "MUNICIPALITY"))


no_incentives <-  ptax_pins %>% 
 # filter(between(class, 600, 899)) %>%
  group_by(clean_name) %>% 
  summarize(incent_pins = sum(ifelse(between(class, 600, 899), 1, 0))) %>%
  filter(incent_pins == 0) %>% 
  distinct(clean_name)

no_incentives <- no_incentives %>% left_join(muni_shp, by = c("clean_name" = "MUNICIPALITY")) 

# saveRDS(no_incentives, "no_incentives_formap.rds")
# to use in 7_incentives.qmd file

```

```{r}
#| label: tbl-cooktotals
#| tbl-cap: "Cook County: Commercial and Industrial Fair Market Value with Incentive Classification. There is over $600 billion FMV in Cook County when including all property types. Of the $600 billion FMV in Cook County, $113 billion is from commercial and industrial property, of which $13 billion FMV has an incentive classification."

nonres_pins2022 <- ptax_pins


table <- nonres_pins2022 %>% 
  group_by(Alea_cat) %>%
  summarize(proptype_pincount = n(),
            projects = n_distinct(keypin),
            av_incent = sum(ifelse(incent_prop == "Incentive", av, 0)),
            fmv_incent = sum(ifelse(incent_prop == "Incentive", fmv, 0)),

            av_nonincent = sum(ifelse(incent_prop == "Non-Incentive", av, 0)),
            fmv_nonincent = sum(ifelse(incent_prop == "Non-Incentive", fmv, 0)),

            av=sum(av), 
fmv=sum(fmv, na.rm=TRUE)
          ) %>% 
  mutate(
         incent_av_adj = 2.5*av_incent) %>%
  mutate(
         total_av_adjusted = av_incent*2.5 + av_nonincent,
         pct_incent = incent_av_adj / total_av_adjusted,
pct_fmv = fmv_incent / fmv ) %>%
    select(-c(av_incent, av_nonincent, incent_av_adj))


flextable(table) %>% 
 # set_caption("Totals for Cook County") %>% 
  set_header_labels(Alea_cat = "Property Type", proptype_pincount = 'PINs', projects = "Project IDs", fmv = 'FMV', av = ' Taxable AV') %>%
  set_table_properties( layout = "autofit", width = .75)
```

```{r}
#| label: tbl-amount-incentivized
#| tbl-cap: "Percent of Commercial and Industrial Market Value for Assessment Purposes that has a reduced assessment rate due to incentive classification"


table2 <- nonres_pins2022 %>% 
  filter(Alea_cat != "Land") %>%
  group_by(clean_name) %>% 
  summarize(pin_count = n(),
            project_count = n_distinct(keypin), 

            av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
            av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
            fmv_w_incent = sum(ifelse(between(class, 600, 899), fmv, 0)),
            av=sum(av), 
fmv =sum(fmv, na.rm=TRUE),
incent_pins_inTIF = sum(ifelse(between(class, 600, 899) & in_tif == 1, 1, 0), na.rm = TRUE),
pins_inTIF = sum(in_tif, na.rm=TRUE)
) %>%
  mutate(av_incent = ifelse(av_incent == 0, NA, av_incent),
    pct_incent = av_incent / av_adjusted,
    pct_incent = ifelse(pct_incent == 0, NA, pct_incent),
pct_fmv = fmv_w_incent/fmv)  %>%
  select(-av_incent) %>% 
  select(clean_name, pct_incent, av, fmv, pct_fmv, av_adjusted, pin_count, project_count, pins_inTIF, incent_pins_inTIF)

datatable(table2,
          rownames= FALSE,
          colnames = c('Municipality' = 'clean_name', #  'Incentivized?' = 'incent_prop', 
                       'PIN Count' = 'pin_count', 'Project Count' = 'project_count', 
                       'Taxable AV' = 'av',
'FMV' = 'fmv', 'Pct FMV with Incentives' = 'pct_fmv',
                       'Assessed Market Value' = 'av_adjusted', 
                       'Pct AV Incentivized' = 'pct_incent')
          ) %>%
  formatCurrency(c('Taxable AV', 'Assessed Market Value', 'FMV'), digits = 0) %>%
  formatPercentage(c('Pct AV Incentivized', 'Pct FMV with Incentives'), digits = 2)
```

# A map!

```{r}
# if (all(top_five_kenya_fixed_1_8$county |>
#   unique() %in% correct_kenya_counties)) {
#   "The cleaned counties match the expected countries"
# } else {
#   "Not all of the counties have been cleaned completely"
# }

if (all(nicknames$shpfile_name |>
  unique() %in% muni_shp$MUNICIPALITY)) {
  "The cleaned municipality names match the expected municipality names"
} else {
  "Not all of the municipalities have been cleaned completely"
}
```

```{r}
#| label: fig-pctincentivevalue-bothtypes
#| fig-cap: "Number of Commercial and Industrial Projects per Municipality. A Project is based off of distinct KeyPINs. Includes all incentive and non-incentive property classes." 



table2 %>% 
  left_join(nicknames) %>%
  filter(clean_name != "Chicago"& clean_name!="0_Unincorporated" &
           !is.na(clean_name)) %>%
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot() +
  geom_sf(aes(geometry = geometry, fill = project_count), color = "black", alpha = 0.3) +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
      high = "brown", low = "black",
show.limits = TRUE,
   # nice.breaks=FALSE,
    na.value = "gray60",
    name = "Number of Projects",
   ) +
   geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'gray70')+
   geom_sf_pattern(data = forests, aes(geometry = geometry),  pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'darkgreen')+
    geom_sf(data = bound_tif, aes(geometry = geometry#, fill = project_count
              ), color = "black", alpha = 0.5, fill ="orange")

```

```{r}
#| label: fig-incentivePINsinTIFS-counts
#| fig-cap: "Number of Commercial and Industrial INCENTIVE PINs within a TIF per Municipality. Chicago and Markham both have around 600 incentive pins in TIFs and are  exclude from this map to avoid washing out other municipalities." 

table2 %>% 
  filter(clean_name != "Chicago" & clean_name != "Markham" & 
           clean_name != "0_Unincorporated" & !is.na(clean_name) ) %>%
    left_join(nicknames) %>%
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  ggplot() +
  geom_sf(aes(geometry = geometry, fill = incent_pins_inTIF), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
      high = "brown", low = "black",
show.limits = TRUE,
    nice.breaks=FALSE,
    na.value = "gray50",
    name = "Number of Incent\nPINs in TIFs",
   ) +
   geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'gray70')+
 #  geom_sf_pattern(data = forests, aes(geometry = geometry),  pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'darkgreen')+
    geom_sf(data = bound_tif, aes(geometry = geometry), color = "black", alpha = 0.5, fill ="orange")

```

```{r}
#| label: fig-pinsInTIF
#| fig-cap: "Number of PINs (all class types) within a TIF, per Municipality. Excludes Chicago (which has nearly 29K PINs located in TIFs)" 



table2 %>% 
  filter(clean_name != "Chicago") %>%
  filter(clean_name != "0_Unincorporated") %>%
    left_join(nicknames) %>%

  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  
  ggplot() +
  geom_sf(aes(geometry = geometry, fill = pins_inTIF), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
        high = "brown", low = "black",
show.limits=TRUE,
    nice.breaks=FALSE,
    na.value = "gray60",
    name = "Number of PINs in TIF",
   ) +
   geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'gray70')+
    geom_sf(data = bound_tif, aes(geometry = geometry#, fill = project_count
              ), color = "black", alpha = 0.5, fill ="orange")

```

```{r}
#| label: fig-pinspertif-onlychicago
#| fig-cap: Number of PINs with incentive property class that are also in a TIF. Chicago only, summarized by assessor neighborhoods.

NBHs <- read_sf("https://datacatalog.cookcountyil.gov/resource/pcdw-pxtg.geojson")

nbh_sum <- read_csv("./Output/ptaxsim_nbh_summary_2022_20240727.csv") %>%     
mutate(nbhd_code = as.character(nbhd_code)) %>%
mutate(township = str_sub(nbhd_code, 1, 2),
) 

nbh_MC <- read_csv("Output/ptaxsim_nbh_MC_summary_2022.csv")


nbh_sum %>% 
filter(triad_name == "City") %>%
left_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%

  ggplot() +
  geom_sf(aes(geometry = geometry, fill = PC_incents_inTIFs), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
        high = "brown", low = "black",
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value = "gray50",
    name = "Number of Incent\nPINs in TIF",
   ) +
    geom_sf(data = bound_tif, aes(geometry = geometry#, fill = project_count
              ), color = "black", alpha = 0.5, fill ="orange")
```


```{r}
#| label: fig-incentpinspertif
#| fig-cap: Number of PINs with incentive property class that are also in a TIF. Summarized by assessor neighborhoods. Excludes the Amazon warehouse located in Markham which has over 600 PINs associated with it."

nbh_sum %>% 
  filter(PC_incents_inTIFs < 500) %>%
  mutate(PC_incents_inTIFs = ifelse(PC_incents_inTIFs == 0, NA, PC_incents_inTIFs))%>%
left_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%

  ggplot() +
  geom_sf(aes(geometry = geometry, fill = PC_incents_inTIFs), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
        high = "brown", low = "black",
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value = "gray50",
    name = "Number of Incent\nPINs in TIF",
   ) +
    geom_sf(data = bound_tif, aes(geometry = geometry#, fill = project_count
              ), color = "black", alpha = 0.5, fill ="orange")
```


```{r}
#| label: fig-nbhfmv-onlychicago
#| fig-cap: Only Chicago. All non-residential FMV on left and incentive PIN FMV located in TIFs on right. 
#| layout-ncol: 2

nbh_sum %>% 
filter(triad_name == "City") %>%
left_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%
  ggplot() +
  geom_sf(aes(geometry = geometry, fill = (fmv_industrial+fmv_commercial)), color = "black")  +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
        high = "brown", low = "black",
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value = "gray50",
    name = "FMV",
    labels = scales::dollar
   ) +
    geom_sf(data = bound_tif, aes(geometry = geometry
              ), color = "black", alpha = 0.5, fill ="orange")

nbh_sum %>% 
filter(triad_name == "City") %>%
left_join(NBHs, by = c("nbhd_code" = "town_nbhd")) %>%

  ggplot() +
  geom_sf(aes(geometry = geometry, fill = fmv_incents_inTIF), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
        high = "brown", low = "black",
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value = "gray50",
    name = "FMV",
    labels = scales::dollar
   ) +
    geom_sf(data = bound_tif, aes(geometry = geometry#, fill = project_count
              ), color = "black", alpha = 0.5, fill ="orange")
```

```{r}
#| label: fig-industrial-commercial-fmv
#| layout-ncol: 2
#| column: page
#| fig-cap: 
#|      - "Pct of Industrial FMV that \nhas Incentive Classification"
#|      - "Pct of Commercial FMV that \nhas Incentive Classification"
#|      

nonres_pins2022 %>% 
  filter(Alea_cat == "Industrial") %>%
  filter(clean_name != "0_Unincorporated") %>%

  group_by(clean_name) %>% 
  summarize(pin_count = n(),
            project_count = n_distinct(keypin), 
            av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
            av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
            av=sum(av)) %>%
  mutate(av_incent = ifelse(av_incent == 0, NA, av_incent),
         pct_incent = av_incent / av_adjusted)  %>%
  select(-av_incent) %>% 
  select(clean_name, pct_incent, av, av_adjusted, pin_count, project_count) %>%
  left_join(nicknames) %>%
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  
  ggplot(aes(fill = pct_incent)) +
  geom_sf(aes(geometry = geometry), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
    high = "brown", low = "black",
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value="gray50",
    n =4,
    name = "Pct of Industrial FMV that \nhas Incentive Classification",
    labels = scales::percent
) +
   geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'gray40')

# nonres_pins2022 %>% 
#   filter(Alea_cat == "Commercial") %>%
#   group_by(clean_name) %>% 
#   summarize(pin_count = n(),
#             project_count = n_distinct(keypin), 
#             av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
#             av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
#             av=sum(av)) %>%
#   mutate(av_incent = ifelse(av_incent == 0, NA, av_incent), 
#                             pct_incent = av_incent / av_adjusted)  %>%
#   select(-av_incent) %>% 
#   select(clean_name, pct_incent, av, av_adjusted, pin_count, project_count) %>%
#   left_join(nicknames) %>%
#   full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
#   
#   ggplot(aes(fill = pct_incent)) +
#   geom_sf(aes(geometry = geometry), color = "black") +
#   theme_void() + 
#   theme(axis.ticks = element_blank(), axis.text = element_blank())+
#   scale_fill_steps2(
#     high = "orange", low = "black",
#      midpoint = 0.04,
#     show.limits=TRUE,
#     nice.breaks=FALSE,
#     na.value="gray",
#    # n =4,
#     name = "Pct of Commercial FMV that \nhas Incentive Classification",
#     labels = scales::percent
# ) +
#    geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, fill = "white", alpha = .5, color = 'gray40')

nonres_pins2022 %>% 
  filter(Alea_cat == "Commercial") %>%
  filter(clean_name != "Unincorporated") %>%

  group_by(clean_name) %>% 
  summarize(pin_count = n(),
            project_count = n_distinct(keypin), 
            av_incent = sum(ifelse(between(class, 600, 899), av*2.5, 0)),
            av_adjusted = sum(ifelse(between(class, 600, 899), av*2.5, av)),
            av=sum(av)) %>%
  mutate(av_incent = ifelse(av_incent == 0, NA, av_incent), 
                            pct_incent = av_incent / av_adjusted)  %>%
  select(-av_incent) %>% 
  select(clean_name, pct_incent, av, av_adjusted, pin_count, project_count) %>%
  left_join(nicknames) %>%
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  
  ggplot(aes(fill = pct_incent)) +
  geom_sf(aes(geometry = geometry), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
    high = "brown", low = "black",
    limits = c(0,.93),
    show.limits=TRUE,
    nice.breaks=FALSE,
    na.value="gray50",
    n =4,
    name = "Pct of Commercial FMV that \nhas Incentive Classification",
    labels = scales::percent
) +
   geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'gray40')
```

```{r}
#| label: tbl-incentiveproject-cooktotals
#| tbl-cap: "Commercial and Industrial Incentive Projects & PINs in Cook County. Includes property classes 500-899 and 100 level land PINs if they are associated with a project id that has at least one incentive PIN."

incentive_pins2022 <- ptax_pins %>% 
  group_by(keypin) %>%
  arrange(keypin, class) %>%
  mutate(min_class = first(class),
         max_class = last(class)) %>% 
  filter(max_class > 599) %>%
  ungroup()

table <- incentive_pins2022 %>% 
  group_by(Alea_cat) %>%
  summarize(proptype_pincount = n(),
            projects = n_distinct(keypin),
            av_incent = sum(if_else(incent_prop == "Incentive", av, 0)),
            av_nonincent = sum(ifelse(incent_prop == "Non-Incentive", av, 0)),
       #     av_adjusted=sum(ifelse(between(class, 600, 899), av*2.5, av)),
            av=sum(av)) %>%
  mutate(
         incent_av_adj = 2.5*av_incent ) %>%
  #mutate(
        # total_av_adjusted = incent_av_adj + av_nonincent,
       #  pct_incent = incent_av_adj / total_av_adjusted) %>%
  select(-c(av_incent, av_nonincent))

  

flextable(table) %>% 
 # set_caption("Totals for Cook County") %>% 
  set_header_labels(Alea_cat = "Property Type", proptype_pincount = 'PINs', projects = "Project IDs", av = ' Current Taxable AV') %>%
  set_table_properties( layout = "autofit", width = .75)

```

```{r}
#| label: tbl-incentivizedornot
#| tbl-cap: "Grouped by Incentivized or not within Municipality"

table2 <- nonres_pins2022 %>% 
    filter(Alea_cat != "Land") %>%
  group_by(clean_name, incent_prop) %>%   # projects can be counted twice if the project has incentive and normal commercial/industrial prop classes.
  summarize(pin_count = n(),
         project_count = n_distinct(keypin), 
         av_adjusted=sum(ifelse(between(class, 600, 899), av*2.5, av)),
         av=sum(av)) 

datatable(table2,
          rownames= FALSE,
          colnames = c('Municipality' = 'clean_name',   'Incentivized?' = 'incent_prop', 'PIN Count' = 'pin_count', 'Project Count' = 'project_count', 'Taxable AV' = 'av')) %>%
  formatCurrency(c('Taxable AV', 'av_adjusted'), digits = 0)
```

```{r}
#| label: fig-projectcountmaps-incentive
#| fig-cap: "Number of projects with Incentive Classification. Areas with hatch marks are municipalities that do not use incentive property classes. A project indicates grouped PINs that belong together (based on sharing an appeal ID from the BOR, prjoect IDs from the commercial valuation data, or determined by some other way to belong together.). Excludes Chicago, which has 240 projects." 



table2 %>% 
  left_join(nicknames) %>%
  filter(clean_name != "Chicago" & !is.na(clean_name)) %>%
  filter(clean_name != "Unincorporated") %>%

  filter(incent_prop  == "Incentive" &!is.na(clean_name)) %>%
  full_join(muni_shp, by = c("shpfile_name" = "MUNICIPALITY")) %>%
  
  ggplot() +
  geom_sf(aes(geometry = geometry, fill = project_count), color = "black") +
  theme_void() + 
  theme(axis.ticks = element_blank(), axis.text = element_blank())+
  scale_fill_steps2(
        high = "brown", low = "black",
show.limits=TRUE,
    breaks = c(0, 10, 50, 100, 150, 200),
    na.value = "gray40",
    name = "Number of Projects",
   ) +
   geom_sf_pattern(data = no_incentives, aes(geometry = geometry), pattern = 'crosshatch', pattern_spacing = 0.015, pattern_density = 0.1, fill = "white", alpha = .5, color = 'gray70')
```