Download monthly building permit data and summarize total and multi-family units for a given time period

bps.R

download_bps_monthly <- function(bps_year, bps_month) {
  bps_url <- paste0("https://www.census.gov/construction/bps/xls/cbsamonthly_",
                    bps_year,
                    bps_month,
                    ".xls")
  bps_dest <- paste0("data/bps/bps_",
                     bps_year,
                     bps_month,
                     ".xls")
  download.file(bps_url, mode = "wb", destfile = bps_dest)
}

years <- c(rep(2024, 9), rep(2025, 3))
months <- c("04",
            "05",
            "06",
            "07",
            "08",
            "09",
            "10",
            "11",
            "12",
            "01",
            "02",
            "03")

years_months <- data.frame(
  year = c(rep(2024, 9), rep(2025, 3)),
  month = c("04",
            "05",
            "06",
            "07",
            "08",
            "09",
            "10",
            "11",
            "12",
            "01",
            "02",
            "03")
)

walk2(years_months$year, years_months$month, download_bps_monthly)

bps_files <- list.files("data/bps/", full.names = T)

read_bps <- function(bps_file){
  read_excel(bps_file,
             skip = 7) |> 
    clean_names()
}

bps <- map_dfr(bps_files, read_bps)

bps |> 
  mutate(multi_unit = total_5 - x1_unit_6) |> 
  filter(name %in% c("Austin-Round Rock-San Marcos, TX",
                     "Madison, WI")
         ) |> 
  group_by(name) |> 
  summarize(total_units = sum(total_5), 
            multi_unit = sum(multi_unit))