library(data.table)
library(tidyverse)

# ludwig institute data
ludwig = readxl::read_xlsx("/Users/admin/Downloads/653a82ee789cb5e11330cb7f_TLC Data 2022.xlsx",
                           sheet = "Inflation by expense category", range = "E1:M23")

# HUD FMR data
hud_fmr = fread("/Users/admin/Downloads/FMR_2Bed_1983_2024(1).csv")
hud_fmr_long = pivot_longer(hud_fmr %>% select(matches("fmr[0-9]+_"),
                                               fips, areaname24, pop2010),
                            cols = contains("fmr"))

setDT(hud_fmr_long)
hud_fmr_long[,year := as.numeric(str_extract(name, "[0-9]+"))]
# fix the year variable from pivoting
hud_fmr_long[,year := fifelse(year < 25, year + 2000, year + 1900)]
# population weighted average of rent prices
# this is a dumb way of aggregating but whatever
hud_fmr_agg = hud_fmr_long[,weighted.mean(value, na.rm = T, w = pop2010), by = year ]
# make percent change
hud_fmr_agg[,p_change := (V1 - V1[year == 2001]) / V1[year == 2001]][]

# cpi data on housing
cpi = fread("/Users/admin/Downloads/CUSR0000SEHA.csv")
cpi[,year := as.numeric(str_extract(DATE, "[0-9]{4}"))]
# take simple average over the year. also kinda dumb but whatever
cpi_agg =  cpi[, list(CUSR0000SEHA_NBD20010301 = mean(CUSR0000SEHA_NBD20010301)),
               by = year]
# scale so 2001 = 0
cpi_agg[,cpi_p_change := CUSR0000SEHA_NBD20010301/100 - 1]

# merge stuff together
hud_fmr_agg = cpi_agg %>%
  merge( hud_fmr_agg, by = 'year') %>%
  merge( ludwig, by = 'year')

# make the pretty graph
ggplot(hud_fmr_agg[year >= 2001], aes(x = year, group = 1)) +
  geom_line(aes(y = cpi_p_change, color = "BLS Data: Rent of Primary Residence")) +
  geom_line(aes(y = p_change, color = "HUD FMR Data")) +
  geom_line(aes(y = increasehousing, color = "Ludwig Data")) +
  labs(y = "Percent Change Since 2001", x = "Year", title  = "Rent Inflation Since 2001")