-
Notifications
You must be signed in to change notification settings - Fork 0
/
joins.html
1869 lines (1838 loc) · 170 KB
/
joins.html
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
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"><head>
<meta charset="utf-8">
<meta name="generator" content="quarto-1.6.33">
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes">
<meta name="author" content="Hadley Wickham, Mine Çetinkaya-Rundel, and Garrett Grolemund">
<title>20 Joins – R for Data Science (2e)</title>
<style>
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
div.columns{display: flex; gap: min(4vw, 1.5em);}
div.column{flex: auto; overflow-x: auto;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
ul.task-list li input[type="checkbox"] {
width: 0.8em;
margin: 0 0.8em 0.2em -1em; /* quarto-specific, see https://github.com/quarto-dev/quarto-cli/issues/4556 */
vertical-align: middle;
}
/* CSS for syntax highlighting */
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 { display: inline-block; 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;
}
pre.numberSource { margin-left: 3em; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
</style>
<script src="site_libs/quarto-nav/quarto-nav.js"></script>
<script src="site_libs/quarto-nav/headroom.min.js"></script>
<script src="site_libs/clipboard/clipboard.min.js"></script>
<script src="site_libs/quarto-search/autocomplete.umd.js"></script>
<script src="site_libs/quarto-search/fuse.min.js"></script>
<script src="site_libs/quarto-search/quarto-search.js"></script>
<meta name="quarto:offset" content="./">
<link href="./import.html" rel="next">
<link href="./missing-values.html" rel="prev">
<link href="./cover.jpg" rel="icon" type="image/jpeg">
<script src="site_libs/quarto-html/quarto.js"></script>
<script src="site_libs/quarto-html/popper.min.js"></script>
<script src="site_libs/quarto-html/tippy.umd.min.js"></script>
<script src="site_libs/quarto-html/anchor.min.js"></script>
<link href="site_libs/quarto-html/tippy.css" rel="stylesheet">
<link href="site_libs/quarto-html/quarto-syntax-highlighting-07ba0ad10f5680c660e360ac31d2f3b6.css" rel="stylesheet" id="quarto-text-highlighting-styles">
<script src="site_libs/bootstrap/bootstrap.min.js"></script>
<link href="site_libs/bootstrap/bootstrap-icons.css" rel="stylesheet">
<link href="site_libs/bootstrap/bootstrap-4e73071aaf4ad36f30f53cb9d4ee01e0.min.css" rel="stylesheet" append-hash="true" id="quarto-bootstrap" data-mode="light">
<script id="quarto-search-options" type="application/json">{
"location": "sidebar",
"copy-button": false,
"collapse-after": 3,
"panel-placement": "start",
"type": "textbox",
"limit": 50,
"keyboard-shortcut": [
"f",
"/",
"s"
],
"show-item-context": false,
"language": {
"search-no-results-text": "No results",
"search-matching-documents-text": "matching documents",
"search-copy-link-title": "Copy link to search",
"search-hide-matches-text": "Hide additional matches",
"search-more-match-text": "more match in this document",
"search-more-matches-text": "more matches in this document",
"search-clear-button-title": "Clear",
"search-text-placeholder": "",
"search-detached-cancel-button-title": "Cancel",
"search-submit-button-title": "Submit",
"search-label": "Search"
}
}</script>
<script defer="" data-domain="r4ds.hadley.nz" src="https://plausible.io/js/plausible.js"></script>
</head>
<body class="nav-sidebar floating">
<div id="quarto-search-results"></div>
<header id="quarto-header" class="headroom fixed-top">
<nav class="quarto-secondary-nav">
<div class="container-fluid d-flex">
<button type="button" class="quarto-btn-toggle btn" data-bs-toggle="collapse" role="button" data-bs-target=".quarto-sidebar-collapse-item" aria-controls="quarto-sidebar" aria-expanded="false" aria-label="Toggle sidebar navigation" onclick="if (window.quartoToggleHeadroom) { window.quartoToggleHeadroom(); }">
<i class="bi bi-layout-text-sidebar-reverse"></i>
</button>
<nav class="quarto-page-breadcrumbs" aria-label="breadcrumb"><ol class="breadcrumb"><li class="breadcrumb-item"><a href="./transform.html">Transform</a></li><li class="breadcrumb-item"><a href="./joins.html"><span class="chapter-number">20</span> <span class="chapter-title">Joins</span></a></li></ol></nav>
<a class="flex-grow-1" role="navigation" data-bs-toggle="collapse" data-bs-target=".quarto-sidebar-collapse-item" aria-controls="quarto-sidebar" aria-expanded="false" aria-label="Toggle sidebar navigation" onclick="if (window.quartoToggleHeadroom) { window.quartoToggleHeadroom(); }">
</a>
<button type="button" class="btn quarto-search-button" aria-label="Search" onclick="window.quartoOpenSearch();">
<i class="bi bi-search"></i>
</button>
</div>
</nav>
</header>
<!-- content -->
<div id="quarto-content" class="quarto-container page-columns page-rows-contents page-layout-article">
<!-- sidebar -->
<nav id="quarto-sidebar" class="sidebar collapse collapse-horizontal quarto-sidebar-collapse-item sidebar-navigation floating overflow-auto">
<div class="pt-lg-2 mt-2 text-left sidebar-header">
<div class="sidebar-title mb-0 py-0">
<a href="./">R for Data Science (2e)</a>
<div class="sidebar-tools-main">
<a href="https://github.com/emunkhtsetseg/r4ds/" title="Source Code" class="quarto-navigation-tool px-1" aria-label="Source Code"><i class="bi bi-github"></i></a>
<a href="" class="quarto-reader-toggle quarto-navigation-tool px-1" onclick="window.quartoToggleReader(); return false;" title="Toggle reader mode">
<div class="quarto-reader-toggle-btn">
<i class="bi"></i>
</div>
</a>
</div>
</div>
</div>
<div class="mt-2 flex-shrink-0 align-items-center">
<div class="sidebar-search">
<div id="quarto-search" class="" title="Search"></div>
</div>
</div>
<div class="sidebar-menu-container">
<ul class="list-unstyled mt-1">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./index.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">1</span> <span class="chapter-title">тавтай морил</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./preface-2e.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Preface to the second edition</span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./intro.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Introduction</span></a>
</div>
</li>
<li class="sidebar-item sidebar-item-section">
<div class="sidebar-item-container">
<a href="./whole-game.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Whole game</span></a>
<a class="sidebar-item-toggle text-start" data-bs-toggle="collapse" data-bs-target="#quarto-sidebar-section-1" role="navigation" aria-expanded="true" aria-label="Toggle section">
<i class="bi bi-chevron-right ms-2"></i>
</a>
</div>
<ul id="quarto-sidebar-section-1" class="collapse list-unstyled sidebar-section depth1 show">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./data-visualize.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">2</span> <span class="chapter-title">Data visualization</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./workflow-basics.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">3</span> <span class="chapter-title">Workflow: basics</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./data-transform.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">4</span> <span class="chapter-title">Data transformation</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./workflow-style.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">5</span> <span class="chapter-title">Workflow: code style</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./data-tidy.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">6</span> <span class="chapter-title">Data tidying</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./workflow-scripts.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">7</span> <span class="chapter-title">Workflow: scripts and projects</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./data-import.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">8</span> <span class="chapter-title">Data import</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./workflow-help.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">9</span> <span class="chapter-title">Workflow: getting help</span></span></a>
</div>
</li>
</ul>
</li>
<li class="sidebar-item sidebar-item-section">
<div class="sidebar-item-container">
<a href="./visualize.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Visualize</span></a>
<a class="sidebar-item-toggle text-start" data-bs-toggle="collapse" data-bs-target="#quarto-sidebar-section-2" role="navigation" aria-expanded="true" aria-label="Toggle section">
<i class="bi bi-chevron-right ms-2"></i>
</a>
</div>
<ul id="quarto-sidebar-section-2" class="collapse list-unstyled sidebar-section depth1 show">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./layers.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">10</span> <span class="chapter-title">Layers</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./EDA.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">11</span> <span class="chapter-title">Exploratory data analysis</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./communication.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">12</span> <span class="chapter-title">Communication</span></span></a>
</div>
</li>
</ul>
</li>
<li class="sidebar-item sidebar-item-section">
<div class="sidebar-item-container">
<a href="./transform.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Transform</span></a>
<a class="sidebar-item-toggle text-start" data-bs-toggle="collapse" data-bs-target="#quarto-sidebar-section-3" role="navigation" aria-expanded="true" aria-label="Toggle section">
<i class="bi bi-chevron-right ms-2"></i>
</a>
</div>
<ul id="quarto-sidebar-section-3" class="collapse list-unstyled sidebar-section depth1 show">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./logicals.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">13</span> <span class="chapter-title">Logical vectors</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./numbers.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">14</span> <span class="chapter-title">Numbers</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./strings.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">15</span> <span class="chapter-title">Strings</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./regexps.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">16</span> <span class="chapter-title">Regular expressions</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./factors.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">17</span> <span class="chapter-title">Factors</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./datetimes.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">18</span> <span class="chapter-title">Dates and times</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./missing-values.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">19</span> <span class="chapter-title">Missing values</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./joins.html" class="sidebar-item-text sidebar-link active">
<span class="menu-text"><span class="chapter-number">20</span> <span class="chapter-title">Joins</span></span></a>
</div>
</li>
</ul>
</li>
<li class="sidebar-item sidebar-item-section">
<div class="sidebar-item-container">
<a href="./import.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Import</span></a>
<a class="sidebar-item-toggle text-start" data-bs-toggle="collapse" data-bs-target="#quarto-sidebar-section-4" role="navigation" aria-expanded="true" aria-label="Toggle section">
<i class="bi bi-chevron-right ms-2"></i>
</a>
</div>
<ul id="quarto-sidebar-section-4" class="collapse list-unstyled sidebar-section depth1 show">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./spreadsheets.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">21</span> <span class="chapter-title">Spreadsheets</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./databases.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">22</span> <span class="chapter-title">Databases</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./arrow.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">23</span> <span class="chapter-title">Arrow</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./rectangling.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">24</span> <span class="chapter-title">Hierarchical data</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./webscraping.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">25</span> <span class="chapter-title">Web scraping</span></span></a>
</div>
</li>
</ul>
</li>
<li class="sidebar-item sidebar-item-section">
<div class="sidebar-item-container">
<a href="./program.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Program</span></a>
<a class="sidebar-item-toggle text-start" data-bs-toggle="collapse" data-bs-target="#quarto-sidebar-section-5" role="navigation" aria-expanded="true" aria-label="Toggle section">
<i class="bi bi-chevron-right ms-2"></i>
</a>
</div>
<ul id="quarto-sidebar-section-5" class="collapse list-unstyled sidebar-section depth1 show">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./functions.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">26</span> <span class="chapter-title">Functions</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./iteration.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">27</span> <span class="chapter-title">Iteration</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./base-R.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">28</span> <span class="chapter-title">A field guide to base R</span></span></a>
</div>
</li>
</ul>
</li>
<li class="sidebar-item sidebar-item-section">
<div class="sidebar-item-container">
<a href="./communicate.html" class="sidebar-item-text sidebar-link">
<span class="menu-text">Communicate</span></a>
<a class="sidebar-item-toggle text-start" data-bs-toggle="collapse" data-bs-target="#quarto-sidebar-section-6" role="navigation" aria-expanded="true" aria-label="Toggle section">
<i class="bi bi-chevron-right ms-2"></i>
</a>
</div>
<ul id="quarto-sidebar-section-6" class="collapse list-unstyled sidebar-section depth1 show">
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./quarto.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">29</span> <span class="chapter-title">Quarto</span></span></a>
</div>
</li>
<li class="sidebar-item">
<div class="sidebar-item-container">
<a href="./quarto-formats.html" class="sidebar-item-text sidebar-link">
<span class="menu-text"><span class="chapter-number">30</span> <span class="chapter-title">Quarto formats</span></span></a>
</div>
</li>
</ul>
</li>
</ul>
</div>
</nav>
<div id="quarto-sidebar-glass" class="quarto-sidebar-collapse-item" data-bs-toggle="collapse" data-bs-target=".quarto-sidebar-collapse-item"></div>
<!-- margin-sidebar -->
<div id="quarto-margin-sidebar" class="sidebar margin-sidebar">
<nav id="TOC" role="doc-toc" class="toc-active">
<h2 id="toc-title">Table of contents</h2>
<ul>
<li><a href="#introduction" id="toc-introduction" class="nav-link active" data-scroll-target="#introduction"><span class="header-section-number">20.1</span> Introduction</a>
<ul class="collapse">
<li><a href="#prerequisites" id="toc-prerequisites" class="nav-link" data-scroll-target="#prerequisites"><span class="header-section-number">20.1.1</span> Prerequisites</a></li>
</ul></li>
<li><a href="#keys" id="toc-keys" class="nav-link" data-scroll-target="#keys"><span class="header-section-number">20.2</span> Keys</a>
<ul class="collapse">
<li><a href="#primary-and-foreign-keys" id="toc-primary-and-foreign-keys" class="nav-link" data-scroll-target="#primary-and-foreign-keys"><span class="header-section-number">20.2.1</span> Primary and foreign keys</a></li>
<li><a href="#checking-primary-keys" id="toc-checking-primary-keys" class="nav-link" data-scroll-target="#checking-primary-keys"><span class="header-section-number">20.2.2</span> Checking primary keys</a></li>
<li><a href="#surrogate-keys" id="toc-surrogate-keys" class="nav-link" data-scroll-target="#surrogate-keys"><span class="header-section-number">20.2.3</span> Surrogate keys</a></li>
<li><a href="#exercises" id="toc-exercises" class="nav-link" data-scroll-target="#exercises"><span class="header-section-number">20.2.4</span> Exercises</a></li>
</ul></li>
<li><a href="#sec-mutating-joins" id="toc-sec-mutating-joins" class="nav-link" data-scroll-target="#sec-mutating-joins"><span class="header-section-number">20.3</span> Basic joins</a>
<ul class="collapse">
<li><a href="#mutating-joins" id="toc-mutating-joins" class="nav-link" data-scroll-target="#mutating-joins"><span class="header-section-number">20.3.1</span> Mutating joins</a></li>
<li><a href="#specifying-join-keys" id="toc-specifying-join-keys" class="nav-link" data-scroll-target="#specifying-join-keys"><span class="header-section-number">20.3.2</span> Specifying join keys</a></li>
<li><a href="#filtering-joins" id="toc-filtering-joins" class="nav-link" data-scroll-target="#filtering-joins"><span class="header-section-number">20.3.3</span> Filtering joins</a></li>
<li><a href="#exercises-1" id="toc-exercises-1" class="nav-link" data-scroll-target="#exercises-1"><span class="header-section-number">20.3.4</span> Exercises</a></li>
</ul></li>
<li><a href="#how-do-joins-work" id="toc-how-do-joins-work" class="nav-link" data-scroll-target="#how-do-joins-work"><span class="header-section-number">20.4</span> How do joins work?</a>
<ul class="collapse">
<li><a href="#row-matching" id="toc-row-matching" class="nav-link" data-scroll-target="#row-matching"><span class="header-section-number">20.4.1</span> Row matching</a></li>
<li><a href="#filtering-joins-1" id="toc-filtering-joins-1" class="nav-link" data-scroll-target="#filtering-joins-1"><span class="header-section-number">20.4.2</span> Filtering joins</a></li>
</ul></li>
<li><a href="#sec-non-equi-joins" id="toc-sec-non-equi-joins" class="nav-link" data-scroll-target="#sec-non-equi-joins"><span class="header-section-number">20.5</span> Non-equi joins</a>
<ul class="collapse">
<li><a href="#cross-joins" id="toc-cross-joins" class="nav-link" data-scroll-target="#cross-joins"><span class="header-section-number">20.5.1</span> Cross joins</a></li>
<li><a href="#inequality-joins" id="toc-inequality-joins" class="nav-link" data-scroll-target="#inequality-joins"><span class="header-section-number">20.5.2</span> Inequality joins</a></li>
<li><a href="#rolling-joins" id="toc-rolling-joins" class="nav-link" data-scroll-target="#rolling-joins"><span class="header-section-number">20.5.3</span> Rolling joins</a></li>
<li><a href="#overlap-joins" id="toc-overlap-joins" class="nav-link" data-scroll-target="#overlap-joins"><span class="header-section-number">20.5.4</span> Overlap joins</a></li>
<li><a href="#exercises-2" id="toc-exercises-2" class="nav-link" data-scroll-target="#exercises-2"><span class="header-section-number">20.5.5</span> Exercises</a></li>
</ul></li>
<li><a href="#summary" id="toc-summary" class="nav-link" data-scroll-target="#summary"><span class="header-section-number">20.6</span> Summary</a></li>
</ul>
<div class="toc-actions"><ul><li><a href="https://github.com/emunkhtsetseg/r4ds/edit/main/joins.qmd" class="toc-action"><i class="bi bi-github"></i>Edit this page</a></li><li><a href="https://github.com/emunkhtsetseg/r4ds/issues/new" class="toc-action"><i class="bi empty"></i>Report an issue</a></li></ul></div></nav>
</div>
<!-- main -->
<main class="content" id="quarto-document-content">
<header id="title-block-header" class="quarto-title-block default"><nav class="quarto-page-breadcrumbs quarto-title-breadcrumbs d-none d-lg-block" aria-label="breadcrumb"><ol class="breadcrumb"><li class="breadcrumb-item"><a href="./transform.html">Transform</a></li><li class="breadcrumb-item"><a href="./joins.html"><span class="chapter-number">20</span> <span class="chapter-title">Joins</span></a></li></ol></nav>
<div class="quarto-title">
<h1 class="title"><span id="sec-joins" class="quarto-section-identifier"><span class="chapter-number">20</span> <span class="chapter-title">Joins</span></span></h1>
</div>
<div class="quarto-title-meta">
</div>
</header>
<section id="introduction" class="level2" data-number="20.1">
<h2 data-number="20.1" class="anchored" data-anchor-id="introduction"><span class="header-section-number">20.1</span> Introduction</h2>
<p>It’s rare that a data analysis involves only a single data frame. Typically you have many data frames, and you must <strong>join</strong> them together to answer the questions that you’re interested in. This chapter will introduce you to two important types of joins:</p>
<ul>
<li>Mutating joins, which add new variables to one data frame from matching observations in another.</li>
<li>Filtering joins, which filter observations from one data frame based on whether or not they match an observation in another.</li>
</ul>
<p>We’ll begin by discussing keys, the variables used to connect a pair of data frames in a join. We cement the theory with an examination of the keys in the datasets from the nycflights13 package, then use that knowledge to start joining data frames together. Next we’ll discuss how joins work, focusing on their action on the rows. We’ll finish up with a discussion of non-equi joins, a family of joins that provide a more flexible way of matching keys than the default equality relationship.</p>
<section id="prerequisites" class="level3" data-number="20.1.1">
<h3 data-number="20.1.1" class="anchored" data-anchor-id="prerequisites"><span class="header-section-number">20.1.1</span> Prerequisites</h3>
<p>In this chapter, we’ll explore the five related datasets from nycflights13 using the join functions from dplyr.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb1"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(tidyverse)</span>
<span id="cb1-2"><a href="#cb1-2" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(nycflights13)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
</section>
</section>
<section id="keys" class="level2" data-number="20.2">
<h2 data-number="20.2" class="anchored" data-anchor-id="keys"><span class="header-section-number">20.2</span> Keys</h2>
<p>To understand joins, you need to first understand how two tables can be connected through a pair of keys, within each table. In this section, you’ll learn about the two types of key and see examples of both in the datasets of the nycflights13 package. You’ll also learn how to check that your keys are valid, and what to do if your table lacks a key.</p>
<section id="primary-and-foreign-keys" class="level3" data-number="20.2.1">
<h3 data-number="20.2.1" class="anchored" data-anchor-id="primary-and-foreign-keys"><span class="header-section-number">20.2.1</span> Primary and foreign keys</h3>
<p>Every join involves a pair of keys: a primary key and a foreign key. A <strong>primary key</strong> is a variable or set of variables that uniquely identifies each observation. When more than one variable is needed, the key is called a <strong>compound key.</strong> For example, in nycflights13:</p>
<ul>
<li><p><code>airlines</code> records two pieces of data about each airline: its carrier code and its full name. You can identify an airline with its two letter carrier code, making <code>carrier</code> the primary key.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb2"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" aria-hidden="true" tabindex="-1"></a>airlines</span>
<span id="cb2-2"><a href="#cb2-2" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 16 × 2</span></span>
<span id="cb2-3"><a href="#cb2-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> carrier name </span></span>
<span id="cb2-4"><a href="#cb2-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> <chr> </span></span>
<span id="cb2-5"><a href="#cb2-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 9E Endeavor Air Inc. </span></span>
<span id="cb2-6"><a href="#cb2-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 AA American Airlines Inc. </span></span>
<span id="cb2-7"><a href="#cb2-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 AS Alaska Airlines Inc. </span></span>
<span id="cb2-8"><a href="#cb2-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 B6 JetBlue Airways </span></span>
<span id="cb2-9"><a href="#cb2-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 DL Delta Air Lines Inc. </span></span>
<span id="cb2-10"><a href="#cb2-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 EV ExpressJet Airlines Inc.</span></span>
<span id="cb2-11"><a href="#cb2-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 10 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div></li>
<li><p><code>airports</code> records data about each airport. You can identify each airport by its three letter airport code, making <code>faa</code> the primary key.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb3"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1" aria-hidden="true" tabindex="-1"></a>airports</span>
<span id="cb3-2"><a href="#cb3-2" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 1,458 × 8</span></span>
<span id="cb3-3"><a href="#cb3-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> faa name lat lon alt tz dst </span></span>
<span id="cb3-4"><a href="#cb3-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <chr></span></span>
<span id="cb3-5"><a href="#cb3-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 04G Lansdowne Airport 41.1 -80.6 1044 -5 A </span></span>
<span id="cb3-6"><a href="#cb3-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 06A Moton Field Municipal Airport 32.5 -85.7 264 -6 A </span></span>
<span id="cb3-7"><a href="#cb3-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 06C Schaumburg Regional 42.0 -88.1 801 -6 A </span></span>
<span id="cb3-8"><a href="#cb3-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 06N Randall Airport 41.4 -74.4 523 -5 A </span></span>
<span id="cb3-9"><a href="#cb3-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 09J Jekyll Island Airport 31.1 -81.4 11 -5 A </span></span>
<span id="cb3-10"><a href="#cb3-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 0A9 Elizabethton Municipal Airpo… 36.4 -82.2 1593 -5 A </span></span>
<span id="cb3-11"><a href="#cb3-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 1,452 more rows</span></span>
<span id="cb3-12"><a href="#cb3-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 1 more variable: tzone <chr></span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div></li>
<li><p><code>planes</code> records data about each plane. You can identify a plane by its tail number, making <code>tailnum</code> the primary key.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb4"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1" aria-hidden="true" tabindex="-1"></a>planes</span>
<span id="cb4-2"><a href="#cb4-2" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 3,322 × 9</span></span>
<span id="cb4-3"><a href="#cb4-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> tailnum year type manufacturer model engines</span></span>
<span id="cb4-4"><a href="#cb4-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> <int> <chr> <chr> <chr> <int></span></span>
<span id="cb4-5"><a href="#cb4-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 N10156 2004 Fixed wing multi… EMBRAER EMB-145XR 2</span></span>
<span id="cb4-6"><a href="#cb4-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 N102UW 1998 Fixed wing multi… AIRBUS INDUSTR… A320-214 2</span></span>
<span id="cb4-7"><a href="#cb4-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 N103US 1999 Fixed wing multi… AIRBUS INDUSTR… A320-214 2</span></span>
<span id="cb4-8"><a href="#cb4-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 N104UW 1999 Fixed wing multi… AIRBUS INDUSTR… A320-214 2</span></span>
<span id="cb4-9"><a href="#cb4-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 N10575 2002 Fixed wing multi… EMBRAER EMB-145LR 2</span></span>
<span id="cb4-10"><a href="#cb4-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 N105UW 1999 Fixed wing multi… AIRBUS INDUSTR… A320-214 2</span></span>
<span id="cb4-11"><a href="#cb4-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 3,316 more rows</span></span>
<span id="cb4-12"><a href="#cb4-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 3 more variables: seats <int>, speed <int>, engine <chr></span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div></li>
<li><p><code>weather</code> records data about the weather at the origin airports. You can identify each observation by the combination of location and time, making <code>origin</code> and <code>time_hour</code> the compound primary key.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb5"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1" aria-hidden="true" tabindex="-1"></a>weather</span>
<span id="cb5-2"><a href="#cb5-2" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 26,115 × 15</span></span>
<span id="cb5-3"><a href="#cb5-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> origin year month day hour temp dewp humid wind_dir</span></span>
<span id="cb5-4"><a href="#cb5-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> <int> <int> <int> <int> <dbl> <dbl> <dbl> <dbl></span></span>
<span id="cb5-5"><a href="#cb5-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 EWR 2013 1 1 1 39.0 26.1 59.4 270</span></span>
<span id="cb5-6"><a href="#cb5-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 EWR 2013 1 1 2 39.0 27.0 61.6 250</span></span>
<span id="cb5-7"><a href="#cb5-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 EWR 2013 1 1 3 39.0 28.0 64.4 240</span></span>
<span id="cb5-8"><a href="#cb5-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 EWR 2013 1 1 4 39.9 28.0 62.2 250</span></span>
<span id="cb5-9"><a href="#cb5-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 EWR 2013 1 1 5 39.0 28.0 64.4 260</span></span>
<span id="cb5-10"><a href="#cb5-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 EWR 2013 1 1 6 37.9 28.0 67.2 240</span></span>
<span id="cb5-11"><a href="#cb5-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 26,109 more rows</span></span>
<span id="cb5-12"><a href="#cb5-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 6 more variables: wind_speed <dbl>, wind_gust <dbl>, …</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div></li>
</ul>
<p>A <strong>foreign key</strong> is a variable (or set of variables) that corresponds to a primary key in another table. For example:</p>
<ul>
<li><code>flights$tailnum</code> is a foreign key that corresponds to the primary key <code>planes$tailnum</code>.</li>
<li><code>flights$carrier</code> is a foreign key that corresponds to the primary key <code>airlines$carrier</code>.</li>
<li><code>flights$origin</code> is a foreign key that corresponds to the primary key <code>airports$faa</code>.</li>
<li><code>flights$dest</code> is a foreign key that corresponds to the primary key <code>airports$faa</code>.</li>
<li><code>flights$origin</code>-<code>flights$time_hour</code> is a compound foreign key that corresponds to the compound primary key <code>weather$origin</code>-<code>weather$time_hour</code>.</li>
</ul>
<p>These relationships are summarized visually in <a href="#fig-flights-relationships" class="quarto-xref">Figure <span>20.1</span></a>.</p>
<div class="cell">
<div class="cell-output-display">
<div id="fig-flights-relationships" class="quarto-float quarto-figure quarto-figure-center anchored" alt="The relationships between airports, planes, flights, weather, and airlines datasets from the nycflights13 package. airports$faa connected to the flights$origin and flights$dest. planes$tailnum is connected to the flights$tailnum. weather$time_hour and weather$origin are jointly connected to flights$time_hour and flights$origin. airlines$carrier is connected to flights$carrier. There are no direct connections between airports, planes, airlines, and weather data frames.">
<figure class="quarto-float quarto-float-fig figure">
<div aria-describedby="fig-flights-relationships-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
<img src="diagrams/relational.png" class="img-fluid figure-img" alt="The relationships between airports, planes, flights, weather, and airlines datasets from the nycflights13 package. airports$faa connected to the flights$origin and flights$dest. planes$tailnum is connected to the flights$tailnum. weather$time_hour and weather$origin are jointly connected to flights$time_hour and flights$origin. airlines$carrier is connected to flights$carrier. There are no direct connections between airports, planes, airlines, and weather data frames." width="502">
</div>
<figcaption class="quarto-float-caption-bottom quarto-float-caption quarto-float-fig" id="fig-flights-relationships-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
Figure 20.1: Connections between all five data frames in the nycflights13 package. Variables making up a primary key are colored grey, and are connected to their corresponding foreign keys with arrows.
</figcaption>
</figure>
</div>
</div>
</div>
<p>You’ll notice a nice feature in the design of these keys: the primary and foreign keys almost always have the same names, which, as you’ll see shortly, will make your joining life much easier. It’s also worth noting the opposite relationship: almost every variable name used in multiple tables has the same meaning in each place. There’s only one exception: <code>year</code> means year of departure in <code>flights</code> and year of manufacturer in <code>planes</code>. This will become important when we start actually joining tables together.</p>
</section>
<section id="checking-primary-keys" class="level3" data-number="20.2.2">
<h3 data-number="20.2.2" class="anchored" data-anchor-id="checking-primary-keys"><span class="header-section-number">20.2.2</span> Checking primary keys</h3>
<p>Now that that we’ve identified the primary keys in each table, it’s good practice to verify that they do indeed uniquely identify each observation. One way to do that is to <code>count()</code> the primary keys and look for entries where <code>n</code> is greater than one. This reveals that <code>planes</code> and <code>weather</code> both look good:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb6"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a>planes <span class="sc">|></span> </span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">count</span>(tailnum) <span class="sc">|></span> </span>
<span id="cb6-3"><a href="#cb6-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(n <span class="sc">></span> <span class="dv">1</span>)</span>
<span id="cb6-4"><a href="#cb6-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 0 × 2</span></span>
<span id="cb6-5"><a href="#cb6-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 2 variables: tailnum <chr>, n <int></span></span>
<span id="cb6-6"><a href="#cb6-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb6-7"><a href="#cb6-7" aria-hidden="true" tabindex="-1"></a>weather <span class="sc">|></span> </span>
<span id="cb6-8"><a href="#cb6-8" aria-hidden="true" tabindex="-1"></a> <span class="fu">count</span>(time_hour, origin) <span class="sc">|></span> </span>
<span id="cb6-9"><a href="#cb6-9" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(n <span class="sc">></span> <span class="dv">1</span>)</span>
<span id="cb6-10"><a href="#cb6-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 0 × 3</span></span>
<span id="cb6-11"><a href="#cb6-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 3 variables: time_hour <dttm>, origin <chr>, n <int></span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>You should also check for missing values in your primary keys — if a value is missing then it can’t identify an observation!</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb7"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb7-1"><a href="#cb7-1" aria-hidden="true" tabindex="-1"></a>planes <span class="sc">|></span> </span>
<span id="cb7-2"><a href="#cb7-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(<span class="fu">is.na</span>(tailnum))</span>
<span id="cb7-3"><a href="#cb7-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 0 × 9</span></span>
<span id="cb7-4"><a href="#cb7-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 9 variables: tailnum <chr>, year <int>, type <chr>, manufacturer <chr>,</span></span>
<span id="cb7-5"><a href="#cb7-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> # model <chr>, engines <int>, seats <int>, speed <int>, engine <chr></span></span>
<span id="cb7-6"><a href="#cb7-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb7-7"><a href="#cb7-7" aria-hidden="true" tabindex="-1"></a>weather <span class="sc">|></span> </span>
<span id="cb7-8"><a href="#cb7-8" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(<span class="fu">is.na</span>(time_hour) <span class="sc">|</span> <span class="fu">is.na</span>(origin))</span>
<span id="cb7-9"><a href="#cb7-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 0 × 15</span></span>
<span id="cb7-10"><a href="#cb7-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 15 variables: origin <chr>, year <int>, month <int>, day <int>,</span></span>
<span id="cb7-11"><a href="#cb7-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> # hour <int>, temp <dbl>, dewp <dbl>, humid <dbl>, wind_dir <dbl>, …</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
</section>
<section id="surrogate-keys" class="level3" data-number="20.2.3">
<h3 data-number="20.2.3" class="anchored" data-anchor-id="surrogate-keys"><span class="header-section-number">20.2.3</span> Surrogate keys</h3>
<p>So far we haven’t talked about the primary key for <code>flights</code>. It’s not super important here, because there are no data frames that use it as a foreign key, but it’s still useful to consider because it’s easier to work with observations if we have some way to describe them to others.</p>
<p>After a little thinking and experimentation, we determined that there are three variables that together uniquely identify each flight:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb8"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" aria-hidden="true" tabindex="-1"></a>flights <span class="sc">|></span> </span>
<span id="cb8-2"><a href="#cb8-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">count</span>(time_hour, carrier, flight) <span class="sc">|></span> </span>
<span id="cb8-3"><a href="#cb8-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(n <span class="sc">></span> <span class="dv">1</span>)</span>
<span id="cb8-4"><a href="#cb8-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 0 × 4</span></span>
<span id="cb8-5"><a href="#cb8-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 4 variables: time_hour <dttm>, carrier <chr>, flight <int>, n <int></span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Does the absence of duplicates automatically make <code>time_hour</code>-<code>carrier</code>-<code>flight</code> a primary key? It’s certainly a good start, but it doesn’t guarantee it. For example, are altitude and latitude a good primary key for <code>airports</code>?</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb9"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a>airports <span class="sc">|></span></span>
<span id="cb9-2"><a href="#cb9-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">count</span>(alt, lat) <span class="sc">|></span> </span>
<span id="cb9-3"><a href="#cb9-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(n <span class="sc">></span> <span class="dv">1</span>)</span>
<span id="cb9-4"><a href="#cb9-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 1 × 3</span></span>
<span id="cb9-5"><a href="#cb9-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> alt lat n</span></span>
<span id="cb9-6"><a href="#cb9-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <dbl> <dbl> <int></span></span>
<span id="cb9-7"><a href="#cb9-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 13 40.6 2</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Identifying an airport by its altitude and latitude is clearly a bad idea, and in general it’s not possible to know from the data alone whether or not a combination of variables makes a good a primary key. But for flights, the combination of <code>time_hour</code>, <code>carrier</code>, and <code>flight</code> seems reasonable because it would be really confusing for an airline and its customers if there were multiple flights with the same flight number in the air at the same time.</p>
<p>That said, we might be better off introducing a simple numeric surrogate key using the row number:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb10"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="ot"><-</span> flights <span class="sc">|></span> </span>
<span id="cb10-2"><a href="#cb10-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">mutate</span>(<span class="at">id =</span> <span class="fu">row_number</span>(), <span class="at">.before =</span> <span class="dv">1</span>)</span>
<span id="cb10-3"><a href="#cb10-3" aria-hidden="true" tabindex="-1"></a>flights2</span>
<span id="cb10-4"><a href="#cb10-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 20</span></span>
<span id="cb10-5"><a href="#cb10-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> id year month day dep_time sched_dep_time dep_delay arr_time</span></span>
<span id="cb10-6"><a href="#cb10-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <int> <int> <int> <int> <int> <dbl> <int></span></span>
<span id="cb10-7"><a href="#cb10-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 1 2013 1 1 517 515 2 830</span></span>
<span id="cb10-8"><a href="#cb10-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2 2013 1 1 533 529 4 850</span></span>
<span id="cb10-9"><a href="#cb10-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 3 2013 1 1 542 540 2 923</span></span>
<span id="cb10-10"><a href="#cb10-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 4 2013 1 1 544 545 -1 1004</span></span>
<span id="cb10-11"><a href="#cb10-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 5 2013 1 1 554 600 -6 812</span></span>
<span id="cb10-12"><a href="#cb10-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 6 2013 1 1 554 558 -4 740</span></span>
<span id="cb10-13"><a href="#cb10-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span>
<span id="cb10-14"><a href="#cb10-14" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 12 more variables: sched_arr_time <int>, arr_delay <dbl>, …</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Surrogate keys can be particularly useful when communicating to other humans: it’s much easier to tell someone to take a look at flight 2001 than to say look at UA430 which departed 9am 2013-01-03.</p>
</section>
<section id="exercises" class="level3" data-number="20.2.4">
<h3 data-number="20.2.4" class="anchored" data-anchor-id="exercises"><span class="header-section-number">20.2.4</span> Exercises</h3>
<ol type="1">
<li><p>We forgot to draw the relationship between <code>weather</code> and <code>airports</code> in <a href="#fig-flights-relationships" class="quarto-xref">Figure <span>20.1</span></a>. What is the relationship and how should it appear in the diagram?</p></li>
<li><p><code>weather</code> only contains information for the three origin airports in NYC. If it contained weather records for all airports in the USA, what additional connection would it make to <code>flights</code>?</p></li>
<li><p>The <code>year</code>, <code>month</code>, <code>day</code>, <code>hour</code>, and <code>origin</code> variables almost form a compound key for <code>weather</code>, but there’s one hour that has duplicate observations. Can you figure out what’s special about that hour?</p></li>
<li><p>We know that some days of the year are special and fewer people than usual fly on them (e.g., Christmas eve and Christmas day). How might you represent that data as a data frame? What would be the primary key? How would it connect to the existing data frames?</p></li>
<li><p>Draw a diagram illustrating the connections between the <code>Batting</code>, <code>People</code>, and <code>Salaries</code> data frames in the Lahman package. Draw another diagram that shows the relationship between <code>People</code>, <code>Managers</code>, <code>AwardsManagers</code>. How would you characterize the relationship between the <code>Batting</code>, <code>Pitching</code>, and <code>Fielding</code> data frames?</p></li>
</ol>
</section>
</section>
<section id="sec-mutating-joins" class="level2" data-number="20.3">
<h2 data-number="20.3" class="anchored" data-anchor-id="sec-mutating-joins"><span class="header-section-number">20.3</span> Basic joins</h2>
<p>Now that you understand how data frames are connected via keys, we can start using joins to better understand the <code>flights</code> dataset. dplyr provides six join functions: <code>left_join()</code>, <code>inner_join()</code>, <code>right_join()</code>, <code>full_join()</code>, <code>semi_join()</code>, and <code>anti_join().</code> They all have the same interface: they take a pair of data frames (<code>x</code> and <code>y</code>) and return a data frame. The order of the rows and columns in the output is primarily determined by <code>x</code>.</p>
<p>In this section, you’ll learn how to use one mutating join, <code>left_join()</code>, and two filtering joins, <code>semi_join()</code> and <code>anti_join()</code>. In the next section, you’ll learn exactly how these functions work, and about the remaining <code>inner_join()</code>, <code>right_join()</code> and <code>full_join()</code>.</p>
<section id="mutating-joins" class="level3" data-number="20.3.1">
<h3 data-number="20.3.1" class="anchored" data-anchor-id="mutating-joins"><span class="header-section-number">20.3.1</span> Mutating joins</h3>
<p>A <strong>mutating join</strong> allows you to combine variables from two data frames: it first matches observations by their keys, then copies across variables from one data frame to the other. Like <code>mutate()</code>, the join functions add variables to the right, so if your dataset has many variables, you won’t see the new ones. For these examples, we’ll make it easier to see what’s going on by creating a narrower dataset with just six variables<a href="#fn1" class="footnote-ref" id="fnref1" role="doc-noteref"><sup>1</sup></a>:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb11"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb11-1"><a href="#cb11-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="ot"><-</span> flights <span class="sc">|></span> </span>
<span id="cb11-2"><a href="#cb11-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">select</span>(year, time_hour, origin, dest, tailnum, carrier)</span>
<span id="cb11-3"><a href="#cb11-3" aria-hidden="true" tabindex="-1"></a>flights2</span>
<span id="cb11-4"><a href="#cb11-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 6</span></span>
<span id="cb11-5"><a href="#cb11-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier</span></span>
<span id="cb11-6"><a href="#cb11-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> </span></span>
<span id="cb11-7"><a href="#cb11-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA </span></span>
<span id="cb11-8"><a href="#cb11-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA </span></span>
<span id="cb11-9"><a href="#cb11-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA </span></span>
<span id="cb11-10"><a href="#cb11-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 </span></span>
<span id="cb11-11"><a href="#cb11-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL </span></span>
<span id="cb11-12"><a href="#cb11-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA </span></span>
<span id="cb11-13"><a href="#cb11-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>There are four types of mutating join, but there’s one that you’ll use almost all of the time: <code>left_join()</code>. It’s special because the output will always have the same rows as <code>x</code>, the data frame you’re joining to<a href="#fn2" class="footnote-ref" id="fnref2" role="doc-noteref"><sup>2</sup></a>. The primary use of <code>left_join()</code> is to add in additional metadata. For example, we can use <code>left_join()</code> to add the full airline name to the <code>flights2</code> data:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb12"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span></span>
<span id="cb12-2"><a href="#cb12-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(airlines)</span>
<span id="cb12-3"><a href="#cb12-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> Joining with `by = join_by(carrier)`</span></span>
<span id="cb12-4"><a href="#cb12-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 7</span></span>
<span id="cb12-5"><a href="#cb12-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier name </span></span>
<span id="cb12-6"><a href="#cb12-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <chr> </span></span>
<span id="cb12-7"><a href="#cb12-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA United Air Lines In…</span></span>
<span id="cb12-8"><a href="#cb12-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA United Air Lines In…</span></span>
<span id="cb12-9"><a href="#cb12-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA American Airlines I…</span></span>
<span id="cb12-10"><a href="#cb12-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 JetBlue Airways </span></span>
<span id="cb12-11"><a href="#cb12-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL Delta Air Lines Inc.</span></span>
<span id="cb12-12"><a href="#cb12-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA United Air Lines In…</span></span>
<span id="cb12-13"><a href="#cb12-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Or we could find out the temperature and wind speed when each plane departed:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb13"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb13-1"><a href="#cb13-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb13-2"><a href="#cb13-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(weather <span class="sc">|></span> <span class="fu">select</span>(origin, time_hour, temp, wind_speed))</span>
<span id="cb13-3"><a href="#cb13-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> Joining with `by = join_by(time_hour, origin)`</span></span>
<span id="cb13-4"><a href="#cb13-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 8</span></span>
<span id="cb13-5"><a href="#cb13-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier temp wind_speed</span></span>
<span id="cb13-6"><a href="#cb13-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <dbl> <dbl></span></span>
<span id="cb13-7"><a href="#cb13-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA 39.0 12.7</span></span>
<span id="cb13-8"><a href="#cb13-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA 39.9 15.0</span></span>
<span id="cb13-9"><a href="#cb13-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA 39.0 15.0</span></span>
<span id="cb13-10"><a href="#cb13-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 39.0 15.0</span></span>
<span id="cb13-11"><a href="#cb13-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL 39.9 16.1</span></span>
<span id="cb13-12"><a href="#cb13-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA 39.0 12.7</span></span>
<span id="cb13-13"><a href="#cb13-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Or what size of plane was flying:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb14"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb14-1"><a href="#cb14-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb14-2"><a href="#cb14-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(planes <span class="sc">|></span> <span class="fu">select</span>(tailnum, type, engines, seats))</span>
<span id="cb14-3"><a href="#cb14-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> Joining with `by = join_by(tailnum)`</span></span>
<span id="cb14-4"><a href="#cb14-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 9</span></span>
<span id="cb14-5"><a href="#cb14-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier type </span></span>
<span id="cb14-6"><a href="#cb14-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <chr> </span></span>
<span id="cb14-7"><a href="#cb14-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA Fixed wing multi en…</span></span>
<span id="cb14-8"><a href="#cb14-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA Fixed wing multi en…</span></span>
<span id="cb14-9"><a href="#cb14-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA Fixed wing multi en…</span></span>
<span id="cb14-10"><a href="#cb14-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 Fixed wing multi en…</span></span>
<span id="cb14-11"><a href="#cb14-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL Fixed wing multi en…</span></span>
<span id="cb14-12"><a href="#cb14-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA Fixed wing multi en…</span></span>
<span id="cb14-13"><a href="#cb14-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span>
<span id="cb14-14"><a href="#cb14-14" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 2 more variables: engines <int>, seats <int></span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>When <code>left_join()</code> fails to find a match for a row in <code>x</code>, it fills in the new variables with missing values. For example, there’s no information about the plane with tail number <code>N3ALAA</code> so the <code>type</code>, <code>engines</code>, and <code>seats</code> will be missing:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb15"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb15-1"><a href="#cb15-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb15-2"><a href="#cb15-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">filter</span>(tailnum <span class="sc">==</span> <span class="st">"N3ALAA"</span>) <span class="sc">|></span> </span>
<span id="cb15-3"><a href="#cb15-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(planes <span class="sc">|></span> <span class="fu">select</span>(tailnum, type, engines, seats))</span>
<span id="cb15-4"><a href="#cb15-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> Joining with `by = join_by(tailnum)`</span></span>
<span id="cb15-5"><a href="#cb15-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 63 × 9</span></span>
<span id="cb15-6"><a href="#cb15-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier type engines seats</span></span>
<span id="cb15-7"><a href="#cb15-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <chr> <int> <int></span></span>
<span id="cb15-8"><a href="#cb15-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 06:00:00 LGA ORD N3ALAA AA <NA> NA NA</span></span>
<span id="cb15-9"><a href="#cb15-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-02 18:00:00 LGA ORD N3ALAA AA <NA> NA NA</span></span>
<span id="cb15-10"><a href="#cb15-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-03 06:00:00 LGA ORD N3ALAA AA <NA> NA NA</span></span>
<span id="cb15-11"><a href="#cb15-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-07 19:00:00 LGA ORD N3ALAA AA <NA> NA NA</span></span>
<span id="cb15-12"><a href="#cb15-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-08 17:00:00 JFK ORD N3ALAA AA <NA> NA NA</span></span>
<span id="cb15-13"><a href="#cb15-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-16 06:00:00 LGA ORD N3ALAA AA <NA> NA NA</span></span>
<span id="cb15-14"><a href="#cb15-14" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 57 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>We’ll come back to this problem a few times in the rest of the chapter.</p>
</section>
<section id="specifying-join-keys" class="level3" data-number="20.3.2">
<h3 data-number="20.3.2" class="anchored" data-anchor-id="specifying-join-keys"><span class="header-section-number">20.3.2</span> Specifying join keys</h3>
<p>By default, <code>left_join()</code> will use all variables that appear in both data frames as the join key, the so called <strong>natural</strong> join. This is a useful heuristic, but it doesn’t always work. For example, what happens if we try to join <code>flights2</code> with the complete <code>planes</code> dataset?</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb16"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb16-1"><a href="#cb16-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb16-2"><a href="#cb16-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(planes)</span>
<span id="cb16-3"><a href="#cb16-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> Joining with `by = join_by(year, tailnum)`</span></span>
<span id="cb16-4"><a href="#cb16-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 13</span></span>
<span id="cb16-5"><a href="#cb16-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier type manufacturer</span></span>
<span id="cb16-6"><a href="#cb16-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <chr> <chr> </span></span>
<span id="cb16-7"><a href="#cb16-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA <NA> <NA> </span></span>
<span id="cb16-8"><a href="#cb16-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA <NA> <NA> </span></span>
<span id="cb16-9"><a href="#cb16-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA <NA> <NA> </span></span>
<span id="cb16-10"><a href="#cb16-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 <NA> <NA> </span></span>
<span id="cb16-11"><a href="#cb16-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL <NA> <NA> </span></span>
<span id="cb16-12"><a href="#cb16-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA <NA> <NA> </span></span>
<span id="cb16-13"><a href="#cb16-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span>
<span id="cb16-14"><a href="#cb16-14" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 5 more variables: model <chr>, engines <int>, seats <int>, …</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>We get a lot of missing matches because our join is trying to use <code>tailnum</code> and <code>year</code> as a compound key. Both <code>flights</code> and <code>planes</code> have a <code>year</code> column but they mean different things: <code>flights$year</code> is the year the flight occurred and <code>planes$year</code> is the year the plane was built. We only want to join on <code>tailnum</code> so we need to provide an explicit specification with <code>join_by()</code>:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb17"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb17-1"><a href="#cb17-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb17-2"><a href="#cb17-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(planes, <span class="fu">join_by</span>(tailnum))</span>
<span id="cb17-3"><a href="#cb17-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 14</span></span>
<span id="cb17-4"><a href="#cb17-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> year.x time_hour origin dest tailnum carrier year.y</span></span>
<span id="cb17-5"><a href="#cb17-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <int></span></span>
<span id="cb17-6"><a href="#cb17-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA 1999</span></span>
<span id="cb17-7"><a href="#cb17-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA 1998</span></span>
<span id="cb17-8"><a href="#cb17-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA 1990</span></span>
<span id="cb17-9"><a href="#cb17-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 2012</span></span>
<span id="cb17-10"><a href="#cb17-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL 1991</span></span>
<span id="cb17-11"><a href="#cb17-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA 2012</span></span>
<span id="cb17-12"><a href="#cb17-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span>
<span id="cb17-13"><a href="#cb17-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 7 more variables: type <chr>, manufacturer <chr>, model <chr>, …</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Note that the <code>year</code> variables are disambiguated in the output with a suffix (<code>year.x</code> and <code>year.y</code>), which tells you whether the variable came from the <code>x</code> or <code>y</code> argument. You can override the default suffixes with the <code>suffix</code> argument.</p>
<p><code>join_by(tailnum)</code> is short for <code>join_by(tailnum == tailnum)</code>. It’s important to know about this fuller form for two reasons. Firstly, it describes the relationship between the two tables: the keys must be equal. That’s why this type of join is often called an <strong>equi join</strong>. You’ll learn about non-equi joins in <a href="#sec-non-equi-joins" class="quarto-xref"><span>Section 20.5</span></a>.</p>
<p>Secondly, it’s how you specify different join keys in each table. For example, there are two ways to join the <code>flight2</code> and <code>airports</code> table: either by <code>dest</code> or <code>origin</code>:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb18"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb18-1"><a href="#cb18-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb18-2"><a href="#cb18-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(airports, <span class="fu">join_by</span>(dest <span class="sc">==</span> faa))</span>
<span id="cb18-3"><a href="#cb18-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 13</span></span>
<span id="cb18-4"><a href="#cb18-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier name </span></span>
<span id="cb18-5"><a href="#cb18-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <chr> </span></span>
<span id="cb18-6"><a href="#cb18-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA George Bush Interco…</span></span>
<span id="cb18-7"><a href="#cb18-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA George Bush Interco…</span></span>
<span id="cb18-8"><a href="#cb18-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA Miami Intl </span></span>
<span id="cb18-9"><a href="#cb18-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 <NA> </span></span>
<span id="cb18-10"><a href="#cb18-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL Hartsfield Jackson …</span></span>
<span id="cb18-11"><a href="#cb18-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA Chicago Ohare Intl </span></span>
<span id="cb18-12"><a href="#cb18-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span>
<span id="cb18-13"><a href="#cb18-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 6 more variables: lat <dbl>, lon <dbl>, alt <dbl>, tz <dbl>, …</span></span>
<span id="cb18-14"><a href="#cb18-14" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb18-15"><a href="#cb18-15" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb18-16"><a href="#cb18-16" aria-hidden="true" tabindex="-1"></a> <span class="fu">left_join</span>(airports, <span class="fu">join_by</span>(origin <span class="sc">==</span> faa))</span>
<span id="cb18-17"><a href="#cb18-17" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 336,776 × 13</span></span>
<span id="cb18-18"><a href="#cb18-18" aria-hidden="true" tabindex="-1"></a><span class="co">#> year time_hour origin dest tailnum carrier name </span></span>
<span id="cb18-19"><a href="#cb18-19" aria-hidden="true" tabindex="-1"></a><span class="co">#> <int> <dttm> <chr> <chr> <chr> <chr> <chr> </span></span>
<span id="cb18-20"><a href="#cb18-20" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 2013 2013-01-01 05:00:00 EWR IAH N14228 UA Newark Liberty Intl</span></span>
<span id="cb18-21"><a href="#cb18-21" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 2013 2013-01-01 05:00:00 LGA IAH N24211 UA La Guardia </span></span>
<span id="cb18-22"><a href="#cb18-22" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 2013 2013-01-01 05:00:00 JFK MIA N619AA AA John F Kennedy Intl</span></span>
<span id="cb18-23"><a href="#cb18-23" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 2013 2013-01-01 05:00:00 JFK BQN N804JB B6 John F Kennedy Intl</span></span>
<span id="cb18-24"><a href="#cb18-24" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 2013 2013-01-01 06:00:00 LGA ATL N668DN DL La Guardia </span></span>
<span id="cb18-25"><a href="#cb18-25" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 2013 2013-01-01 05:00:00 EWR ORD N39463 UA Newark Liberty Intl</span></span>
<span id="cb18-26"><a href="#cb18-26" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 336,770 more rows</span></span>
<span id="cb18-27"><a href="#cb18-27" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 6 more variables: lat <dbl>, lon <dbl>, alt <dbl>, tz <dbl>, …</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>In older code you might see a different way of specifying the join keys, using a character vector:</p>
<ul>
<li><code>by = "x"</code> corresponds to <code>join_by(x)</code>.</li>
<li><code>by = c("a" = "x")</code> corresponds to <code>join_by(a == x)</code>.</li>
</ul>
<p>Now that it exists, we prefer <code>join_by()</code> since it provides a clearer and more flexible specification.</p>
<p><code>inner_join()</code>, <code>right_join()</code>, <code>full_join()</code> have the same interface as <code>left_join()</code>. The difference is which rows they keep: left join keeps all the rows in <code>x</code>, the right join keeps all rows in <code>y</code>, the full join keeps all rows in either <code>x</code> or <code>y</code>, and the inner join only keeps rows that occur in both <code>x</code> and <code>y</code>. We’ll come back to these in more detail later.</p>
</section>
<section id="filtering-joins" class="level3" data-number="20.3.3">
<h3 data-number="20.3.3" class="anchored" data-anchor-id="filtering-joins"><span class="header-section-number">20.3.3</span> Filtering joins</h3>
<p>As you might guess the primary action of a <strong>filtering join</strong> is to filter the rows. There are two types: semi-joins and anti-joins. <strong>Semi-joins</strong> keep all rows in <code>x</code> that have a match in <code>y</code>. For example, we could use a semi-join to filter the <code>airports</code> dataset to show just the origin airports:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb19"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb19-1"><a href="#cb19-1" aria-hidden="true" tabindex="-1"></a>airports <span class="sc">|></span> </span>
<span id="cb19-2"><a href="#cb19-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">semi_join</span>(flights2, <span class="fu">join_by</span>(faa <span class="sc">==</span> origin))</span>
<span id="cb19-3"><a href="#cb19-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 3 × 8</span></span>
<span id="cb19-4"><a href="#cb19-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> faa name lat lon alt tz dst tzone </span></span>
<span id="cb19-5"><a href="#cb19-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <chr> <chr> </span></span>
<span id="cb19-6"><a href="#cb19-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 EWR Newark Liberty Intl 40.7 -74.2 18 -5 A America/New_York</span></span>
<span id="cb19-7"><a href="#cb19-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 JFK John F Kennedy Intl 40.6 -73.8 13 -5 A America/New_York</span></span>
<span id="cb19-8"><a href="#cb19-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 LGA La Guardia 40.8 -73.9 22 -5 A America/New_York</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Or just the destinations:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb20"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb20-1"><a href="#cb20-1" aria-hidden="true" tabindex="-1"></a>airports <span class="sc">|></span> </span>
<span id="cb20-2"><a href="#cb20-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">semi_join</span>(flights2, <span class="fu">join_by</span>(faa <span class="sc">==</span> dest))</span>
<span id="cb20-3"><a href="#cb20-3" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 101 × 8</span></span>
<span id="cb20-4"><a href="#cb20-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> faa name lat lon alt tz dst tzone </span></span>
<span id="cb20-5"><a href="#cb20-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <chr> <chr> </span></span>
<span id="cb20-6"><a href="#cb20-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 ABQ Albuquerque Internati… 35.0 -107. 5355 -7 A America/Denver </span></span>
<span id="cb20-7"><a href="#cb20-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 ACK Nantucket Mem 41.3 -70.1 48 -5 A America/New_Yo…</span></span>
<span id="cb20-8"><a href="#cb20-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 ALB Albany Intl 42.7 -73.8 285 -5 A America/New_Yo…</span></span>
<span id="cb20-9"><a href="#cb20-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 ANC Ted Stevens Anchorage… 61.2 -150. 152 -9 A America/Anchor…</span></span>
<span id="cb20-10"><a href="#cb20-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 ATL Hartsfield Jackson At… 33.6 -84.4 1026 -5 A America/New_Yo…</span></span>
<span id="cb20-11"><a href="#cb20-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 AUS Austin Bergstrom Intl 30.2 -97.7 542 -6 A America/Chicago</span></span>
<span id="cb20-12"><a href="#cb20-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 95 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p><strong>Anti-joins</strong> are the opposite: they return all rows in <code>x</code> that don’t have a match in <code>y</code>. They’re useful for finding missing values that are <strong>implicit</strong> in the data, the topic of <a href="missing-values.html#sec-missing-implicit" class="quarto-xref"><span>Section 19.3</span></a>. Implicitly missing values don’t show up as <code>NA</code>s but instead only exist as an absence. For example, we can find rows that are missing from <code>airports</code> by looking for flights that don’t have a matching destination airport:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb21"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb21-1"><a href="#cb21-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span> </span>
<span id="cb21-2"><a href="#cb21-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">anti_join</span>(airports, <span class="fu">join_by</span>(dest <span class="sc">==</span> faa)) <span class="sc">|></span> </span>
<span id="cb21-3"><a href="#cb21-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">distinct</span>(dest)</span>
<span id="cb21-4"><a href="#cb21-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 4 × 1</span></span>
<span id="cb21-5"><a href="#cb21-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> dest </span></span>
<span id="cb21-6"><a href="#cb21-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr></span></span>
<span id="cb21-7"><a href="#cb21-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 BQN </span></span>
<span id="cb21-8"><a href="#cb21-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 SJU </span></span>
<span id="cb21-9"><a href="#cb21-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 STT </span></span>
<span id="cb21-10"><a href="#cb21-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 PSE</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Or we can find which <code>tailnum</code>s are missing from <code>planes</code>:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb22"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb22-1"><a href="#cb22-1" aria-hidden="true" tabindex="-1"></a>flights2 <span class="sc">|></span></span>
<span id="cb22-2"><a href="#cb22-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">anti_join</span>(planes, <span class="fu">join_by</span>(tailnum)) <span class="sc">|></span> </span>
<span id="cb22-3"><a href="#cb22-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">distinct</span>(tailnum)</span>
<span id="cb22-4"><a href="#cb22-4" aria-hidden="true" tabindex="-1"></a><span class="co">#> # A tibble: 722 × 1</span></span>
<span id="cb22-5"><a href="#cb22-5" aria-hidden="true" tabindex="-1"></a><span class="co">#> tailnum</span></span>
<span id="cb22-6"><a href="#cb22-6" aria-hidden="true" tabindex="-1"></a><span class="co">#> <chr> </span></span>
<span id="cb22-7"><a href="#cb22-7" aria-hidden="true" tabindex="-1"></a><span class="co">#> 1 N3ALAA </span></span>
<span id="cb22-8"><a href="#cb22-8" aria-hidden="true" tabindex="-1"></a><span class="co">#> 2 N3DUAA </span></span>
<span id="cb22-9"><a href="#cb22-9" aria-hidden="true" tabindex="-1"></a><span class="co">#> 3 N542MQ </span></span>
<span id="cb22-10"><a href="#cb22-10" aria-hidden="true" tabindex="-1"></a><span class="co">#> 4 N730MQ </span></span>
<span id="cb22-11"><a href="#cb22-11" aria-hidden="true" tabindex="-1"></a><span class="co">#> 5 N9EAMQ </span></span>
<span id="cb22-12"><a href="#cb22-12" aria-hidden="true" tabindex="-1"></a><span class="co">#> 6 N532UA </span></span>
<span id="cb22-13"><a href="#cb22-13" aria-hidden="true" tabindex="-1"></a><span class="co">#> # ℹ 716 more rows</span></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
</section>
<section id="exercises-1" class="level3" data-number="20.3.4">
<h3 data-number="20.3.4" class="anchored" data-anchor-id="exercises-1"><span class="header-section-number">20.3.4</span> Exercises</h3>
<ol type="1">
<li><p>Find the 48 hours (over the course of the whole year) that have the worst delays. Cross-reference it with the <code>weather</code> data. Can you see any patterns?</p></li>
<li><p>Imagine you’ve found the top 10 most popular destinations using this code:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb23"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb23-1"><a href="#cb23-1" aria-hidden="true" tabindex="-1"></a>top_dest <span class="ot"><-</span> flights2 <span class="sc">|></span></span>
<span id="cb23-2"><a href="#cb23-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">count</span>(dest, <span class="at">sort =</span> <span class="cn">TRUE</span>) <span class="sc">|></span></span>
<span id="cb23-3"><a href="#cb23-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">head</span>(<span class="dv">10</span>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>How can you find all flights to those destinations?</p></li>
<li><p>Does every departing flight have corresponding weather data for that hour?</p></li>
<li><p>What do the tail numbers that don’t have a matching record in <code>planes</code> have in common? (Hint: one variable explains ~90% of the problems.)</p></li>
<li><p>Add a column to <code>planes</code> that lists every <code>carrier</code> that has flown that plane. You might expect that there’s an implicit relationship between plane and airline, because each plane is flown by a single airline. Confirm or reject this hypothesis using the tools you’ve learned in previous chapters.</p></li>
<li><p>Add the latitude and the longitude of the origin <em>and</em> destination airport to <code>flights</code>. Is it easier to rename the columns before or after the join?</p></li>
<li><p>Compute the average delay by destination, then join on the <code>airports</code> data frame so you can show the spatial distribution of delays. Here’s an easy way to draw a map of the United States:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb24"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb24-1"><a href="#cb24-1" aria-hidden="true" tabindex="-1"></a>airports <span class="sc">|></span></span>
<span id="cb24-2"><a href="#cb24-2" aria-hidden="true" tabindex="-1"></a> <span class="fu">semi_join</span>(flights, <span class="fu">join_by</span>(faa <span class="sc">==</span> dest)) <span class="sc">|></span></span>
<span id="cb24-3"><a href="#cb24-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">ggplot</span>(<span class="fu">aes</span>(<span class="at">x =</span> lon, <span class="at">y =</span> lat)) <span class="sc">+</span></span>
<span id="cb24-4"><a href="#cb24-4" aria-hidden="true" tabindex="-1"></a> <span class="fu">borders</span>(<span class="st">"state"</span>) <span class="sc">+</span></span>
<span id="cb24-5"><a href="#cb24-5" aria-hidden="true" tabindex="-1"></a> <span class="fu">geom_point</span>() <span class="sc">+</span></span>
<span id="cb24-6"><a href="#cb24-6" aria-hidden="true" tabindex="-1"></a> <span class="fu">coord_quickmap</span>()</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>You might want to use the <code>size</code> or <code>color</code> of the points to display the average delay for each airport.</p></li>
<li><p>What happened on June 13 2013? Draw a map of the delays, and then use Google to cross-reference with the weather.</p></li>
</ol>
</section>
</section>
<section id="how-do-joins-work" class="level2" data-number="20.4">
<h2 data-number="20.4" class="anchored" data-anchor-id="how-do-joins-work"><span class="header-section-number">20.4</span> How do joins work?</h2>
<p>Now that you’ve used joins a few times it’s time to learn more about how they work, focusing on how each row in <code>x</code> matches rows in <code>y</code>. We’ll begin by introducing a visual representation of joins, using the simple tibbles defined below and shown in <a href="#fig-join-setup" class="quarto-xref">Figure <span>20.2</span></a>. In these examples we’ll use a single key called <code>key</code> and a single value column (<code>val_x</code> and <code>val_y</code>), but the ideas all generalize to multiple keys and multiple values.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb25"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb25-1"><a href="#cb25-1" aria-hidden="true" tabindex="-1"></a>x <span class="ot"><-</span> <span class="fu">tribble</span>(</span>
<span id="cb25-2"><a href="#cb25-2" aria-hidden="true" tabindex="-1"></a> <span class="sc">~</span>key, <span class="sc">~</span>val_x,</span>
<span id="cb25-3"><a href="#cb25-3" aria-hidden="true" tabindex="-1"></a> <span class="dv">1</span>, <span class="st">"x1"</span>,</span>
<span id="cb25-4"><a href="#cb25-4" aria-hidden="true" tabindex="-1"></a> <span class="dv">2</span>, <span class="st">"x2"</span>,</span>
<span id="cb25-5"><a href="#cb25-5" aria-hidden="true" tabindex="-1"></a> <span class="dv">3</span>, <span class="st">"x3"</span></span>
<span id="cb25-6"><a href="#cb25-6" aria-hidden="true" tabindex="-1"></a>)</span>
<span id="cb25-7"><a href="#cb25-7" aria-hidden="true" tabindex="-1"></a>y <span class="ot"><-</span> <span class="fu">tribble</span>(</span>
<span id="cb25-8"><a href="#cb25-8" aria-hidden="true" tabindex="-1"></a> <span class="sc">~</span>key, <span class="sc">~</span>val_y,</span>
<span id="cb25-9"><a href="#cb25-9" aria-hidden="true" tabindex="-1"></a> <span class="dv">1</span>, <span class="st">"y1"</span>,</span>
<span id="cb25-10"><a href="#cb25-10" aria-hidden="true" tabindex="-1"></a> <span class="dv">2</span>, <span class="st">"y2"</span>,</span>
<span id="cb25-11"><a href="#cb25-11" aria-hidden="true" tabindex="-1"></a> <span class="dv">4</span>, <span class="st">"y3"</span></span>
<span id="cb25-12"><a href="#cb25-12" aria-hidden="true" tabindex="-1"></a>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<div class="cell">
<div class="cell-output-display">
<div id="fig-join-setup" class="quarto-float quarto-figure quarto-figure-center anchored" alt="x and y are two data frames with 2 columns and 3 rows, with contents as described in the text. The values of the keys are colored: 1 is green, 2 is purple, 3 is orange, and 4 is yellow.">
<figure class="quarto-float quarto-float-fig figure">
<div aria-describedby="fig-join-setup-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
<img src="diagrams/join/setup.png" class="img-fluid figure-img" alt="x and y are two data frames with 2 columns and 3 rows, with contents as described in the text. The values of the keys are colored: 1 is green, 2 is purple, 3 is orange, and 4 is yellow." width="160">
</div>
<figcaption class="quarto-float-caption-bottom quarto-float-caption quarto-float-fig" id="fig-join-setup-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
Figure 20.2: Graphical representation of two simple tables. The colored <code>key</code> columns map background color to key value. The grey columns represent the “value” columns that are carried along for the ride.
</figcaption>
</figure>
</div>
</div>
</div>
<p><a href="#fig-join-setup2" class="quarto-xref">Figure <span>20.3</span></a> introduces the foundation for our visual representation. It shows all potential matches between <code>x</code> and <code>y</code> as the intersection between lines drawn from each row of <code>x</code> and each row of <code>y</code>. The rows and columns in the output are primarily determined by <code>x</code>, so the <code>x</code> table is horizontal and lines up with the output.</p>
<div class="cell">
<div class="cell-output-display">
<div id="fig-join-setup2" class="quarto-float quarto-figure quarto-figure-center anchored" alt="x and y are placed at right-angles, with horizonal lines extending from x and vertical lines extending from y. There are 3 rows in x and 3 rows in y, which leads to nine intersections representing nine potential matches.">
<figure class="quarto-float quarto-float-fig figure">
<div aria-describedby="fig-join-setup2-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
<img src="diagrams/join/setup2.png" class="img-fluid figure-img" alt="x and y are placed at right-angles, with horizonal lines extending from x and vertical lines extending from y. There are 3 rows in x and 3 rows in y, which leads to nine intersections representing nine potential matches." width="170">
</div>
<figcaption class="quarto-float-caption-bottom quarto-float-caption quarto-float-fig" id="fig-join-setup2-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
Figure 20.3: To understand how joins work, it’s useful to think of every possible match. Here we show that with a grid of connecting lines.
</figcaption>
</figure>
</div>
</div>
</div>
<p>To describe a specific type of join, we indicate matches with dots. The matches determine the rows in the output, a new data frame that contains the key, the x values, and the y values. For example, <a href="#fig-join-inner" class="quarto-xref">Figure <span>20.4</span></a> shows an inner join, where rows are retained if and only if the keys are equal.</p>
<div class="cell">
<div class="cell-output-display">
<div id="fig-join-inner" class="quarto-float quarto-figure quarto-figure-center anchored" alt="x and y are placed at right-angles with lines forming a grid of potential matches. Keys 1 and 2 appear in both x and y, so we get a match, indicated by a dot. Each dot corresponds to a row in the output, so the resulting joined data frame has two rows.">
<figure class="quarto-float quarto-float-fig figure">
<div aria-describedby="fig-join-inner-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
<img src="diagrams/join/inner.png" class="img-fluid figure-img" alt="x and y are placed at right-angles with lines forming a grid of potential matches. Keys 1 and 2 appear in both x and y, so we get a match, indicated by a dot. Each dot corresponds to a row in the output, so the resulting joined data frame has two rows." width="363">
</div>
<figcaption class="quarto-float-caption-bottom quarto-float-caption quarto-float-fig" id="fig-join-inner-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
Figure 20.4: An inner join matches each row in <code>x</code> to the row in <code>y</code> that has the same value of <code>key</code>. Each match becomes a row in the output.
</figcaption>
</figure>
</div>
</div>
</div>
<p>We can apply the same principles to explain the <strong>outer joins</strong>, which keep observations that appear in at least one of the data frames. These joins work by adding an additional “virtual” observation to each data frame. This observation has a key that matches if no other key matches, and values filled with <code>NA</code>. There are three types of outer joins:</p>
<ul>
<li><p>A <strong>left join</strong> keeps all observations in <code>x</code>, <a href="#fig-join-left" class="quarto-xref">Figure <span>20.5</span></a>. Every row of <code>x</code> is preserved in the output because it can fall back to matching a row of <code>NA</code>s in <code>y</code>.</p>
<div class="cell">
<div class="cell-output-display">
<div id="fig-join-left" class="quarto-float quarto-figure quarto-figure-center anchored" alt="Compared to the previous diagram showing an inner join, the y table gets a new virtual row containin NA that will match any row in x that didn't otherwise match. This means that the output now has three rows. For key = 3, which matches this virtual row, val_y takes value NA.">
<figure class="quarto-float quarto-float-fig figure">
<div aria-describedby="fig-join-left-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
<img src="diagrams/join/left.png" class="img-fluid figure-img" alt="Compared to the previous diagram showing an inner join, the y table gets a new virtual row containin NA that will match any row in x that didn't otherwise match. This means that the output now has three rows. For key = 3, which matches this virtual row, val_y takes value NA." width="385">
</div>
<figcaption class="quarto-float-caption-bottom quarto-float-caption quarto-float-fig" id="fig-join-left-caption-0ceaefa1-69ba-4598-a22c-09a6ac19f8ca">
Figure 20.5: A visual representation of the left join where every row in <code>x</code> appears in the output.
</figcaption>
</figure>
</div>
</div>
</div></li>