# Load required libraries
library(readxl)
library(dplyr)
library(tidyr)
library(ggplot2)
library(plotly)

# Load the data
df <- read_excel("62380_Table01.xlsx",sheet = "Data 1.1",skip = 7)

# Rename key columns
names(df)[1:4] <- c("Year","State","Sex","Category")

# Rename final column as Total
names(df)[41] <- "Total"

# Data Pre-processing like text fields to character format,Remove extra space and only Australian-level data
df <- df %>%
  mutate(
    Year = trimws(as.character(Year)),
    State = trimws(as.character(State)),
    Sex = trimws(as.character(Sex)),
    Category = trimws(as.character(Category)),
    Total = as.numeric(Total)
  ) %>%
  filter(!is.na(Year),State == "Australia")

# Define latest
latest_year <- "2024-25"

Visualisation 2: Older Workers Remaining in the Workforce

age_trend <- df %>%
  filter(
    Sex == "Persons",
    Category == "In the labour force"
  ) %>%
  select(
    Year,
    `45-49` = `\'000...5`,
    `55-59` = `\'000...11`,
    `65-69` = `\'000...17`,
    `70+` = `\'000...20`
  ) %>%
  pivot_longer(
    -Year,
    names_to = "Age_Group",
    values_to = "Population"
  )

# Create multi-line chart
p2 <- ggplot(
  age_trend,
  aes(
    x = Year,
    y = Population,
    colour = Age_Group,
    group = Age_Group
  )
) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 2) +
  labs(
    title = "Workforce is remained longer by many older Australians",
    x = "Year",
    y = "People ('000)"
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(
      angle = 45,
      hjust = 1
    )
  )

ggplotly(p2)

Visualisation 3: Retirement Transition Preferences

p3 <- df %>%
  filter(
    Year == latest_year,
    Sex == "Persons",
    Category %in% c(
      "Continue full-time until retirement",
      "Change to part-time before retirement",
      "Intends to retire from full-time and work part-time"
    )
  ) %>%
  ggplot(
    aes(
      x = reorder(Category, Total),
      y = Total,
      fill = Category
    )
  ) +
  geom_col(width = 0.7) +
  labs(
    title = "Shifting the retirement with a gradual transition / Shifting the retirement from single event to a phased journey",
    x = "",
    y = "People ('000)"
  ) +
  theme_minimal() +
  theme(
    legend.position = "none",
    axis.text.x = element_text(
      angle = 45,
      hjust = 1
    )
  )

ggplotly(p3)

Visualisation 4: Gender Differences in Retirement

p4 <- df %>%
  filter(
    Year == latest_year,
    Sex %in% c("Males", "Females"),
    Category %in% c(
      "In the labour force",
      "Retired from the labour force"
    )
  ) %>%
  ggplot(
    aes(
      x = Sex,
      y = Total,
      fill = Category
    )
  ) +
  geom_col(position = "dodge") +
  labs(
    title = "Different retirement journey is experienced by Men and Women",
    x = "",
    y = "People ('000)"
  ) +
  theme_minimal() +
  theme(
    legend.position = "top"
  )

ggplotly(p4)

Visualisation 5: Delayed or Uncertain Retirement Intentions

p5 <- df %>%
  filter(
    Year == latest_year,
    Sex == "Persons",
    Category %in% c(
      "Intends to continue working full-time",
      "Never intends to retire from full-time work",
      "Continue full-time, unsure if will retire"
    )
  ) %>%
  ggplot(
    aes(
      x = reorder(Category, Total),
      y = Total,
      fill = Category
    )
  ) +
  geom_col(width = 0.7) +
  labs(
    title = "Retirement may never be completed fully for some Australians",
    x = "",
    y = "People ('000)"
  ) +
  theme_minimal() +
  theme(
    legend.position = "none",
    axis.text.x = element_text(
      angle = 45,
      hjust = 1
    )
  )

ggplotly(p5)