tidy_rents.R

library(readxl)
library(tidyverse)
library(lubridate)
library(janitor)

rents_url <- "https://www.dffh.vic.gov.au/moving-annual-rents-suburb-june-quarter-2021-excel"
rents_loc <- tempfile(fileext = ".xlsx")

download.file(url = rents_url,
              destfile = rents_loc,
              mode = "wb")

read_sheet <- function(file, sheet) {
  raw_df <- suppressMessages(read_excel(file,
                       sheet = sheet,
                       skip = 1,
                       col_names = FALSE))

  dates <- raw_df[1, 3:ncol(raw_df)] |>
    pivot_longer(cols = everything(),
                 names_to = "col",
                 values_to = "date") |>
    fill(date)

  col_names <- raw_df |>
    filter(row_number() == 2) |>
    pivot_longer(everything(),
                 names_to = "col",
                 values_to = "series")

  tidy_df <- raw_df |>
    filter(row_number() > 2) |>
    fill(1) |>
    pivot_longer(cols = !c(1, 2),
                 names_to = "col")  |>
    left_join(col_names, by = "col") |>
    left_join(dates, by = "col") |>
    select(-col) |>
    rename(region = 1,
           suburb = 2) |>
    mutate(value = suppressWarnings(as.numeric(value)),
           date = lubridate::dmy(paste0("01 ", date)))

  tidy_df
}

sheets <- excel_sheets(rents_loc)

rents <- map_dfr(.x = sheets,
                 .f = read_sheet,
                 file = rents_loc)




# https://gist.github.com/ba66ae84829720530939eca4fd14a161