# Load required libraries
library(tidyverse)
library(readxl)
library(plotly)
library(lubridate)
library(scales)
clean_plotly <- function(p) {
ggplotly(p, tooltip = "text") %>%
layout(
margin = list(t = 80)
) %>%
config(displayModeBar = TRUE)
}
chart_theme <- theme_minimal(base_size = 13) +
theme(
plot.title = element_text(size = 16, face = "bold", hjust = 0),
plot.subtitle = element_text(size = 11),
plot.caption = element_text(size = 9, colour = "grey40"),
legend.position = "bottom",
panel.grid.minor = element_blank(),
plot.margin = margin(20, 25, 20, 25)
)
# Load all dataset
student_file <- "34070DO004_202425.xlsx"
rent_file <- "13. Rental affordability, lower income renter households, national housing and homelessness agreement basis.xlsx"
cpi_file <- "ABS,CPI,2.0.0+all.csv"
student_raw <- read_csv(student_file, show_col_types = FALSE)
cpi_raw <- read_csv(cpi_file, show_col_types = FALSE)
rent_raw <- read_excel(rent_file, sheet = "Table 13.1", col_names = FALSE)
# Clean and prepare student visa arrivals data from ABS Excel tables
read_student_sheet <- function(sheet_name, state_name) {
raw <- read_excel(student_file, sheet = sheet_name, col_names = FALSE)
header_row <- which(raw[[1]] == "Direction")[1]
year_cols <- 4:ncol(raw)
years <- as.character(unlist(raw[header_row, year_cols]))
student_row <- raw |>
mutate(row_number = row_number()) |>
filter(row_number > header_row) |>
filter(str_detect(as.character(...1), "Overseas migrant arrivals") | as.character(...3) == "Student") |>
fill(...1, .direction = "down") |>
filter(str_detect(as.character(...1), "Overseas migrant arrivals")) |>
filter(as.character(...3) == "Student")
values <- student_row[1, year_cols] |>
unlist(use.names = FALSE) |>
as.numeric()
tibble(
state = state_name,
year_label = years,
year = as.numeric(str_sub(years, 1, 4)) + 1,
arrivals = values
)
}
student_clean <- bind_rows(
read_student_sheet("Table 4.1", "Australia"),
read_student_sheet("Table 4.2", "New South Wales"),
read_student_sheet("Table 4.3", "Victoria"),
read_student_sheet("Table 4.4", "Queensland"),
read_student_sheet("Table 4.5", "South Australia"),
read_student_sheet("Table 4.6", "Western Australia"),
read_student_sheet("Table 4.7", "Tasmania")
)
# Clean and prepare rental affordability data
rent_estimates <- rent_raw[10:16, c(1, 3:11)]
colnames(rent_estimates) <- c("financial_year", "NSW", "Vic", "Qld", "SA", "WA", "Tas", "NT", "ACT", "Australia")
rent_long <- rent_estimates %>%
pivot_longer(
cols = -financial_year,
names_to = "state",
values_to = "rental_stress"
) %>%
mutate(
rental_stress = as.numeric(rental_stress),
year_start = as.integer(str_sub(financial_year, 1, 4)),
state = recode(state, "Vic" = "Victoria", "Tas" = "Tasmania")
) %>%
filter(!is.na(rental_stress))
rent_focus <- rent_long %>%
filter(state %in% c("NSW", "Victoria", "Qld", "WA", "Tasmania"))
# Clean and prepare CPI data
cpi_categories <- c(
"Housing",
"Rents",
"Food and non-alcoholic beverages",
"Transport",
"Education"
)
cpi_quarterly <- cpi_raw %>%
filter(
Region == "Australia",
Measure == "Index numbers",
Frequency == "Quarterly",
`Adjustment Type` == "Original",
Index %in% cpi_categories,
str_detect(TIME_PERIOD, "Q")
) %>%
mutate(
year = as.integer(str_sub(TIME_PERIOD, 1, 4)),
quarter = as.integer(str_sub(TIME_PERIOD, -1, -1)),
date = make_date(year, (quarter - 1) * 3 + 1, 1),
category = case_when(
Index == "Food and non-alcoholic beverages" ~ "Food",
TRUE ~ as.character(Index)
),
index_value = as.numeric(OBS_VALUE)
) %>%
filter(
year >= 2018,
!is.na(date),
!is.na(index_value)
) %>%
select(date, year, quarter, category, index_value) %>%
arrange(category, date) %>%
distinct(category, date, .keep_all = TRUE)
cpi_growth <- cpi_quarterly %>%
group_by(category) %>%
arrange(date) %>%
mutate(
base_value = first(index_value),
rebased_index = index_value / base_value * 100,
growth_since_start = (index_value / base_value - 1) * 100
) %>%
ungroup()
cpi_latest_growth <- cpi_growth %>%
group_by(category) %>%
filter(date == max(date)) %>%
summarise(
growth_since_start = max(growth_since_start, na.rm = TRUE),
date = max(date),
.groups = "drop"
) %>%
arrange(desc(growth_since_start))
student_total <- student_clean |>
filter(state == "Australia")
p1 <- ggplot(
student_total,
aes(
x = year,
y = arrivals,
text = paste0(
"Year: ", year,
"<br>Student visa arrivals: ", comma(arrivals)
)
)
) +
geom_line(aes(group = 1), linewidth = 1.2) +
geom_point(size = 2) +
scale_x_continuous(breaks = seq(min(student_total$year), max(student_total$year), by = 2)) +
scale_y_continuous(labels = comma) +
labs(
title = "Student arrivals rebounded after the pandemic drop",
x = "Year",
y = "Arrivals",
) +
chart_theme +
theme(plot.title = element_text(size = 13, face = "bold", hjust = 0.5),legend.position = "none")
clean_plotly(p1)
student_states <- student_clean |>
filter(state %in% c(
"New South Wales",
"Victoria",
"Queensland",
"South Australia",
"Western Australia"
))
student_states <- student_states |>
mutate(
state_short = recode(
state,
"New South Wales" = "NSW",
"Victoria" = "VIC",
"Queensland" = "QLD",
"South Australia" = "SA",
"Western Australia" = "WA"
)
)
p2 <- ggplot(
student_states,
aes(
x = year,
y = arrivals,
colour = state_short,
group = state_short,
text = paste0(
"Year: ", year,
"<br>State: ", state_short,
"<br>Student visa arrivals: ", comma(arrivals)
)
)
) +
geom_line(linewidth = 1) +
geom_point(size = 1.4) +
scale_x_continuous(breaks = seq(min(student_states$year), max(student_states$year), by = 4)) +
scale_y_continuous(labels = comma) +
labs(
title = "Student arrivals are concentrated in NSW and Victoria",
x = "Year",
y = "Arrivals",
colour = NULL,
) +
chart_theme +
theme(plot.title = element_text(size = 13, face = "bold", hjust = 0.5))
clean_plotly(p2)
p3 <- ggplot(
rent_focus,
aes(
x = year_start,
y = rental_stress,
colour = state,
group = state,
text = paste0(
"Year: ", financial_year,
"<br>State: ", state,
"<br>Rental stress: ", rental_stress, "%"
)
)
) +
geom_line(linewidth = 1) +
geom_point(size = 2) +
scale_x_continuous(breaks = sort(unique(rent_focus$year_start))) +
scale_y_continuous(labels = function(x) paste0(x, "%")) +
labs(
title = "Rental stress affects many lower-income renters",
subtitle = "Share paying more than 30% of income on housing costs, selected states",
x = "Year",
y = "Rental stress rate",
colour = NULL,
caption = "Source: ABS Housing Occupancy and Costs, Table 13.1"
) +
chart_theme +
theme(plot.title = element_text(size = 13, face = "bold", hjust = 0.5))
clean_plotly(p3)
p4 <- ggplot(
cpi_growth,
aes(
x = date,
y = rebased_index,
colour = category,
group = category,
text = paste0(
"Quarter: ", paste0(year, " Q", quarter),
"<br>Category: ", category,
"<br>Index: ", round(rebased_index, 1)
)
)
) +
geom_line(linewidth = 1.1) +
geom_point(size = 1.3) +
scale_x_date(date_breaks = "1 year", date_labels = "%Y") +
labs(
title = "Essential living costs have risen since 2018",
subtitle = "Quarterly CPI index values, rebased to 2018 Q1 = 100",
x = "Year",
y = "CPI Index",
colour = NULL,
caption = "Source: ABS Consumer Price Index, Australia"
) +
chart_theme +
theme(plot.title = element_text(size = 13, face = "bold", hjust = 0.5))
clean_plotly(p4)
p5 <- ggplot(
cpi_latest_growth,
aes(
x = reorder(category, growth_since_start),
y = growth_since_start,
text = paste0(
"Category: ", category,
"<br>Latest quarter: ", format(date, "%b %Y"),
"<br>Growth since 2018 Q1: ", round(growth_since_start, 1), "%"
)
)
) +
geom_col(width = 0.7) +
coord_flip() +
scale_y_continuous(labels = function(x) paste0(x, "%")) +
labs(
title = "Housing and education costs have increased the most",
subtitle = "Percentage growth in essential CPI categories since 2018 Q1",
x = NULL,
y = "Growth",
caption = "Source: ABS Consumer Price Index, Australia"
) +
chart_theme +
theme(plot.title = element_text(size = 13, face = "bold", hjust = 0.5),legend.position = "none")
clean_plotly(p5)
Australian Bureau of Statistics. (2025). Overseas migration and visa statistics: International student visa holders [Data set]. https://www.abs.gov.au/statistics/people/population/overseas-migration/latest-release
Australian Bureau of Statistics. (2026). Consumer Price Index, Australia [Data set]. https://www.abs.gov.au/statistics/economy/price-indexes-and-inflation/consumer-price-index-australia/latest-release
Australian Bureau of Statistics. (2022). Housing occupancy and costs, Australia [Data set]. https://www.abs.gov.au/statistics/people/housing/housing-occupancy-and-costs/latest-release