Extract tables from messy spreadsheets with jailbreakr

╰半橙微兮° 2022-09-25 10:19 41阅读 0赞

R has some good tools for importing data from spreadsheets, among them the [readxl package][] for Excel and the [googlesheets package][] for Google Sheets. But these only work well when the data in the spreadsheet are arranged as a rectangular table, and not overly encumbered with formatting or generated with formulas. As Jenny Bryan pointed out in her [recent talk at the useR!2016 conference][recent talk at the useR_2016 conference] (and embedded below, or [download PDF slides here][]), in practice few spreadsheets have “a clean little rectangle of data in the upper-left corner”, because most people use spreadsheets not just a file format for data retrieval, but also as a reporting/visualization/analysis tool.

Nonetheless, for a practicing data scientist, there’s a lot of useful data locked up in these messy spreadsheets that needs to be imported into R before we can begin analysis. As just one example given by Jenny in her talk, this spreadsheet was included as one of 15,000 spreadsheet attachments (one with 175 tabs!) in the [Enron Corpus][].

[![Enron][]][Enron 1]

To make it easier to import data into R from messy spreadsheets like this, Jenny and co-author Richard G. FitzJohn created the [jailbreakr package][]. The package is in its early stages, but it can already import Excel (xlsx format) and Google Sheets intro R as a new “linen” objects from which small sub-tables can easily be extracted as data frames. It can also print spreadsheets in a condensed text-based format with one character per cell — useful if you’re trying to figure out why an apparently simple spreadsheet isn’t importing as you expect. (Check out the “weekend getaway winner” story near the end of Jenny’s talk for a great example.)

The jailbreakr package isn’t yet on CRAN, but if you want to try it out you can download it from the Github repository (or even contribute!) at the link below.

Github (rsheets): [jailbreakr][jailbreakr package]

[readxl package]: https://github.com/hadley/readxl
[googlesheets package]: https://github.com/jennybc/googlesheets
[recent talk at the useR_2016 conference]: https://channel9.msdn.com/Events/useR-international-R-User-conference/useR2016/jailbreakr-Get-out-of-Excel-free
[download PDF slides here]: https://github.com/jennybc/2016-06_spreadsheets/raw/master/2016-06_useR-stanford.pdf
[Enron Corpus]: https://gitlab.com/rsheets/enron_corpus
[Enron]: /images/20220718/590afd1ca58a4c6693ed3efeec7d660f.png
[Enron 1]: http://revolution-computing.typepad.com/.a/6a010534b1db25970b01bb092b8911970d-pi
[jailbreakr package]: https://github.com/rsheets/jailbreakr