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

Chart 1: Australia continues to attract international students

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)

Chart 2: Student arrivals are concentrated in major states

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)

Chart 3: Rental stress remains high

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)

Chart 4: Essential living costs are rising

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)

Chart 5: Which costs have grown fastest?

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)

References

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