{a11ytables}

#EarlConf, September 2022
NHS-R, January 2023

Matt Dray, Civil Service

tl;dr

Generate best-practice spreadsheets with help from {a11ytables}

co-analysis.github.io/a11ytables

github.com/co-analysis/a11ytables

• I’m not talking about anything complex in this talk, but in case you get bored or feel sleepy, you only need to take a photo of this page.
• Yes, I want to tell you about the package, but also I’m tricking you: I want to tell you about the importance of inclusivity, taking the initiative and fixing things.
• So keep listening for more about that.

Ahoy

• R for reproducibility (official)
• Off-piste R (personal)

@mattdray@fosstodon.org

matt-dray.com

• I work in the Civil Service and obviously nothing interesting is happening this week in government, so I decided to come to EARL.
• I’m talking today about something I made in an official capacity.
• I personally can be found elsewhere in an unofficial capacity (e.g. website and Twitter).
• You can read blogposts at rostrum.blog about building an interactive roguelike videogame in R; making a Twitterbot with R and GitHub Actions; trolling about assignment operators; and more.

An opportunity

• Not in a multilevel-marketing way.
• The UK government has an extremely pressing problem… spreadsheets.
• Who has opened an ‘official’ spreadsheet and immediately winced?
• Yes, they sometimes hurt my eyes too, but rather than complain, maybe we can see the opportunity.
• I’ll show an example of a government spreadsheet that could be improved. It was one of the first ones that came up in my search of GOV.UK statistics.
• The intent is to demonstrate opportunity, not to call anyone out. The maintainer of the statistics knows I’m talking about this.

It looks like you’re trying to improve a spreadsheet.

• Defra’s ‘Latest UK egg statistics’.
• Mundane? Definitely a classic government spreadsheet.
• Collects data on egg production and prices from a survey.
• Branded as National Statistics. That’s important, particularly when it comes to thinking about the Code of Practice.

It looks like you’re trying to improve a spreadsheet.

• This is unofficial use of the celebrity digital assistant, Clippy (apologies to Microsoft staff in the audience, please don’t sue me).
• Yes, for the lolz, but also because I want to suggest some areas to improve in a non-threatening manner (depending on how threatening you think Clippy is).
• Also this presentation was made in Quarto (the sort-of successor to R Markdown), which makes this type of animation fairly straightforward.

• This is the ‘UK egg packing station throughput and prices dataset’ spreadsheet.
• First tab: ‘Packers’.
• Being nitpicky, how would this fare for machine readability? Screenreaders?
• Look out for:
  • empty rows, columns, cells (including A1)
  • gridlines sometimes missing, sometimes not
  • triple headers
  • centred values (mostly)
  • the number of decimals varies (though Excel hides this)
  • there’s some stray values with no explanation

• Second tab: ‘Packers_Annual’.
• Look out for same as previous tab, plus:
  • notes in a footer, not labelled
  • the year is ‘20212’
  • two ‘year’ columns?
  • some rogue values on the far right
  • shorthand not fully conforming to guidance (‘c’ rather than ‘[c]’?)

• Third tab: ‘Information’.
• Look out for:
  • important information on the last page instead of a cover or explicit annex
  • information is in text boxes rather than cells
  • ‘updated’ information is in a different place
  • GOV.UK link way off to the right

Improve

1. User experience
2. Producer experience

• There’s two main areas of focus to improve.
• 1. Useability and accessibility for the people consuming the stats.
• 2. The creators and developers of the publication.
• Sometimes the producers are the users too!

User experience

• Maybe you can see the things you’d like to fix, maybe some are obvious, maybe some are more subjective.
• But there are a thousand reasons why teams can’t just improve things overnight, like lack of staff, lack of time, inertia from pre-existing point-and-click solutions, etc.
• However, there is a place to start: some codified and exhaustive guidance on the useability and accessibility of the output.
• In other words, the user experience aspect is ‘solved’.

• The Analysis Function has a webpage with some bumper advice on releasing data in spreadsheet format. This was coordinated by Hannah Thomas.
• This is important role of the newly-formed Analysis Function: unify guidance across the analytical professions.
• Promotes consistency and usability, but particularly accessibility.
• This guidance is being used already in publications.

• The Analysis Function also has a handy checklist of the basics for accessibility.
• This gives a quick lookup for the minimum requirements and more.

• Hannah also wrote a great blog about accessibility empathy for spreadsheet users.
• Can you find information in a spreadsheet where all the cells are blacked out?
• It ’shows you what it is like trying to navigate a data table when you cannot see the layout.
• Gives ‘some idea of the issues faced by users with visual impairments.’
• Hannah also runs an accessibility clinic for civil servants to chat through concerns or ideas.

• We can help make users’ lives better with better spreadsheets. But there’s also a way to make out lives easier as producers.
• Reproducible Analytical Pipelines (RAP) is a grassroots movement that’s been bubbling around UK government for a few years.
• RAPs are ‘automated statistical and analytical processes [that] incorporate elements of software engineering best practice to ensure that the pipelines are reproducible, auditable, efficient, and high quality.’
• There’s now a formal strategy for adoption that should empower publishers to improve their outputs.

• The UK Statistics Authority’s Code of Practice for Official Statistics.
• As producers of statistical publications, we should adhere to the three pillars of trustworthiness, quality and value.

Producer experience

• Many teams have applied this advice to their publications, but not all.
• How can we make enacting the guidance easier on the producer side:
  • as part of a RAP?
  • for entry-level coders?
  • by taking into account the realities of ‘at the coalface’ production?
  • by taking the initiative?
  • to prevent duplication of effort?
• A tool might help people do this consistently.

Tool requirements

1. Simple
2. Opinionated
3. Compliant
4. Known

• What would be the requirements of a tool to help analysts?
• We want a tool that starts with data and information and arranges and styles it for us.
• We aren’t converting an existing spreadsheet to meet guidance. It would be very hard to make a general tool to do that.

1. Simple user-interface/API for users; internal structure should be understood/maintainable by devs.
2. I don’t mean I’m going to exert my personal will on the design. Just that some decisions should be taken on the user’s behalf to help speed things up and maintain consistency.
3. The whole point is to meet the guidance (see the {a11ytables} accessibility checklist vignette).
4. We have centralised/open guidance, why not a centralised/open tool? This will help prevent duplicated effort; it needs to put out there for discussion and improvement. (Also because I wanted the acronym to spell ‘SOCK’.)

• Good news! The problem is already solved!
• gptables is an official Python solution from the Analysis Function.
• But: Python (though accessible via {reticulate}), wasn’t up-to-date with best-practice guidance when I needed it (it is now).

• So there was a gap in the market for an up-to-date R native solution.
• But actually, I initially created this gradually ‘bottom-up’ to solve my own needs.
• ‘A11y’ is short for ‘accessibility’ (there’s 11 letters between the ‘A’ and the ‘Y’).
• This is the documentation website (co-analysis.github.io/a11ytables/), made with {pkgdown}.

1. Simple: interface has only two main functions; only two dependencies are {openxlsx} and {pillar}; familiarity of dataframes and pipes; user brings data and information, {a11ytables} does the rest.
2. Opinionated: the package makes some choices for you so you don’t have to think very hard; some flexibly is lost, not all spreadsheets are simple, but you can intervene.
3. Compliant: a vignette compares the package’s functionality to to the accessibility checklist in the guidance and shows that it meets the guidance.
4. Known: have been sharing this in cross-government fora, Twitter, government presentation champions meeting, Scottish government. It’s being used for sure in MoJ, ONS, Scottish Government and Cabinet Office.

Fresh eggs

• A demo: let’s use {a11ytables} on the data from the published eggs spreadsheet.
• Walkthrough, starting with package installation.
• Remember: we aren’t converting that spreadsheet. We’ll pretend we have the extracted egg data already.
• You can’t make a best practice spreadsheet without breaking some eggs, lololol.

Install

install.packages("remotes")

remotes::install_github(
  "co-analysis/a11ytables",
  build_vignettes = TRUE
)

library(a11ytables)

• Package is GitHub only, version 0.1.
• Usable but still more to do.
• Also installs {openxlsx} and {pillar}
• CRAN is a future goal, maybe.

Workflow

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

• There’s only two major functions in the package, plus one from the {openxlsx} package.

1. Organise all the required information into a special dataframe with additional class ‘a11ytable’.
2. Convert to {openxlsx} ‘Workbook’ class with structure and style information.
3. Write that object to an xlsx file.

• I haven’t added any arguments here, because I want to show you the simplicity of the pipeline. Let’s add those in.

Workflow

create_a11ytable(
  tab_titles, sheet_types, sheet_titles, # required char vectors
  blank_cells, sources,                  # optional char vectors
  tables                                 # required list of dataframes
) |>
  generate_workbook() |>
  openxlsx::saveWorkbook("file.xlsx")

# The package also has an RStudio Addin with templates

• The create_a11ytable() function takes a few arguments that represent the data you need.
• These are:
  • tab_titles is a required character vector for the text that goes on the tabs
  • sheet_types is a required character vector (one of "cover", "contents", "notes", "tables") that provides information about how the sheet will be styled
  • sheet_titles is a required character vector of the worksheet titles that will go in cell A1
  • blank_cells is an optional character vector of meta-information that explains the meaning of any blank cells in the sheet
  • sources is an optional character vector of meta-information that contains
  • tables is a required list of dataframes that contain the main bulk of each sheet (stats tables are obviously tables, but the cover and notes are also given as tables; you also provide the cover as a dataframe, where each row becomes a subsection with a title and body)

Pre-prepared inputs

source("eggs/eggs.R")  # load pre-prepared data objects
ls()                   # print environment objects

[1] "egg_blank_cells"  "egg_sheet_titles" "egg_sheet_types"  "egg_sources"     
[5] "egg_tab_titles"   "egg_tables"      

• I pre-wrote some code to extract data from the eggs spreadsheet.
• You can find the code and original spreadsheet in the repo that hosts these slides.
• Importantly, I didn’t mimic the whole process of production for this example; I just copied the data out of the pre-existing spreadsheet.
• The code outputs some objects that are ready to be passed to the arguments of create_a11ytable().

Pre-prepared inputs

source("eggs/eggs.R")  # pre-prepared data objects
ls()                   # print environment objects

[1] "egg_blank_cells"  "egg_sheet_titles" "egg_sheet_types"  "egg_sources"     
[5] "egg_tab_titles"   "egg_tables"      

egg_sheet_titles[1:4]  # first few sheet titles

[1] "UK egg packing station throughput and prices"
[2] "Contents"                                    
[3] "Notes"                                       
[4] "Table 1: Throughput by country, quarterly"   

class(egg_tables)      # a list of tables

[1] "list"

• Here’s the sheet_titles object, which is a required character vector that contains the titles of each worksheet, for example.

Combine

create_a11ytable()

• Now we can pass our egg objects to the arguments of create_a11ytable().
• Basically, this combines all the data and information together into a single object.
• I recommend generating each object outside of the call to create_a11ytable() where possible *which I’ve done here), it keeps things cleaner.
• For smaller spreadsheets it would be fine to provide the arguments in the function call itself.

Combine

create_a11ytable(
  tab_titles   = egg_tab_titles, 
  sheet_types  = egg_sheet_types, 
  sheet_titles = egg_sheet_titles,
  blank_cells  = egg_blank_cells, 
  sources      = egg_sources,
  tables       = egg_tables
) -> egg_at

# Provides warnings/errors if there's problems

• Basically, each arg becomes a column in a dataframe.
• User is warned and/or errors raised and/or corrections applied if the provided information is insufficient.
• That’s an important feature for error-checking and to help keep things simpler for developers.
• User provides the info, function does the rest.

Combine

create_a11ytable(
  tab_titles   = egg_tab_titles, 
  sheet_types  = egg_sheet_types, 
  sheet_titles = egg_sheet_titles,
  blank_cells  = egg_blank_cells, 
  sources      = egg_sources,
  tables       = egg_tables
) -> egg_at

# Provides warnings/errors if there's problems

class(egg_at)

[1] "a11ytable"  "tbl"        "data.frame"

• You can see that the object gets the special ‘a11ytables’ S3 class, which helps with validation.
• But it also has classes of ‘tbl’ (i.e. a {tibble} dataframe) and classic ‘data.frame’.
• This means you can manipulate the output much like any dataframe object.

A dataframe!

egg_at

# a11ytable: 9 x 6
  tab_title sheet_type sheet_title                          blank…¹ source table
  <chr>     <chr>      <chr>                                <chr>   <chr>  <nam>
1 Cover     cover      UK egg packing station throughput a… <NA>    <NA>   <df> 
2 Contents  contents   Contents                             <NA>    <NA>   <df> 
3 Notes     notes      Notes                                <NA>    <NA>   <df> 
4 Table_1   tables     Table 1: Throughput by country, qua… <NA>    Quart… <df> 
5 Table_2   tables     Table 2: Throughput by system, quar… <NA>    Quart… <df> 
6 Table_3   tables     Table 3: Average packer to producer… <NA>    Quart… <df> 
7 Table_4   tables     Table 4: Throughput by country, ann… <NA>    Quart… <df> 
8 Table_5   tables     Table 5: Throughput by system, annu… <NA>    Quart… <df> 
9 Table_6   tables     Table 6: Average packer to producer… <NA>    Quart… <df> 
# … with abbreviated variable name ¹​blank_cells

• This is an ‘a11ytable’. It’s just a dataframe, really.
• Familiar dataframe structure holds all the information.
• You can see a row per sheet.
• Each argument has become a column.
• The list of dataframes has become a list-column.
• Empty content (NA) is allowed.

One row is one sheet

str(egg_at[egg_at$tab_title == "Table_2", ] )

Classes 'a11ytable', 'tbl' and 'data.frame':    1 obs. of  6 variables:
 $ tab_title  : chr "Table_2"
 $ sheet_type : chr "tables"
 $ sheet_title: chr "Table 2: Throughput by system, quarterly"
 $ blank_cells: chr NA
 $ source     : chr "Quarterly UK Egg Packing Station Survey."
 $ table      :List of 1
  ..$ qtrly_system:'data.frame':    424 obs. of  4 variables:
  .. ..$ Year                : chr  "1996" "1996" "1996" "1996" ...
  .. ..$ Quarter             : chr  "Q1" "Q1" "Q1" "Q1" ...
  .. ..$ System [note 1]     : chr  "Enriched" "Barn" "Free Range" "Organic" ...
  .. ..$ Eggs (million dozen): chr  "151" "3.2" "16.6" "[z]" ...

• Each row of the a11ytable is the content required to create a worksheet.
• Here’s the information needed for the sheet that contains Table 2.
• You can see the character vectors contain the information needed in that sheet, along with the dataframe that contains the stat table itself.

Convert to Workbook

egg_wb <- generate_workbook(egg_at)
class(egg_wb)

[1] "Workbook"
attr(,"package")
[1] "openxlsx"

• Next step is to turn the a11ytable-class object into an {openxlsx} Workbook-class object.
• This adds all the required structure and style information without the user needing to do anything.
• Works with a series of underlying add_*() functions to add elements to each sheet and style_*() functions that do simple stuff like adjust the font, but also dynamically change column widths and detect numeric columns that are stored as character due to notes or shorthand.

Structured, styled

egg_wb

A Workbook object.
 
Worksheets:
 Sheet 1: "Cover"
 
    Custom row heights (row: height)
     2: 34, 4: 34, 6: 34, 8: 34, 10: 34, 12: 34 
    Custom column widths (column: width)
      1: 72 
 

 Sheet 2: "Contents"
 
    Custom column widths (column: width)
      1: 16, 2: 56 
 

 Sheet 3: "Notes"
 
    Custom column widths (column: width)
      1: 16, 2: 56 
 

 Sheet 4: "Table_1"
 
    Custom column widths (column: width)
      1: 16, 2: 16, 3: 16, 4: 16 
 

 Sheet 5: "Table_2"
 
    Custom column widths (column: width)
      1: 16, 2: 16, 3: 16, 4: 16 
 

 Sheet 6: "Table_3"
 
    Custom column widths (column: width)
      1: 16, 2: 16, 3: 16, 4: 16 
 

 Sheet 7: "Table_4"
 
    Custom column widths (column: width)
      1: 16, 2: 16, 3: 32 
 

 Sheet 8: "Table_5"
 
    Custom column widths (column: width)
      1: 16, 2: 16, 3: 32 
 

 Sheet 9: "Table_6"
 
    Custom column widths (column: width)
      1: 16, 2: 16, 3: 32 
 

 
 Worksheet write order: 1, 2, 3, 4, 5, 6, 7, 8, 9
 Active Sheet 1: "Cover" 
    Position: 1

• Doesn’t really matter what the Workbook-class object looks like, but the summary shows the worksheets that have been created and some of the custom changes, like row heights.

Write

openxlsx::saveWorkbook(
  egg_wb,
  "eggs/2022-07-28_eggs-packers_test.xlsx",
  overwrite = TRUE
)

• {openxlsx} already has a function to write a Workbook-class object to disk.
• This saves us hassle.
• There’s also a handy openXL() function for opening a temporary copy.

Crack it open

• Lets take a look at the output spreadsheet.

• Most of this information was in the ‘information’ tab or footnotes of the original spreadsheet.
• Now it’s up front and provides an introduction to the workbook.
• Note the intentional simplicty in structure and style, which is in the best practice guidance.
• Makes it more accessible, useable, consistent.
• Note on the cover that:
  • the font is Arial 12
  • the title is in larger font and in A1
  • the font subsection headers are also adjusted
  • the subsection titles are in tall rows to create the illusion of whitespace without leaving an empty row

• Note in the contents that:
  • there is up-front screenreader-friendly information about the number of tables in the sheet
  • the table has no empty rows or columns
  • the columns have clear header names indicated by bold font
  • the table is marked up as a table (there’s a small marker in the lower right to indicate this), which is important

• On the notes page, note that:
  • the theming follows on from the cover and contents
  • the notes are in the standardised style of ‘note x’ written in square brackets

• Note in Table 1 that:
  • the theming again follows on from the other sheets
  • there is a new metadata element that provides the data source
  • the table has been dynamically placed in row 4 this time, after the three pre-table elements (was only two for the contents and notes pages)
  • the column has been judged by {a11ytables} to be numeric, despite the shorthand it contains, and so it has been correctly right-aligned

• Note in Table 2 that:
  • this table has a note in it, so there’s a new dynamically generated pre-table metadata row so users of screenreaders can be made aware early
  • the placement of the table is again dynamic; there are four pre-table metadata elements, so the table begins in row 5

• Table 5 has something different again, which is that the long header has led to an opinionated decision for the column to be widened
• An extra pre-table metadata row would also appear if we had provided information to the blank_cells argument of create_a11ytable()

But

Not perfect

• Final tweaks needed
• Assumes simple spreadsheets
• Garbage in, garbage out

• The package doesn’t get you a 100% compliant output.
• To keep the package simple, certain choices may not suit the user; some extra editing of the a11ytable or Workbook may be required, but you can intervene by editing the a11ytable or the Workbook.
• The package is opinionated for simplicity and is not completely flexible as a result (e.g. each cover subsection has only a single body row, the pre-table metadata is only table count, note presence, blank-cell meaning and data source).
• Thousands of spreadsheets are produced; there’s likely more complicated ones out there.
• Can’t completely protect from bad user input.

Clunk!

• {openxlsx} has limitations
• Output isn’t ODS
• Fiddly prep?

• {openxlsx} is versatile, but some things are tricky (placeholder zeroes in decimals, filling in file properties, etc), but I’m keeping an eye on {openxlsx2}.
• In particular, published spreadsheets should be in an open format, but {openxlsx} nor other packages write Workbook-class objects to ODS. Turns out ODS is a pretty clunky file format.
• A more focused example: notes are pretty clunky to add; might be a better solution?

Future

• Bugfixes, requests, user testing
• YAML input?
• Converge with gptables

• There’s plenty to be done to improve the package.
• As ever, user feedback and bugs; particularly testing internally with developers and externally with people who use assistive tech.
• Perhaps a new, more readable input option, like YAML, to arrange input to create_a11ytable().
• gptables and a11ytables should converge as much as possible so that analyst users can expect the same compliant output despite which tool they use. What are the optimum opinionated features?
• The problem is I have no time! PRs always welcome.

So what?

• So what am I telling you to do?
• To use the package? Sure.

Forget spreadsheets!

1. Improve user experience
2. Make things easier for you
3. Create common tools

• Syke! What I really want you to do is consider three things.

1. Guiding principle: improve accessibility, usability.
2. Simplify complex things, make reproducible, reusable, maintainable.
3. Stop divided, wasted effort to solve the same problem.

• Are you a manager that can encourage this type of work?
• Are you an analyst that could improve one small thing?
• Do you know someone who knows someone who might be interested in helping?

tl;dr

Generate best-practice spreadsheets with help from {a11ytables}

co-analysis.github.io/a11ytables

github.com/co-analysis/a11ytables

matt-dray.com

Credits

• Guidance docs: Hannah Thomas, Analysis Function
• gptables: Rowan Hemsi and contributors
• {a11ytables} contributors: Tim Taylor, Matt Kerlogue
• {openxlsx} and {pillar} contributors/maintainers
• Quarto presentations with Revealjs
• OpenMoji: paperclip, heart (edited, CC BY-SA 4.0)

• The idea is to fix the problem at the source.
• But this won’t be possible for older spreadsheets and for pipelines that have inertia.
• What can you do?
• Spreadsheet Munging Strategies by another colleague, Duncan Garmonsway.
• Untidy, inaccessible spreadsheets pose a particular problem for further analysis.
• See Duncan’s {tidyxl} and {unpivotr} R packages.
• I want to help limit the need for these tools (sorry Duncan).