---
title: "Part 1"
author: "Eric Langowski"
output: 
  html_document:
    code_folding: hide
    df_print: paged
    theme: sandstone
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(warning = FALSE)
library(tidyverse)
library(lubridate)
library(tidymodels)
theme_set(theme_bw())

if(file.exists("database/cook.sqlite")){
  f <- "database/cook.sqlite"
} else {
  f <- "static/solutions/database/cook.sqlite"
}

con <- DBI::dbConnect(RSQLite::SQLite(), f)

assessments <- dplyr::tbl(con, 'assessments') %>% collect()
sales <- dplyr::tbl(con, 'sales') %>% collect() %>% mutate(sale_date = as_datetime(sale_date))
characteristics <- dplyr::tbl(con, 'characteristics') %>% collect()
geo_uni <- dplyr::tbl(con, 'geospatial_universe') %>% collect()

joined <- sales %>%
  mutate(year = year(ymd(sale_date))) %>%
  filter(sale_filter_less_than_10k == 0,
         sale_filter_deed_type == 0,
         is_multisale == 0) %>%
  left_join(assessments %>% select(-township_code, -class), 
            join_by('pin', 'year'=='tax_year'))

ratios <- cmfproperty::reformat_data(
  joined %>% mutate(assessed_value = if_else(year == 2023, 10*certified_tot, 10*board_tot)),
  'sale_price',
  'assessed_value',
  'year'
)

min_yr <- 2021
max_yr <- 2023


stats <- cmfproperty::calc_iaao_stats(ratios)

output <- cmfproperty::diagnostic_plots(ratios = ratios,
                                        stats = stats, 
                                        min_reporting_yr = min_yr,
                                        max_reporting_yr = max_yr)
```

# Introduction

Cook County, Illinois includes almost 2 million parcels ranging from suburban mansions to thousand feet skyscrapers. The system is administered by a series of countywide agencies and it is responsible for fairly dividing property tax levies (the amounts that taxing bodies such as school districts levy against properties in their districts) among all these properties.

In the past, assessments in Cook County were highly regressive, where lower valued properties paid a significantly higher tax rate than higher valued properties. A study by the Center for Municipal Finance at the University of Chicago showed that from 2011 to 2015 that these regressive assessments lead to over $2 billion of property tax liability being paid by lower valued homes instead of higher valued ones.

This study, alongside a public reckoning, resulted in the election of a new assessor in 2018.

```{r}
output[[2]]
```

This analysis focuses on single family homes Additionally, using the `cmfproperty` package, the IAAO arm's length standard was applied to the data. This will present a conservative picture of the housing market in Cook County. Note that homes in Cook County as supposed to be assessed at 10% of their fair market value. For clarity, assessed values here are converted to market values.

```{r}
output[[1]]
```

## Data Issues

At the time of this analysis, final assessed values were not released in 2023 and sales information was only available through September 2023.

```{r}
joined %>% count(week=floor_date(sale_date, 'month')) %>%
  ggplot(aes(x=week, y=n)) +
  geom_bar(stat='identity') +
  labs(x='Month', y='Sales', title='Monthly Sales')
```

# Sales Ratio Analysis

The sales ratio is a key unit for analyzing the accuracy of assessments. It is calculated by taking the ratio of a property's sale price to its assessment at the time of sale. The sales ratios can be evaluated using metrics from the International Association of Assessing Officers.


```{r}
iaao <- cmfproperty::iaao_graphs(stats=stats, ratios=ratios, min_reporting_yr = min_yr, max_reporting_yr = max_yr, jurisdiction_name = 'Cook County')
```

`r iaao[[1]]`

```{r}
iaao[[2]]
```

`r iaao[[3]]`


```{r}
iaao[[4]]
```

`r iaao[[5]]`

```{r}
iaao[[6]]
```

```{r}
bs <- cmfproperty::binned_scatter(ratios = ratios, min_reporting_yr = min_yr, max_reporting_yr = max_yr, jurisdiction_name = 'Cook County')
```

`r bs[[1]]`

```{r}
bs[[2]]
```

# Initial Relationships

## Property Sales

```{r}
ratios_chars <- 
  ratios %>%
  left_join(characteristics %>% select(-township_code, -class) %>% distinct(), by=c('pin')) %>%
  mutate(year = as.character(TAX_YEAR))

lm_model <-
  parsnip::linear_reg() %>%
  set_engine("lm") %>%
  set_mode('regression')

first_recipe <- recipe(SALE_PRICE_ADJ ~
                         char_type_resd + char_bldg_sf +
                         year, data=ratios_chars) %>%
  step_unknown(all_nominal_predictors()) %>%
  step_dummy(all_nominal_predictors())

first_workflow <-
  workflow() %>%
  add_model(lm_model) %>%
  add_recipe(first_recipe)

model_fit <- first_workflow %>%
  fit(data=ratios_chars)

model_fit %>% glance()
model_fit %>% tidy()
```

