5 min read

Spreadsheet Love in the time of COVID-19

2020-10-11

I unapologetically love excel spreadsheets. As a data analyst I am happy to explore a downloaded file with power query, calculations and pivot tables to quickly get a grasp of a data situation. As a developer and architect I hate repetitive actions and challenge myself to do better than opening a spreadsheet when I need a quick data answer. But that’s no reason to get down on spreadsheets because they are often the best and only source of data when supporting software developed in response to a pandemic.

In July I had the unique opportunity to contribute to the King County HMAC (Health & Medical Area Command) response team by taking a turn as PPE Technical Specialist and support the allocation of PPE to PHKSC (Public Health Seattle King County) agencies, hospitals, long term care facilities, community based organizations, homeless shelters, dental and health clinics. I rotated in from KCIT to maintain the allocation algorithm, developed by a brilliant epidemiologist at King County, Daniel Casey. It’s a Shiny R web application that features drake and data tables to ensure fast and efficient data processing.

As a technologist with a love of R, shiny and markdown I was very excited to get this chance to serve King County and perhaps a little daunted by the responsibility of ensuring the efficient and fair allocation of PPE throughout the county in accordance with state guidelines and adhering to the equity direction by the county. Many of the data sources that feed into the allocation algorithm come in the form of spreadsheets, some of which involve human data entry via a public web form open to anyone providing health services in King County. Long term care facilities are licensed and are eligible to receive N95s when they have confirmed or suspected COVID-19 cases. Within the allocation algorithm code, the license number entered in the order form is used to check current reports of cases tracked by Public Health Seattle King County systems.

Data Driven Documentation

\(Bookdown + Excel + DT = Love\)

When triggered by code validation or manual QA, I would occasionally need to verify a license number for a facility by checking a downloaded file from the Washington State Department of Social and Health Services website to reconcile a requestor’s information to ensure that we accurately retrieved active case information. Navigating to the folder of my lookup file and filtering by name wasn’t the most onerous process in the world, but also was not delightful or efficient. As a huge fan of Yihui Xie’s bookdown package, I decided to combine all of the allocation documentation plus lookup files, including the long term care facilities file into a book! I initially read in the same files used by the allocation algorithm code but realized that viewing data from large files with thousands of records is absolutely not delightful.

The DT package to the rescue!

library(DT)
datatable(data, extensions = c('Buttons'), 
          options = list(dom = 'Blfrtip',
                         buttons = c('copy', 'excel', 'csv', 'print'),
                         pageLength = 5,
                         lengthMenu = list(c(5,10,25,50,-1),
                                           c(5,10,25,50,"All"))))

This package provides a searchable, paginating and downloadable experience that makes data validation an absolute delight! Powered by a JavaScript html widget, it’s trivial to create data driven documentation in any R Markdown document with html output. If users prefer pdfs to read the rest of the documentation, as noted in chapter 2.10 HTML Widgets, you can use webshot::install_phantomjs() so that a snapshot will be inserted into the pdf output in place of the widget.

Dynamic Playbooks

As an analyst I love to document requirements and decisions made in One Note and Word to enable collaboration with others. I also think that migrating requirements to Markdown and referencing real data with html widgets to author an interactive playbook is most delightful!