Ideas for using readxl to increase reproducibiliy and reduce tedium.

Reproducibility is much easier in theory than in reality. Here are some special dilemmas we face with spreadsheets:

  • We say: Keep raw data raw! But we also say: Store data in a tool-agnostic, future-proof format! If data comes in the form of an .xls[x] file, we’re in a pickle. The .xls[x] file should obviously be preserved, and probably write-protected. But a faithful copy as CSV is a wonderful complement, as long as you can ensure the two are the same.
  • We say: Don’t do analysis “by hand” or “by mouse”! But then we break this rule by manually exporting spreadsheets to file with File > Save As … > (save to .csv). readxl helps you get data directly out of a spreadsheet and into R, where you can record every step of your analysis as code. Below we show how to cache a CSV snapshot as part of this process.

The examples below also demonstrate the use of functional programming or “apply” techniques to iterate over the worksheets in a workbook.

Cache a CSV snapshot

Break analyses into logical steps, via a series of scripts that relate to one theme, such as “clean the data” or “make exploratory and diagnostic plots”.

This forces you to transmit info from step i to step i + 1 via a set of output files. The cumulative outputs of steps 1, 2, …, i are available as inputs for steps i + 1 and beyond.

These outputs constitute an API for your analysis, i.e. they provide clean entry points that can be used (and understood) in isolation, possibly using an entirely different toolkit. Contrast this with the alternative of writing one monolithic script or transmitting entire workspaces via save(), load(), and R-specific .rds files.

If raw data is stored only as an Excel spreadsheet, this limits your ability to inspect it when solving the little puzzles that crop up in dowstream work. You’ll need to fire up Excel (or similar) and get busy with your mouse. You certainly can’t poke around it or view diffs on GitHub.

Solution: cache a CSV snapshot of your raw data tables at the time of export. Even if you use read_excel() for end-to-end reproducibility, this complementary CSV leaves your analysis in a more accessible state.

Pipe the output of read_excel() directly into readr::write_csv() like so:

Why does this work? readr::write_csv() is a well-mannered “write” function: it does its main job and returns its input invisibly. The above command reads the iris sheet from readxl’s datasets.xlsx example workbook and caches a CSV version of the resulting data frame to file.

Let’s check. Did we still import the data? Did we write the CSV file?

Yes! Is the data written to CSV an exact copy of what we imported from Excel?

Yes! If we needed to restart or troubleshoot this fictional analysis, iris-raw.csv is available as a second, highly accessible alternative to datasets.xlsx.

Iterate over multiple worksheets in a workbook

Some Excel workbooks contain only data and you are tempted to ask “Why, God, why is this data stored in Excel? Why not store this as a series of CSV files?” One possible answer is this: because the workbook structure keeps them all together.

Let’s accept that this happens and that it is not entirely crazy. How can you efficiently load all of that into R?

Here’s how to load all the sheets in a workbook into a list of data frames:

  • Get worksheet names as a self-named character vector (these names propagate nicely).
  • Use purrr::map() to iterate sheet reading.

CSV caching and iterating over sheets

What if we want to read all the sheets in at once and simultaneously cache to CSV? We define read_then_csv() as read_excel(...) %>% write_csv() and use purrr::map() again.

We could even define this on-the-fly as an anonymous function inside map(), but I think this is more readable.

In a real analysis, starting with workbook "foo.xlsx", you might want to create the directory foo and place the CSVs inside that.

Concatenate worksheets into one data frame

What if the datasets found on different sheets have the same variables? Then you’ll want to row-bind them, after import, to form one big, beautiful data frame.

readxl ships with an example sheet deaths.xlsx, containing data on famous people who died in 2016 or 2017. It has two worksheets named “arts” and “other”, but the spreadsheet layout is the same in each and the data tables have the same variables, e.g., name and date of death.

The map_df() function from purrr makes it easy to iterate over worksheets and glue together the resulting data frames, all at once.

  • Store a self-named vector of worksheet names (critical for the ID variable below).
  • Use purrr::map_df() to import the data, create an ID variable for the source worksheet, and row bind.
path <- readxl_example("deaths.xlsx")
deaths <- path %>%
  excel_sheets() %>%
  set_names() %>% 
  map_df(~ read_excel(path = path, sheet = .x, range = "A5:F15"), .id = "sheet")
print(deaths, n = Inf)
#> # A tibble: 20 x 7
#>    sheet Name              Profession   Age `Has kids` `Date of birth`    
#>    <chr> <chr>             <chr>      <dbl> <lgl>      <dttm>             
#>  1 arts  David Bowie       musician     69. TRUE       1947-01-08 00:00:00
#>  2 arts  Carrie Fisher     actor        60. TRUE       1956-10-21 00:00:00
#>  3 arts  Chuck Berry       musician     90. TRUE       1926-10-18 00:00:00
#>  4 arts  Bill Paxton       actor        61. TRUE       1955-05-17 00:00:00
#>  5 arts  Prince            musician     57. TRUE       1958-06-07 00:00:00
#>  6 arts  Alan Rickman      actor        69. FALSE      1946-02-21 00:00:00
#>  7 arts  Florence Henders… actor        82. TRUE       1934-02-14 00:00:00
#>  8 arts  Harper Lee        author       89. FALSE      1926-04-28 00:00:00
#>  9 arts  Zsa Zsa Gábor     actor        99. TRUE       1917-02-06 00:00:00
#> 10 arts  George Michael    musician     53. FALSE      1963-06-25 00:00:00
#> 11 other Vera Rubin        scientist    88. TRUE       1928-07-23 00:00:00
#> 12 other Mohamed Ali       athlete      74. TRUE       1942-01-17 00:00:00
#> 13 other Morley Safer      journalist   84. TRUE       1931-11-08 00:00:00
#> 14 other Fidel Castro      politician   90. TRUE       1926-08-13 00:00:00
#> 15 other Antonin Scalia    lawyer       79. TRUE       1936-03-11 00:00:00
#> 16 other Jo Cox            politician   41. TRUE       1974-06-22 00:00:00
#> 17 other Janet Reno        lawyer       78. FALSE      1938-07-21 00:00:00
#> 18 other Gwen Ifill        journalist   61. FALSE      1955-09-29 00:00:00
#> 19 other John Glenn        astronaut    95. TRUE       1921-07-28 00:00:00
#> 20 other Pat Summit        coach        64. TRUE       1952-06-14 00:00:00
#> # ... with 1 more variable: `Date of death` <dttm>

Note the use of range = "A5:E15" here. deaths.xlsx is a typical spreadsheet and includes a few non-data lines at the top and bottom and this argument specifies where the data rectangle lives.

Putting it all together

All at once now:

  • Multiple worksheets feeding into one data frame
  • Sheet-specific target rectangles
  • Cache to CSV upon import

Even though the worksheets in deaths.xlsx have the same layout, we’ll pretend they don’t and specify the target rectangle in two different ways here. This shows how this can work if each worksheet has it’s own peculiar geometry. Here’s the workflow:

  • Store a self-named vector of worksheet names.
  • Store a vector of cell range specifications.
  • Use purrr::map2_df() to iterate over those two vectors in parallel, importing the data, row binding, and creating an ID variable for the source worksheet.
  • Cache the unified data to CSV.
path <- readxl_example("deaths.xlsx")
sheets <- path %>%
  excel_sheets() %>% 
  set_names()
ranges <- list("A5:F15", cell_rows(5:15))
deaths <- map2_df(
  sheets,
  ranges,
  ~ read_excel(path, sheet = .x, range = .y),
  .id = "sheet"
) %>%
  write_csv("deaths.csv")
print(deaths, n = Inf)
#> # A tibble: 20 x 7
#>    sheet Name  Profession   Age `Has kids` `Date of birth` `Date of death`
#>    <chr> <chr> <chr>      <dbl> <lgl>      <chr>           <chr>          
#>  1 arts  Davi… musician     69. TRUE       1947-01-08T00:… 2016-01-10T00:…
#>  2 arts  Carr… actor        60. TRUE       1956-10-21T00:… 2016-12-27T00:…
#>  3 arts  Chuc… musician     90. TRUE       1926-10-18T00:… 2017-03-18T00:…
#>  4 arts  Bill… actor        61. TRUE       1955-05-17T00:… 2017-02-25T00:…
#>  5 arts  Prin… musician     57. TRUE       1958-06-07T00:… 2016-04-21T00:…
#>  6 arts  Alan… actor        69. FALSE      1946-02-21T00:… 2016-01-14T00:…
#>  7 arts  Flor… actor        82. TRUE       1934-02-14T00:… 2016-11-24T00:…
#>  8 arts  Harp… author       89. FALSE      1926-04-28T00:… 2016-02-19T00:…
#>  9 arts  Zsa … actor        99. TRUE       1917-02-06T00:… 2016-12-18T00:…
#> 10 arts  Geor… musician     53. FALSE      1963-06-25T00:… 2016-12-25T00:…
#> 11 other Vera… scientist    88. TRUE       1928-07-23T00:… 2016-12-25T00:…
#> 12 other Moha… athlete      74. TRUE       1942-01-17T00:… 2016-06-03T00:…
#> 13 other Morl… journalist   84. TRUE       1931-11-08T00:… 2016-05-19T00:…
#> 14 other Fide… politician   90. TRUE       1926-08-13T00:… 2016-11-25T00:…
#> 15 other Anto… lawyer       79. TRUE       1936-03-11T00:… 2016-02-13T00:…
#> 16 other Jo C… politician   41. TRUE       1974-06-22T00:… 2016-06-16T00:…
#> 17 other Jane… lawyer       78. FALSE      1938-07-21T00:… 2016-11-07T00:…
#> 18 other Gwen… journalist   61. FALSE      1955-09-29T00:… 2016-11-14T00:…
#> 19 other John… astronaut    95. TRUE       1921-07-28T00:… 2016-12-08T00:…
#> 20 other Pat … coach        64. TRUE       1952-06-14T00:… 2016-06-28T00:…

Base version

Rework examples from above but using base R only, other than readxl.

Iterate over multiple worksheets in a workbook