# SETUP: load packages, read the three data sources, tidy and join them, and prepare the
# numbers used in the text. Everything here runs once, before any chart is drawn.
knitr::opts_chunk$set(echo = TRUE, message = FALSE, warning = FALSE)
library(tidyverse) # dplyr, tidyr, stringr, forcats, readr
library(readxl) # reads the DFFH Excel files
library(lubridate) # my() turns "Dec 2025" into a date
library(sf) # spatial data
library(arcgislayers) # downloads boundaries from the ABS web service
library(leaflet) # interactive map
library(plotly) # interactive charts
# 1. DATA SOURCES
# Source A: Department of Families, Fairness and Housing (DFFH), Rental Report, quarterly median
# rents by LGA (lga) and moving annual median rents by suburb group (suburb), to Dec 2025.
# File locations: the two .xlsx files in the data folder.
# Source B: ABS Census of Population and Housing 2021, table G02, Victorian LGAs (g02).
# File location: data/2021Census_G02_VIC_LGA.csv
# Source C: ABS ASGS Edition 3 (2021) LGA boundaries, downloaded from the ABS web service below.
# The first row of each rent sheet is only a title; the next two rows hold the dates and
# "Count"/"Median" labels, which tidy_rent() below uses to rebuild the columns.
lga <- read_excel("C:/Users/rdeep/Downloads/quarterly-median-rents-by-local-government-area_december-quarter-2025.xlsx",
sheet = "All Properties", col_names = FALSE,
skip = 1)
suburb <- read_excel("C:/Users/rdeep/Downloads/moving-annual-median-rent-by-suburb-town_december-quarter-2025.xlsx",
sheet = "All properties", col_names = FALSE,
skip = 1)
g02 <- read_csv("C:/Users/rdeep/Downloads/2021 Census GCP All Geographies for VIC/LGA/VIC/2021Census_G02_VIC_LGA.csv")
# 2. TIDY THE RENT SPREADSHEETS
# The DFFH sheets have two header rows (date, then Count/Median) and "-" for suppressed cells.
tidy_rent <- function(raw, name_col) {
dates <- unlist(raw[1, ], use.names = FALSE)
meas <- unlist(raw[2, ], use.names = FALSE)
dates <- fill(data.frame(d = dates), d, .direction = "down")$d
keep <- c(TRUE, TRUE, !is.na(meas[-(1:2)]))
raw <- raw[, keep]; dates <- dates[keep]; meas <- meas[keep]
names(raw) <- c("region", name_col, paste(dates[-(1:2)], meas[-(1:2)], sep = "|"))
raw[-(1:2), ] |>
fill(region, .direction = "down") |>
filter(!is.na(.data[[name_col]]), .data[[name_col]] != "Group Total") |>
pivot_longer(-c(region, all_of(name_col)),
names_to = "key", values_to = "value",
values_transform = list(value = as.character)) |>
separate(key, c("quarter", "measure"), sep = "\\|") |>
pivot_wider(names_from = measure, values_from = value) |>
mutate(date = my(quarter),
median = suppressWarnings(as.numeric(Median)), # "-" becomes NA (missing, not zero)
count = suppressWarnings(as.numeric(Count)))
}
rent_lga <- tidy_rent(lga, "lga")
rent_suburb <- tidy_rent(suburb, "suburb")
# 3. CENSUS INCOME AND BOUNDARIES
census <- g02 |>
select(LGA_CODE_2021,
income_2021 = Median_tot_hhd_inc_weekly,
rent_2021 = Median_rent_weekly) |>
mutate(lga_code_2021 = str_remove(LGA_CODE_2021, "^LGA"))
lga_layer <- arc_open("https://geo.abs.gov.au/arcgis/rest/services/ASGS2021/LGA/FeatureServer/1")
lga_sf <- arc_select(lga_layer, where = "state_name_2021 = 'Victoria'") |>
st_transform(4326)
# 4. JOIN RENT, INCOME AND BOUNDARIES
# Council names are spelled differently in the sources, so they are matched by a cleaned key.
clean <- function(x) x |> str_remove("\\s*\\(.*\\)") |> str_to_lower() |> str_trim()
latest <- rent_lga |>
filter(date == max(date)) |>
mutate(key = clean(lga)) |>
filter(!key %in% c("victoria", "metro", "non-metro")) |>
mutate(key = case_when(
key == "colac-otway" ~ "colac otway",
key == "merri-bek" ~ "moreland", # ABS 2021 boundaries still say Moreland
key == "mornington penin'a" ~ "mornington peninsula",
TRUE ~ key)) |>
select(key, region, rent_now = median, lettings = count)
d <- lga_sf |>
left_join(census, by = "lga_code_2021") |>
mutate(key = clean(lga_name_2021)) |>
left_join(latest, by = "key") |>
mutate(ratio = rent_now / income_2021)
d_map <- d |>
filter(!is.na(ratio)) |>
mutate(display_name = lga_name_2021 |>
str_remove("\\s*\\(Vic\\.\\)") |>
(\(x) if_else(x == "Moreland", "Merri-bek (was Moreland)", x))())
# 5. NUMBERS USED IN THE TEXT
pc <- function(x) round(x * 100) # ratio -> whole percent
shares <- d_map |> st_drop_geometry()
n_councils <- nrow(shares)
n_over30 <- sum(shares$ratio > 0.30) # councils above the 30% line
hi_ratio <- shares |> slice_max(ratio, n = 1, with_ties = FALSE)
lo_ratio <- shares |> slice_min(ratio, n = 1, with_ties = FALSE)
rent_at <- function(council, dt) { # median rent for one council and month
rent_lga |> filter(lga == council, date == as.Date(dt)) |> pull(median)
}
mel_now <- rent_at("Melbourne", "2025-12-01")
wyn_now <- rent_at("Wyndham", "2025-12-01")
mel_low <- rent_lga |>
filter(lga == "Melbourne", !is.na(median),
date >= as.Date("2019-01-01"), date <= as.Date("2022-12-01")) |>
slice_min(median, n = 1, with_ties = FALSE) # Melbourne's low point around 2020-21
sub_latest <- rent_suburb |> filter(date == max(date), !is.na(median)) |> arrange(median)
cheapest <- sub_latest |> slice_min(median, n = 1, with_ties = FALSE)
dearest <- sub_latest |> slice_max(median, n = 1, with_ties = FALSE)
gap <- dearest$median - cheapest$median
growth <- rent_lga |>
filter(date %in% as.Date(c("2021-12-01", "2025-12-01")), !is.na(median),
!str_detect(lga, regex("^(victoria|metro|non-metro|group total)$", ignore_case = TRUE))) |>
mutate(lga = recode(lga, "Mornington Penin'a" = "Mornington Peninsula"),
year = format(date, "%Y")) |>
select(lga, year, median) |>
pivot_wider(names_from = year, values_from = median, names_prefix = "y") |>
filter(!is.na(y2021), !is.na(y2025)) |>
mutate(pct = round((y2025 / y2021 - 1) * 100),
lga = fct_reorder(lga, pct))
top_growth <- growth |> slice_max(pct, n = 1, with_ties = FALSE)
n_growth <- nrow(growth)
# 6. SOURCE LINES shown under the charts
src_map <- "Data sources: DFFH Rental Report (December 2025); ABS Census of Population and Housing 2021; ABS ASGS Edition 3 LGA boundaries."
src_rent <- "Data source: Department of Families, Fairness and Housing (DFFH) Rental Report, December 2025."Rents in Victoria have increased rapidly, while household incomes have not moved at the same speed. This report shows the difference by comparing new rental prices in December 2025 with household incomes recorded in the 2021 Census for each council area. Using the standard 30% income threshold to identify potential housing stress, 54 out of 78 councils exceed this benchmark based on the comparison.
The story has five steps: a map highlighting areas under the rental pressure, a scatter plot showing the relationship between rent and income, line charts tracking rental trends over time, ranked dot plots comparing the most and least expensive areas, and a dumbbell chart showing how much rents have increased since 2021.
Rent takes the smallest share of income in Nillumbik (23%) and the largest in Mount Alexander (42%). Hover over a council to see its figures.
# Chart 1: map of rent as a share of household income
pal <- colorBin("viridis", domain = d_map$ratio,
bins = c(0, .20, .25, .30, .35, .40, 1), reverse = TRUE)
labels <- sprintf(
"<strong>%s</strong><br/>Median rent (Dec 2025): $%s per week<br/>Household income (2021): $%s per week<br/>Rent as share of income: %s%%",
d_map$display_name, d_map$rent_now, d_map$income_2021, round(d_map$ratio * 100, 1)
) |> lapply(htmltools::HTML)
map1 <- leaflet(d_map) |>
addProviderTiles(providers$Esri.WorldGrayCanvas) |>
addPolygons(fillColor = ~pal(ratio), weight = 1, color = "white", fillOpacity = 0.85,
label = labels,
highlightOptions = highlightOptions(weight = 3, color = "#333333", bringToFront = TRUE)) |>
addLegend(pal = pal, values = ~ratio, position = "bottomright",
title = "Rent as share of<br>household income",
labFormat = labelFormat(suffix = "%", transform = function(x) 100 * x)) |>
addControl("<b>Rent as a share of household income, by council</b><br><small>Median rent (Dec 2025) divided by median household income (2021)</small>",
position = "topright") |>
addControl("<small>Sources: DFFH Rental Report; ABS Census 2021; ABS ASGS Edition 3</small>",
position = "bottomleft")
map1