A3

library(readxl)
library(dplyr)

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

library(scales)
library(viridis)

## Loading required package: viridisLite

## 
## Attaching package: 'viridis'

## The following object is masked from 'package:scales':
## 
##     viridis_pal

df <- read_excel("5682001.xlsx",sheet = "Data1",skip = 10,col_names = FALSE)

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

df <- df[,1:14]
names(df) <- c("Month","Total","Goods","Services","Discretionary","NonDiscretionary","Total_Monthly",
               "Goods_Monthly","Services_Monthly","Discretionary_Monthly","NonDiscretionary_Monthly","Total_Annual",
               "Goods_Annual","Services_Annual")

df <- df %>% mutate(across(-Month, as.numeric))

df$Month <- as.Date(df$Month)

summary(df$Total)

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   39762   48794   54055   57629   68041   86199

class(df$Total)

## [1] "numeric"

sum(is.na(df$Month))

## [1] 0

head(df$Month, 20)

##  [1] "2012-07-01" "2012-08-01" "2012-09-01" "2012-10-01" "2012-11-01"
##  [6] "2012-12-01" "2013-01-01" "2013-02-01" "2013-03-01" "2013-04-01"
## [11] "2013-05-01" "2013-06-01" "2013-07-01" "2013-08-01" "2013-09-01"
## [16] "2013-10-01" "2013-11-01" "2013-12-01" "2014-01-01" "2014-02-01"

tail(df$Month, 20)

##  [1] "2024-09-01" "2024-10-01" "2024-11-01" "2024-12-01" "2025-01-01"
##  [6] "2025-02-01" "2025-03-01" "2025-04-01" "2025-05-01" "2025-06-01"
## [11] "2025-07-01" "2025-08-01" "2025-09-01" "2025-10-01" "2025-11-01"
## [16] "2025-12-01" "2026-01-01" "2026-02-01" "2026-03-01" "2026-04-01"

p1 <- ggplot(df, aes(Month, Total)) +geom_line(colour = "#D55E00",linewidth = 1.2) +
  labs(title = "Australian Household Spending Since 2012",subtitle = "Monthly Household Spending Indicator",
       x = "",y = "Spending ($ Million)") +scale_y_continuous(labels = comma) + theme_minimal()
ggplotly(p1)

goods_services <- df %>% select(Month,Goods,Services) %>% pivot_longer(-Month,names_to = "Type",values_to = "Spending")

p2 <- ggplot(goods_services,aes(Month,Spending,colour = Type)) +
  geom_line(linewidth = 1.2) + labs(
    title = "Services Spending Has Grown Faster Than Goods Spending",
    subtitle = "Australian household expenditure by spending type",
    x = "", y = "Spending ($ Million)") + scale_y_continuous(labels = comma) +
  theme_minimal()
ggplotly(p2)

essential <- df %>% select(Month,Discretionary,NonDiscretionary) %>%
  pivot_longer(-Month,names_to = "Category",values_to = "Spending")

p3 <- ggplot(essential,aes(Month,Spending,fill = Category)) +
  geom_area(alpha = 0.8) +
  labs(title = "Essential Spending Continues To Dominate Household Budgets",
       subtitle = "Discretionary versus non-discretionary spending",x = "",y = "Spending ($ Million)") +
  scale_y_continuous(labels = comma) +
  theme_minimal()
ggplotly(p3)

growth <- df %>% select(Month,Total_Monthly,Goods_Monthly,Services_Monthly,Discretionary_Monthly,NonDiscretionary_Monthly) %>%
  pivot_longer(-Month,names_to = "Measure",values_to = "Growth")

p4 <- ggplot(growth,aes(Month,Growth,colour = Measure)) +
  geom_line(linewidth = 1) +
  geom_hline(yintercept = 0,linetype = "dashed")+
  labs(title = "Monthly Spending Growth Is Uneven Across Categories",
       subtitle = "Monthly percentage change",x = "",y = "% Change") +
  theme_minimal()
ggplotly(p4)

library(lubridate)

## 
## Attaching package: 'lubridate'

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

df$Year <- year(df$Month)

p5 <- ggplot(
  df,
  aes(
    x = Goods,
    y = Services,
    size = Total,
    colour = Year
  )
) +

  geom_point(
    alpha = 0.7
  ) +

  scale_size_continuous(
    range = c(2, 12)
  ) +

  scale_x_continuous(
    labels = comma
  ) +

  scale_y_continuous(
    labels = comma
  ) +

  labs(
    title = "Relationship Between Goods, Services and Total Spending",
    subtitle = "Bubble size represents total household spending",
    x = "Goods Spending ($ Million)",
    y = "Services Spending ($ Million)",
    colour = "Year",
    size = "Total Spending"
  ) +

  theme_minimal()

ggplotly(p5)