tl;dr

I presented some slides at the EARL 2022 conference about {a11ytables}: an R package that helps automate the production of reproducible and accessible spreadsheets, with a focus on publication of government statistics.

Counting sheets

The UK government publishes a lot of spreadsheets that contain statistical tables. Compared to each other—and to themselves over time—these files are often:

• inconsistent in structure (e.g. cover or contents sheets are missing)
• inconsistent in style (e.g. different fonts, different shorthand codes for suppressed values)
• inaccessible to users of assistive technology (e.g. they contain blank columns or unannounced footnotes)

Luckily, the government’s Analysis Function released some excellent guidance for releasing statistics in spreadsheets, with particular attention to accessibility.

The government’s grassroots Reproducible Analytical Pipelines (RAP) movement is also growing at pace. RAP’s purpose is to overcome a legacy of fragmented point-and-click processes into code-driven end-to-end pipelines that improve speed, accuracy and reproducibility; including workflows that generate statistical spreadsheets for publication.

It will take time for these approaches to become 100% embedded across government, due to factors like the inevitable inertia that comes with trying to leave legacy processes behind.¹

How can we grease the wheels?

Spreadsheet socialism

I work in a government team that publishes data² and I wanted to make it easier for us to:

• generate spreadsheets via R (the most commonly used language here)
• be able to reproduce outputs in future (i.e. we produce files monthly, quarterly, annually)
• apply accessible styles and structure (so we don’t have to spend ages with checklists and point-and-click interfaces)

First I looked for existing code-based solutions and found the Python package gptables, written by the Analysis Function Core Team. At the time, the package created spreadsheets in accordance with an older version of the best-practice guidance. There was no R-native solution either, though gptables could be accessed in R via {reticulate}.

As a result, I created {a11ytables}: an R package to generate spreadsheets that meet the latest best-practice guidance for releasing statistics in spreadsheets.

It can be downloaded from GitHub (currently v0.1), which also installs {openxlsx} (which does all the hard work of building a workbook) and {pillar} (for prettier printing).

install.packages("remotes")  # if not yet installed
remotes::install_github("co-analysis/a11ytables")
library(a11ytables)

Do submit issues or pull requests to the repo if you have ideas or solutions.

Easy does it

A major aim of {a11ytables} is to make it easy for stats producers to more easily complete the last mile of their ‘data-in to spreadsheet-out’ pipeline. As such, the workflow is relatively simple and is composed of only three functions (arguments ignored for brevity):

create_a11ytable() |> 
  generate_workbook() |>
  openxlsx::saveWorkbook()

Basically:

1. Pass information and data as arguments to create_a11ytable(), which creates a special a11ytables-class dataframe representation of your spreadsheet content
2. Pass that object to generate_workbook() to convert it to a Workbook-class object that applies the required structure and styling
3. Use saveWorkbook() from the {openxlsx} package to write the spreadsheet output to an xlsx file

I recommend that you read the vignettes and function documentation on the package website to better understand how to use {a11ytables} and to learn about its caveats³; I won’t go into depth in this post.

Over-easy does it

I wrote some slides about the package and presented it at the EARL 2022 conference⁴ in London. Yes, to expose the package, but also to make a wider point about the general importance of reproducibility, accessibility and the power of reusable tools.

You can access the slides for my talk on the web, or find the source on GitHub.

The slides show an example of some tables published by the UK government—the latest UK egg statistics⁵—and walks through how they might be developed using {a11ytables}.

I wrote the slides in Quarto and made heavy use of {quartostamp}—my package of Quarto helpers exposed as an RStudio Addin—which I wrote about recently. Click ‘settings’ in the hamburger menu (lower left) to go fullscreen, see presenter notes, or get a slide overview.

## ─ Session info ───────────────────────────────────────────────────────────────
##  setting  value
##  version  R version 4.2.0 (2022-04-22)
##  os       macOS Big Sur/Monterey 10.16
##  system   x86_64, darwin17.0
##  ui       X11
##  language (EN)
##  collate  en_GB.UTF-8
##  ctype    en_GB.UTF-8
##  tz       Europe/London
##  date     2022-09-07
##  pandoc   2.18 @ /Applications/RStudio.app/Contents/MacOS/quarto/bin/tools/ (via rmarkdown)
## 
## ─ Packages ───────────────────────────────────────────────────────────────────
##  package     * version date (UTC) lib source
##  blogdown      1.9     2022-03-28 [1] CRAN (R 4.2.0)
##  bookdown      0.26    2022-04-15 [1] CRAN (R 4.2.0)
##  bslib         0.3.1   2021-10-06 [1] CRAN (R 4.2.0)
##  cli           3.3.0   2022-04-25 [1] CRAN (R 4.2.0)
##  digest        0.6.29  2021-12-01 [1] CRAN (R 4.2.0)
##  evaluate      0.15    2022-02-18 [1] CRAN (R 4.2.0)
##  fastmap       1.1.0   2021-01-25 [1] CRAN (R 4.2.0)
##  htmltools     0.5.2   2021-08-25 [1] CRAN (R 4.2.0)
##  jquerylib     0.1.4   2021-04-26 [1] CRAN (R 4.2.0)
##  jsonlite      1.8.0   2022-02-22 [1] CRAN (R 4.2.0)
##  knitr         1.39    2022-04-26 [1] CRAN (R 4.2.0)
##  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.2.0)
##  R6            2.5.1   2021-08-19 [1] CRAN (R 4.2.0)
##  rlang         1.0.2   2022-03-04 [1] CRAN (R 4.2.0)
##  rmarkdown     2.14    2022-04-25 [1] CRAN (R 4.2.0)
##  rstudioapi    0.13    2020-11-12 [1] CRAN (R 4.2.0)
##  sass          0.4.1   2022-03-23 [1] CRAN (R 4.2.0)
##  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.2.0)
##  stringi       1.7.6   2021-11-29 [1] CRAN (R 4.2.0)
##  stringr       1.4.0   2019-02-10 [1] CRAN (R 4.2.0)
##  xfun          0.30    2022-03-02 [1] CRAN (R 4.2.0)
##  yaml          2.3.5   2022-02-21 [1] CRAN (R 4.2.0)
## 
##  [1] /Library/Frameworks/R.framework/Versions/4.2/Resources/library
## 
## ──────────────────────────────────────────────────────────────────────────────