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 388 524 TUE TUE
2 439 579 THU TUE
3 637 235 TUE WED
4 508 615 THU WED
5 629 904 THU MON
6 411 154 TUE THU
7 962 29 TUE MON
8 70 374 THU FRI
9 860 929 MON MON
10 148 327 WED FRI
Table 2: B
id test day measurement
1 a TUE 388
1 b TUE 524
2 a THU 439
2 b TUE 579
3 a TUE 637
3 b WED 235
4 a THU 508
4 b WED 615
5 a THU 629
5 b MON 904
6 a TUE 411
6 b THU 154
7 a TUE 962
7 b MON 29
8 a THU 70
8 b FRI 374
9 a MON 860
9 b MON 929
10 a WED 148
10 b FRI 327
Table 3: C
id test_a test_b
1 388,TUE 524,TUE
2 439,THU 579,TUE
3 637,TUE 235,WED
4 508,THU 615,WED
5 629,THU 904,MON
6 411,TUE 154,THU
7 962,TUE 29,MON
8 70,THU 374,FRI
9 860,MON 929,MON
10 148,WED 327,FRI

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=TUE and measurement=388 are related as a single observation. Similarly, the data id=1, test=b, weekday=TUE and measurement=524 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 195 790 WED FRI
2 996 602 FRI TUE
3 950 292 WED MON
4 470 256 MON THU
5 941 624 WED MON
6 516 576 THU FRI
7 959 166 FRI WED
8 630 902 FRI WED
9 364 227 WED TUE
10 327 600 THU FRI
Table 5: B
id test day measurement
1 a WED 195
1 b FRI 790
2 a FRI 996
2 b TUE 602
3 a WED 950
3 b MON 292
4 a MON 470
4 b THU 256
5 a WED 941
5 b MON 624
6 a THU 516
6 b FRI 576
7 a FRI 959
7 b WED 166
8 a FRI 630
8 b WED 902
9 a WED 364
9 b TUE 227
10 a THU 327
10 b FRI 600
Table 6: C
id test_a test_b
1 195,WED 790,FRI
2 996,FRI 602,TUE
3 950,WED 292,MON
4 470,MON 256,THU
5 941,WED 624,MON
6 516,THU 576,FRI
7 959,FRI 166,WED
8 630,FRI 902,WED
9 364,WED 227,TUE
10 327,THU 600,FRI

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=WED og measurement=195 relateret som en enkelt observation. Tilsvarende er dataene id=1, test=b, weekday=FRI og measurement=790 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).