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 844 124 MON WED
2 94 779 WED THU
3 927 33 THU WED
4 204 840 MON TUE
5 781 449 THU TUE
6 488 83 WED MON
7 935 17 TUE FRI
8 596 944 MON TUE
9 765 893 MON THU
10 421 288 WED TUE
Table 2: B
id test day measurement
1 a MON 844
1 b WED 124
2 a WED 94
2 b THU 779
3 a THU 927
3 b WED 33
4 a MON 204
4 b TUE 840
5 a THU 781
5 b TUE 449
6 a WED 488
6 b MON 83
7 a TUE 935
7 b FRI 17
8 a MON 596
8 b TUE 944
9 a MON 765
9 b THU 893
10 a WED 421
10 b TUE 288
Table 3: C
id test_a test_b
1 844,MON 124,WED
2 94,WED 779,THU
3 927,THU 33,WED
4 204,MON 840,TUE
5 781,THU 449,TUE
6 488,WED 83,MON
7 935,TUE 17,FRI
8 596,MON 944,TUE
9 765,MON 893,THU
10 421,WED 288,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=MON and measurement=844 are related as a single observation. Similarly, the data id=1, test=b, weekday=WED and measurement=124 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 430 974 WED WED
2 250 284 FRI TUE
3 151 694 MON WED
4 353 248 FRI WED
5 892 190 FRI WED
6 294 778 WED TUE
7 856 417 MON TUE
8 750 990 WED FRI
9 3 201 MON THU
10 44 548 THU TUE
Table 5: B
id test day measurement
1 a WED 430
1 b WED 974
2 a FRI 250
2 b TUE 284
3 a MON 151
3 b WED 694
4 a FRI 353
4 b WED 248
5 a FRI 892
5 b WED 190
6 a WED 294
6 b TUE 778
7 a MON 856
7 b TUE 417
8 a WED 750
8 b FRI 990
9 a MON 3
9 b THU 201
10 a THU 44
10 b TUE 548
Table 6: C
id test_a test_b
1 430,WED 974,WED
2 250,FRI 284,TUE
3 151,MON 694,WED
4 353,FRI 248,WED
5 892,FRI 190,WED
6 294,WED 778,TUE
7 856,MON 417,TUE
8 750,WED 990,FRI
9 3,MON 201,THU
10 44,THU 548,TUE

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