1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
|
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>Tidy data</title>
<script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to
// be compatible with the behavior of Pandoc < 2.8).
document.addEventListener('DOMContentLoaded', function(e) {
var hs = document.querySelectorAll("div.section[class*='level'] > :first-child");
var i, h, a;
for (i = 0; i < hs.length; i++) {
h = hs[i];
if (!/^h[1-6]$/i.test(h.tagName)) continue; // it should be a header h1-h6
a = h.attributes;
while (a.length > 0) h.removeAttribute(a[0].name);
}
});
</script>
<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
</style>
<style type="text/css">
code {
white-space: pre;
}
.sourceCode {
overflow: visible;
}
</style>
<style type="text/css" data-origin="pandoc">
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; }
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; }
code span.at { color: #7d9029; }
code span.bn { color: #40a070; }
code span.bu { color: #008000; }
code span.cf { color: #007020; font-weight: bold; }
code span.ch { color: #4070a0; }
code span.cn { color: #880000; }
code span.co { color: #60a0b0; font-style: italic; }
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; }
code span.do { color: #ba2121; font-style: italic; }
code span.dt { color: #902000; }
code span.dv { color: #40a070; }
code span.er { color: #ff0000; font-weight: bold; }
code span.ex { }
code span.fl { color: #40a070; }
code span.fu { color: #06287e; }
code span.im { color: #008000; font-weight: bold; }
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; }
code span.kw { color: #007020; font-weight: bold; }
code span.op { color: #666666; }
code span.ot { color: #007020; }
code span.pp { color: #bc7a00; }
code span.sc { color: #4070a0; }
code span.ss { color: #bb6688; }
code span.st { color: #4070a0; }
code span.va { color: #19177c; }
code span.vs { color: #4070a0; }
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; }
</style>
<script>
// apply pandoc div.sourceCode style to pre.sourceCode instead
(function() {
var sheets = document.styleSheets;
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue;
try { var rules = sheets[i].cssRules; } catch (e) { continue; }
var j = 0;
while (j < rules.length) {
var rule = rules[j];
// check if there is a div.sourceCode rule
if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") {
j++;
continue;
}
var style = rule.style.cssText;
// check if color or background-color is set
if (rule.style.color === '' && rule.style.backgroundColor === '') {
j++;
continue;
}
// replace div.sourceCode by a pre.sourceCode rule
sheets[i].deleteRule(j);
sheets[i].insertRule('pre.sourceCode{' + style + '}', j);
}
}
})();
</script>
<style type="text/css">body {
background-color: #fff;
margin: 1em auto;
max-width: 700px;
overflow: visible;
padding-left: 2em;
padding-right: 2em;
font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 14px;
line-height: 1.35;
}
#TOC {
clear: both;
margin: 0 0 10px 10px;
padding: 4px;
width: 400px;
border: 1px solid #CCCCCC;
border-radius: 5px;
background-color: #f6f6f6;
font-size: 13px;
line-height: 1.3;
}
#TOC .toctitle {
font-weight: bold;
font-size: 15px;
margin-left: 5px;
}
#TOC ul {
padding-left: 40px;
margin-left: -1.5em;
margin-top: 5px;
margin-bottom: 5px;
}
#TOC ul ul {
margin-left: -2em;
}
#TOC li {
line-height: 16px;
}
table {
margin: 1em auto;
border-width: 1px;
border-color: #DDDDDD;
border-style: outset;
border-collapse: collapse;
}
table th {
border-width: 2px;
padding: 5px;
border-style: inset;
}
table td {
border-width: 1px;
border-style: inset;
line-height: 18px;
padding: 5px 5px;
}
table, table th, table td {
border-left-style: none;
border-right-style: none;
}
table thead, table tr.even {
background-color: #f7f7f7;
}
p {
margin: 0.5em 0;
}
blockquote {
background-color: #f6f6f6;
padding: 0.25em 0.75em;
}
hr {
border-style: solid;
border: none;
border-top: 1px solid #777;
margin: 28px 0;
}
dl {
margin-left: 0;
}
dl dd {
margin-bottom: 13px;
margin-left: 13px;
}
dl dt {
font-weight: bold;
}
ul {
margin-top: 0;
}
ul li {
list-style: circle outside;
}
ul ul {
margin-bottom: 0;
}
pre, code {
background-color: #f7f7f7;
border-radius: 3px;
color: #333;
white-space: pre-wrap;
}
pre {
border-radius: 3px;
margin: 5px 0px 10px 0px;
padding: 10px;
}
pre:not([class]) {
background-color: #f7f7f7;
}
code {
font-family: Consolas, Monaco, 'Courier New', monospace;
font-size: 85%;
}
p > code, li > code {
padding: 2px 0px;
}
div.figure {
text-align: center;
}
img {
background-color: #FFFFFF;
padding: 2px;
border: 1px solid #DDDDDD;
border-radius: 3px;
border: 1px solid #CCCCCC;
margin: 0 5px;
}
h1 {
margin-top: 0;
font-size: 35px;
line-height: 40px;
}
h2 {
border-bottom: 4px solid #f7f7f7;
padding-top: 10px;
padding-bottom: 2px;
font-size: 145%;
}
h3 {
border-bottom: 2px solid #f7f7f7;
padding-top: 10px;
font-size: 120%;
}
h4 {
border-bottom: 1px solid #f7f7f7;
margin-left: 8px;
font-size: 105%;
}
h5, h6 {
border-bottom: 1px solid #ccc;
font-size: 105%;
}
a {
color: #0033dd;
text-decoration: none;
}
a:hover {
color: #6666ff; }
a:visited {
color: #800080; }
a:visited:hover {
color: #BB00BB; }
a[href^="http:"] {
text-decoration: underline; }
a[href^="https:"] {
text-decoration: underline; }
code > span.kw { color: #555; font-weight: bold; }
code > span.dt { color: #902000; }
code > span.dv { color: #40a070; }
code > span.bn { color: #d14; }
code > span.fl { color: #d14; }
code > span.ch { color: #d14; }
code > span.st { color: #d14; }
code > span.co { color: #888888; font-style: italic; }
code > span.ot { color: #007020; }
code > span.al { color: #ff0000; font-weight: bold; }
code > span.fu { color: #900; font-weight: bold; }
code > span.er { color: #a61717; background-color: #e3d2d2; }
</style>
</head>
<body>
<h1 class="title toc-ignore">Tidy data</h1>
<p>(This is an informal and code heavy version of the full <a href="https://vita.had.co.nz/papers/tidy-data.html">tidy data paper</a>.
Please refer to that for more details.)</p>
<div id="data-tidying" class="section level2">
<h2>Data tidying</h2>
<p>It is often said that 80% of data analysis is spent on the cleaning
and preparing data. And it’s not just a first step, but it must be
repeated many times over the course of analysis as new problems come to
light or new data is collected. To get a handle on the problem, this
paper focuses on a small, but important, aspect of data cleaning that I
call data <strong>tidying</strong>: structuring datasets to facilitate
analysis.</p>
<p>The principles of tidy data provide a standard way to organise data
values within a dataset. A standard makes initial data cleaning easier
because you don’t need to start from scratch and reinvent the wheel
every time. The tidy data standard has been designed to facilitate
initial exploration and analysis of the data, and to simplify the
development of data analysis tools that work well together. Current
tools often require translation. You have to spend time munging the
output from one tool so you can input it into another. Tidy datasets and
tidy tools work hand in hand to make data analysis easier, allowing you
to focus on the interesting domain problem, not on the uninteresting
logistics of data.</p>
</div>
<div id="defining" class="section level2">
<h2>Defining tidy data</h2>
<blockquote>
<p>Happy families are all alike; every unhappy family is unhappy in its
own way — Leo Tolstoy</p>
</blockquote>
<p>Like families, tidy datasets are all alike but every messy dataset is
messy in its own way. Tidy datasets provide a standardized way to link
the structure of a dataset (its physical layout) with its semantics (its
meaning). In this section, I’ll provide some standard vocabulary for
describing the structure and semantics of a dataset, and then use those
definitions to define tidy data.</p>
<div id="data-structure" class="section level3">
<h3>Data structure</h3>
<p>Most statistical datasets are data frames made up of
<strong>rows</strong> and <strong>columns</strong>. The columns are
almost always labeled and the rows are sometimes labeled. The following
code provides some data about an imaginary classroom in a format
commonly seen in the wild. The table has three columns and four rows,
and both rows and columns are labeled.</p>
<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" tabindex="-1"></a><span class="fu">library</span>(tibble)</span>
<span id="cb1-2"><a href="#cb1-2" tabindex="-1"></a>classroom <span class="ot"><-</span> <span class="fu">tribble</span>(</span>
<span id="cb1-3"><a href="#cb1-3" tabindex="-1"></a> <span class="sc">~</span>name, <span class="sc">~</span>quiz1, <span class="sc">~</span>quiz2, <span class="sc">~</span>test1,</span>
<span id="cb1-4"><a href="#cb1-4" tabindex="-1"></a> <span class="st">"Billy"</span>, <span class="cn">NA</span>, <span class="st">"D"</span>, <span class="st">"C"</span>,</span>
<span id="cb1-5"><a href="#cb1-5" tabindex="-1"></a> <span class="st">"Suzy"</span>, <span class="st">"F"</span>, <span class="cn">NA</span>, <span class="cn">NA</span>,</span>
<span id="cb1-6"><a href="#cb1-6" tabindex="-1"></a> <span class="st">"Lionel"</span>, <span class="st">"B"</span>, <span class="st">"C"</span>, <span class="st">"B"</span>,</span>
<span id="cb1-7"><a href="#cb1-7" tabindex="-1"></a> <span class="st">"Jenny"</span>, <span class="st">"A"</span>, <span class="st">"A"</span>, <span class="st">"B"</span></span>
<span id="cb1-8"><a href="#cb1-8" tabindex="-1"></a> )</span>
<span id="cb1-9"><a href="#cb1-9" tabindex="-1"></a>classroom</span>
<span id="cb1-10"><a href="#cb1-10" tabindex="-1"></a><span class="co">#> # A tibble: 4 × 4</span></span>
<span id="cb1-11"><a href="#cb1-11" tabindex="-1"></a><span class="co">#> name quiz1 quiz2 test1</span></span>
<span id="cb1-12"><a href="#cb1-12" tabindex="-1"></a><span class="co">#> <chr> <chr> <chr> <chr></span></span>
<span id="cb1-13"><a href="#cb1-13" tabindex="-1"></a><span class="co">#> 1 Billy <NA> D C </span></span>
<span id="cb1-14"><a href="#cb1-14" tabindex="-1"></a><span class="co">#> 2 Suzy F <NA> <NA> </span></span>
<span id="cb1-15"><a href="#cb1-15" tabindex="-1"></a><span class="co">#> 3 Lionel B C B </span></span>
<span id="cb1-16"><a href="#cb1-16" tabindex="-1"></a><span class="co">#> 4 Jenny A A B</span></span></code></pre></div>
<p>There are many ways to structure the same underlying data. The
following table shows the same data as above, but the rows and columns
have been transposed.</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" tabindex="-1"></a><span class="fu">tribble</span>(</span>
<span id="cb2-2"><a href="#cb2-2" tabindex="-1"></a> <span class="sc">~</span>assessment, <span class="sc">~</span>Billy, <span class="sc">~</span>Suzy, <span class="sc">~</span>Lionel, <span class="sc">~</span>Jenny,</span>
<span id="cb2-3"><a href="#cb2-3" tabindex="-1"></a> <span class="st">"quiz1"</span>, <span class="cn">NA</span>, <span class="st">"F"</span>, <span class="st">"B"</span>, <span class="st">"A"</span>,</span>
<span id="cb2-4"><a href="#cb2-4" tabindex="-1"></a> <span class="st">"quiz2"</span>, <span class="st">"D"</span>, <span class="cn">NA</span>, <span class="st">"C"</span>, <span class="st">"A"</span>,</span>
<span id="cb2-5"><a href="#cb2-5" tabindex="-1"></a> <span class="st">"test1"</span>, <span class="st">"C"</span>, <span class="cn">NA</span>, <span class="st">"B"</span>, <span class="st">"B"</span></span>
<span id="cb2-6"><a href="#cb2-6" tabindex="-1"></a> )</span>
<span id="cb2-7"><a href="#cb2-7" tabindex="-1"></a><span class="co">#> # A tibble: 3 × 5</span></span>
<span id="cb2-8"><a href="#cb2-8" tabindex="-1"></a><span class="co">#> assessment Billy Suzy Lionel Jenny</span></span>
<span id="cb2-9"><a href="#cb2-9" tabindex="-1"></a><span class="co">#> <chr> <chr> <chr> <chr> <chr></span></span>
<span id="cb2-10"><a href="#cb2-10" tabindex="-1"></a><span class="co">#> 1 quiz1 <NA> F B A </span></span>
<span id="cb2-11"><a href="#cb2-11" tabindex="-1"></a><span class="co">#> 2 quiz2 D <NA> C A </span></span>
<span id="cb2-12"><a href="#cb2-12" tabindex="-1"></a><span class="co">#> 3 test1 C <NA> B B</span></span></code></pre></div>
<p>The data is the same, but the layout is different. Our vocabulary of
rows and columns is simply not rich enough to describe why the two
tables represent the same data. In addition to appearance, we need a way
to describe the underlying semantics, or meaning, of the values
displayed in the table.</p>
</div>
<div id="data-semantics" class="section level3">
<h3>Data semantics</h3>
<p>A dataset is a collection of <strong>values</strong>, usually either
numbers (if quantitative) or strings (if qualitative). Values are
organised in two ways. Every value belongs to a
<strong>variable</strong> and an <strong>observation</strong>. A
variable contains all values that measure the same underlying attribute
(like height, temperature, duration) across units. An observation
contains all values measured on the same unit (like a person, or a day,
or a race) across attributes.</p>
<p>A tidy version of the classroom data looks like this: (you’ll learn
how the functions work a little later)</p>
<div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1" tabindex="-1"></a><span class="fu">library</span>(tidyr)</span>
<span id="cb3-2"><a href="#cb3-2" tabindex="-1"></a><span class="fu">library</span>(dplyr)</span></code></pre></div>
<div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1" tabindex="-1"></a>classroom2 <span class="ot"><-</span> classroom <span class="sc">%>%</span> </span>
<span id="cb4-2"><a href="#cb4-2" tabindex="-1"></a> <span class="fu">pivot_longer</span>(quiz1<span class="sc">:</span>test1, <span class="at">names_to =</span> <span class="st">"assessment"</span>, <span class="at">values_to =</span> <span class="st">"grade"</span>) <span class="sc">%>%</span> </span>
<span id="cb4-3"><a href="#cb4-3" tabindex="-1"></a> <span class="fu">arrange</span>(name, assessment)</span>
<span id="cb4-4"><a href="#cb4-4" tabindex="-1"></a>classroom2</span>
<span id="cb4-5"><a href="#cb4-5" tabindex="-1"></a><span class="co">#> # A tibble: 12 × 3</span></span>
<span id="cb4-6"><a href="#cb4-6" tabindex="-1"></a><span class="co">#> name assessment grade</span></span>
<span id="cb4-7"><a href="#cb4-7" tabindex="-1"></a><span class="co">#> <chr> <chr> <chr></span></span>
<span id="cb4-8"><a href="#cb4-8" tabindex="-1"></a><span class="co">#> 1 Billy quiz1 <NA> </span></span>
<span id="cb4-9"><a href="#cb4-9" tabindex="-1"></a><span class="co">#> 2 Billy quiz2 D </span></span>
<span id="cb4-10"><a href="#cb4-10" tabindex="-1"></a><span class="co">#> 3 Billy test1 C </span></span>
<span id="cb4-11"><a href="#cb4-11" tabindex="-1"></a><span class="co">#> 4 Jenny quiz1 A </span></span>
<span id="cb4-12"><a href="#cb4-12" tabindex="-1"></a><span class="co">#> 5 Jenny quiz2 A </span></span>
<span id="cb4-13"><a href="#cb4-13" tabindex="-1"></a><span class="co">#> 6 Jenny test1 B </span></span>
<span id="cb4-14"><a href="#cb4-14" tabindex="-1"></a><span class="co">#> # ℹ 6 more rows</span></span></code></pre></div>
<p>This makes the values, variables, and observations more clear. The
dataset contains 36 values representing three variables and 12
observations. The variables are:</p>
<ol style="list-style-type: decimal">
<li><p><code>name</code>, with four possible values (Billy, Suzy,
Lionel, and Jenny).</p></li>
<li><p><code>assessment</code>, with three possible values (quiz1,
quiz2, and test1).</p></li>
<li><p><code>grade</code>, with five or six values depending on how you
think of the missing value (A, B, C, D, F, NA).</p></li>
</ol>
<p>The tidy data frame explicitly tells us the definition of an
observation. In this classroom, every combination of <code>name</code>
and <code>assessment</code> is a single measured observation. The
dataset also informs us of missing values, which can and do have
meaning. Billy was absent for the first quiz, but tried to salvage his
grade. Suzy failed the first quiz, so she decided to drop the class. To
calculate Billy’s final grade, we might replace this missing value with
an F (or he might get a second chance to take the quiz). However, if we
want to know the class average for Test 1, dropping Suzy’s structural
missing value would be more appropriate than imputing a new value.</p>
<p>For a given dataset, it’s usually easy to figure out what are
observations and what are variables, but it is surprisingly difficult to
precisely define variables and observations in general. For example, if
the columns in the classroom data were <code>height</code> and
<code>weight</code> we would have been happy to call them variables. If
the columns were <code>height</code> and <code>width</code>, it would be
less clear cut, as we might think of height and width as values of a
<code>dimension</code> variable. If the columns were
<code>home phone</code> and <code>work phone</code>, we could treat
these as two variables, but in a fraud detection environment we might
want variables <code>phone number</code> and <code>number type</code>
because the use of one phone number for multiple people might suggest
fraud. A general rule of thumb is that it is easier to describe
functional relationships between variables (e.g., <code>z</code> is a
linear combination of <code>x</code> and <code>y</code>,
<code>density</code> is the ratio of <code>weight</code> to
<code>volume</code>) than between rows, and it is easier to make
comparisons between groups of observations (e.g., average of group a
vs. average of group b) than between groups of columns.</p>
<p>In a given analysis, there may be multiple levels of observation. For
example, in a trial of new allergy medication we might have three
observational types: demographic data collected from each person
(<code>age</code>, <code>sex</code>, <code>race</code>), medical data
collected from each person on each day (<code>number of sneezes</code>,
<code>redness of eyes</code>), and meteorological data collected on each
day (<code>temperature</code>, <code>pollen count</code>).</p>
<p>Variables may change over the course of analysis. Often the variables
in the raw data are very fine grained, and may add extra modelling
complexity for little explanatory gain. For example, many surveys ask
variations on the same question to better get at an underlying trait. In
early stages of analysis, variables correspond to questions. In later
stages, you change focus to traits, computed by averaging together
multiple questions. This considerably simplifies analysis because you
don’t need a hierarchical model, and you can often pretend that the data
is continuous, not discrete.</p>
</div>
<div id="tidy-data" class="section level3">
<h3>Tidy data</h3>
<p>Tidy data is a standard way of mapping the meaning of a dataset to
its structure. A dataset is messy or tidy depending on how rows, columns
and tables are matched up with observations, variables and types. In
<strong>tidy data</strong>:</p>
<ol style="list-style-type: decimal">
<li><p>Each variable is a column; each column is a variable.</p></li>
<li><p>Each observation is a row; each row is an observation.</p></li>
<li><p>Each value is a cell; each cell is a single value.</p></li>
</ol>
<p>This is Codd’s 3rd normal form, but with the constraints framed in
statistical language, and the focus put on a single dataset rather than
the many connected datasets common in relational databases.
<strong>Messy data</strong> is any other arrangement of the data.</p>
<p>Tidy data makes it easy for an analyst or a computer to extract
needed variables because it provides a standard way of structuring a
dataset. Compare the different versions of the classroom data: in the
messy version you need to use different strategies to extract different
variables. This slows analysis and invites errors. If you consider how
many data analysis operations involve all of the values in a variable
(every aggregation function), you can see how important it is to extract
these values in a simple, standard way. Tidy data is particularly well
suited for vectorised programming languages like R, because the layout
ensures that values of different variables from the same observation are
always paired.</p>
<p>While the order of variables and observations does not affect
analysis, a good ordering makes it easier to scan the raw values. One
way of organising variables is by their role in the analysis: are values
fixed by the design of the data collection, or are they measured during
the course of the experiment? Fixed variables describe the experimental
design and are known in advance. Computer scientists often call fixed
variables dimensions, and statisticians usually denote them with
subscripts on random variables. Measured variables are what we actually
measure in the study. Fixed variables should come first, followed by
measured variables, each ordered so that related variables are
contiguous. Rows can then be ordered by the first variable, breaking
ties with the second and subsequent (fixed) variables. This is the
convention adopted by all tabular displays in this paper.</p>
</div>
</div>
<div id="tidying" class="section level2">
<h2>Tidying messy datasets</h2>
<p>Real datasets can, and often do, violate the three precepts of tidy
data in almost every way imaginable. While occasionally you do get a
dataset that you can start analysing immediately, this is the exception,
not the rule. This section describes the five most common problems with
messy datasets, along with their remedies:</p>
<ul>
<li><p>Column headers are values, not variable names.</p></li>
<li><p>Multiple variables are stored in one column.</p></li>
<li><p>Variables are stored in both rows and columns.</p></li>
<li><p>Multiple types of observational units are stored in the same
table.</p></li>
<li><p>A single observational unit is stored in multiple
tables.</p></li>
</ul>
<p>Surprisingly, most messy datasets, including types of messiness not
explicitly described above, can be tidied with a small set of tools:
pivoting (longer and wider) and separating. The following sections
illustrate each problem with a real dataset that I have encountered, and
show how to tidy them.</p>
<div id="column-headers-are-values-not-variable-names" class="section level3">
<h3>Column headers are values, not variable names</h3>
<p>A common type of messy dataset is tabular data designed for
presentation, where variables form both the rows and columns, and column
headers are values, not variable names. While I would call this
arrangement messy, in some cases it can be extremely useful. It provides
efficient storage for completely crossed designs, and it can lead to
extremely efficient computation if desired operations can be expressed
as matrix operations.</p>
<p>The following code shows a subset of a typical dataset of this form.
This dataset explores the relationship between income and religion in
the US. It comes from a report produced by the Pew Research Center, an
American think-tank that collects data on attitudes to topics ranging
from religion to the internet, and produces many reports that contain
datasets in this format.</p>
<div class="sourceCode" id="cb5"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1" tabindex="-1"></a>relig_income</span>
<span id="cb5-2"><a href="#cb5-2" tabindex="-1"></a><span class="co">#> # A tibble: 18 × 11</span></span>
<span id="cb5-3"><a href="#cb5-3" tabindex="-1"></a><span class="co">#> religion `<$10k` `$10-20k` `$20-30k` `$30-40k` `$40-50k` `$50-75k` `$75-100k`</span></span>
<span id="cb5-4"><a href="#cb5-4" tabindex="-1"></a><span class="co">#> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl></span></span>
<span id="cb5-5"><a href="#cb5-5" tabindex="-1"></a><span class="co">#> 1 Agnostic 27 34 60 81 76 137 122</span></span>
<span id="cb5-6"><a href="#cb5-6" tabindex="-1"></a><span class="co">#> 2 Atheist 12 27 37 52 35 70 73</span></span>
<span id="cb5-7"><a href="#cb5-7" tabindex="-1"></a><span class="co">#> 3 Buddhist 27 21 30 34 33 58 62</span></span>
<span id="cb5-8"><a href="#cb5-8" tabindex="-1"></a><span class="co">#> 4 Catholic 418 617 732 670 638 1116 949</span></span>
<span id="cb5-9"><a href="#cb5-9" tabindex="-1"></a><span class="co">#> 5 Don’t kn… 15 14 15 11 10 35 21</span></span>
<span id="cb5-10"><a href="#cb5-10" tabindex="-1"></a><span class="co">#> 6 Evangeli… 575 869 1064 982 881 1486 949</span></span>
<span id="cb5-11"><a href="#cb5-11" tabindex="-1"></a><span class="co">#> # ℹ 12 more rows</span></span>
<span id="cb5-12"><a href="#cb5-12" tabindex="-1"></a><span class="co">#> # ℹ 3 more variables: `$100-150k` <dbl>, `>150k` <dbl>,</span></span>
<span id="cb5-13"><a href="#cb5-13" tabindex="-1"></a><span class="co">#> # `Don't know/refused` <dbl></span></span></code></pre></div>
<p>This dataset has three variables, <code>religion</code>,
<code>income</code> and <code>frequency</code>. To tidy it, we need to
<strong>pivot</strong> the non-variable columns into a two-column
key-value pair. This action is often described as making a wide dataset
longer (or taller).</p>
<p>When pivoting variables, we need to provide the name of the new
key-value columns to create. After defining the columns to pivot (every
column except for religion), you will need the name of the key column,
which is the name of the variable defined by the values of the column
headings. In this case, it’s <code>income</code>. The second argument is
the name of the value column, <code>frequency</code>.</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" tabindex="-1"></a>relig_income <span class="sc">%>%</span> </span>
<span id="cb6-2"><a href="#cb6-2" tabindex="-1"></a> <span class="fu">pivot_longer</span>(<span class="sc">-</span>religion, <span class="at">names_to =</span> <span class="st">"income"</span>, <span class="at">values_to =</span> <span class="st">"frequency"</span>)</span>
<span id="cb6-3"><a href="#cb6-3" tabindex="-1"></a><span class="co">#> # A tibble: 180 × 3</span></span>
<span id="cb6-4"><a href="#cb6-4" tabindex="-1"></a><span class="co">#> religion income frequency</span></span>
<span id="cb6-5"><a href="#cb6-5" tabindex="-1"></a><span class="co">#> <chr> <chr> <dbl></span></span>
<span id="cb6-6"><a href="#cb6-6" tabindex="-1"></a><span class="co">#> 1 Agnostic <$10k 27</span></span>
<span id="cb6-7"><a href="#cb6-7" tabindex="-1"></a><span class="co">#> 2 Agnostic $10-20k 34</span></span>
<span id="cb6-8"><a href="#cb6-8" tabindex="-1"></a><span class="co">#> 3 Agnostic $20-30k 60</span></span>
<span id="cb6-9"><a href="#cb6-9" tabindex="-1"></a><span class="co">#> 4 Agnostic $30-40k 81</span></span>
<span id="cb6-10"><a href="#cb6-10" tabindex="-1"></a><span class="co">#> 5 Agnostic $40-50k 76</span></span>
<span id="cb6-11"><a href="#cb6-11" tabindex="-1"></a><span class="co">#> 6 Agnostic $50-75k 137</span></span>
<span id="cb6-12"><a href="#cb6-12" tabindex="-1"></a><span class="co">#> # ℹ 174 more rows</span></span></code></pre></div>
<p>This form is tidy because each column represents a variable and each
row represents an observation, in this case a demographic unit
corresponding to a combination of <code>religion</code> and
<code>income</code>.</p>
<p>This format is also used to record regularly spaced observations over
time. For example, the Billboard dataset shown below records the date a
song first entered the billboard top 100. It has variables for
<code>artist</code>, <code>track</code>, <code>date.entered</code>,
<code>rank</code> and <code>week</code>. The rank in each week after it
enters the top 100 is recorded in 75 columns, <code>wk1</code> to
<code>wk75</code>. This form of storage is not tidy, but it is useful
for data entry. It reduces duplication since otherwise each song in each
week would need its own row, and song metadata like title and artist
would need to be repeated. This will be discussed in more depth in <a href="#multiple-types">multiple types</a>.</p>
<div class="sourceCode" id="cb7"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb7-1"><a href="#cb7-1" tabindex="-1"></a>billboard</span>
<span id="cb7-2"><a href="#cb7-2" tabindex="-1"></a><span class="co">#> # A tibble: 317 × 79</span></span>
<span id="cb7-3"><a href="#cb7-3" tabindex="-1"></a><span class="co">#> artist track date.entered wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8</span></span>
<span id="cb7-4"><a href="#cb7-4" tabindex="-1"></a><span class="co">#> <chr> <chr> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl></span></span>
<span id="cb7-5"><a href="#cb7-5" tabindex="-1"></a><span class="co">#> 1 2 Pac Baby… 2000-02-26 87 82 72 77 87 94 99 NA</span></span>
<span id="cb7-6"><a href="#cb7-6" tabindex="-1"></a><span class="co">#> 2 2Ge+her The … 2000-09-02 91 87 92 NA NA NA NA NA</span></span>
<span id="cb7-7"><a href="#cb7-7" tabindex="-1"></a><span class="co">#> 3 3 Doors Do… Kryp… 2000-04-08 81 70 68 67 66 57 54 53</span></span>
<span id="cb7-8"><a href="#cb7-8" tabindex="-1"></a><span class="co">#> 4 3 Doors Do… Loser 2000-10-21 76 76 72 69 67 65 55 59</span></span>
<span id="cb7-9"><a href="#cb7-9" tabindex="-1"></a><span class="co">#> 5 504 Boyz Wobb… 2000-04-15 57 34 25 17 17 31 36 49</span></span>
<span id="cb7-10"><a href="#cb7-10" tabindex="-1"></a><span class="co">#> 6 98^0 Give… 2000-08-19 51 39 34 26 26 19 2 2</span></span>
<span id="cb7-11"><a href="#cb7-11" tabindex="-1"></a><span class="co">#> # ℹ 311 more rows</span></span>
<span id="cb7-12"><a href="#cb7-12" tabindex="-1"></a><span class="co">#> # ℹ 68 more variables: wk9 <dbl>, wk10 <dbl>, wk11 <dbl>, wk12 <dbl>,</span></span>
<span id="cb7-13"><a href="#cb7-13" tabindex="-1"></a><span class="co">#> # wk13 <dbl>, wk14 <dbl>, wk15 <dbl>, wk16 <dbl>, wk17 <dbl>, wk18 <dbl>,</span></span>
<span id="cb7-14"><a href="#cb7-14" tabindex="-1"></a><span class="co">#> # wk19 <dbl>, wk20 <dbl>, wk21 <dbl>, wk22 <dbl>, wk23 <dbl>, wk24 <dbl>,</span></span>
<span id="cb7-15"><a href="#cb7-15" tabindex="-1"></a><span class="co">#> # wk25 <dbl>, wk26 <dbl>, wk27 <dbl>, wk28 <dbl>, wk29 <dbl>, wk30 <dbl>,</span></span>
<span id="cb7-16"><a href="#cb7-16" tabindex="-1"></a><span class="co">#> # wk31 <dbl>, wk32 <dbl>, wk33 <dbl>, wk34 <dbl>, wk35 <dbl>, wk36 <dbl>,</span></span>
<span id="cb7-17"><a href="#cb7-17" tabindex="-1"></a><span class="co">#> # wk37 <dbl>, wk38 <dbl>, wk39 <dbl>, wk40 <dbl>, wk41 <dbl>, wk42 <dbl>, …</span></span></code></pre></div>
<p>To tidy this dataset, we first use <code>pivot_longer()</code> to
make the dataset longer. We transform the columns from <code>wk1</code>
to <code>wk76</code>, making a new column for their names,
<code>week</code>, and a new value for their values,
<code>rank</code>:</p>
<div class="sourceCode" id="cb8"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" tabindex="-1"></a>billboard2 <span class="ot"><-</span> billboard <span class="sc">%>%</span> </span>
<span id="cb8-2"><a href="#cb8-2" tabindex="-1"></a> <span class="fu">pivot_longer</span>(</span>
<span id="cb8-3"><a href="#cb8-3" tabindex="-1"></a> wk1<span class="sc">:</span>wk76, </span>
<span id="cb8-4"><a href="#cb8-4" tabindex="-1"></a> <span class="at">names_to =</span> <span class="st">"week"</span>, </span>
<span id="cb8-5"><a href="#cb8-5" tabindex="-1"></a> <span class="at">values_to =</span> <span class="st">"rank"</span>, </span>
<span id="cb8-6"><a href="#cb8-6" tabindex="-1"></a> <span class="at">values_drop_na =</span> <span class="cn">TRUE</span></span>
<span id="cb8-7"><a href="#cb8-7" tabindex="-1"></a> )</span>
<span id="cb8-8"><a href="#cb8-8" tabindex="-1"></a>billboard2</span>
<span id="cb8-9"><a href="#cb8-9" tabindex="-1"></a><span class="co">#> # A tibble: 5,307 × 5</span></span>
<span id="cb8-10"><a href="#cb8-10" tabindex="-1"></a><span class="co">#> artist track date.entered week rank</span></span>
<span id="cb8-11"><a href="#cb8-11" tabindex="-1"></a><span class="co">#> <chr> <chr> <date> <chr> <dbl></span></span>
<span id="cb8-12"><a href="#cb8-12" tabindex="-1"></a><span class="co">#> 1 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk1 87</span></span>
<span id="cb8-13"><a href="#cb8-13" tabindex="-1"></a><span class="co">#> 2 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk2 82</span></span>
<span id="cb8-14"><a href="#cb8-14" tabindex="-1"></a><span class="co">#> 3 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk3 72</span></span>
<span id="cb8-15"><a href="#cb8-15" tabindex="-1"></a><span class="co">#> 4 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk4 77</span></span>
<span id="cb8-16"><a href="#cb8-16" tabindex="-1"></a><span class="co">#> 5 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk5 87</span></span>
<span id="cb8-17"><a href="#cb8-17" tabindex="-1"></a><span class="co">#> 6 2 Pac Baby Don't Cry (Keep... 2000-02-26 wk6 94</span></span>
<span id="cb8-18"><a href="#cb8-18" tabindex="-1"></a><span class="co">#> # ℹ 5,301 more rows</span></span></code></pre></div>
<p>Here we use <code>values_drop_na = TRUE</code> to drop any missing
values from the rank column. In this data, missing values represent
weeks that the song wasn’t in the charts, so can be safely dropped.</p>
<p>In this case it’s also nice to do a little cleaning, converting the
week variable to a number, and figuring out the date corresponding to
each week on the charts:</p>
<div class="sourceCode" id="cb9"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" tabindex="-1"></a>billboard3 <span class="ot"><-</span> billboard2 <span class="sc">%>%</span></span>
<span id="cb9-2"><a href="#cb9-2" tabindex="-1"></a> <span class="fu">mutate</span>(</span>
<span id="cb9-3"><a href="#cb9-3" tabindex="-1"></a> <span class="at">week =</span> <span class="fu">as.integer</span>(<span class="fu">gsub</span>(<span class="st">"wk"</span>, <span class="st">""</span>, week)),</span>
<span id="cb9-4"><a href="#cb9-4" tabindex="-1"></a> <span class="at">date =</span> <span class="fu">as.Date</span>(date.entered) <span class="sc">+</span> <span class="dv">7</span> <span class="sc">*</span> (week <span class="sc">-</span> <span class="dv">1</span>),</span>
<span id="cb9-5"><a href="#cb9-5" tabindex="-1"></a> <span class="at">date.entered =</span> <span class="cn">NULL</span></span>
<span id="cb9-6"><a href="#cb9-6" tabindex="-1"></a> )</span>
<span id="cb9-7"><a href="#cb9-7" tabindex="-1"></a>billboard3</span>
<span id="cb9-8"><a href="#cb9-8" tabindex="-1"></a><span class="co">#> # A tibble: 5,307 × 5</span></span>
<span id="cb9-9"><a href="#cb9-9" tabindex="-1"></a><span class="co">#> artist track week rank date </span></span>
<span id="cb9-10"><a href="#cb9-10" tabindex="-1"></a><span class="co">#> <chr> <chr> <int> <dbl> <date> </span></span>
<span id="cb9-11"><a href="#cb9-11" tabindex="-1"></a><span class="co">#> 1 2 Pac Baby Don't Cry (Keep... 1 87 2000-02-26</span></span>
<span id="cb9-12"><a href="#cb9-12" tabindex="-1"></a><span class="co">#> 2 2 Pac Baby Don't Cry (Keep... 2 82 2000-03-04</span></span>
<span id="cb9-13"><a href="#cb9-13" tabindex="-1"></a><span class="co">#> 3 2 Pac Baby Don't Cry (Keep... 3 72 2000-03-11</span></span>
<span id="cb9-14"><a href="#cb9-14" tabindex="-1"></a><span class="co">#> 4 2 Pac Baby Don't Cry (Keep... 4 77 2000-03-18</span></span>
<span id="cb9-15"><a href="#cb9-15" tabindex="-1"></a><span class="co">#> 5 2 Pac Baby Don't Cry (Keep... 5 87 2000-03-25</span></span>
<span id="cb9-16"><a href="#cb9-16" tabindex="-1"></a><span class="co">#> 6 2 Pac Baby Don't Cry (Keep... 6 94 2000-04-01</span></span>
<span id="cb9-17"><a href="#cb9-17" tabindex="-1"></a><span class="co">#> # ℹ 5,301 more rows</span></span></code></pre></div>
<p>Finally, it’s always a good idea to sort the data. We could do it by
artist, track and week:</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" tabindex="-1"></a>billboard3 <span class="sc">%>%</span> <span class="fu">arrange</span>(artist, track, week)</span>
<span id="cb10-2"><a href="#cb10-2" tabindex="-1"></a><span class="co">#> # A tibble: 5,307 × 5</span></span>
<span id="cb10-3"><a href="#cb10-3" tabindex="-1"></a><span class="co">#> artist track week rank date </span></span>
<span id="cb10-4"><a href="#cb10-4" tabindex="-1"></a><span class="co">#> <chr> <chr> <int> <dbl> <date> </span></span>
<span id="cb10-5"><a href="#cb10-5" tabindex="-1"></a><span class="co">#> 1 2 Pac Baby Don't Cry (Keep... 1 87 2000-02-26</span></span>
<span id="cb10-6"><a href="#cb10-6" tabindex="-1"></a><span class="co">#> 2 2 Pac Baby Don't Cry (Keep... 2 82 2000-03-04</span></span>
<span id="cb10-7"><a href="#cb10-7" tabindex="-1"></a><span class="co">#> 3 2 Pac Baby Don't Cry (Keep... 3 72 2000-03-11</span></span>
<span id="cb10-8"><a href="#cb10-8" tabindex="-1"></a><span class="co">#> 4 2 Pac Baby Don't Cry (Keep... 4 77 2000-03-18</span></span>
<span id="cb10-9"><a href="#cb10-9" tabindex="-1"></a><span class="co">#> 5 2 Pac Baby Don't Cry (Keep... 5 87 2000-03-25</span></span>
<span id="cb10-10"><a href="#cb10-10" tabindex="-1"></a><span class="co">#> 6 2 Pac Baby Don't Cry (Keep... 6 94 2000-04-01</span></span>
<span id="cb10-11"><a href="#cb10-11" tabindex="-1"></a><span class="co">#> # ℹ 5,301 more rows</span></span></code></pre></div>
<p>Or by date and rank:</p>
<div class="sourceCode" id="cb11"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb11-1"><a href="#cb11-1" tabindex="-1"></a>billboard3 <span class="sc">%>%</span> <span class="fu">arrange</span>(date, rank)</span>
<span id="cb11-2"><a href="#cb11-2" tabindex="-1"></a><span class="co">#> # A tibble: 5,307 × 5</span></span>
<span id="cb11-3"><a href="#cb11-3" tabindex="-1"></a><span class="co">#> artist track week rank date </span></span>
<span id="cb11-4"><a href="#cb11-4" tabindex="-1"></a><span class="co">#> <chr> <chr> <int> <dbl> <date> </span></span>
<span id="cb11-5"><a href="#cb11-5" tabindex="-1"></a><span class="co">#> 1 Lonestar Amazed 1 81 1999-06-05</span></span>
<span id="cb11-6"><a href="#cb11-6" tabindex="-1"></a><span class="co">#> 2 Lonestar Amazed 2 54 1999-06-12</span></span>
<span id="cb11-7"><a href="#cb11-7" tabindex="-1"></a><span class="co">#> 3 Lonestar Amazed 3 44 1999-06-19</span></span>
<span id="cb11-8"><a href="#cb11-8" tabindex="-1"></a><span class="co">#> 4 Lonestar Amazed 4 39 1999-06-26</span></span>
<span id="cb11-9"><a href="#cb11-9" tabindex="-1"></a><span class="co">#> 5 Lonestar Amazed 5 38 1999-07-03</span></span>
<span id="cb11-10"><a href="#cb11-10" tabindex="-1"></a><span class="co">#> 6 Lonestar Amazed 6 33 1999-07-10</span></span>
<span id="cb11-11"><a href="#cb11-11" tabindex="-1"></a><span class="co">#> # ℹ 5,301 more rows</span></span></code></pre></div>
</div>
<div id="multiple-variables-stored-in-one-column" class="section level3">
<h3>Multiple variables stored in one column</h3>
<p>After pivoting columns, the key column is sometimes a combination of
multiple underlying variable names. This happens in the <code>tb</code>
(tuberculosis) dataset, shown below. This dataset comes from the World
Health Organisation, and records the counts of confirmed tuberculosis
cases by <code>country</code>, <code>year</code>, and demographic group.
The demographic groups are broken down by <code>sex</code> (m, f) and
<code>age</code> (0-14, 15-25, 25-34, 35-44, 45-54, 55-64, unknown).</p>
<div class="sourceCode" id="cb12"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" tabindex="-1"></a>tb <span class="ot"><-</span> <span class="fu">as_tibble</span>(<span class="fu">read.csv</span>(<span class="st">"tb.csv"</span>, <span class="at">stringsAsFactors =</span> <span class="cn">FALSE</span>))</span>
<span id="cb12-2"><a href="#cb12-2" tabindex="-1"></a>tb</span>
<span id="cb12-3"><a href="#cb12-3" tabindex="-1"></a><span class="co">#> # A tibble: 5,769 × 22</span></span>
<span id="cb12-4"><a href="#cb12-4" tabindex="-1"></a><span class="co">#> iso2 year m04 m514 m014 m1524 m2534 m3544 m4554 m5564 m65 mu f04</span></span>
<span id="cb12-5"><a href="#cb12-5" tabindex="-1"></a><span class="co">#> <chr> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int></span></span>
<span id="cb12-6"><a href="#cb12-6" tabindex="-1"></a><span class="co">#> 1 AD 1989 NA NA NA NA NA NA NA NA NA NA NA</span></span>
<span id="cb12-7"><a href="#cb12-7" tabindex="-1"></a><span class="co">#> 2 AD 1990 NA NA NA NA NA NA NA NA NA NA NA</span></span>
<span id="cb12-8"><a href="#cb12-8" tabindex="-1"></a><span class="co">#> 3 AD 1991 NA NA NA NA NA NA NA NA NA NA NA</span></span>
<span id="cb12-9"><a href="#cb12-9" tabindex="-1"></a><span class="co">#> 4 AD 1992 NA NA NA NA NA NA NA NA NA NA NA</span></span>
<span id="cb12-10"><a href="#cb12-10" tabindex="-1"></a><span class="co">#> 5 AD 1993 NA NA NA NA NA NA NA NA NA NA NA</span></span>
<span id="cb12-11"><a href="#cb12-11" tabindex="-1"></a><span class="co">#> 6 AD 1994 NA NA NA NA NA NA NA NA NA NA NA</span></span>
<span id="cb12-12"><a href="#cb12-12" tabindex="-1"></a><span class="co">#> # ℹ 5,763 more rows</span></span>
<span id="cb12-13"><a href="#cb12-13" tabindex="-1"></a><span class="co">#> # ℹ 9 more variables: f514 <int>, f014 <int>, f1524 <int>, f2534 <int>,</span></span>
<span id="cb12-14"><a href="#cb12-14" tabindex="-1"></a><span class="co">#> # f3544 <int>, f4554 <int>, f5564 <int>, f65 <int>, fu <int></span></span></code></pre></div>
<p>First we use <code>pivot_longer()</code> to gather up the
non-variable columns:</p>
<div class="sourceCode" id="cb13"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb13-1"><a href="#cb13-1" tabindex="-1"></a>tb2 <span class="ot"><-</span> tb <span class="sc">%>%</span> </span>
<span id="cb13-2"><a href="#cb13-2" tabindex="-1"></a> <span class="fu">pivot_longer</span>(</span>
<span id="cb13-3"><a href="#cb13-3" tabindex="-1"></a> <span class="sc">!</span><span class="fu">c</span>(iso2, year), </span>
<span id="cb13-4"><a href="#cb13-4" tabindex="-1"></a> <span class="at">names_to =</span> <span class="st">"demo"</span>, </span>
<span id="cb13-5"><a href="#cb13-5" tabindex="-1"></a> <span class="at">values_to =</span> <span class="st">"n"</span>, </span>
<span id="cb13-6"><a href="#cb13-6" tabindex="-1"></a> <span class="at">values_drop_na =</span> <span class="cn">TRUE</span></span>
<span id="cb13-7"><a href="#cb13-7" tabindex="-1"></a> )</span>
<span id="cb13-8"><a href="#cb13-8" tabindex="-1"></a>tb2</span>
<span id="cb13-9"><a href="#cb13-9" tabindex="-1"></a><span class="co">#> # A tibble: 35,750 × 4</span></span>
<span id="cb13-10"><a href="#cb13-10" tabindex="-1"></a><span class="co">#> iso2 year demo n</span></span>
<span id="cb13-11"><a href="#cb13-11" tabindex="-1"></a><span class="co">#> <chr> <int> <chr> <int></span></span>
<span id="cb13-12"><a href="#cb13-12" tabindex="-1"></a><span class="co">#> 1 AD 1996 m014 0</span></span>
<span id="cb13-13"><a href="#cb13-13" tabindex="-1"></a><span class="co">#> 2 AD 1996 m1524 0</span></span>
<span id="cb13-14"><a href="#cb13-14" tabindex="-1"></a><span class="co">#> 3 AD 1996 m2534 0</span></span>
<span id="cb13-15"><a href="#cb13-15" tabindex="-1"></a><span class="co">#> 4 AD 1996 m3544 4</span></span>
<span id="cb13-16"><a href="#cb13-16" tabindex="-1"></a><span class="co">#> 5 AD 1996 m4554 1</span></span>
<span id="cb13-17"><a href="#cb13-17" tabindex="-1"></a><span class="co">#> 6 AD 1996 m5564 0</span></span>
<span id="cb13-18"><a href="#cb13-18" tabindex="-1"></a><span class="co">#> # ℹ 35,744 more rows</span></span></code></pre></div>
<p>Column headers in this format are often separated by a
non-alphanumeric character (e.g. <code>.</code>, <code>-</code>,
<code>_</code>, <code>:</code>), or have a fixed width format, like in
this dataset. <code>separate()</code> makes it easy to split a compound
variables into individual variables. You can either pass it a regular
expression to split on (the default is to split on non-alphanumeric
columns), or a vector of character positions. In this case we want to
split after the first character:</p>
<div class="sourceCode" id="cb14"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb14-1"><a href="#cb14-1" tabindex="-1"></a>tb3 <span class="ot"><-</span> tb2 <span class="sc">%>%</span> </span>
<span id="cb14-2"><a href="#cb14-2" tabindex="-1"></a> <span class="fu">separate</span>(demo, <span class="fu">c</span>(<span class="st">"sex"</span>, <span class="st">"age"</span>), <span class="dv">1</span>)</span>
<span id="cb14-3"><a href="#cb14-3" tabindex="-1"></a>tb3</span>
<span id="cb14-4"><a href="#cb14-4" tabindex="-1"></a><span class="co">#> # A tibble: 35,750 × 5</span></span>
<span id="cb14-5"><a href="#cb14-5" tabindex="-1"></a><span class="co">#> iso2 year sex age n</span></span>
<span id="cb14-6"><a href="#cb14-6" tabindex="-1"></a><span class="co">#> <chr> <int> <chr> <chr> <int></span></span>
<span id="cb14-7"><a href="#cb14-7" tabindex="-1"></a><span class="co">#> 1 AD 1996 m 014 0</span></span>
<span id="cb14-8"><a href="#cb14-8" tabindex="-1"></a><span class="co">#> 2 AD 1996 m 1524 0</span></span>
<span id="cb14-9"><a href="#cb14-9" tabindex="-1"></a><span class="co">#> 3 AD 1996 m 2534 0</span></span>
<span id="cb14-10"><a href="#cb14-10" tabindex="-1"></a><span class="co">#> 4 AD 1996 m 3544 4</span></span>
<span id="cb14-11"><a href="#cb14-11" tabindex="-1"></a><span class="co">#> 5 AD 1996 m 4554 1</span></span>
<span id="cb14-12"><a href="#cb14-12" tabindex="-1"></a><span class="co">#> 6 AD 1996 m 5564 0</span></span>
<span id="cb14-13"><a href="#cb14-13" tabindex="-1"></a><span class="co">#> # ℹ 35,744 more rows</span></span></code></pre></div>
<p>Storing the values in this form resolves a problem in the original
data. We want to compare rates, not counts, which means we need to know
the population. In the original format, there is no easy way to add a
population variable. It has to be stored in a separate table, which
makes it hard to correctly match populations to counts. In tidy form,
adding variables for population and rate is easy because they’re just
additional columns.</p>
<p>In this case, we could also do the transformation in a single step by
supplying multiple column names to <code>names_to</code> and also
supplying a grouped regular expression to
<code>names_pattern</code>:</p>
<div class="sourceCode" id="cb15"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb15-1"><a href="#cb15-1" tabindex="-1"></a>tb <span class="sc">%>%</span> <span class="fu">pivot_longer</span>(</span>
<span id="cb15-2"><a href="#cb15-2" tabindex="-1"></a> <span class="sc">!</span><span class="fu">c</span>(iso2, year), </span>
<span id="cb15-3"><a href="#cb15-3" tabindex="-1"></a> <span class="at">names_to =</span> <span class="fu">c</span>(<span class="st">"sex"</span>, <span class="st">"age"</span>), </span>
<span id="cb15-4"><a href="#cb15-4" tabindex="-1"></a> <span class="at">names_pattern =</span> <span class="st">"(.)(.+)"</span>,</span>
<span id="cb15-5"><a href="#cb15-5" tabindex="-1"></a> <span class="at">values_to =</span> <span class="st">"n"</span>, </span>
<span id="cb15-6"><a href="#cb15-6" tabindex="-1"></a> <span class="at">values_drop_na =</span> <span class="cn">TRUE</span></span>
<span id="cb15-7"><a href="#cb15-7" tabindex="-1"></a>)</span>
<span id="cb15-8"><a href="#cb15-8" tabindex="-1"></a><span class="co">#> # A tibble: 35,750 × 5</span></span>
<span id="cb15-9"><a href="#cb15-9" tabindex="-1"></a><span class="co">#> iso2 year sex age n</span></span>
<span id="cb15-10"><a href="#cb15-10" tabindex="-1"></a><span class="co">#> <chr> <int> <chr> <chr> <int></span></span>
<span id="cb15-11"><a href="#cb15-11" tabindex="-1"></a><span class="co">#> 1 AD 1996 m 014 0</span></span>
<span id="cb15-12"><a href="#cb15-12" tabindex="-1"></a><span class="co">#> 2 AD 1996 m 1524 0</span></span>
<span id="cb15-13"><a href="#cb15-13" tabindex="-1"></a><span class="co">#> 3 AD 1996 m 2534 0</span></span>
<span id="cb15-14"><a href="#cb15-14" tabindex="-1"></a><span class="co">#> 4 AD 1996 m 3544 4</span></span>
<span id="cb15-15"><a href="#cb15-15" tabindex="-1"></a><span class="co">#> 5 AD 1996 m 4554 1</span></span>
<span id="cb15-16"><a href="#cb15-16" tabindex="-1"></a><span class="co">#> 6 AD 1996 m 5564 0</span></span>
<span id="cb15-17"><a href="#cb15-17" tabindex="-1"></a><span class="co">#> # ℹ 35,744 more rows</span></span></code></pre></div>
</div>
<div id="variables-are-stored-in-both-rows-and-columns" class="section level3">
<h3>Variables are stored in both rows and columns</h3>
<p>The most complicated form of messy data occurs when variables are
stored in both rows and columns. The code below loads daily weather data
from the Global Historical Climatology Network for one weather station
(MX17004) in Mexico for five months in 2010.</p>
<div class="sourceCode" id="cb16"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb16-1"><a href="#cb16-1" tabindex="-1"></a>weather <span class="ot"><-</span> <span class="fu">as_tibble</span>(<span class="fu">read.csv</span>(<span class="st">"weather.csv"</span>, <span class="at">stringsAsFactors =</span> <span class="cn">FALSE</span>))</span>
<span id="cb16-2"><a href="#cb16-2" tabindex="-1"></a>weather</span>
<span id="cb16-3"><a href="#cb16-3" tabindex="-1"></a><span class="co">#> # A tibble: 22 × 35</span></span>
<span id="cb16-4"><a href="#cb16-4" tabindex="-1"></a><span class="co">#> id year month element d1 d2 d3 d4 d5 d6 d7 d8</span></span>
<span id="cb16-5"><a href="#cb16-5" tabindex="-1"></a><span class="co">#> <chr> <int> <int> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl></span></span>
<span id="cb16-6"><a href="#cb16-6" tabindex="-1"></a><span class="co">#> 1 MX17004 2010 1 tmax NA NA NA NA NA NA NA NA</span></span>
<span id="cb16-7"><a href="#cb16-7" tabindex="-1"></a><span class="co">#> 2 MX17004 2010 1 tmin NA NA NA NA NA NA NA NA</span></span>
<span id="cb16-8"><a href="#cb16-8" tabindex="-1"></a><span class="co">#> 3 MX17004 2010 2 tmax NA 27.3 24.1 NA NA NA NA NA</span></span>
<span id="cb16-9"><a href="#cb16-9" tabindex="-1"></a><span class="co">#> 4 MX17004 2010 2 tmin NA 14.4 14.4 NA NA NA NA NA</span></span>
<span id="cb16-10"><a href="#cb16-10" tabindex="-1"></a><span class="co">#> 5 MX17004 2010 3 tmax NA NA NA NA 32.1 NA NA NA</span></span>
<span id="cb16-11"><a href="#cb16-11" tabindex="-1"></a><span class="co">#> 6 MX17004 2010 3 tmin NA NA NA NA 14.2 NA NA NA</span></span>
<span id="cb16-12"><a href="#cb16-12" tabindex="-1"></a><span class="co">#> # ℹ 16 more rows</span></span>
<span id="cb16-13"><a href="#cb16-13" tabindex="-1"></a><span class="co">#> # ℹ 23 more variables: d9 <lgl>, d10 <dbl>, d11 <dbl>, d12 <lgl>, d13 <dbl>,</span></span>
<span id="cb16-14"><a href="#cb16-14" tabindex="-1"></a><span class="co">#> # d14 <dbl>, d15 <dbl>, d16 <dbl>, d17 <dbl>, d18 <lgl>, d19 <lgl>,</span></span>
<span id="cb16-15"><a href="#cb16-15" tabindex="-1"></a><span class="co">#> # d20 <lgl>, d21 <lgl>, d22 <lgl>, d23 <dbl>, d24 <lgl>, d25 <dbl>,</span></span>
<span id="cb16-16"><a href="#cb16-16" tabindex="-1"></a><span class="co">#> # d26 <dbl>, d27 <dbl>, d28 <dbl>, d29 <dbl>, d30 <dbl>, d31 <dbl></span></span></code></pre></div>
<p>It has variables in individual columns (<code>id</code>,
<code>year</code>, <code>month</code>), spread across columns
(<code>day</code>, d1-d31) and across rows (<code>tmin</code>,
<code>tmax</code>) (minimum and maximum temperature). Months with fewer
than 31 days have structural missing values for the last day(s) of the
month.</p>
<p>To tidy this dataset we first use pivot_longer to gather the day
columns:</p>
<div class="sourceCode" id="cb17"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb17-1"><a href="#cb17-1" tabindex="-1"></a>weather2 <span class="ot"><-</span> weather <span class="sc">%>%</span> </span>
<span id="cb17-2"><a href="#cb17-2" tabindex="-1"></a> <span class="fu">pivot_longer</span>(</span>
<span id="cb17-3"><a href="#cb17-3" tabindex="-1"></a> d1<span class="sc">:</span>d31, </span>
<span id="cb17-4"><a href="#cb17-4" tabindex="-1"></a> <span class="at">names_to =</span> <span class="st">"day"</span>, </span>
<span id="cb17-5"><a href="#cb17-5" tabindex="-1"></a> <span class="at">values_to =</span> <span class="st">"value"</span>, </span>
<span id="cb17-6"><a href="#cb17-6" tabindex="-1"></a> <span class="at">values_drop_na =</span> <span class="cn">TRUE</span></span>
<span id="cb17-7"><a href="#cb17-7" tabindex="-1"></a> ) </span>
<span id="cb17-8"><a href="#cb17-8" tabindex="-1"></a>weather2</span>
<span id="cb17-9"><a href="#cb17-9" tabindex="-1"></a><span class="co">#> # A tibble: 66 × 6</span></span>
<span id="cb17-10"><a href="#cb17-10" tabindex="-1"></a><span class="co">#> id year month element day value</span></span>
<span id="cb17-11"><a href="#cb17-11" tabindex="-1"></a><span class="co">#> <chr> <int> <int> <chr> <chr> <dbl></span></span>
<span id="cb17-12"><a href="#cb17-12" tabindex="-1"></a><span class="co">#> 1 MX17004 2010 1 tmax d30 27.8</span></span>
<span id="cb17-13"><a href="#cb17-13" tabindex="-1"></a><span class="co">#> 2 MX17004 2010 1 tmin d30 14.5</span></span>
<span id="cb17-14"><a href="#cb17-14" tabindex="-1"></a><span class="co">#> 3 MX17004 2010 2 tmax d2 27.3</span></span>
<span id="cb17-15"><a href="#cb17-15" tabindex="-1"></a><span class="co">#> 4 MX17004 2010 2 tmax d3 24.1</span></span>
<span id="cb17-16"><a href="#cb17-16" tabindex="-1"></a><span class="co">#> 5 MX17004 2010 2 tmax d11 29.7</span></span>
<span id="cb17-17"><a href="#cb17-17" tabindex="-1"></a><span class="co">#> 6 MX17004 2010 2 tmax d23 29.9</span></span>
<span id="cb17-18"><a href="#cb17-18" tabindex="-1"></a><span class="co">#> # ℹ 60 more rows</span></span></code></pre></div>
<p>For presentation, I’ve dropped the missing values, making them
implicit rather than explicit. This is ok because we know how many days
are in each month and can easily reconstruct the explicit missing
values.</p>
<p>We’ll also do a little cleaning:</p>
<div class="sourceCode" id="cb18"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb18-1"><a href="#cb18-1" tabindex="-1"></a>weather3 <span class="ot"><-</span> weather2 <span class="sc">%>%</span> </span>
<span id="cb18-2"><a href="#cb18-2" tabindex="-1"></a> <span class="fu">mutate</span>(<span class="at">day =</span> <span class="fu">as.integer</span>(<span class="fu">gsub</span>(<span class="st">"d"</span>, <span class="st">""</span>, day))) <span class="sc">%>%</span></span>
<span id="cb18-3"><a href="#cb18-3" tabindex="-1"></a> <span class="fu">select</span>(id, year, month, day, element, value)</span>
<span id="cb18-4"><a href="#cb18-4" tabindex="-1"></a>weather3</span>
<span id="cb18-5"><a href="#cb18-5" tabindex="-1"></a><span class="co">#> # A tibble: 66 × 6</span></span>
<span id="cb18-6"><a href="#cb18-6" tabindex="-1"></a><span class="co">#> id year month day element value</span></span>
<span id="cb18-7"><a href="#cb18-7" tabindex="-1"></a><span class="co">#> <chr> <int> <int> <int> <chr> <dbl></span></span>
<span id="cb18-8"><a href="#cb18-8" tabindex="-1"></a><span class="co">#> 1 MX17004 2010 1 30 tmax 27.8</span></span>
<span id="cb18-9"><a href="#cb18-9" tabindex="-1"></a><span class="co">#> 2 MX17004 2010 1 30 tmin 14.5</span></span>
<span id="cb18-10"><a href="#cb18-10" tabindex="-1"></a><span class="co">#> 3 MX17004 2010 2 2 tmax 27.3</span></span>
<span id="cb18-11"><a href="#cb18-11" tabindex="-1"></a><span class="co">#> 4 MX17004 2010 2 3 tmax 24.1</span></span>
<span id="cb18-12"><a href="#cb18-12" tabindex="-1"></a><span class="co">#> 5 MX17004 2010 2 11 tmax 29.7</span></span>
<span id="cb18-13"><a href="#cb18-13" tabindex="-1"></a><span class="co">#> 6 MX17004 2010 2 23 tmax 29.9</span></span>
<span id="cb18-14"><a href="#cb18-14" tabindex="-1"></a><span class="co">#> # ℹ 60 more rows</span></span></code></pre></div>
<p>This dataset is mostly tidy, but the <code>element</code> column is
not a variable; it stores the names of variables. (Not shown in this
example are the other meteorological variables <code>prcp</code>
(precipitation) and <code>snow</code> (snowfall)). Fixing this requires
widening the data: <code>pivot_wider()</code> is inverse of
<code>pivot_longer()</code>, pivoting <code>element</code> and
<code>value</code> back out across multiple columns:</p>
<div class="sourceCode" id="cb19"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb19-1"><a href="#cb19-1" tabindex="-1"></a>weather3 <span class="sc">%>%</span> </span>
<span id="cb19-2"><a href="#cb19-2" tabindex="-1"></a> <span class="fu">pivot_wider</span>(<span class="at">names_from =</span> element, <span class="at">values_from =</span> value)</span>
<span id="cb19-3"><a href="#cb19-3" tabindex="-1"></a><span class="co">#> # A tibble: 33 × 6</span></span>
<span id="cb19-4"><a href="#cb19-4" tabindex="-1"></a><span class="co">#> id year month day tmax tmin</span></span>
<span id="cb19-5"><a href="#cb19-5" tabindex="-1"></a><span class="co">#> <chr> <int> <int> <int> <dbl> <dbl></span></span>
<span id="cb19-6"><a href="#cb19-6" tabindex="-1"></a><span class="co">#> 1 MX17004 2010 1 30 27.8 14.5</span></span>
<span id="cb19-7"><a href="#cb19-7" tabindex="-1"></a><span class="co">#> 2 MX17004 2010 2 2 27.3 14.4</span></span>
<span id="cb19-8"><a href="#cb19-8" tabindex="-1"></a><span class="co">#> 3 MX17004 2010 2 3 24.1 14.4</span></span>
<span id="cb19-9"><a href="#cb19-9" tabindex="-1"></a><span class="co">#> 4 MX17004 2010 2 11 29.7 13.4</span></span>
<span id="cb19-10"><a href="#cb19-10" tabindex="-1"></a><span class="co">#> 5 MX17004 2010 2 23 29.9 10.7</span></span>
<span id="cb19-11"><a href="#cb19-11" tabindex="-1"></a><span class="co">#> 6 MX17004 2010 3 5 32.1 14.2</span></span>
<span id="cb19-12"><a href="#cb19-12" tabindex="-1"></a><span class="co">#> # ℹ 27 more rows</span></span></code></pre></div>
<p>This form is tidy: there’s one variable in each column, and each row
represents one day.</p>
</div>
<div id="multiple-types" class="section level3">
<h3>Multiple types in one table</h3>
<p>Datasets often involve values collected at multiple levels, on
different types of observational units. During tidying, each type of
observational unit should be stored in its own table. This is closely
related to the idea of database normalisation, where each fact is
expressed in only one place. It’s important because otherwise
inconsistencies can arise.</p>
<p>The billboard dataset actually contains observations on two types of
observational units: the song and its rank in each week. This manifests
itself through the duplication of facts about the song:
<code>artist</code> is repeated many times.</p>
<p>This dataset needs to be broken down into two pieces: a song dataset
which stores <code>artist</code> and <code>song name</code>, and a
ranking dataset which gives the <code>rank</code> of the
<code>song</code> in each <code>week</code>. We first extract a
<code>song</code> dataset:</p>
<div class="sourceCode" id="cb20"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb20-1"><a href="#cb20-1" tabindex="-1"></a>song <span class="ot"><-</span> billboard3 <span class="sc">%>%</span> </span>
<span id="cb20-2"><a href="#cb20-2" tabindex="-1"></a> <span class="fu">distinct</span>(artist, track) <span class="sc">%>%</span></span>
<span id="cb20-3"><a href="#cb20-3" tabindex="-1"></a> <span class="fu">mutate</span>(<span class="at">song_id =</span> <span class="fu">row_number</span>())</span>
<span id="cb20-4"><a href="#cb20-4" tabindex="-1"></a>song</span>
<span id="cb20-5"><a href="#cb20-5" tabindex="-1"></a><span class="co">#> # A tibble: 317 × 3</span></span>
<span id="cb20-6"><a href="#cb20-6" tabindex="-1"></a><span class="co">#> artist track song_id</span></span>
<span id="cb20-7"><a href="#cb20-7" tabindex="-1"></a><span class="co">#> <chr> <chr> <int></span></span>
<span id="cb20-8"><a href="#cb20-8" tabindex="-1"></a><span class="co">#> 1 2 Pac Baby Don't Cry (Keep... 1</span></span>
<span id="cb20-9"><a href="#cb20-9" tabindex="-1"></a><span class="co">#> 2 2Ge+her The Hardest Part Of ... 2</span></span>
<span id="cb20-10"><a href="#cb20-10" tabindex="-1"></a><span class="co">#> 3 3 Doors Down Kryptonite 3</span></span>
<span id="cb20-11"><a href="#cb20-11" tabindex="-1"></a><span class="co">#> 4 3 Doors Down Loser 4</span></span>
<span id="cb20-12"><a href="#cb20-12" tabindex="-1"></a><span class="co">#> 5 504 Boyz Wobble Wobble 5</span></span>
<span id="cb20-13"><a href="#cb20-13" tabindex="-1"></a><span class="co">#> 6 98^0 Give Me Just One Nig... 6</span></span>
<span id="cb20-14"><a href="#cb20-14" tabindex="-1"></a><span class="co">#> # ℹ 311 more rows</span></span></code></pre></div>
<p>Then use that to make a <code>rank</code> dataset by replacing
repeated song facts with a pointer to song details (a unique song
id):</p>
<div class="sourceCode" id="cb21"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb21-1"><a href="#cb21-1" tabindex="-1"></a>rank <span class="ot"><-</span> billboard3 <span class="sc">%>%</span></span>
<span id="cb21-2"><a href="#cb21-2" tabindex="-1"></a> <span class="fu">left_join</span>(song, <span class="fu">c</span>(<span class="st">"artist"</span>, <span class="st">"track"</span>)) <span class="sc">%>%</span></span>
<span id="cb21-3"><a href="#cb21-3" tabindex="-1"></a> <span class="fu">select</span>(song_id, date, week, rank)</span>
<span id="cb21-4"><a href="#cb21-4" tabindex="-1"></a>rank</span>
<span id="cb21-5"><a href="#cb21-5" tabindex="-1"></a><span class="co">#> # A tibble: 5,307 × 4</span></span>
<span id="cb21-6"><a href="#cb21-6" tabindex="-1"></a><span class="co">#> song_id date week rank</span></span>
<span id="cb21-7"><a href="#cb21-7" tabindex="-1"></a><span class="co">#> <int> <date> <int> <dbl></span></span>
<span id="cb21-8"><a href="#cb21-8" tabindex="-1"></a><span class="co">#> 1 1 2000-02-26 1 87</span></span>
<span id="cb21-9"><a href="#cb21-9" tabindex="-1"></a><span class="co">#> 2 1 2000-03-04 2 82</span></span>
<span id="cb21-10"><a href="#cb21-10" tabindex="-1"></a><span class="co">#> 3 1 2000-03-11 3 72</span></span>
<span id="cb21-11"><a href="#cb21-11" tabindex="-1"></a><span class="co">#> 4 1 2000-03-18 4 77</span></span>
<span id="cb21-12"><a href="#cb21-12" tabindex="-1"></a><span class="co">#> 5 1 2000-03-25 5 87</span></span>
<span id="cb21-13"><a href="#cb21-13" tabindex="-1"></a><span class="co">#> 6 1 2000-04-01 6 94</span></span>
<span id="cb21-14"><a href="#cb21-14" tabindex="-1"></a><span class="co">#> # ℹ 5,301 more rows</span></span></code></pre></div>
<p>You could also imagine a <code>week</code> dataset which would record
background information about the week, maybe the total number of songs
sold or similar “demographic” information.</p>
<p>Normalisation is useful for tidying and eliminating inconsistencies.
However, there are few data analysis tools that work directly with
relational data, so analysis usually also requires denormalisation or
the merging the datasets back into one table.</p>
</div>
<div id="one-type-in-multiple-tables" class="section level3">
<h3>One type in multiple tables</h3>
<p>It’s also common to find data values about a single type of
observational unit spread out over multiple tables or files. These
tables and files are often split up by another variable, so that each
represents a single year, person, or location. As long as the format for
individual records is consistent, this is an easy problem to fix:</p>
<ol style="list-style-type: decimal">
<li><p>Read the files into a list of tables.</p></li>
<li><p>For each table, add a new column that records the original file
name (the file name is often the value of an important
variable).</p></li>
<li><p>Combine all tables into a single table.</p></li>
</ol>
<p>Purrr makes this straightforward in R. The following code generates a
vector of file names in a directory (<code>data/</code>) which match a
regular expression (ends in <code>.csv</code>). Next we name each
element of the vector with the name of the file. We do this because will
preserve the names in the following step, ensuring that each row in the
final data frame is labeled with its source. Finally,
<code>map_dfr()</code> loops over each path, reading in the csv file and
combining the results into a single data frame.</p>
<div class="sourceCode" id="cb22"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb22-1"><a href="#cb22-1" tabindex="-1"></a><span class="fu">library</span>(purrr)</span>
<span id="cb22-2"><a href="#cb22-2" tabindex="-1"></a>paths <span class="ot"><-</span> <span class="fu">dir</span>(<span class="st">"data"</span>, <span class="at">pattern =</span> <span class="st">"</span><span class="sc">\\</span><span class="st">.csv$"</span>, <span class="at">full.names =</span> <span class="cn">TRUE</span>)</span>
<span id="cb22-3"><a href="#cb22-3" tabindex="-1"></a><span class="fu">names</span>(paths) <span class="ot"><-</span> <span class="fu">basename</span>(paths)</span>
<span id="cb22-4"><a href="#cb22-4" tabindex="-1"></a><span class="fu">map_dfr</span>(paths, read.csv, <span class="at">stringsAsFactors =</span> <span class="cn">FALSE</span>, <span class="at">.id =</span> <span class="st">"filename"</span>)</span></code></pre></div>
<p>Once you have a single table, you can perform additional tidying as
needed. An example of this type of cleaning can be found at <a href="https://github.com/hadley/data-baby-names" class="uri">https://github.com/hadley/data-baby-names</a> which takes
129 yearly baby name tables provided by the US Social Security
Administration and combines them into a single file.</p>
<p>A more complicated situation occurs when the dataset structure
changes over time. For example, the datasets may contain different
variables, the same variables with different names, different file
formats, or different conventions for missing values. This may require
you to tidy each file to individually (or, if you’re lucky, in small
groups) and then combine them once tidied. An example of this type of
tidying is illustrated in <a href="https://github.com/hadley/data-fuel-economy" class="uri">https://github.com/hadley/data-fuel-economy</a>, which shows
the tidying of <span>epa</span> fuel economy data for over 50,000 cars
from 1978 to 2008. The raw data is available online, but each year is
stored in a separate file and there are four major formats with many
minor variations, making tidying this dataset a considerable
challenge.</p>
</div>
</div>
<!-- code folding -->
<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
(function () {
var script = document.createElement("script");
script.type = "text/javascript";
script.src = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
document.getElementsByTagName("head")[0].appendChild(script);
})();
</script>
</body>
</html>
|
