SA4 employment figures by QLD region

employment-QLD

library(grattanCharts)  # devtools::install_github('hughparsonage/grattanCharts')
library(dplyr)
library(httr)
library(tidyr)
library(readxl)
library(scales)
library(ggplot2)
library(data.table)
library(lubridate)
library(hutils)

SA2016_decoder <- fread("https://raw.githubusercontent.com/HughParsonage/ABS-data/master/SA2016_decoder.csv")

temp.xlsx <- tempfile(fileext = ".xlsx")

GET("https://docs.employment.gov.au/system/files/doc/other/salm_smoothed_sa2_datafiles_-_march_quarter_2017.xlsx",
    write_disk(temp.xlsx, overwrite = TRUE))

Queensland_labour_data <-
  read_excel(temp.xlsx,
             sheet = "Smoothed SA2 labour force",
             skip = 2) %>%
  gather(Date, Labour_force, -`Statistical Area Level 2 (SA2)`, -`SA2 Code`) %>%
  mutate(Date = as.Date("1900-01-01") + days(Date)) %>%
  setnames(c("Statistical Area Level 2 (SA2)", "SA2 Code"),
           c("SA2_NAME16", "SA2_MAIN16")) %>%
  inner_join(SA2016_decoder, by = c("SA2_NAME16", "SA2_MAIN16")) %>%
  filter(STE_NAME16 == "Queensland")

Jobs_added_by_Region <- 
  Queensland_labour_data %>%
  group_by(SA2_NAME16) %>%
  mutate(Jobs_added = Labour_force - first(Labour_force)) %>%
  group_by(GCC_NAME16, SA4_NAME16, Date) %>%
  summarise(Jobs_added = sum(Jobs_added)) %>%
  mutate(Region = if_else(SA4_NAME16 %pin% "Brisbane",
                          "Brisbane",
                          if_else(SA4_NAME16 %pin% c("Ipswich",
                                                     "Gold Coast",
                                                     "Sunshine Coast",
                                                     "Moreton Bay", 
                                                     "Beaudesert"),
                                  "Outer Brisbane",
                                  if_else(SA4_NAME16 %pin% c("Toowoomba",
                                                             "Townsville",
                                                             "Cairns"),
                                          "Regional city",
                                          "Rest of Qld")))) 
Jobs_added_by_Region %>%
  mutate(label = if_else(1:n() == n(),
                         gsub("Brisbane - (.*)$", "\\1 BNE", SA4_NAME16),
                         NA_character_),
         label = if_else(grepl("Brisbane.*Inner", label), "Inner BNE", label),
         label = gsub("Moreton Bay - ([NS])(or|ou)(th)", "\\1\\3 Moreton Bay", label),
         label = gsub(" - ", "-", label, fixed = TRUE), 
         label = if_else(!is.na(label), paste0(" ", label), NA_character_)) %>%
  filter(Region %pin% "Brisbane") %>%
  {
    grplot(., aes(x = Date, y = Jobs_added, group = SA4_NAME16, color = SA4_NAME16)) + 
      geom_line() +
      scale_x_date(expand = expand_scale(add = c(0, 720))) +
      scale_y_continuous(labels = function(x) if_else(x < 0, paste0("\u2212", comma(abs(x))), comma(x)),
                         limits = Jobs_added_by_Region %$% range(Jobs_added)) +
      geom_text(aes(label = label),
                fontface = "bold",
                hjust = 0,
                na.rm = TRUE) +
      facet_wrap(~Region, nrow = 1)
  } %>%
  save_pptx("Jobs-added-Brisbane.pptx")

Jobs_added_by_Region %>%
  mutate(label = if_else(1:n() == n(),
                         gsub("Brisbane - (.*)$", "\\1 BNE", SA4_NAME16),
                         NA_character_),
         label = if_else(grepl("Brisbane.*Inner", label), "Inner BNE", label),
         label = gsub("Moreton Bay - ([NS])(or|ou)(th)", "\\1\\3 Moreton Bay", label),
         label = gsub(" - ", "-", label, fixed = TRUE), 
         label = if_else(!is.na(label), paste0(" ", label), NA_character_)) %>%
  filter(!(Region %pin% "Brisbane")) %>%
  {
    grplot(., aes(x = Date, y = Jobs_added, group = SA4_NAME16, color = SA4_NAME16)) + 
      geom_line() +
      scale_x_date(expand = expand_scale(add = c(0, 720))) +
      scale_y_continuous(labels = function(x) if_else(x < 0, paste0("\u2212", comma(abs(x))), comma(x)),
                         limits = Jobs_added_by_Region %$% range(Jobs_added)) +
      geom_text(aes(label = label),
                fontface = "bold",
                hjust = 0,
                na.rm = TRUE) +
      facet_wrap(~Region, nrow = 1)
  } %>%
  save_pptx("Jobs-added-notBrisbane.pptx")

Jobs_growth_by_Region <-
  Queensland_labour_data %>% 
  arrange(SA2_NAME16, Date) %>%
  group_by(SA2_NAME16) %>%
  mutate(Jobs_growth = Labour_force / first(Labour_force)) %>%
  group_by(GCC_NAME16, SA4_NAME16, Date) %>%
  summarise(Jobs_growth = weighted.mean(Jobs_growth, rep_len(last(Labour_force), n())) - 1)  %>%
  mutate(Region = if_else(SA4_NAME16 %pin% "Brisbane",
                          "Brisbane",
                          if_else(SA4_NAME16 %pin% c("Ipswich",
                                                     "Gold Coast",
                                                     "Sunshine Coast",
                                                     "Moreton Bay", 
                                                     "Beaudesert"),
                                  "Outer Brisbane",
                                  if_else(SA4_NAME16 %pin% c("Toowoomba",
                                                             "Townsville",
                                                             "Cairns"),
                                          "Regional city",
                                          "Rest of Qld")))) 
  

Jobs_growth_by_Region %>%
  mutate(label = if_else(1:n() == n(),
                         gsub("Brisbane - (.*)$", "\\1 BNE", SA4_NAME16),
                         NA_character_),
         label = if_else(grepl("Brisbane.*Inner", label), "Inner BNE", label),
         label = gsub("Moreton Bay - ([NS])(or|ou)(th)", "\\1\\3 Moreton Bay", label),
         label = gsub(" - ", "-", label, fixed = TRUE), 
         label = if_else(!is.na(label), paste0(" ", label), NA_character_)) %>%
  filter(Region %pin% "Brisbane") %>%
  {
    grplot(., aes(x = Date, y = Jobs_growth, group = SA4_NAME16, color = SA4_NAME16)) + 
      geom_line() +
      scale_x_date(expand = expand_scale(add = c(0, 720))) +
      scale_y_continuous(labels = function(x) if_else(x < 0, paste0("\u2212", percent(abs(x))), percent(x)),
                         limits = Jobs_growth_by_Region %$% range(Jobs_growth)) +
      geom_text(aes(label = label),
                fontface = "bold",
                hjust = 0,
                na.rm = TRUE) +
      facet_wrap(~Region, nrow = 1)
  } %T>%
  print %>%
  save_pptx("Jobs-growth-Brisbane.pptx")

Jobs_growth_by_Region %>%
  mutate(label = if_else(1:n() == n(),
                         gsub("Brisbane - (.*)$", "\\1 BNE", SA4_NAME16),
                         NA_character_),
         label = if_else(grepl("Brisbane.*Inner", label), "Inner BNE", label),
         label = gsub("Moreton Bay - ([NS])(or|ou)(th)", "\\1\\3 Moreton Bay", label),
         label = gsub(" - ", "-", label, fixed = TRUE), 
         label = if_else(!is.na(label), paste0(" ", label), NA_character_)) %>%
  filter(!(Region %pin% "Brisbane")) %>%
  {
    grplot(., aes(x = Date, y = Jobs_growth, group = SA4_NAME16, color = SA4_NAME16)) + 
      geom_line() +
      scale_x_date(expand = expand_scale(add = c(0, 720))) +
      scale_y_continuous(labels = function(x) if_else(x < 0, paste0("\u2212", percent(abs(x))), percent(x)),
                         limits = Jobs_growth_by_Region %$% range(Jobs_growth)) +
      geom_text(aes(label = label),
                fontface = "bold",
                hjust = 0,
                na.rm = TRUE) +
      facet_wrap(~Region, nrow = 1)
  } %T>%
  print %>%
  save_pptx("Jobs-growth-notBrisbane.pptx")