2/9/26 Class Excersice

Rutherford FMR, by size and ZIP
ZIP	Studio	BR1	BR2	BR3	BR4	ZIP_Average	Rent_Category
37037	1900	1990	2180	2790	3400	2452	Above BR2 average
37086	1730	1820	1990	2540	3100	2236	Above BR2 average
37153	1670	1750	1920	2450	2990	2156	Above BR2 average
37128	1570	1640	1800	2300	2800	2022	Above BR2 average
37129	1570	1640	1800	2300	2800	2022	Above BR2 average
37167	1430	1500	1640	2100	2560	1846	Below BR2 average
37127	1360	1420	1560	1990	2430	1752	Below BR2 average
37085	1320	1380	1520	1940	2360	1704	Below BR2 average
37130	1280	1340	1470	1880	2290	1652	Below BR2 average
37132	1280	1340	1470	1880	2290	1652	Below BR2 average
37149	1150	1180	1320	1660	2020	1466	Below BR2 average
37118	1150	1170	1320	1660	2020	1464	Below BR2 average

CODE:

# ----------------------------------------------------------
# Install & load required packages
# ----------------------------------------------------------

if (!require("tidyverse"))
  install.packages("tidyverse")
if (!require("gt"))
  install.packages("gt")

library(tidyverse)
library(readxl)
library(gt)

# ----------------------------------------------------------
# Download HUD SAFMR Excel file
# ----------------------------------------------------------

download.file(
  "https://www.huduser.gov/portal/datasets/fmr/fmr2026/fy2026_safmrs.xlsx",
  "rent.xlsx",
  mode = "wb"
)

# ----------------------------------------------------------
# Read Excel data
# ----------------------------------------------------------

FMR <- read_xlsx(path = "rent.xlsx", .name_repair = "universal")

# ----------------------------------------------------------
# Rutherford County ZIP Codes
# ----------------------------------------------------------

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

# ----------------------------------------------------------
# Filter, select columns, and rename
# ----------------------------------------------------------

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

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

# ----------------------------------------------------------
# Adding each ZIP code's average
# ----------------------------------------------------------

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

FMR_RuCo <- FMR_RuCo %>%
  mutate(BR2 = round(BR2,0))

# ----------------------------------------------------------
# Sorting by ZIP_Average, in descending order
# ----------------------------------------------------------

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

# ----------------------------------------------------------
# Averaging the ZIP code averages
# ----------------------------------------------------------

Average_BR2 <- round(mean(FMR_RuCo$BR2),0)

Average_BR2

# ----------------------------------------------------------
# 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"))

# ----------------------------------------------------------
# Displaying the table, with the new columns
# ----------------------------------------------------------

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

FMR_RuCo_table