Walkthrough of {a11ytables} given at Web Dissemination Committee, Q1 2022

web-dissemination-a11ytables.R

# Web Dissemination Committee Q1
# Matt Dray, March 2022


# Packages ----------------------------------------------------------------


# Install {a11ytables} from GitHub
install.packages(remotes)  # if not yet installed
remotes::install_github("co-analysis/a11ytables")  # v0.0.0.90012 at time of demo

# Minimum needed packages
library(a11ytables)
library(openxlsx)  # installed with a11ytables

# For convenience
library(dplyr, warn.conflicts = FALSE)
library(tibble)


# Demo with new_a11ytable() -----------------------------------------------


# Create your stats tables

stats_1_df <-
  head(mtcars) |>
  rownames_to_column("car") |>
  select(
    "Car [note 1]" = car,  # this example has notes
    "Miles per gallon [note 2]" = mpg,
    "Weight [note 3]" = wt
  )

stats_2_df <-
  head(mtcars) |>
  rownames_to_column("car") |>
  select(
    "Car" = car,
    "Horsepower" = hp,
    "Transmission" = am
  )

# Create tables for contextual sheets

cover_df <-  # the cover has high-level information
  tribble(
    ~"Subsection title", ~"Subsection body",
    "Description", "The data was extracted from the 1974 Motor Trend US magazine, and comprises fuel consumption and 10 aspects of automobile design and performance for 32 automobiles (1973–74 models)",
    "Format", "A data frame with 32 observations on 11 (numeric) variables.",
    "Contact", "The mtcars Team, telephone 0123456789."
  )

contents_df <-
  tribble(
    ~"Sheet name", ~"Sheet title",
    "Notes", "Notes",
    "Table 1", "Car Road Tests (demo 1)",
    "Table 1", "Car Road Tests (demo 2)"
  )

notes_df <-  # notes should match what you put in your tables
  tribble(
    ~"Note number", ~"Note text",
    "[1]", "Cars are a type of transport.",
    "[2]", "US gallons.",
    "[3]", "Thousand pounds."
  )

# Build a11ytable

mtcars_a11ytable <-
  new_a11ytable(
    tab_titles = c(  # will appear on tabs
      "Cover",
      "Contents",
      "Notes",
      "Table 1",
      "Table 2"
    ),
    sheet_types = c(  # so that the correct styling is applied
      "cover",
      "contents",
      "notes",
      "tables",
      "tables"
    ),
    sheet_titles = c(  # title for cell A1 of each sheet
      "The mtcars demo dataset",
      "Table of contents",
      "Notes",
      "Car Road Tests (demo 1)",
      "Car Road Tests (demo 2)"
    ),
    sources = c(  # data source, if there is one 
      NA_character_,  # no source for cover/contents/notes
      NA_character_,
      NA_character_,
      "Motor Trend (1974)",
      NA_character_
    ),
    table_names = c(  # unique name for marked-up table cells in Excel output
      "cover_sheet",
      "table_of_contents",
      "notes_table",
      "cars_table_1",
      "cars_table_2"
    ),
    tables = list(  # a list-column of tables we prepared earlier
      cover_df,
      contents_df,
      notes_df,
      stats_1_df,
      stats_2_df
    )
  )

mtcars_a11ytable  # preview (uses pretty tibble-style output)

is_a11ytable(mtcars_a11ytable)  # check the class is 'a11ytable'

class(mtcars_a11ytable)  # all classes, note tbl and data.frame

summary(mtcars_a11ytable)  # special S3 summary method

# You can treat an a11ytable like a data.frame

mtcars_a11ytable$tab_title  # e.g. extract tab titles

mtcars_a11ytable |>  # e.g. use dplyr syntax
  filter(tab_title == "Table 1") |>
  pull(table)

mtcars_a11ytable[2, 3] <- "Table of Contents"  # e.g. make a change
mtcars_a11ytable  # observe the change

# Convert a11ytable to styled workbook (uses {openxlsx})

mtcars_wb <-
  mtcars_a11ytable |>
  create_a11y_wb()  # applies formatting based on sheet_types and content

mtcars_wb  # preview Workbook-class object

# Write file

openXL(mtcars_wb)  # open a temporary copy
saveWorkbook(demo_wb, "demo.xlsx")  # save out to xlsx


# Demo with as_a11ytable() ------------------------------------------------


# You can also convert a compliant data.frame directly to a11ytable

demo_wb <-
  mtcars_df |>         # example compliant data.frame built into package
  as_a11ytable() |>    # convert to a11ytable-class
  create_a11y_wb() |>  # convert to Workbook-class
  openXL()  # open temp copy

Best Practice and Impact team (BPID, at ONS/GSS) have developed some new guidance on releasing stats in spreadsheets.

BPID have a Python package called gptables that helps build spreadsheet outputs. It hasn't yet been updated for the latest release of best practice guidance, which has a stronger focus on accessibility of spreadsheet outputs. It also doesn't have a native R version.

{a11ytables} aims to fill these gaps. It uses the familiar R data.frame as its basis and the Java-free {openxlsx} package to generate workbooks.

Its functions encourage and enact accessibility best-practice to produce reproducible and consistently-styled spreadsheets for publication on GOV.UK or elsewhere.

Perhaps most importantly, it requires little thinking or preparation from the analyst. You need only two functions from {a11ytables} to get from data to compliant spreadsheet.

Features include:

• auto styling
• auto table and header markup
• dynamic meta-element insertion
• warnings
• {tibble}-like printing

Process is basically:

1. Create tables
2. new_a11ytables() to create an a11ytables-class data.frame
3. create_a11y_wb() to maker an {openxlsx} workbook with styling, etc
4. save with openxlsx::saveWorkbook()

Currently approaching version 0.1.

Will need much testing with (1) spreadsheet producers (ONS and MoJ have been using) and (2) end-users themselves.

Upcoming:

• solve outstanding accessibility issues, like H1 headers
• additional minor niceties, like thousands separators
• wider range of warnings when accessibility issues may be apparent
• improvements to S3 back-end