To generate the report, first Download Data.This dataset is a raw public dataset we can use.

After downloading the dataset, please run the below R code, for example in RStudio. Remember to replace “your output directory” with the path where you want to save the generated report.

library(tidyverse)
library(data.table)
library(readstata13)
library(cmfproperty)
require(lubridate)

#st louis county new data
cur_file_loc <- “new data/2020-stlco-assessments/”

yrs <- 2009:2019
for (yr in yrs){if(yr %in% c(2011, 2012,2019)){ext <- “csv”} else {ext <- “txt”}
if(yr %in% c(2009, 2010, 2011, 2012)){name <- “ASSESSMENT”} else {name <- “assessment”}

  if (yr %in% c(2013)){assessment <- read_delim(paste0(cur_file_loc, yr, “/”, name, “.”, ext), delim=”,”)} else {assessment <- read_delim(paste0(cur_file_loc, yr, “/”, name, “.”, ext), delim=”|”)}
fwrite(assessment, “new data/assessments.csv”, append=TRUE)}

yrs <- 2019:2009
for (yr in yrs){if(yr %in% c(2011,2019)){ext <- “csv”} else {ext <- “txt”}
if(yr %in% c(2009, 2010, 2011, 2012)){name <- “TAXDATA”} else {name <- “taxdata”}
if (yr %in% c(2009, 2010, 2012)){
tax <- read.table(paste0(cur_file_loc, yr, “/”, name, “.”, ext), fill=TRUE)
tax <- tax %>% filter(V1 == “REAL”)} else if (yr %in% c(2013)){
tax <- read_delim(paste0(cur_file_loc, yr, “/”, name, “.”, ext), delim=”,”)} else {
tax <- read_delim(paste0(cur_file_loc, yr, “/”, name, “.”, ext), delim=”|”)}

fwrite(tax, “new data/taxes.csv”, append=TRUE)}

assessments <- fread(“new data/assessments.csv”)
taxes <- read_csv(“new data/taxes.csv”) %>% select(PARID, TAXYR, VALCLASS, TAXAMOUNT, OTHERTAX)

sales <- read_delim(paste0(cur_file_loc, “/2019/sales.csv”), delim=”|”)
sales$SALEDT <- dmy(sales$SALEDT)
sales <- sales %>% filter(!is.na(PRICE))
sales <- sales %>% mutate(SALE_YEAR = year(SALEDT))
mini_sales <- sales %>% select(PARID, SALE_YEAR, PRICE, SALETYPE, SALEVAL) %>% filter(between(SALE_YEAR, 2005, 2020), PRICE > 0)

joined <- assessments %>% filter(TAXCODE!=””) %>% select(-VALCLASS) %>% left_join(taxes)
mini_joined <- joined %>% select(PARID, TAXYR, CLASS, LUC, APRTOT, ASMTOT, TAXTOT, TAXAMOUNT, OTHERTAX)
mini_joined <- mini_joined %>% left_join(mini_sales, by=c(“PARID”, “TAXYR”=”SALE_YEAR”))

fwrite(mini_joined, “new data/fullroll.csv”)

sold_only <- mini_joined %>% filter(!is.na(PRICE))
sold_only <- sold_only %>% mutate(arms_length_transaction = ifelse(SALEVAL == “X”, 1, 0))
fwrite(sold_only, “new data/joined.csv”)

df <- fread(“new data/fullroll.csv”)

df <- df %>% mutate(SALE_PRICE = APRTOT, SALE_YEAR = TAXYR, TAX_YEAR = TAXYR, ASSESSED_VALUE = ASMTOT)

df <- df[c(‘SALE_PRICE’, ‘SALE_YEAR’, ‘TAX_YEAR’, ‘ASSESSED_VALUE’)]

ratios <- cmfproperty::reformat_data(df, sale_col = “SALE_PRICE”, assessment_col = “ASSESSED_VALUE”, sale_year_col = “SALE_YEAR”)