Tidy data

A few words about tidy data structure…

Author

Søren O’Neill & Steen Harsted

Published

Invalid Date

It is often said that 80% of data analysis is spent on cleaning and preparing data.

… a quote from the tidyverse page on tidy data.

Please consider the differences between:

  • Raw data
  • Cleaned data
  • Wrangled data

The raw data is the unadulterated version of the data as collected by what-ever-means you collect your data: questionnaires, machine sensor readings, etc. Within the limits set by GDPR rules, you should always keep a version of the raw data in its original form.

Cleaned data is the raw data after you have made the minimal changes necessary to make data useful. For example, deletion of observations which are flawed due to apparatus malfunction, or data entry mistakes, deletion of variables that were never collected, and conversion of data types if necessary. The process whereby the raw data is cleaned should be scripted (coded) to ensure, that it is reproducible and documented.

The cleaned data probably needs to be wrangled into a shape (and content) appropriate for specific analyses. For example, a wrangled data set may include only specific observations of specific variables relevant to a given analysis, in a format/shape suited for that analysis. This should also be scripted to ensure, that it is reproducible and documented.

The above link to the tidyverse page on tidy data provides a lot of information about tidy data, but the central principle is, that with tidy data:

  • Every column is a variable.
  • Every row is an observation.
  • Every cell is a single value.

…this sort of assumes, that data is stored in a rectangular data frame (or tibble).

On the following three tabs, you can see three examples of the same data set, structured in different ways. Look at each of them in turn…

Table 1: A
id test_a test_b day_of_test_a day_of_test_b
1 234 41 THU THU
2 435 480 MON WED
3 189 352 TUE WED
4 361 913 TUE THU
5 985 273 THU THU
6 488 925 THU THU
7 727 900 MON THU
8 421 449 MON FRI
9 463 874 THU WED
10 269 840 THU TUE
Table 2: B
id test day measurement
1 a THU 234
1 b THU 41
2 a MON 435
2 b WED 480
3 a TUE 189
3 b WED 352
4 a TUE 361
4 b THU 913
5 a THU 985
5 b THU 273
6 a THU 488
6 b THU 925
7 a MON 727
7 b THU 900
8 a MON 421
8 b FRI 449
9 a THU 463
9 b WED 874
10 a THU 269
10 b TUE 840
Table 3: C
id test_a test_b
1 234,THU 41,THU
2 435,MON 480,WED
3 189,TUE 352,WED
4 361,TUE 913,THU
5 985,THU 273,THU
6 488,THU 925,THU
7 727,MON 900,THU
8 421,MON 449,FRI
9 463,THU 874,WED
10 269,THU 840,TUE

Consider the three different ways to structure the data in light of:

  • Every column is a variable.
  • Every row is an observation.
  • Every cell is a single value.

Which of the three structures/tables represent the most tidy data structure?

Ask yourself, what different units of information (i.e. data points) constitutes each observation … and how the relation between data points specifies such an observation?

It is obvious, that each of the numerical values (measurements) represent data, but so does ‘id’ and the ‘weekday’, as well as the test ‘a’ versus ‘b’.

It seems from the data, that each id was tested on two occasions (‘a’ and ‘b’) which fell on different weekdays.

In other words, ‘id’, ‘test’, ‘weekday’ and ‘measurement’ all represent units of information (data points) which together constitutes an observation, but they are related in a non-trivial manner:

For instance, the data id=1, test=a, weekday=THU and measurement=234 are related as a single observation. Similarly, the data id=1, test=b, weekday=THU and measurement=41 are related as another unique observation.

The most tidy data structure is thus Table 2 above: Each row represents an observation and each column represents one of the variables that constitutes each observation. Note however, that there is no one-single variable that is unique per observation – instead, it is the combination of variables that constitutes a unique identifier (in this case, ‘id’ and ‘test’ in combination). This is not necessarily a problem.

Table 1 may seem more intuitive, and probably easier to set up as a data entry interface, e.g. a spreadsheet. At first impression, it also has the benefit that each line includes a unique identifier (id). In reality however, this data structure stores some information (e.g. whether the test was ‘a’ and ‘b’) as column names, rather than as actual data in cells. Table 3 is even more problematic, not only does it store data in the column names, it also stores multiple data points in each cell, and data of different types (numeric vs text) at that.

Det siges ofte, at 80% af dataanalysen bruges på at rense og forberede data.

…et citat fra tidyverse-siden om tidy data.

Overvej forskellene mellem:

  • Rå data
  • Rensede data
  • Tilrettelagte data

Rå data er den uforarbejdede version af data, som den er indsamlet gennem spørgeskemaer, maskinsensoraflæsninger osv. Inden for GDPR-reglernes rammer bør du altid gemme en version af de rå data i deres oprindelige form.

Rensede data er rå data, efter du har foretaget de minimale ændringer, der er nødvendige for at gøre data anvendelige. For eksempel kan det være at slette observationer, som er fejlbehæftede på grund af apparatfejl, tastefejl eller manglende variabler, samt at konvertere datatyper om nødvendigt. Processen, hvorved de rå data renses, bør være scriptet (kodet) for at sikre, at den er reproducerbar og dokumenteret.

De rensede data skal sandsynligvis tilrettes til en form (og et indhold), der er passende til specifikke analyser. For eksempel kan et tilrettet datasæt inkludere kun specifikke observationer af specifikke variabler, der er relevante for en given analyse, i et format der passer til denne analyse. Dette bør også scriptes for at sikre, at det er reproducerbart og dokumenteret.

Ovenstående link til tidyverse-siden om tidy data indeholder meget information om tidy data, men det centrale princip er, at for tidy data gælder det, at:

  • Hver kolonne er en variabel.
  • Hver række er en observation.
  • Hver celle indeholder én enkelt værdi.

… dette forudsætter, at data opbevares i et rektangulært datasæt (eller tibble).

I de følgende tre faner kan du se tre eksempler på det samme datasæt, struktureret på forskellige måder. Undersøg hver af dem:

Table 4: A
id test_a test_b day_of_test_a day_of_test_b
1 719 514 FRI TUE
2 562 371 THU MON
3 301 363 MON MON
4 118 694 FRI TUE
5 538 534 MON TUE
6 42 318 FRI WED
7 454 336 WED THU
8 958 995 FRI WED
9 979 29 MON THU
10 550 152 WED WED
Table 5: B
id test day measurement
1 a FRI 719
1 b TUE 514
2 a THU 562
2 b MON 371
3 a MON 301
3 b MON 363
4 a FRI 118
4 b TUE 694
5 a MON 538
5 b TUE 534
6 a FRI 42
6 b WED 318
7 a WED 454
7 b THU 336
8 a FRI 958
8 b WED 995
9 a MON 979
9 b THU 29
10 a WED 550
10 b WED 152
Table 6: C
id test_a test_b
1 719,FRI 514,TUE
2 562,THU 371,MON
3 301,MON 363,MON
4 118,FRI 694,TUE
5 538,MON 534,TUE
6 42,FRI 318,WED
7 454,WED 336,THU
8 958,FRI 995,WED
9 979,MON 29,THU
10 550,WED 152,WED

Overvej de tre forskellige måder at strukturere data på i lyset af:

  • Hver kolonne er en variabel.
  • Hver række er en observation.
  • Hver celle indeholder én enkelt værdi.

Hvilken af de tre strukturer/tabeller repræsenterer den mest tidy data-struktur?

Spørg dig selv, hvilke forskellige informationsenheder (dvs. datapunkter) udgør hver observation, og hvordan relationen mellem datapunkter specificerer en sådan observation.

Det er tydeligt, at hver af de numeriske værdier (målinger) repræsenterer data, men det samme gør ‘id’ og ‘ugedag’ samt test ‘a’ versus ‘b’.

Ud fra dataene ser det ud til, at hver id blev testet to gange (‘a’ og ‘b’), som fandt sted på forskellige ugedage.

Med andre ord repræsenterer ‘id’, ‘test’, ‘ugedag’ og ‘måling’ informationsenheder (datapunkter), der tilsammen udgør en observation, men de er relateret på en ikke-triviel måde:

For eksempel er dataene id=1, test=a, weekday=FRI og measurement=719 relateret som en enkelt observation. Tilsvarende er dataene id=1, test=b, weekday=TUE og measurement=514 relateret som en anden unik observation.

Den mest tidy datastruktur er således Table 2 ovenfor: Hver række repræsenterer en observation, og hver kolonne repræsenterer en af de variabler, der udgør hver observation. Bemærk dog, at der ikke er én enkelt variabel, der er unik per observation – i stedet er det kombinationen af variabler, der udgør en unik identifikator (i dette tilfælde ‘id’ og ‘test’ i kombination). Dette er ikke nødvendigvis et problem.

Table 1 kan virke mere intuitiv og sandsynligvis lettere at opsætte som en dataindtastningsgrænseflade, fx et regneark. Ved første øjekast har den også fordelen af, at hver linje inkluderer en unik identifikator (id). I virkeligheden gemmer denne datastruktur dog noget information (fx om testen var ‘a’ og ‘b’) som kolonnenavne, snarere end som faktiske data i celler. Table 3 er endnu mere problematisk, da den ikke kun gemmer data i kolonnenavne, men også gemmer flere datapunkter i hver celle og data af forskellige typer (numerisk vs. tekst).