Import the Australian Marriage Law Postal Survey, 2017 into R

participation.R

# Import the participation data

library(tidyverse)
library(tidyxl)   # You'll need the dev versions devtools::install_github("nacnudus/tidyxl")
library(unpivotr) # You'll need the dev versions devtools::install_github("nacnudus/unpivotr")
library(here)

path <- here("inst", "extdata", "australian_marriage_law_postal_survey_2017_-_participation_final.xlsx")
book <- xlsx_cells(path)
formats <- xlsx_formats(path)

import_1_to_3 <- function(cells) {
  age_band <-
    cells %>%
    filter(row == 6, col >= 3) %>%
    select(row, col, age_band = character)
  state <-
    cells %>%
    filter(row >= 7, col == 1, !is_blank) %>%
    select(row, col, state = character)
  measure <-
    cells %>%
    filter(row >= 7, col == 2) %>%
    select(row, col, measure = character)
  data_cells <-
    cells %>%
    filter(row >= 7, col >= 3, !is_blank) %>%
    select(row, col, count = numeric)
  data_cells %>%
    WNW(state) %>%
    W(measure) %>%
    N(age_band) %>%
    select(-row, -col)
}

tables_1_to_3 <-
  book %>%
  filter(sheet %in% paste("Table", 1:3)) %>%
  nest(-sheet) %>%
  mutate(data = map(data, import_1_to_3),
         sex = c("all", "male", "female")) %>%
  unnest()

import_4_to_6 <- function(cells) {
  age_band <-
    cells %>%
    filter(row == 6, col >= 3) %>%
    select(row, col, age_band = character)
  state <-
    cells %>%
    filter(row >= 7, col == 1, !is_blank,
           formats$local$font$bold[local_format_id]) %>%
    select(row, col, state = character)
  territory <-
    cells %>%
    filter(row >= 7, col == 1, !is_blank,
           !formats$local$font$bold[local_format_id]) %>%
    select(row, col, territory = character)
  measure <-
    cells %>%
    filter(row >= 7, col == 2) %>%
    select(row, col, measure = character)
  data_cells <-
    cells %>%
    filter(row >= 7, col >= 3, !is_blank) %>%
    select(row, col, count = numeric)
  data_cells %>%
    WNW(state) %>%
    WNW(territory) %>%
    W(measure) %>%
    N(age_band) %>%
    select(-row, -col)
}

tables_4_to_6 <-
  book %>%
  filter(sheet %in% paste("Table", 4:6)) %>%
  nest(-sheet) %>%
  mutate(data = map(data, import_4_to_6),
         sex = c("all", "male", "female")) %>%
  unnest()

all_tables <- bind_rows(tables_1_to_3, tables_4_to_6)

results.R

# Import the results data

library(tidyverse)
library(tidyxl)   # You'll need the dev versions devtools::install_github("nacnudus/tidyxl")
library(unpivotr) # You'll need the dev versions devtools::install_github("nacnudus/unpivotr")
library(here)

path <- here("inst", "extdata", "australian_marriage_law_postal_survey_2017_-_response_final.xlsx")
book <- xlsx_cells(path)
formats <- xlsx_formats(path)

import_1 <- function(cells) {
  population <-
    cells %>%
    filter(row == 5, col >= 2, !is_blank) %>%
    select(row, col, population = character)
  response <-
    cells %>%
    filter(row == 6, col >= 2, !is_blank) %>%
    mutate(character = str_trim(character)) %>%
    select(row, col, response = character)
  unit <-
    cells %>%
    filter(row == 7, col >= 2, !is_blank) %>%
    select(row, col, unit = character)
  state <-
    cells %>%
    filter(row >= 8, col == 1, !is_blank) %>%
    select(row, col, state = character)
  data_cells <-
    cells %>%
    filter(row >= 8, col >= 2, !is_blank) %>%
    select(row, col, value = numeric)
  data_cells %>%
    W(state) %>%
    NNW(population) %>%
    NNW(response) %>%
    N(unit) %>%
    select(-row, -col)
}

table_1 <-
  book %>%
  filter(sheet == "Table 1") %>%
  import_1()

import_2 <- function(cells) {
  population <-
    cells %>%
    filter(row == 5, col >= 2, !is_blank) %>%
    select(row, col, population = character)
  response <-
    cells %>%
    filter(row == 6, col >= 2, !is_blank) %>%
    mutate(character = str_trim(character)) %>%
    select(row, col, response = character)
  unit <-
    cells %>%
    filter(row == 7, col >= 2, !is_blank) %>%
    select(row, col, unit = character)
  state <-
    cells %>%
    filter(row >= 8, col == 1, !is_blank,
           formats$local$font$bold[local_format_id]) %>%
    select(row, col, state = character)
  territory <-
    cells %>%
    filter(row >= 7, col == 1, !is_blank,
           !formats$local$font$bold[local_format_id]) %>%
    select(row, col, territory = character)
  data_cells <-
    cells %>%
    filter(row >= 8, col >= 2, !is_blank) %>%
    select(row, col, value = numeric)
  data_cells %>%
    WNW(state) %>%
    W(territory) %>%
    NNW(population) %>%
    NNW(response) %>%
    N(unit) %>%
    select(-row, -col)
}

table_2 <-
  book %>%
  filter(sheet == "Table 2") %>%
  import_2()

all_tables <- bind_rows("Table 1" = table_1, "Table 2" = table_2, .id = "sheet")