1 of 52

Get good data out of bad spreadsheets

With tidy tools

Duncan Garmonsway

@nacnudus

2 of 52

https://twitter.com/albertocairo/status/963133927530483712

3 of 52

https://twitter.com/albertocairo/status/963133927530483712

4 of 52

https://nacnudus.github.io/underground/

5 of 52

Uh oh

https://nacnudus.github.io/nzpullover/

To build up a bit more intuition, here’s a simpler spreadsheet with a problem. It’s the amount of money the New Zealand police fined speeding drivers. I was involved in making it. And what’s worse, I knew better but I did it anyway.

This is a bad spreadsheet because of the cells in the orange box. There are two rows of column headers. The cell beneath “Dec” doesn’t just relate to December, it also relates to 2009. That’s a problem because there is no way with any standard R package or package in any language to say that offices fined people $38,750 in December 2009 in part of Auckland.

Why do people like me create spreadsheets like these? Because we believe that’s what you want. In the NZ Police, nobody ever asked us for tidy data. Only policy analysts interacted with us, and they wanted spreadsheets. If you need tidy data, please ask for it.

Worked example in the nzpullover package: https://github.com/nacnudus/nzpullover/tree/master/data-raw

Source data: https://www.police.govt.nz/about-us/publication/road-policing-driver-offence-data-january-2009-march-2019

6 of 52

7 of 52

@sharlagelfand https://twitter.com/sharlagelfand/status/996739127562391552

@dvaughan32

https://blog.davisvaughan.com/post/tidying-excel-cash-flow-spreadsheets-in-r/

I collect spreadsheets like this. They’re easy to find because people complain about them on Twitter, and I’ve had to use them too. They’re not just one table, they’re several tables joined by a common dimension -- on the left they’re joined by year, on the right they’re joined by month.

Left: via Sharla Gelfand @sharlagelfand https://twitter.com/sharlagelfand/status/996739127562391552

Worked example in Spreadsheet Munging Strategies: https://nacnudus.github.io/spreadsheet-munging-strategies/toronto-transit-commission.html

Source data: https://portal0.cf.opendata.inter.sandbox-toronto.ca/dataset/ttc-ridership-analysis

Right: via Davis Vaughan @dvaughan32 https://blog.davisvaughan.com/post/tidying-excel-cash-flow-spreadsheets-in-r/

Worked example in Spreadsheet Munging Strategies: https://nacnudus.github.io/spreadsheet-munging-strategies/cashflows.html

Source data: https://github.com/DavisVaughan/tidying-excel-cashflows-blog-companion

8 of 52

https://www.brodrigues.co/blog/2018-09-11-human_to_machine/

9 of 52

https://nacnudus.github.io/nzmigration/

@kuri_pablo http://170.238.108.227/binhnd/RpWebEngine.exe/Portal?BASE=CPVHND2013NAC&lang=ESP

10 of 52

https://github.com/nacnudus/ukfarm

11 of 52

https://www.mic.com/articles/160125/pokemon-go-cp-changes-balance-update-hits-every-pokemon-but-one

https://www.reddit.com/r/TheSilphRoad/comments/5e6jev/silph_research_group_on_cp_balancing/

https://docs.google.com/spreadsheets/d/1_WS9FIGSlKVk6XAJTE3TxXIqlBPIQ5Lsx5qifE72vXY/edit?usp=sharing

12 of 52

https://informationisbeautiful.net/visualizations/best-in-show-whats-the-top-data-dog/ via @JennyBryan

13 of 52

I’m here to reassure you that anything can be tidy.

Except ambiguity. A cat can be tidy, but Schroedinger’s cat can’t be tidy, because it’s ambiguous. You don’t know whether the cat exists or not. It’s uncertain. The value in the previous slide “1.83 - really?” is uncertain -- if you have uncertain information, you need to deal with that first before you tidy it. But anything else can be tidy.

How can a cat be tidy? A cat is cat-shaped, not square. But a cat is mostly fur, millions of individual hairs, and you can know the position of every hair, and then you can manipulate the position so that all the hairs lie along a straight edge. This is how animators can create an animated cat. They can create a database that describes the position of every individual hair, and its position, and then they can control the position to give it movement and life. An animated cat is tidy data.

A photograph of a cat is also tidy data. That photograph can be described as a database of individual pixels. Every pixel has an x position and a y position, and a colour. Colour can be represented as tidy data -- every colour has a red value and a blue value and a green value.

This cat image was inspired by a similar one tweeted by Jenny Bryan from RStudio. She was tweeting about rectangular data, meaning something slightly different from tidy data. In this talk I don’t make a distinction -- if I say rectangular or tidy I just mean something like a data frame. Once you have a data frame, you can use the tidyverse.

Image: several versions found on the web from various sources, inspired by Jenny Bryan’s tweet https://twitter.com/JennyBryan/status/1126582138344595456

14 of 52

15 of 52

First let’s think about how a data frame rectangle works. The image shows a ‘tibble’, which is a kind of data frame that has a nice print method.

Dimension: 2 x 3, which tells us that the data is rectangular. Every column has the same number of rows. Every row has the same number of columns. These are useful properties. Because of those properties you can say that every cell in in the first column is the name of a pupil. And every cell in the same row describes the same pupil.
Data types: each column has a data type. That means you can do things with the columns that are guaranteed to work. You could take the mean of the Mark column, and it would work because every cell is a number.
Positions: the position of a cell in a particular row and a particular column tells you everything you need to know about the context of that cell. You don’t have to read any of the other rows. You can read one row at a time and get all the information. That means you can filter for what interests you.

16 of 52

Compare that with the spreadsheet on the right. The information is the almost the same, but all the rules of the data frame have been broken.

Not rectangular. The cell in the top left corner is missing. The cells beside the names Ron and Ginny are also missing -- what would it mean if there were numbers in those cells? In terms of dimensions you could think of this as a 2d projection of a 3d array where the dimensions are pupils, years and subjects, and the values are marks.
Data types: each column does not have the same data type. They are completely independent of each other. There’s that value “10 - really?”, which is a character value. It’s nice to be able to do write that kind of thing in a spreadsheet, which is one reason why people use them. I don’t know how you would record that information in a data frame. You could put it in another column, but what would you do with it?
Positions: A cell’s row and column position doesn’t tell you everything about that cell. You can’t read one row and know everything. If you read thee fourth row, you see a mark of 2 in potions in the 2nd year, but you don’t know whose mark.
Formatting: It’s obvious to you, because of formatting and because you understand language, that the cells “Ron” and “1st year” describe different things. The “1st year” cell is indented slightly, and the “Ron” cell is bold, so they are distinguished. You intuitively wrangle that in your brain. But the computer doesn’t necessarily know about it. What does the computer know?
Formulas: what if there is also a formula in one of those cells?

How can we get a spreadsheet like that into a rectangular data frame?

17 of 52

The standard tool for reading spreadsheets is {readxl} from the tidyverse. It produces the data frame on the left. It lets you down in a few ways

It thought the cells in the top row were column names, which they are, but one is missing so it invented the name “...1”. That’s not a daft thing to do, but we can do better.
It converted the numbers in the right-hand column into character values, because it was forced to by the pink cell, which is a character value.
It didn’t import any information about the indentation of the cells in the first column, or the colour of the text, or anything that might help you filter for particular cells that mean particular things. For example it will be difficult to filter for a person’s name, because the names are mixed up with the 1st year and 2nd year values, and you can’t use the formatting to distinguish between them.

Readxl is a wonderful tool that wasn’t designed for this job, and in fact in the README they have very kindly suggested that if you have this kind of spreadsheet you should consider using one of my packages instead.

18 of 52

19 of 52

20 of 52

21 of 52

22 of 52

filter(row == 3, col == 1)$character

23 of 52

readr::melt_csv()

googlesheets4::sheets_cells()

tidyxl::xlsx_cells()

24 of 52

25 of 52

26 of 52

27 of 52

28 of 52

29 of 52

30 of 52

31 of 52

32 of 52

33 of 52

34 of 52

35 of 52

36 of 52

37 of 52

38 of 52

39 of 52

40 of 52

41 of 52

One-row-per-cell format:

42 of 52

One-row-per-cell format:

tidyxl

readr

googlesheets4

43 of 52

One-row-per-cell format:

tidyxl

readr

googlesheets4

behead() each header in turn

44 of 52

One-row-per-cell format:

tidyxl

readr

googlesheets4

behead() each header in turn

unpivotr

45 of 52

46 of 52

Via David Robinson and Jenny Bryan https://rpubs.com/dgrtwo/tidying-enron

47 of 52

48 of 52

((IF((103-B$89)=103,0,(103-B$89)))+(IF((200-B$95)=200,0,(200-B$95)))+(IF((196-B$98)=196,0,(196-B$98)))+(IF((200-B$101)=200,0,(200-B$101)))+(IF((70-B$104)=70,0,(MIN(40,(70-B$104))))+(IF((78-B$109)=78,0,(MIN(50,(78-B$109)))))+(IF((103-B$114)=103,0,(MIN(66,(103-B$114)))))+(IF((195-B$119-B$124-B$129-B$134-B$139)=195,0,(MIN(70,(195-B$119-B$124-B$129-B$134-B$139)))))+(IF((64-B$144)=64,0,(MIN(50,(64-B$144)))))+(IF((48-B$149)=48,0,(MIN(20,(48-B$149)))))+(IF((44-B$154)=44,0,(MIN(20,(44-B$154)))))+(IF((130-B$159)=130,0,(MIN(20,(130-B$159)))))))

49 of 52

50 of 52

51 of 52

https://rstudio.cloud/project/269487

52 of 52

Get good data out of bad spreadsheets

My stuff:

https://github.com/nacnudus/tidyxl

https://github.com/nacnudus/unpivotr

https://nacnudus.github.io/spreadsheet-munging-strategies/

https://rstudio.cloud/project/269487

https://github.com/ianmoran11/tidyABS

https://databaker.sensiblecode.io/

Duncan Garmonsway

@nacnudus