Two Bedroom Stats

Two Bedroom Stats

Two-bedroom stats, by rent category
Rent_Category	Count	Minimum	Average	Maximum
Above average	5	1800	1938	2180
Below average	7	1320	1471	1640

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

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

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

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

Average_ZIP_Average <- round(mean(FMR_RuCo$ZIP_Average),0)

Average_ZIP_Average

# ----------------------------------------------------------
# Recoding
# ----------------------------------------------------------

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

# ----------------------------------------------------------
# Grouping and summarizing
# ----------------------------------------------------------

Summary_BR2 <- FMR_RuCo %>% 
  group_by(Rent_Category) %>% 
  summarize(Count = n(),
            Minimum = min(BR2),
            Average = round(mean(BR2), 0),
            Maximum = max(BR2))

# ----------------------------------------------------------
# Displaying the new table
# ----------------------------------------------------------

Summary_BR2_table <- gt(Summary_BR2) %>%
  tab_header(title = "Two-bedroom stats, by rent category") %>%
  cols_align(align = "left")

Summary_BR2_table