BR2!

# Installing and loading required packages

if (!require("tidyverse"))
  install.packages("tidyverse")
if (!require("openxlsx"))
  install.packages("openxlsx")
if (!require("gtExtras"))
  install.packages("gtExtras")

library(tidyverse)
library(openxlsx)
library(gtExtras)

# Reading data from:
# https://www.huduser.gov/portal/datasets/fmr/smallarea/index.html#year2024

FMR <- read.xlsx(
  "https://www.huduser.gov/portal/datasets/fmr/fmr2024/fy2024_safmrs_revised.xlsx",
  sheet = 1)

# Making a list of Rutherford County ZIP codes

ZIPList <- c(
  "37127",
  "37128",
  "37129",
  "37130",
  "37132",
  "37085",
  "37118",
  "37149",
  "37037",
  "37153",
  "37167",
  "37086")

# Filtering for Rutherford ZIP codes and
# selecting columns of interest

FMR_RuCo <- FMR %>%
  filter(ZIP.Code %in% ZIPList) %>%
  select(ZIP.Code, SAFMR.0BR, SAFMR.1BR, SAFMR.2BR, SAFMR.3BR, SAFMR.4BR) %>% 
  distinct()

# Renaming the columns

colnames(FMR_RuCo) <- c("ZIP", "Studio", "BR1", "BR2", "BR3", "BR4")

# Averaging estimates

FMR_RuCo <- FMR_RuCo %>%
  mutate(ZIP_Average = (Studio + BR1 + BR2 + BR3 + BR4) / 5)

# Sorting in descending order by ZIP_Average

FMR_RuCo <- FMR_RuCo %>%
  arrange(desc(ZIP_Average))

# Showing the data as a table

FMR_RuCo_table <- gt(FMR_RuCo) %>%
  tab_header("Rutherford FMR, by size and ZIP") %>%
  cols_align(align = "left") %>%
  gt_theme_538

FMR_RuCo_table

Rutherford FMR, by size and ZIP
ZIP	Studio	BR1	BR2	BR3	BR4	ZIP_Average
37037	1660	1710	1920	2410	2940	2128
37086	1580	1620	1820	2290	2790	2020
37128	1510	1550	1740	2190	2670	1932
37129	1420	1460	1640	2060	2510	1818
37153	1410	1450	1630	2040	2490	1804
37167	1290	1330	1490	1870	2280	1652
37085	1260	1290	1450	1820	2210	1606
37127	1240	1270	1430	1800	2190	1586
37130	1180	1210	1360	1710	2080	1508
37132	1180	1210	1360	1710	2080	1508
37118	1100	1130	1270	1590	1960	1410
37149	1100	1130	1270	1590	1960	1410

# Finding the average of the BR2 values

Average_BR2 <- mean(FMR_RuCo$BR2)

Average_BR2

## [1] 1531.667

# Recoding 

FMR_RuCo <- FMR_RuCo %>%
  mutate(
    Rent_Category = case_when(
      BR2 > Average_BR2 ~ "Above BR2 average",
      BR2 == Average_BR2 ~ "BR2 Average",
      BR2 < Average_BR2 ~ "Below BR2 average",
      .default = "Error"))

FMR_RuCo_table <- gt(FMR_RuCo) %>% 
  tab_header("Rutherford FMR, by size and ZIP") %>%
  cols_align(align = "left") %>%
  gt_theme_538

FMR_RuCo_table

Rutherford FMR, by size and ZIP
ZIP	Studio	BR1	BR2	BR3	BR4	ZIP_Average	Rent_Category
37037	1660	1710	1920	2410	2940	2128	Above BR2 average
37086	1580	1620	1820	2290	2790	2020	Above BR2 average
37128	1510	1550	1740	2190	2670	1932	Above BR2 average
37129	1420	1460	1640	2060	2510	1818	Above BR2 average
37153	1410	1450	1630	2040	2490	1804	Above BR2 average
37167	1290	1330	1490	1870	2280	1652	Below BR2 average
37085	1260	1290	1450	1820	2210	1606	Below BR2 average
37127	1240	1270	1430	1800	2190	1586	Below BR2 average
37130	1180	1210	1360	1710	2080	1508	Below BR2 average
37132	1180	1210	1360	1710	2080	1508	Below BR2 average
37118	1100	1130	1270	1590	1960	1410	Below BR2 average
37149	1100	1130	1270	1590	1960	1410	Below BR2 average