Recreating A Table By The Economist Using Reactable
May 19, 2020
The Economist recently released a series of country-level datasets on ‘excess mortality’, a term used to describe ‘the gap between the total number of people who died from any cause, and the historical average for the same place and time of year.’ In simpler terms, the measure captures how many deaths are happening that shouldn’t be.
In the (free!) articles accompanying that data, I came across the following table:
I thought the table was clean and sent a clear message. The addition of inline barcharts is not intrusive but still helps the reader takeaway insights about the data. It’s a rather pretty table. Having recently come across Greg Lin’s package reactable, I thought this could be a good opportunity to try my hand at recreating the above.
(Coincidentally, while I was working on this project, Malcolm Barrett released a similar blog post documenting his recreation of a NYT table using gt. Check it out!)
Our process uses standard packages: reactable (obviously), htmltools as its buddy, lubridate for days and times, hrbrthemes for The Economist’s font, and tidyverse for general purpose data wrangling.
Gather the data
You can definitely skip this step if you’re not interested in the data collection and cleaning process.
Unfortunately, one of the more time-consuming steps of this project was getting the data in the same format The Economist used in their article. The data they released comes in the form of a series of country-level CSVs; although helpful for country-level analysis, this meant that we have to modify the data into a joined format in order to create a table.
Let’s begin by creating a function which reads in each individual CSV, selects relevant columns, and stores that specific dataframe in the global environment.
With that function created, we then want to loop it with each country The Economist has included.
To do so, we grab their list of sources from GitHub and pull each country into a list:
Then, we loop!
Now, we have a list of dataframes, with each containing one country’s data on excess mortality.
Finally, we merge each of these new dataframes into one master dataset. Here, we are defining in dfs a list of all objects in the global environment that are of the structure data frame. Then, we rbind them all together!
But unfortunately, that’s not all. We need to filter our data to only include the places that are in The Economist’s table. To make matters more difficult, the table’s identifying row is titled ‘Region/Country’, and includes data from two separate rows in the CSVs.
Let’s begin by manually defining and filtering according to the countries and regions that The Economist includes. (This selection does not seem to have an order to it; as such, it has to be manual).
Because the table only has one row for country/region, and groups them accordingly, we can go ahead and replace the country variable in the data_filtered_regions dataframe with region.
Next, we notice that the table title says ‘Excess mortality since region/country’s first 50 covid deaths.’ This means we need to exclude counts of excess deaths before a region had 50 COVID deaths.
At this point (after only selecting our relevant columns), our data looks like this:
We need to group each country according to its total deaths related to COVID-19, and excess deaths. Then, using those two numbers, we calculate the percentage of excess deaths attributable to COVID-19. This can be used as a metric for underreporting of COVID-19 cases in a country.
The only thing missing at this point is the date range. In order to find and display the dates, we need to find the first date after a given country/region hit 50 COVID-19 cases and the last date in the data for that country/region.
How do we do this? First, we’ll create a function called append_date_suffix which, according to a given day, appends the appropriate suffix.
We’ll then group by the country variable and find the min and max date (with the minimum only appearing after a country has seen 50 COVID deaths). Then, we do a lot of formatting of individual days and months, and append them all together with dashes in The Economist’s style. Sorry, there’s a lot going on here.
This creates date ranges that look like this:
Join these dates with our existing data…
and we get our finalized dataset:
Creating the table
Finally, we’re ready to take that dataset and create our table. We can begin by defining some parameters that make the table easier to use and more aesthetically pleasing. Here, we sort according to excess deaths (but don’t include an arrow), make it compact, and show all results on one page.
Next, let’s make the column headers stylistically similar to The Economist. We do so with reactable’s defaultColDef, where we define a colDef with styles for the header and regular cells. Here, we can include CSS (which you can find by inspecting the table at hand). Throughout this post, you’ll notice my constant references to font_es. This is from Bob Rudis’s hrbrthemes. It contains the font name for Economist Sans Condensed, which is the font that The Economist uses!
Now, we can start to format the specific columns appropriately. The three easiest columns are Region/Country, Time Period, COVID-19 as % of Total. In each of these columns, we create a colDef which defines the column name, as well as some styling.
Add the barcharts
We can now create the ‘deaths’ columns, which include barcharts.
Let’s break that down step-by-step, with a focus on covid_deaths.
First, we need to define some CSS. reactable allows you to easily include CSS is RMarkdown documents, in chunks defined as css.
Now, let’s look at how we define covid_deaths:
This creates a table that looks like this:
Add a title
Finally, we can add the table title and subtitle. We do so by storing the above table in our environment. (This is the final table code!)
Now, we can include a title and subtitle above the table. We use some htmltools functions to create divs, headers, and paragraphs.
Yikes! Those font sizes don’t quite line up with The Economist’s. Let’s add classes to our divs to match their style.
Let’s compare that to the table we’re attempting to replicate. Note that some of the data has changed in the time between The Economist published their table and I created mine.