A3

library(readxl)
library(dplyr)

## Warning: package 'dplyr' was built under R version 4.3.3

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(tidyr)
library(ggplot2)
library(plotly)

## 
## Attaching package: 'plotly'

## The following object is masked from 'package:ggplot2':
## 
##     last_plot

## The following object is masked from 'package:stats':
## 
##     filter

## The following object is masked from 'package:graphics':
## 
##     layout

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

## New names:
## • `'000` -> `'000...5`
## • `%` -> `%...6`
## • `RSE` -> `RSE...7`
## • `'000` -> `'000...8`
## • `%` -> `%...9`
## • `RSE` -> `RSE...10`
## • `'000` -> `'000...11`
## • `%` -> `%...12`
## • `RSE` -> `RSE...13`
## • `'000` -> `'000...14`
## • `%` -> `%...15`
## • `RSE` -> `RSE...16`
## • `'000` -> `'000...17`
## • `%` -> `%...18`
## • `RSE` -> `RSE...19`
## • `'000` -> `'000...20`
## • `%` -> `%...21`
## • `RSE` -> `RSE...22`
## • `'000` -> `'000...23`
## • `%` -> `%...24`
## • `RSE` -> `RSE...25`
## • `'000` -> `'000...26`
## • `%` -> `%...27`
## • `RSE` -> `RSE...28`
## • `'000` -> `'000...29`
## • `%` -> `%...30`
## • `RSE` -> `RSE...31`
## • `'000` -> `'000...32`
## • `%` -> `%...33`
## • `RSE` -> `RSE...34`
## • `'000` -> `'000...35`
## • `%` -> `%...36`
## • `RSE` -> `RSE...37`
## • `'000` -> `'000...38`
## • `%` -> `%...39`
## • `RSE` -> `RSE...40`
## • `'000` -> `'000...41`
## • `%` -> `%...42`
## • `RSE` -> `RSE...43`

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

names(df)[41] <- "Total"

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

latest_year <- "2024-25"

p1 <- df %>%
  filter(
    Sex == "Persons",
    Category %in% c(
      "In the labour force",
      "Retired from the labour force"
    )
  ) %>%
  ggplot(
    aes(
      x = Year,
      y = Total,
      colour = Category,
      group = Category
    )
  ) +
  geom_line(linewidth = 1.3) +
  geom_point(size = 3) +
  labs(
    title = "Working lives are extended for Australians with late retirement ",
    x = "Year",
    y = "People ('000)"
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(
      angle = 45,
      hjust = 1
    )
  )

ggplotly(p1)

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

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)

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)

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)

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)