In djpr-data/djprlabourdash: DJSIR Jobs Dashboard

library(officedown)
library(flextable)
library(djprshiny)
library(djprlabourdash)
library(automate.labourforce.checks)
library(dplyr)
library(dbplyr)

knitr::opts_chunk$set(
  echo = FALSE,
  message = FALSE,
  error = FALSE,
  warning = FALSE,
  tab.topcaption = TRUE
  )

Sys.setenv("R_DJPRLABOURDASH_TABLEDEST" = "briefing")


table_split <- function(tbl){
  title <- tbl$caption$value
  tbl$caption <- NULL

  return(list(tbl = tbl,
              title = title))
}

check_title <- function(table){
  if(inherits(table$title, "data.frame")){
    table$title <- table$title$txt
  }
  return(table)
}


flex_adjust <- function(flex, 
                        pivot = FALSE, 
                        extract_pc = FALSE, 
                        series = NULL, 
                        names_width = 2, 
                        tbl_width = 1){

  df <- flex$body$dataset |> 
    dplyr::select(-`LAST 3 YEARS`) 

  if (!is.null(series)) {

    df <- data.frame(SERIES_ID = series) |>
      left_join(df) # should filter and order in one

  }

  if (extract_pc) {
    df <- df |> 
      dplyr::mutate(
        dplyr::across(
          dplyr::starts_with('SINCE'),
          ~ stringr::str_match(.x, "(?<=\\().+?(?=\\))")
          )
        )
  }

  if (pivot) {

    out_data <- df |>
      tidyr::pivot_longer(-SERIES_ID) |>
      tidyr::pivot_wider(names_from = 'SERIES_ID', values_from = 'value')

    colnames(out_data) <- out_data |> dplyr::slice(1)
    out_data <- out_data[-1, ]

    names_width <- 1.5

    #TODO: option to remove units from each cell and add to first column

  } else {

    # spark already removed, maybe add filter here
    out_data <- df |>
      select(-SERIES_ID)

  } 

  flextable(out_data) |>
    flextable::font(fontname = "Arial") |>
    flextable::font(fontname = "Arial", part = "header") |>
    flextable::fontsize(size = 8, part = 'all') |>
    flextable::bold(i = 1, part = "header") |>
    flextable::bg(i = 1, bg = "#003366", part = "header") |>
    flextable::color(color = "white", part = "header") |>
    flextable::hline(border = officer::fp_border(color="gray", width = 1)) |>
    flextable::width(j = 1, width = names_width) |>
    flextable::set_table_properties(width = tbl_width)

}

abs_labour_force <- get_labourforce_db()

Whole of Victoria

t1 <- table_overview(df = abs_labour_force) |>
  table_split() |>
  check_title()

overview_series <- c("A84423349V", # Employed persons ,
                     "A84423357V", #   Employed full-time    ,
                     "pt_emp_vic", #   Employed part-time    ,
                     "A84423350C", #   Unemployed persons    ,
                     "A84423354L", #     Unemployment rate     ,
                     "A84423355R", #   Participation rate    ,
                     "A84426256L", #   Monthly hours work    ,
                     "A85223450L", # Underemployment rate       
                     "A85223451R", # Underutilisation rate 
                     "A84423356T") # Employment/pop  ratio )

Table 1: r t1$title

r t1$tbl |> flex_adjust(series = overview_series)

t2 <- table_gr_sex(df = abs_labour_force) |>
  table_split()

t2 <- check_title(t2)

# TODO: split male/female into separate column
# TODO: remove percentages from first 4 rows

Table 2: r t2$title

r t2$tbl |> flex_adjust()

t3 <- table_ind_unemp_state(df = abs_labour_force) |>
  table_split()

t3 <- check_title(t3)

Table 3: r t3$title

r t3$tbl |> flex_adjust(pivot = TRUE)

\newpage

Youth

t4 <- table_gr_youth_summary(df = abs_labour_force) |>
  table_split()

t4 <- check_title(t4)

youth_series <- c("A84424687C", #  Youth employment                      
                  "A84424688F", #  Unemployed total                      
                  "A84424691V", #  Youth unemployment rate               
                  "A84424692W", #  Youth participation rate              
                  "A84424602F", #  Participation rate - not ft study     ,
                  "15-24_males_unemployment rate",    #  Male youth unemployment rate          
                  "15-24_females_unemployment rate")  #  Female youth unemployment rate        )

Table 4: r t4$title

r t4$tbl |> flex_adjust(series = youth_series)

t5 <- table_gr_youth_unemp_region(df = abs_labour_force) |>
  table_split()

t5 <- check_title(t5)


youth_melb_series <- c("15-24_Unemployment rate_Greater Melbourne",       #   Greater Melbourne youth unemployment rate
                       "15-24_Unemployment rate_Mornington Peninsula",        #  Mornington Peninsula
                       "15-24_Unemployment rate_Melbourne - Inner",       #   Melbourne - Inner
                       "15-24_Unemployment rate_Melbourne - Inner East",  #   Melbourne - Inner East
                       "15-24_Unemployment rate_Melbourne - Inner South", #   Melbourne - Inner South
                       "15-24_Unemployment rate_Melbourne - North East",  #   Melbourne - North East
                       "15-24_Unemployment rate_Melbourne - North West",  #   Melbourne - North West
                       "15-24_Unemployment rate_Melbourne - Outer East",  #   Melbourne - Outer East
                       "15-24_Unemployment rate_Melbourne - South East",  #   Melbourne - South East
                       "15-24_Unemployment rate_Melbourne - West")        #   Melbourne - West

youth_reg_series <- c("15-24_Unemployment rate_Rest of Vic.",                #  Regional Victoria youth unemployment rate
                      "15-24_Unemployment rate_Ballarat",                    #  Ballarat
                      "15-24_Unemployment rate_Bendigo",                     #  Bendigo
                      "15-24_Unemployment rate_Geelong",                     #  Geelong
                      "15-24_Unemployment rate_Hume",                        #  Hume
                      "15-24_Unemployment rate_Latrobe - Gippsland",         #  Latrobe - Gippsland
                      "15-24_Unemployment rate_Shepparton",                  #  Shepparton
                      "15-24_Unemployment rate_Victoria - North West",       #  Victoria - North West
                      "15-24_Unemployment rate_Warrnambool and South West")  #  Warrnambool and South West

# TODO: update titles

Table 5: r t5$title

r t5$tbl |> flex_adjust(pivot = TRUE, series = youth_reg_series)

Table 6: r t5$title

r t5$tbl |> flex_adjust(pivot = TRUE, series = youth_melb_series)

\newpage

Regional Victoria

# was t12
t7 <- djprlabourdash::table_reg_nonmetro_emp(df = abs_labour_force) |>
  table_split()

t7 <- check_title(t7)

Table 7: r t7$title

r t7$tbl |> flex_adjust(pivot = TRUE)

# was t13
t8 <- djprlabourdash::table_reg_nonmetro_unemp(df = abs_labour_force) |>
  table_split()

t8 <- check_title(t8)

Table 8: r t8$title

r t8$tbl |> flex_adjust(pivot = TRUE)

# was t14
t9 <- djprlabourdash::table_reg_nonmetro_unemprate(df = abs_labour_force) |>
  table_split()

t9 <- check_title(t9)

Table 9: r t9$title

r t9$tbl |> flex_adjust(pivot = TRUE)

\newpage

# was t11
t10 <- djprlabourdash::table_reg_nonmetro_states_unemprate(df = abs_labour_force) |>
  table_split()

t10 <- check_title(t10)

Table 10: r t10$title

r t10$tbl |> flex_adjust(pivot = TRUE)

# was t15
t11 <- djprlabourdash::table_reg_nonmetro_partrate(df = abs_labour_force) |>
  table_split()

t11 <- check_title(t11)

Table 11: r t11$title

r t11$tbl |> flex_adjust(pivot = TRUE)

\newpage

Metropolitan Melbourne

# was t7
t12 <- djprlabourdash::table_reg_metro_emp(df = abs_labour_force) |>
  table_split()

t12 <- check_title(t12)

Table 12: r t12$title

r t12$tbl |> flex_adjust(pivot = TRUE)

# was t8
t13 <- djprlabourdash::table_reg_metro_unemp(df = abs_labour_force) |>
  table_split()

t13 <- check_title(t13)

Table 13: r t13$title

r t13$tbl |> flex_adjust(pivot = TRUE)

\newpage

# was t9
t14 <- djprlabourdash::table_reg_metro_unemprate(df = abs_labour_force) |>
  table_split()

t14 <- check_title(t14)

Table 14: r t14$title

r t14$tbl |> flex_adjust(pivot = TRUE)

# was t6
t15 <- djprlabourdash::table_reg_metro_states_unemprate(df = abs_labour_force) |>
  table_split()

t15 <- check_title(t15)

Table 15: r t15$title

r t15$tbl |> flex_adjust(pivot = TRUE)

# was 10
t16 <- djprlabourdash::table_reg_metro_partrate(df = abs_labour_force) |>
  table_split()

t16 <- check_title(t16)

Table 16: r t16$title

r t16$tbl |> flex_adjust(pivot = TRUE)

\newpage

Industry

# was t16
# t17 <- table_industries_summary(df = abs_labour_force) |>
#   table_split()
# 
# t17 <- check_title(t17)

t17 <- table_industries_summary_ppqs() |>
    flextable::font(fontname = "Arial") |>
    flextable::font(fontname = "Arial", part = "header") |>
    flextable::fontsize(size = 8, part = 'all') |>
    flextable::bold(i = 1, part = "header") |>
    flextable::bg(i = 1, bg = "#003366", part = "header") |>
    flextable::color(color = "white", part = "header") |>
    flextable::hline(border = officer::fp_border(color="gray", width = 1)) |>
    flextable::width(j = 1, width = 2) |>
    flextable::set_table_properties(width = 1)

Table 17: Victorian employment by industry, November 2022 quarter (not seasonally adjusted)

r t17

djpr-data/djprlabourdash documentation built on April 28, 2023, 6:16 p.m.