Relational data¶

Introduction¶

It's rare that a data analysis involves only a single table of data. Typically you have many tables of data, and you must combine them to answer the questions that you're interested in. Collectively, multiple tables of data are called relational data because it is the relations, not just the individual datasets, that are important.

Relations are always defined between a pair of tables. All other relations are built up from this simple idea: the relations of three or more tables are always a property of the relations between each pair. Sometimes both elements of a pair can be the same table! This is needed if, for example, you have a table of people, and each person has a reference to their parents.

To work with relational data you need verbs that work with pairs of tables. There are three families of verbs designed to work with relational data:

Mutating joins, which add new variables to one data frame from matching observations in another.
Filtering joins, which filter observations from one data frame based on whether or not they match an observation in the other table.
Set operations, which treat observations as if they were set elements.

The most common place to find relational data is in a relational database management system (or RDBMS), a term that encompasses almost all modern databases. If you've used a database before, you've almost certainly used SQL. If so, you should find the concepts in this chapter familiar, although their expression in dplyr is a little different. Generally, dplyr is a little easier to use than SQL because dplyr is specialised to do data analysis: it makes common data analysis operations easier, at the expense of making it more difficult to do other things that aren't commonly needed for data analysis.

Prerequisites¶

We will explore relational data from nycflights13 using the two-table verbs from dplyr.

In [1]:

            
                Copied!
                
install.packages("nycflights13")
library(tidyverse)
library(nycflights13)
install.packages("nycflights13")
library(tidyverse)
library(nycflights13)

Installing package into ‘/usr/local/lib/R/site-library’
(as ‘lib’ is unspecified)

Warning message in system("timedatectl", intern = TRUE):
“running command 'timedatectl' had status 1”
── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──

✔ ggplot2 3.3.6     ✔ purrr   0.3.4
✔ tibble  3.1.7     ✔ dplyr   1.0.9
✔ tidyr   1.2.0     ✔ stringr 1.4.0
✔ readr   2.1.2     ✔ forcats 0.5.1

── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

nycflights13¶

We will use the nycflights13 package to learn about relational data. nycflights13 contains five tibbles : airlines, airports, weather and planes which are all related to the flights table that you used in [data transformation]:

airlines lets you look up the full carrier name from its abbreviated code:

In [2]:

            
                Copied!
                
airlines
airlines

A tibble: 16 × 2
carrier	name
<chr>	<chr>
9E	Endeavor Air Inc.
AA	American Airlines Inc.
AS	Alaska Airlines Inc.
B6	JetBlue Airways
DL	Delta Air Lines Inc.
EV	ExpressJet Airlines Inc.
F9	Frontier Airlines Inc.
FL	AirTran Airways Corporation
HA	Hawaiian Airlines Inc.
MQ	Envoy Air
OO	SkyWest Airlines Inc.
UA	United Air Lines Inc.
US	US Airways Inc.
VX	Virgin America
WN	Southwest Airlines Co.
YV	Mesa Airlines Inc.

airports gives information about each airport, identified by the faa airport code:

In [3]:

            
                Copied!
                
airports
airports

A tibble: 1458 × 8
faa	name	lat	lon	alt	tz	dst	tzone
<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<chr>	<chr>
04G	Lansdowne Airport	41.13047	-80.61958	1044	-5	A	America/New_York
06A	Moton Field Municipal Airport	32.46057	-85.68003	264	-6	A	America/Chicago
06C	Schaumburg Regional	41.98934	-88.10124	801	-6	A	America/Chicago
06N	Randall Airport	41.43191	-74.39156	523	-5	A	America/New_York
09J	Jekyll Island Airport	31.07447	-81.42778	11	-5	A	America/New_York
0A9	Elizabethton Municipal Airport	36.37122	-82.17342	1593	-5	A	America/New_York
0G6	Williams County Airport	41.46731	-84.50678	730	-5	A	America/New_York
0G7	Finger Lakes Regional Airport	42.88356	-76.78123	492	-5	A	America/New_York
0P2	Shoestring Aviation Airfield	39.79482	-76.64719	1000	-5	U	America/New_York
0S9	Jefferson County Intl	48.05381	-122.81064	108	-8	A	America/Los_Angeles
0W3	Harford County Airport	39.56684	-76.20240	409	-5	A	America/New_York
10C	Galt Field Airport	42.40289	-88.37511	875	-6	U	America/Chicago
17G	Port Bucyrus-Crawford County Airport	40.78156	-82.97481	1003	-5	A	America/New_York
19A	Jackson County Airport	34.17586	-83.56160	951	-5	U	America/New_York
1A3	Martin Campbell Field Airport	35.01581	-84.34683	1789	-5	A	America/New_York
1B9	Mansfield Municipal	42.00013	-71.19677	122	-5	A	America/New_York
1C9	Frazier Lake Airpark	54.01333	-124.76833	152	-8	A	America/Vancouver
1CS	Clow International Airport	41.69597	-88.12923	670	-6	U	America/Chicago
1G3	Kent State Airport	41.15139	-81.41511	1134	-5	A	America/New_York
1G4	Grand Canyon West Airport	35.89990	-113.81567	4813	-7	A	America/Phoenix
1H2	Effingham Memorial Airport	39.07000	-88.53400	585	-6	A	America/Chicago
1OH	Fortman Airport	40.55533	-84.38662	885	-5	U	America/New_York
1RL	Point Roberts Airpark	48.97972	-123.07889	10	-8	A	America/Los_Angeles
23M	Clarke CO	32.05170	-88.44340	320	-6	A	America/Chicago
24C	Lowell City Airport	42.95392	-85.34391	681	-5	A	America/New_York
24J	Suwannee County Airport	30.30013	-83.02469	104	-5	A	America/New_York
25D	Forest Lake Airport	45.24775	-92.99439	925	-6	A	America/Chicago
29D	Grove City Airport	41.14603	-80.16775	1371	-5	A	America/New_York
2A0	Mark Anton Airport	35.48625	-84.93108	718	-5	A	America/New_York
2B2	Plum Island Airport	42.79536	-70.83944	11	-5	A	America/New_York
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
X59	Valkaria Municipal	27.96086	-80.55833	26	-5	A	America/New_York
XFL	Flagler County Airport	29.28210	-81.12120	33	-5	A	America/New_York
XNA	NW Arkansas Regional	36.28187	-94.30681	1287	-6	A	America/Chicago
XZK	Amherst Amtrak Station AMM	42.37500	-72.51139	258	-5	A	America/New_York
Y51	Municipal Airport	43.57936	-90.89647	1292	-6	A	America/Chicago
Y72	Bloyer Field	43.97622	-90.48061	966	-6	A	America/Chicago
YAK	Yakutat	59.30120	-139.39370	33	-9	A	NA
YIP	Willow Run	42.23793	-83.53041	716	-5	A	America/New_York
YKM	Yakima Air Terminal McAllister Field	46.56820	-120.54400	1095	-8	A	America/Los_Angeles
YKN	Chan Gurney	42.87110	-97.39690	1200	-6	A	America/Chicago
YNG	Youngstown Warren Rgnl	41.26074	-80.67910	1196	-5	A	America/New_York
YUM	Yuma Mcas Yuma Intl	32.65658	-114.60598	216	-7	N	America/Phoenix
Z84	Clear	64.30120	-149.12014	552	-9	A	America/Anchorage
ZBP	Penn Station	39.30722	-76.61556	66	-5	A	America/New_York
ZFV	Philadelphia 30th St Station	39.95570	-75.18200	0	-5	A	America/New_York
ZPH	Municipal Airport	28.22806	-82.15583	90	-5	A	America/New_York
ZRA	Atlantic City Rail Terminal	39.36650	-74.44200	8	-5	A	America/New_York
ZRD	Train Station	37.53430	-77.42945	26	-5	A	America/New_York
ZRP	Newark Penn Station	40.73472	-74.16417	0	-5	A	America/New_York
ZRT	Hartford Union Station	41.76888	-72.68150	0	-5	A	America/New_York
ZRZ	New Carrollton Rail Station	38.94800	-76.87190	39	-5	A	America/New_York
ZSF	Springfield Amtrak Station	42.10600	-72.59305	65	-5	A	America/New_York
ZSY	Scottsdale Airport	33.62289	-111.91053	1519	-7	A	America/Phoenix
ZTF	Stamford Amtrak Station	41.04694	-73.54149	0	-5	A	America/New_York
ZTY	Boston Back Bay Station	42.34780	-71.07500	20	-5	A	America/New_York
ZUN	Black Rock	35.08323	-108.79178	6454	-7	A	America/Denver
ZVE	New Haven Rail Station	41.29867	-72.92599	7	-5	A	America/New_York
ZWI	Wilmington Amtrak Station	39.73667	-75.55167	0	-5	A	America/New_York
ZWU	Washington Union Station	38.89746	-77.00643	76	-5	A	America/New_York
ZYP	Penn Station	40.75050	-73.99350	35	-5	A	America/New_York

planes gives information about each plane, identified by its tailnum:

In [4]:

            
                Copied!
                
planes
planes

A tibble: 3322 × 9
tailnum	year	type	manufacturer	model	engines	seats	speed	engine
<chr>	<int>	<chr>	<chr>	<chr>	<int>	<int>	<int>	<chr>
N10156	2004	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N102UW	1998	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N103US	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N104UW	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N10575	2002	Fixed wing multi engine	EMBRAER	EMB-145LR	2	55	NA	Turbo-fan
N105UW	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N107US	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N108UW	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N109UW	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N110UW	1999	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-214	2	182	NA	Turbo-fan
N11106	2002	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11107	2002	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11109	2002	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11113	2002	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11119	2002	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11121	2003	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11127	2003	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11137	2003	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11140	2003	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11150	2003	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11155	2004	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11164	2004	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11165	2004	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11176	2004	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11181	2005	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11184	2005	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11187	2005	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11189	2005	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11191	2005	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
N11192	2005	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
N984DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N985AT	2001	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N985DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N986AT	2001	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N986DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N987AT	2001	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N987DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N988AT	2001	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N988DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N989AT	2001	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N989DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N990AT	2001	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N990DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N991AT	NA	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N991DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N992AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N992DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N993AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N993DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N994AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N994DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS CORPORATION	MD-88	2	142	NA	Turbo-jet
N995AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N995DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N996AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N996DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N997AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N997DL	1992	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
N998AT	2002	Fixed wing multi engine	BOEING	717-200	2	100	NA	Turbo-fan
N998DL	1992	Fixed wing multi engine	MCDONNELL DOUGLAS CORPORATION	MD-88	2	142	NA	Turbo-jet
N999DN	1992	Fixed wing multi engine	MCDONNELL DOUGLAS CORPORATION	MD-88	2	142	NA	Turbo-jet

weather gives the weather at each NYC airport for each hour:

In [5]:

            
                Copied!
                
weather
weather

A tibble: 26115 × 15
origin	year	month	day	hour	temp	dewp	humid	wind_dir	wind_speed	wind_gust	precip	pressure	visib	time_hour
<chr>	<int>	<int>	<int>	<int>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
EWR	2013	1	1	1	39.02	26.06	59.37	270	10.35702	NA	0	1012.0	10	2013-01-01 01:00:00
EWR	2013	1	1	2	39.02	26.96	61.63	250	8.05546	NA	0	1012.3	10	2013-01-01 02:00:00
EWR	2013	1	1	3	39.02	28.04	64.43	240	11.50780	NA	0	1012.5	10	2013-01-01 03:00:00
EWR	2013	1	1	4	39.92	28.04	62.21	250	12.65858	NA	0	1012.2	10	2013-01-01 04:00:00
EWR	2013	1	1	5	39.02	28.04	64.43	260	12.65858	NA	0	1011.9	10	2013-01-01 05:00:00
EWR	2013	1	1	6	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
EWR	2013	1	1	7	39.02	28.04	64.43	240	14.96014	NA	0	1012.2	10	2013-01-01 07:00:00
EWR	2013	1	1	8	39.92	28.04	62.21	250	10.35702	NA	0	1012.2	10	2013-01-01 08:00:00
EWR	2013	1	1	9	39.92	28.04	62.21	260	14.96014	NA	0	1012.7	10	2013-01-01 09:00:00
EWR	2013	1	1	10	41.00	28.04	59.65	260	13.80936	NA	0	1012.4	10	2013-01-01 10:00:00
EWR	2013	1	1	11	41.00	26.96	57.06	260	14.96014	NA	0	1011.4	10	2013-01-01 11:00:00
EWR	2013	1	1	13	39.20	28.40	69.67	330	16.11092	NA	0	NA	10	2013-01-01 13:00:00
EWR	2013	1	1	14	39.02	24.08	54.68	280	13.80936	NA	0	1010.8	10	2013-01-01 14:00:00
EWR	2013	1	1	15	37.94	24.08	57.04	290	9.20624	NA	0	1011.9	10	2013-01-01 15:00:00
EWR	2013	1	1	16	37.04	19.94	49.62	300	13.80936	20.71404	0	1012.1	10	2013-01-01 16:00:00
EWR	2013	1	1	17	35.96	19.04	49.83	330	11.50780	NA	0	1013.2	10	2013-01-01 17:00:00
EWR	2013	1	1	18	33.98	15.08	45.43	310	12.65858	25.31716	0	1014.1	10	2013-01-01 18:00:00
EWR	2013	1	1	19	33.08	12.92	42.84	320	10.35702	NA	0	1014.4	10	2013-01-01 19:00:00
EWR	2013	1	1	20	32.00	15.08	49.19	310	14.96014	NA	0	1015.2	10	2013-01-01 20:00:00
EWR	2013	1	1	21	30.02	12.92	48.48	320	18.41248	26.46794	0	1016.0	10	2013-01-01 21:00:00
EWR	2013	1	1	22	28.94	12.02	48.69	320	18.41248	25.31716	0	1016.5	10	2013-01-01 22:00:00
EWR	2013	1	1	23	28.04	10.94	48.15	310	16.11092	NA	0	1016.4	10	2013-01-01 23:00:00
EWR	2013	1	2	0	26.96	10.94	50.34	310	14.96014	25.31716	0	1016.3	10	2013-01-02 00:00:00
EWR	2013	1	2	1	26.06	10.94	52.25	330	12.65858	24.16638	0	1016.3	10	2013-01-02 01:00:00
EWR	2013	1	2	2	24.98	10.94	54.65	330	13.80936	NA	0	1017.0	10	2013-01-02 02:00:00
EWR	2013	1	2	3	24.08	8.96	51.93	320	14.96014	NA	0	1016.6	10	2013-01-02 03:00:00
EWR	2013	1	2	4	24.08	8.96	51.93	330	12.65858	NA	0	1016.9	10	2013-01-02 04:00:00
EWR	2013	1	2	5	24.08	8.96	51.93	330	6.90468	NA	0	1016.9	10	2013-01-02 05:00:00
EWR	2013	1	2	6	24.08	8.96	51.93	310	3.45234	NA	0	1017.2	10	2013-01-02 06:00:00
EWR	2013	1	2	7	24.98	10.04	52.50	300	6.90468	NA	0	1017.6	10	2013-01-02 07:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
LGA	2013	12	29	13	42.80	37.94	88.76	70	12.65858	NA	0.19	NA	2.50	2013-12-29 13:00:00
LGA	2013	12	29	14	41.00	37.94	93.19	60	18.41248	NA	0.21	NA	1.75	2013-12-29 14:00:00
LGA	2013	12	29	15	41.00	39.02	92.59	40	13.80936	NA	0.37	999.9	1.50	2013-12-29 15:00:00
LGA	2013	12	29	16	41.00	37.94	88.76	350	8.05546	23.01560	0.28	998.7	1.50	2013-12-29 16:00:00
LGA	2013	12	29	17	44.06	41.00	93.24	350	20.71404	NA	0.04	NA	5.00	2013-12-29 17:00:00
LGA	2013	12	29	18	42.08	39.02	88.81	330	14.96014	NA	0.00	997.2	3.00	2013-12-29 18:00:00
LGA	2013	12	29	19	42.80	37.94	85.13	320	17.26170	NA	0.00	NA	8.00	2013-12-29 19:00:00
LGA	2013	12	29	20	42.08	37.94	86.89	320	19.56326	NA	0.00	NA	10.00	2013-12-29 20:00:00
LGA	2013	12	29	21	42.80	37.40	82.17	320	16.11092	NA	0.00	NA	10.00	2013-12-29 21:00:00
LGA	2013	12	29	22	42.98	37.04	79.38	300	9.20624	NA	0.00	1003.8	10.00	2013-12-29 22:00:00
LGA	2013	12	29	23	42.98	35.06	73.39	310	17.26170	24.16638	0.00	1005.1	10.00	2013-12-29 23:00:00
LGA	2013	12	30	0	42.08	33.98	72.78	320	11.50780	NA	0.00	1005.9	10.00	2013-12-30 00:00:00
LGA	2013	12	30	1	42.08	33.98	72.78	250	9.20624	NA	0.00	1007.6	10.00	2013-12-30 01:00:00
LGA	2013	12	30	2	41.00	33.98	75.88	240	8.05546	NA	0.00	1008.3	10.00	2013-12-30 02:00:00
LGA	2013	12	30	3	42.98	33.98	70.30	270	9.20624	NA	0.00	1008.2	10.00	2013-12-30 03:00:00
LGA	2013	12	30	4	41.00	33.08	73.19	0	0.00000	NA	0.00	1008.9	10.00	2013-12-30 04:00:00
LGA	2013	12	30	5	42.98	33.08	67.81	250	10.35702	NA	0.00	1009.2	10.00	2013-12-30 05:00:00
LGA	2013	12	30	6	42.98	33.98	70.30	230	6.90468	NA	0.00	1010.8	10.00	2013-12-30 06:00:00
LGA	2013	12	30	7	44.06	35.06	70.42	240	11.50780	NA	0.00	1011.9	10.00	2013-12-30 07:00:00
LGA	2013	12	30	8	44.06	33.98	67.45	260	11.50780	NA	0.00	1012.9	10.00	2013-12-30 08:00:00
LGA	2013	12	30	9	44.06	33.08	65.07	260	13.80936	NA	0.00	1013.7	10.00	2013-12-30 09:00:00
LGA	2013	12	30	10	42.98	33.80	70.28	330	16.11092	NA	0.00	NA	10.00	2013-12-30 10:00:00
LGA	2013	12	30	11	41.00	28.40	62.21	340	13.80936	23.01560	0.00	NA	10.00	2013-12-30 11:00:00
LGA	2013	12	30	12	37.94	23.00	54.51	330	21.86482	27.61872	0.00	1015.7	10.00	2013-12-30 12:00:00
LGA	2013	12	30	13	37.04	21.92	53.97	340	17.26170	20.71404	0.00	1016.5	10.00	2013-12-30 13:00:00
LGA	2013	12	30	14	35.96	19.94	51.78	340	13.80936	21.86482	0.00	1017.1	10.00	2013-12-30 14:00:00
LGA	2013	12	30	15	33.98	17.06	49.51	330	17.26170	21.86482	0.00	1018.8	10.00	2013-12-30 15:00:00
LGA	2013	12	30	16	32.00	15.08	49.19	340	14.96014	23.01560	0.00	1019.5	10.00	2013-12-30 16:00:00
LGA	2013	12	30	17	30.92	12.92	46.74	320	17.26170	NA	0.00	1019.9	10.00	2013-12-30 17:00:00
LGA	2013	12	30	18	28.94	10.94	46.41	330	18.41248	NA	0.00	1020.9	10.00	2013-12-30 18:00:00

One way to show the relationships between the different tables is with a drawing:

This diagram is a little overwhelming, but it's simple compared to some you'll see in the wild! The key to understanding diagrams like this is to remember each relation always concerns a pair of tables. You don't need to understand the whole thing; you just need to understand the chain of relations between the tables that you are interested in.

For nycflights13:

flights connects to planes via a single variable, tailnum.
flights connects to airlines through the carrier variable.
flights connects to airports in two ways: via the origin and dest variables.
flights connects to weather via origin (the location), and year, month, day and hour (the time).

Exercises¶

Imagine you wanted to draw (approximately) the route each plane flies from its origin to its destination. What variables would you need? What tables would you need to combine?
I forgot to draw the relationship between weather and airports. What is the relationship and how should it appear in the diagram?
weather only contains information for the origin (NYC) airports. If it contained weather records for all airports in the USA, what additional relation would it define with flights?

Keys¶

The variables used to connect each pair of tables are called keys. A key is a variable (or set of variables) that uniquely identifies an observation. In simple cases, a single variable is sufficient to identify an observation. For example, each plane is uniquely identified by its tailnum. In other cases, multiple variables may be needed. For example, to identify an observation in weather you need five variables: year, month, day, hour, and origin.

There are two types of keys:

A primary key uniquely identifies an observation in its own table. For example, planes$tailnum is a primary key because it uniquely identifies each plane in the planes table.
A foreign key uniquely identifies an observation in another table. For example, flights$tailnum is a foreign key because it appears in the flights table where it matches each flight to a unique plane.

A variable can be both a primary key and a foreign key. For example, origin is part of the weather primary key, and is also a foreign key for the airports table.

Once you've identified the primary keys in your tables, it's good practice to verify that they do indeed uniquely identify each observation. One way to do that is to count() the primary keys and look for entries where n is greater than one:

In [6]:

            
                Copied!
                
planes %>% 
  count(tailnum) %>% 
  filter(n > 1)
planes %>% 
  count(tailnum) %>% 
  filter(n > 1)

A tibble: 0 × 2
tailnum	n
<chr>	<int>

In [7]:

            
                Copied!
                
weather %>% 
  count(year, month, day, hour, origin) %>% 
  filter(n > 1)
weather %>% 
  count(year, month, day, hour, origin) %>% 
  filter(n > 1)

A tibble: 3 × 6
year	month	day	hour	origin	n
<int>	<int>	<int>	<int>	<chr>	<int>
2013	11	3	1	EWR	2
2013	11	3	1	JFK	2
2013	11	3	1	LGA	2

Sometimes a table doesn't have an explicit primary key: each row is an observation, but no combination of variables reliably identifies it. For example, what's the primary key in the flights table? You might think it would be the date plus the flight or tail number, but neither of those are unique:

In [8]:

            
                Copied!
                
flights %>% 
  count(year, month, day, flight) %>% 
  filter(n > 1)
flights %>% 
  count(year, month, day, flight) %>% 
  filter(n > 1)

A tibble: 29768 × 5
year	month	day	flight	n
<int>	<int>	<int>	<int>	<int>
2013	1	1	1	2
2013	1	1	3	2
2013	1	1	4	2
2013	1	1	11	3
2013	1	1	15	2
2013	1	1	21	2
2013	1	1	27	4
2013	1	1	31	2
2013	1	1	32	2
2013	1	1	35	2
2013	1	1	59	2
2013	1	1	85	2
2013	1	1	87	2
2013	1	1	91	2
2013	1	1	117	2
2013	1	1	119	2
2013	1	1	128	2
2013	1	1	133	3
2013	1	1	179	2
2013	1	1	181	3
2013	1	1	185	2
2013	1	1	257	2
2013	1	1	269	2
2013	1	1	301	2
2013	1	1	303	2
2013	1	1	321	2
2013	1	1	329	2
2013	1	1	341	2
2013	1	1	347	2
2013	1	1	353	2
⋮	⋮	⋮	⋮	⋮
2013	12	31	456	2
2013	12	31	477	2
2013	12	31	483	2
2013	12	31	486	2
2013	12	31	561	2
2013	12	31	575	2
2013	12	31	586	2
2013	12	31	653	2
2013	12	31	687	2
2013	12	31	703	2
2013	12	31	1109	2
2013	12	31	1111	2
2013	12	31	1131	2
2013	12	31	1143	2
2013	12	31	1151	2
2013	12	31	1185	2
2013	12	31	1203	2
2013	12	31	1205	2
2013	12	31	1289	2
2013	12	31	1371	2
2013	12	31	1401	2
2013	12	31	1701	2
2013	12	31	1703	2
2013	12	31	1729	2
2013	12	31	1734	2
2013	12	31	2043	2
2013	12	31	2181	2
2013	12	31	2285	2
2013	12	31	2488	2
2013	12	31	3425	2

In [9]:

            
                Copied!
                
flights %>% 
  count(year, month, day, tailnum) %>% 
  filter(n > 1)
flights %>% 
  count(year, month, day, tailnum) %>% 
  filter(n > 1)

A tibble: 64928 × 5
year	month	day	tailnum	n
<int>	<int>	<int>	<chr>	<int>
2013	1	1	N0EGMQ	2
2013	1	1	N11189	2
2013	1	1	N11536	2
2013	1	1	N11544	3
2013	1	1	N11551	2
2013	1	1	N12540	2
2013	1	1	N12567	2
2013	1	1	N13123	2
2013	1	1	N13538	3
2013	1	1	N13566	3
2013	1	1	N13903	2
2013	1	1	N13958	3
2013	1	1	N13969	2
2013	1	1	N13978	2
2013	1	1	N13988	2
2013	1	1	N14168	3
2013	1	1	N14542	2
2013	1	1	N14543	2
2013	1	1	N14558	2
2013	1	1	N14972	2
2013	1	1	N14998	2
2013	1	1	N15572	2
2013	1	1	N15912	3
2013	1	1	N15986	2
2013	1	1	N16546	2
2013	1	1	N16561	3
2013	1	1	N17108	2
2013	1	1	N178JB	3
2013	1	1	N18120	2
2013	1	1	N184JB	3
⋮	⋮	⋮	⋮	⋮
2013	12	31	N796JB	2
2013	12	31	N802MQ	2
2013	12	31	N805JB	2
2013	12	31	N813MQ	3
2013	12	31	N818MQ	2
2013	12	31	N824MQ	2
2013	12	31	N832UA	2
2013	12	31	N835UA	2
2013	12	31	N838UA	2
2013	12	31	N844MQ	3
2013	12	31	N857MQ	3
2013	12	31	N903JB	2
2013	12	31	N904DL	2
2013	12	31	N905DE	2
2013	12	31	N909DL	2
2013	12	31	N913JB	2
2013	12	31	N915DL	2
2013	12	31	N919XJ	2
2013	12	31	N922XJ	2
2013	12	31	N926DL	2
2013	12	31	N932DL	2
2013	12	31	N936XJ	3
2013	12	31	N940DL	2
2013	12	31	N944DL	2
2013	12	31	N945DL	2
2013	12	31	N959DL	2
2013	12	31	N959UW	2
2013	12	31	N973DL	2
2013	12	31	N978AT	2
2013	12	31	NA	11

When starting to work with this data, I had naively assumed that each flight number would be only used once per day: that would make it much easier to communicate problems with a specific flight. Unfortunately that is not the case! If a table lacks a primary key, it's sometimes useful to add one with mutate() and row_number(). That makes it easier to match observations if you've done some filtering and want to check back in with the original data. This is called a surrogate key.

A primary key and the corresponding foreign key in another table form a relation. Relations are typically one-to-many. For example, each flight has one plane, but each plane has many flights. In other data, you'll occasionally see a 1-to-1 relationship. You can think of this as a special case of 1-to-many. You can model many-to-many relations with a many-to-1 relation plus a 1-to-many relation. For example, in this data there's a many-to-many relationship between airlines and airports: each airline flies to many airports; each airport hosts many airlines.

Exercises¶

Add a surrogate key to flights.
We know that some days of the year are "special", and fewer people than usual fly on them. How might you represent that data as a data frame? What would be the primary keys of that table? How would it connect to the existing tables?
Identify the keys in the following datasets

a. Lahman::Batting, b. babynames::babynames c. nasaweather::atmos d. fueleconomy::vehicles e. ggplot2::diamonds

(You might need to install some packages and read some documentation.)
Draw a diagram illustrating the connections between the Batting, People, and Salaries tables in the Lahman package. Draw another diagram that shows the relationship between People, Managers, AwardsManagers.

How would you characterise the relationship between the Batting, Pitching, and Fielding tables?

Mutating joins¶

The first tool we'll look at for combining a pair of tables is the mutating join. A mutating join allows you to combine variables from two tables. It first matches observations by their keys, then copies across variables from one table to the other.

Like mutate(), the join functions add variables to the right, so if you have a lot of variables already, the new variables won't get printed out. For these examples, we'll make it easier to see what's going on in the examples by creating a narrower dataset:

In [10]:

            
                Copied!
                
flights2 <- flights %>% 
  select(year:day, hour, origin, dest, tailnum, carrier)
flights2
flights2 <- flights %>% 
  select(year:day, hour, origin, dest, tailnum, carrier)
flights2

A tibble: 336776 × 8
year	month	day	hour	origin	dest	tailnum	carrier
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>	<chr>
2013	1	1	5	EWR	IAH	N14228	UA
2013	1	1	5	LGA	IAH	N24211	UA
2013	1	1	5	JFK	MIA	N619AA	AA
2013	1	1	5	JFK	BQN	N804JB	B6
2013	1	1	6	LGA	ATL	N668DN	DL
2013	1	1	5	EWR	ORD	N39463	UA
2013	1	1	6	EWR	FLL	N516JB	B6
2013	1	1	6	LGA	IAD	N829AS	EV
2013	1	1	6	JFK	MCO	N593JB	B6
2013	1	1	6	LGA	ORD	N3ALAA	AA
2013	1	1	6	JFK	PBI	N793JB	B6
2013	1	1	6	JFK	TPA	N657JB	B6
2013	1	1	6	JFK	LAX	N29129	UA
2013	1	1	6	EWR	SFO	N53441	UA
2013	1	1	6	LGA	DFW	N3DUAA	AA
2013	1	1	5	JFK	BOS	N708JB	B6
2013	1	1	6	EWR	LAS	N76515	UA
2013	1	1	6	LGA	FLL	N595JB	B6
2013	1	1	6	LGA	ATL	N542MQ	MQ
2013	1	1	6	EWR	PBI	N644JB	B6
2013	1	1	6	LGA	MSP	N971DL	DL
2013	1	1	6	LGA	DTW	N730MQ	MQ
2013	1	1	6	EWR	MIA	N633AA	AA
2013	1	1	6	JFK	ATL	N3739P	DL
2013	1	1	6	EWR	MIA	N53442	UA
2013	1	1	6	EWR	ORD	N9EAMQ	MQ
2013	1	1	6	JFK	SFO	N532UA	UA
2013	1	1	6	JFK	RSW	N635JB	B6
2013	1	1	6	JFK	SJU	N794JB	B6
2013	1	1	6	EWR	ATL	N326NB	DL
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	LGA	CHO	N712EV	EV
2013	9	30	21	EWR	CLT	N16546	EV
2013	9	30	21	JFK	DEN	N807JB	B6
2013	9	30	20	LGA	RIC	N751EV	EV
2013	9	30	21	JFK	DCA	N807MQ	MQ
2013	9	30	21	JFK	LAX	N335AA	AA
2013	9	30	21	EWR	PWM	N12957	EV
2013	9	30	21	JFK	SJU	N633JB	B6
2013	9	30	21	LGA	FLL	N627JB	B6
2013	9	30	21	EWR	BOS	N813UA	UA
2013	9	30	21	EWR	MHT	N10575	EV
2013	9	30	18	JFK	BUF	N906XJ	9E
2013	9	30	22	LGA	BGR	N722EV	EV
2013	9	30	21	LGA	BNA	N532MQ	MQ
2013	9	30	20	EWR	STL	N12145	EV
2013	9	30	22	JFK	PWM	N193JB	B6
2013	9	30	21	EWR	SFO	N578UA	UA
2013	9	30	20	JFK	MCO	N804JB	B6
2013	9	30	22	JFK	BTV	N318JB	B6
2013	9	30	22	JFK	SYR	N354JB	B6
2013	9	30	22	JFK	BUF	N281JB	B6
2013	9	30	22	JFK	ROC	N346JB	B6
2013	9	30	22	JFK	BOS	N565JB	B6
2013	9	30	23	JFK	PSE	N516JB	B6
2013	9	30	18	LGA	BNA	N740EV	EV
2013	9	30	14	JFK	DCA	NA	9E
2013	9	30	22	LGA	SYR	NA	9E
2013	9	30	12	LGA	BNA	N535MQ	MQ
2013	9	30	11	LGA	CLE	N511MQ	MQ
2013	9	30	8	LGA	RDU	N839MQ	MQ

(Remember, when you're in RStudio, you can also use View() to avoid this problem.)

Imagine you want to add the full airline name to the flights2 data. You can combine the airlines and flights2 data frames with left_join():

In [11]:

            
                Copied!
                
flights2 %>%
  select(-origin, -dest) %>% 
  left_join(airlines, by = "carrier")
flights2 %>%
  select(-origin, -dest) %>% 
  left_join(airlines, by = "carrier")

A tibble: 336776 × 7
year	month	day	hour	tailnum	carrier	name
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>
2013	1	1	5	N14228	UA	United Air Lines Inc.
2013	1	1	5	N24211	UA	United Air Lines Inc.
2013	1	1	5	N619AA	AA	American Airlines Inc.
2013	1	1	5	N804JB	B6	JetBlue Airways
2013	1	1	6	N668DN	DL	Delta Air Lines Inc.
2013	1	1	5	N39463	UA	United Air Lines Inc.
2013	1	1	6	N516JB	B6	JetBlue Airways
2013	1	1	6	N829AS	EV	ExpressJet Airlines Inc.
2013	1	1	6	N593JB	B6	JetBlue Airways
2013	1	1	6	N3ALAA	AA	American Airlines Inc.
2013	1	1	6	N793JB	B6	JetBlue Airways
2013	1	1	6	N657JB	B6	JetBlue Airways
2013	1	1	6	N29129	UA	United Air Lines Inc.
2013	1	1	6	N53441	UA	United Air Lines Inc.
2013	1	1	6	N3DUAA	AA	American Airlines Inc.
2013	1	1	5	N708JB	B6	JetBlue Airways
2013	1	1	6	N76515	UA	United Air Lines Inc.
2013	1	1	6	N595JB	B6	JetBlue Airways
2013	1	1	6	N542MQ	MQ	Envoy Air
2013	1	1	6	N644JB	B6	JetBlue Airways
2013	1	1	6	N971DL	DL	Delta Air Lines Inc.
2013	1	1	6	N730MQ	MQ	Envoy Air
2013	1	1	6	N633AA	AA	American Airlines Inc.
2013	1	1	6	N3739P	DL	Delta Air Lines Inc.
2013	1	1	6	N53442	UA	United Air Lines Inc.
2013	1	1	6	N9EAMQ	MQ	Envoy Air
2013	1	1	6	N532UA	UA	United Air Lines Inc.
2013	1	1	6	N635JB	B6	JetBlue Airways
2013	1	1	6	N794JB	B6	JetBlue Airways
2013	1	1	6	N326NB	DL	Delta Air Lines Inc.
⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	N712EV	EV	ExpressJet Airlines Inc.
2013	9	30	21	N16546	EV	ExpressJet Airlines Inc.
2013	9	30	21	N807JB	B6	JetBlue Airways
2013	9	30	20	N751EV	EV	ExpressJet Airlines Inc.
2013	9	30	21	N807MQ	MQ	Envoy Air
2013	9	30	21	N335AA	AA	American Airlines Inc.
2013	9	30	21	N12957	EV	ExpressJet Airlines Inc.
2013	9	30	21	N633JB	B6	JetBlue Airways
2013	9	30	21	N627JB	B6	JetBlue Airways
2013	9	30	21	N813UA	UA	United Air Lines Inc.
2013	9	30	21	N10575	EV	ExpressJet Airlines Inc.
2013	9	30	18	N906XJ	9E	Endeavor Air Inc.
2013	9	30	22	N722EV	EV	ExpressJet Airlines Inc.
2013	9	30	21	N532MQ	MQ	Envoy Air
2013	9	30	20	N12145	EV	ExpressJet Airlines Inc.
2013	9	30	22	N193JB	B6	JetBlue Airways
2013	9	30	21	N578UA	UA	United Air Lines Inc.
2013	9	30	20	N804JB	B6	JetBlue Airways
2013	9	30	22	N318JB	B6	JetBlue Airways
2013	9	30	22	N354JB	B6	JetBlue Airways
2013	9	30	22	N281JB	B6	JetBlue Airways
2013	9	30	22	N346JB	B6	JetBlue Airways
2013	9	30	22	N565JB	B6	JetBlue Airways
2013	9	30	23	N516JB	B6	JetBlue Airways
2013	9	30	18	N740EV	EV	ExpressJet Airlines Inc.
2013	9	30	14	NA	9E	Endeavor Air Inc.
2013	9	30	22	NA	9E	Endeavor Air Inc.
2013	9	30	12	N535MQ	MQ	Envoy Air
2013	9	30	11	N511MQ	MQ	Envoy Air
2013	9	30	8	N839MQ	MQ	Envoy Air

The result of joining airlines to flights2 is an additional variable: name. This is why I call this type of join a mutating join. In this case, you could have got to the same place using mutate() and R's base subsetting:

In [12]:

            
                Copied!
                
flights2 %>%
  select(-origin, -dest) %>% 
  mutate(name = airlines$name[match(carrier, airlines$carrier)])
flights2 %>%
  select(-origin, -dest) %>% 
  mutate(name = airlinesname[match(carrier,airlinescarrier)])

A tibble: 336776 × 7
year	month	day	hour	tailnum	carrier	name
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>
2013	1	1	5	N14228	UA	United Air Lines Inc.
2013	1	1	5	N24211	UA	United Air Lines Inc.
2013	1	1	5	N619AA	AA	American Airlines Inc.
2013	1	1	5	N804JB	B6	JetBlue Airways
2013	1	1	6	N668DN	DL	Delta Air Lines Inc.
2013	1	1	5	N39463	UA	United Air Lines Inc.
2013	1	1	6	N516JB	B6	JetBlue Airways
2013	1	1	6	N829AS	EV	ExpressJet Airlines Inc.
2013	1	1	6	N593JB	B6	JetBlue Airways
2013	1	1	6	N3ALAA	AA	American Airlines Inc.
2013	1	1	6	N793JB	B6	JetBlue Airways
2013	1	1	6	N657JB	B6	JetBlue Airways
2013	1	1	6	N29129	UA	United Air Lines Inc.
2013	1	1	6	N53441	UA	United Air Lines Inc.
2013	1	1	6	N3DUAA	AA	American Airlines Inc.
2013	1	1	5	N708JB	B6	JetBlue Airways
2013	1	1	6	N76515	UA	United Air Lines Inc.
2013	1	1	6	N595JB	B6	JetBlue Airways
2013	1	1	6	N542MQ	MQ	Envoy Air
2013	1	1	6	N644JB	B6	JetBlue Airways
2013	1	1	6	N971DL	DL	Delta Air Lines Inc.
2013	1	1	6	N730MQ	MQ	Envoy Air
2013	1	1	6	N633AA	AA	American Airlines Inc.
2013	1	1	6	N3739P	DL	Delta Air Lines Inc.
2013	1	1	6	N53442	UA	United Air Lines Inc.
2013	1	1	6	N9EAMQ	MQ	Envoy Air
2013	1	1	6	N532UA	UA	United Air Lines Inc.
2013	1	1	6	N635JB	B6	JetBlue Airways
2013	1	1	6	N794JB	B6	JetBlue Airways
2013	1	1	6	N326NB	DL	Delta Air Lines Inc.
⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	N712EV	EV	ExpressJet Airlines Inc.
2013	9	30	21	N16546	EV	ExpressJet Airlines Inc.
2013	9	30	21	N807JB	B6	JetBlue Airways
2013	9	30	20	N751EV	EV	ExpressJet Airlines Inc.
2013	9	30	21	N807MQ	MQ	Envoy Air
2013	9	30	21	N335AA	AA	American Airlines Inc.
2013	9	30	21	N12957	EV	ExpressJet Airlines Inc.
2013	9	30	21	N633JB	B6	JetBlue Airways
2013	9	30	21	N627JB	B6	JetBlue Airways
2013	9	30	21	N813UA	UA	United Air Lines Inc.
2013	9	30	21	N10575	EV	ExpressJet Airlines Inc.
2013	9	30	18	N906XJ	9E	Endeavor Air Inc.
2013	9	30	22	N722EV	EV	ExpressJet Airlines Inc.
2013	9	30	21	N532MQ	MQ	Envoy Air
2013	9	30	20	N12145	EV	ExpressJet Airlines Inc.
2013	9	30	22	N193JB	B6	JetBlue Airways
2013	9	30	21	N578UA	UA	United Air Lines Inc.
2013	9	30	20	N804JB	B6	JetBlue Airways
2013	9	30	22	N318JB	B6	JetBlue Airways
2013	9	30	22	N354JB	B6	JetBlue Airways
2013	9	30	22	N281JB	B6	JetBlue Airways
2013	9	30	22	N346JB	B6	JetBlue Airways
2013	9	30	22	N565JB	B6	JetBlue Airways
2013	9	30	23	N516JB	B6	JetBlue Airways
2013	9	30	18	N740EV	EV	ExpressJet Airlines Inc.
2013	9	30	14	NA	9E	Endeavor Air Inc.
2013	9	30	22	NA	9E	Endeavor Air Inc.
2013	9	30	12	N535MQ	MQ	Envoy Air
2013	9	30	11	N511MQ	MQ	Envoy Air
2013	9	30	8	N839MQ	MQ	Envoy Air

But this is hard to generalise when you need to match multiple variables, and takes close reading to figure out the overall intent.

The following sections explain, in detail, how mutating joins work. You'll start by learning a useful visual representation of joins. We'll then use that to explain the four mutating join functions: the inner join, and the three outer joins. When working with real data, keys don't always uniquely identify observations, so next we'll talk about what happens when there isn't a unique match. Finally, you'll learn how to tell dplyr which variables are the keys for a given join.

Understanding joins¶

To help you learn how joins work, I'm going to use a visual representation:

In [13]:

            
                Copied!
                
                    
                    
                
                

        
x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     3, "x3"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     4, "y3"
)
x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     3, "x3"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     4, "y3"
)

The coloured column represents the "key" variable: these are used to match the rows between the tables. The grey column represents the "value" column that is carried along for the ride. In these examples I'll show a single key variable, but the idea generalises in a straightforward way to multiple keys and multiple values.

A join is a way of connecting each row in x to zero, one, or more rows in y. The following diagram shows each potential match as an intersection of a pair of lines.

(If you look closely, you might notice that we've switched the order of the key and value columns in x. This is to emphasise that joins match based on the key; the value is just carried along for the ride.)

In an actual join, matches will be indicated with dots. The number of dots = the number of matches = the number of rows in the output.

Inner join¶

The simplest type of join is the inner join. An inner join matches pairs of observations whenever their keys are equal:

(To be precise, this is an inner equijoin because the keys are matched using the equality operator. Since most joins are equijoins we usually drop that specification.)

The output of an inner join is a new data frame that contains the key, the x values, and the y values. We use by to tell dplyr which variable is the key:

In [14]:

            
                Copied!
                
x %>% 
  inner_join(y, by = "key")
x %>% 
  inner_join(y, by = "key")

A tibble: 2 × 3
key	val_x	val_y
<dbl>	<chr>	<chr>
1	x1	y1
2	x2	y2

The most important property of an inner join is that unmatched rows are not included in the result. This means that generally inner joins are usually not appropriate for use in analysis because it's too easy to lose observations.

Outer joins¶

An inner join keeps observations that appear in both tables. An outer join keeps observations that appear in at least one of the tables. There are three types of outer joins:

A left join keeps all observations in x.
A right join keeps all observations in y.
A full join keeps all observations in x and y.

These joins work by adding an additional "virtual" observation to each table. This observation has a key that always matches (if no other key matches), and a value filled with NA.

Graphically, that looks like:

The most commonly used join is the left join: you use this whenever you look up additional data from another table, because it preserves the original observations even when there isn't a match. The left join should be your default join: use it unless you have a strong reason to prefer one of the others.

Another way to depict the different types of joins is with a Venn diagram:

However, this is not a great representation. It might jog your memory about which join preserves the observations in which table, but it suffers from a major limitation: a Venn diagram can't show what happens when keys don't uniquely identify an observation.

Duplicate keys¶

So far all the diagrams have assumed that the keys are unique. But that's not always the case. This section explains what happens when the keys are not unique. There are two possibilities:

One table has duplicate keys. This is useful when you want to add in additional information as there is typically a one-to-many relationship.

Note that I've put the key column in a slightly different position in the output. This reflects that the key is a primary key in y and a foreign key in x.

In [15]:

            
                Copied!
                
                    
                    
                
                

        
x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3",
     1, "x4"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2"
)
left_join(x, y, by = "key")
x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3",
     1, "x4"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2"
)
left_join(x, y, by = "key")

A tibble: 4 × 3
key	val_x	val_y
<dbl>	<chr>	<chr>
1	x1	y1
2	x2	y2
2	x3	y2
1	x4	y1

Both tables have duplicate keys. This is usually an error because in neither table do the keys uniquely identify an observation. When you join duplicated keys, you get all possible combinations, the Cartesian product:

In [16]:

            
                Copied!
                
                    
                    
                
                

        
x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3",
     3, "x4"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     2, "y3",
     3, "y4"
)
left_join(x, y, by = "key")
x <- tribble(
  ~key, ~val_x,
     1, "x1",
     2, "x2",
     2, "x3",
     3, "x4"
)
y <- tribble(
  ~key, ~val_y,
     1, "y1",
     2, "y2",
     2, "y3",
     3, "y4"
)
left_join(x, y, by = "key")

A tibble: 6 × 3
key	val_x	val_y
<dbl>	<chr>	<chr>
1	x1	y1
2	x2	y2
2	x2	y3
2	x3	y2
2	x3	y3
3	x4	y4

Defining the key columns¶

So far, the pairs of tables have always been joined by a single variable, and that variable has the same name in both tables. That constraint was encoded by by = "key". You can use other values for by to connect the tables in other ways:

The default, by = NULL, uses all variables that appear in both tables, the so called natural join. For example, the flights and weather tables match on their common variables: year, month, day, hour and origin.

In [17]:

            
                Copied!
                
flights2 %>% 
  left_join(weather)
flights2 %>% 
  left_join(weather)

Joining, by = c("year", "month", "day", "hour", "origin")

A tibble: 336776 × 18
year	month	day	hour	origin	dest	tailnum	carrier	temp	dewp	humid	wind_dir	wind_speed	wind_gust	precip	pressure	visib	time_hour
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	5	EWR	IAH	N14228	UA	39.02	28.04	64.43	260	12.65858	NA	0	1011.9	10	2013-01-01 05:00:00
2013	1	1	5	LGA	IAH	N24211	UA	39.92	24.98	54.81	250	14.96014	21.86482	0	1011.4	10	2013-01-01 05:00:00
2013	1	1	5	JFK	MIA	N619AA	AA	39.02	26.96	61.63	260	14.96014	NA	0	1012.1	10	2013-01-01 05:00:00
2013	1	1	5	JFK	BQN	N804JB	B6	39.02	26.96	61.63	260	14.96014	NA	0	1012.1	10	2013-01-01 05:00:00
2013	1	1	6	LGA	ATL	N668DN	DL	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	5	EWR	ORD	N39463	UA	39.02	28.04	64.43	260	12.65858	NA	0	1011.9	10	2013-01-01 05:00:00
2013	1	1	6	EWR	FLL	N516JB	B6	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	LGA	IAD	N829AS	EV	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	6	JFK	MCO	N593JB	B6	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	LGA	ORD	N3ALAA	AA	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	6	JFK	PBI	N793JB	B6	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	JFK	TPA	N657JB	B6	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	JFK	LAX	N29129	UA	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	EWR	SFO	N53441	UA	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	LGA	DFW	N3DUAA	AA	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	5	JFK	BOS	N708JB	B6	39.02	26.96	61.63	260	14.96014	NA	0	1012.1	10	2013-01-01 05:00:00
2013	1	1	6	EWR	LAS	N76515	UA	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	LGA	FLL	N595JB	B6	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	6	LGA	ATL	N542MQ	MQ	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	6	EWR	PBI	N644JB	B6	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	LGA	MSP	N971DL	DL	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	6	LGA	DTW	N730MQ	MQ	39.92	24.98	54.81	260	16.11092	23.01560	0	1011.7	10	2013-01-01 06:00:00
2013	1	1	6	EWR	MIA	N633AA	AA	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	JFK	ATL	N3739P	DL	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	EWR	MIA	N53442	UA	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	EWR	ORD	N9EAMQ	MQ	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
2013	1	1	6	JFK	SFO	N532UA	UA	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	JFK	RSW	N635JB	B6	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	JFK	SJU	N794JB	B6	37.94	26.96	64.29	260	13.80936	NA	0	1012.6	10	2013-01-01 06:00:00
2013	1	1	6	EWR	ATL	N326NB	DL	37.94	28.04	67.21	240	11.50780	NA	0	1012.4	10	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	LGA	CHO	N712EV	EV	64.94	53.06	65.37	210	8.05546	NA	0	1015.8	10	2013-09-30 21:00:00
2013	9	30	21	EWR	CLT	N16546	EV	62.96	55.04	75.33	190	3.45234	NA	0	1016.1	10	2013-09-30 21:00:00
2013	9	30	21	JFK	DEN	N807JB	B6	62.06	57.02	83.54	230	9.20624	NA	0	1016.4	10	2013-09-30 21:00:00
2013	9	30	20	LGA	RIC	N751EV	EV	64.94	53.96	67.57	190	6.90468	NA	0	1015.7	10	2013-09-30 20:00:00
2013	9	30	21	JFK	DCA	N807MQ	MQ	62.06	57.02	83.54	230	9.20624	NA	0	1016.4	10	2013-09-30 21:00:00
2013	9	30	21	JFK	LAX	N335AA	AA	62.06	57.02	83.54	230	9.20624	NA	0	1016.4	10	2013-09-30 21:00:00
2013	9	30	21	EWR	PWM	N12957	EV	62.96	55.04	75.33	190	3.45234	NA	0	1016.1	10	2013-09-30 21:00:00
2013	9	30	21	JFK	SJU	N633JB	B6	62.06	57.02	83.54	230	9.20624	NA	0	1016.4	10	2013-09-30 21:00:00
2013	9	30	21	LGA	FLL	N627JB	B6	64.94	53.06	65.37	210	8.05546	NA	0	1015.8	10	2013-09-30 21:00:00
2013	9	30	21	EWR	BOS	N813UA	UA	62.96	55.04	75.33	190	3.45234	NA	0	1016.1	10	2013-09-30 21:00:00
2013	9	30	21	EWR	MHT	N10575	EV	62.96	55.04	75.33	190	3.45234	NA	0	1016.1	10	2013-09-30 21:00:00
2013	9	30	18	JFK	BUF	N906XJ	9E	64.04	55.94	74.94	180	6.90468	NA	0	1016.0	10	2013-09-30 18:00:00
2013	9	30	22	LGA	BGR	N722EV	EV	64.94	53.06	65.37	200	6.90468	NA	0	1015.8	10	2013-09-30 22:00:00
2013	9	30	21	LGA	BNA	N532MQ	MQ	64.94	53.06	65.37	210	8.05546	NA	0	1015.8	10	2013-09-30 21:00:00
2013	9	30	20	EWR	STL	N12145	EV	64.94	55.04	70.28	170	3.45234	NA	0	1016.0	10	2013-09-30 20:00:00
2013	9	30	22	JFK	PWM	N193JB	B6	60.98	55.94	83.47	230	9.20624	NA	0	1016.5	10	2013-09-30 22:00:00
2013	9	30	21	EWR	SFO	N578UA	UA	62.96	55.04	75.33	190	3.45234	NA	0	1016.1	10	2013-09-30 21:00:00
2013	9	30	20	JFK	MCO	N804JB	B6	62.06	57.02	83.54	210	8.05546	NA	0	1016.4	10	2013-09-30 20:00:00
2013	9	30	22	JFK	BTV	N318JB	B6	60.98	55.94	83.47	230	9.20624	NA	0	1016.5	10	2013-09-30 22:00:00
2013	9	30	22	JFK	SYR	N354JB	B6	60.98	55.94	83.47	230	9.20624	NA	0	1016.5	10	2013-09-30 22:00:00
2013	9	30	22	JFK	BUF	N281JB	B6	60.98	55.94	83.47	230	9.20624	NA	0	1016.5	10	2013-09-30 22:00:00
2013	9	30	22	JFK	ROC	N346JB	B6	60.98	55.94	83.47	230	9.20624	NA	0	1016.5	10	2013-09-30 22:00:00
2013	9	30	22	JFK	BOS	N565JB	B6	60.98	55.94	83.47	230	9.20624	NA	0	1016.5	10	2013-09-30 22:00:00
2013	9	30	23	JFK	PSE	N516JB	B6	60.08	55.04	83.41	240	9.20624	NA	0	1016.3	10	2013-09-30 23:00:00
2013	9	30	18	LGA	BNA	N740EV	EV	66.92	51.98	58.65	160	9.20624	NA	0	1015.4	10	2013-09-30 18:00:00
2013	9	30	14	JFK	DCA	NA	9E	68.00	55.04	63.21	190	11.50780	NA	0	1016.6	10	2013-09-30 14:00:00
2013	9	30	22	LGA	SYR	NA	9E	64.94	53.06	65.37	200	6.90468	NA	0	1015.8	10	2013-09-30 22:00:00
2013	9	30	12	LGA	BNA	N535MQ	MQ	69.08	48.02	46.99	70	5.75390	NA	0	1016.7	10	2013-09-30 12:00:00
2013	9	30	11	LGA	CLE	N511MQ	MQ	66.92	48.92	52.35	70	8.05546	NA	0	1017.5	10	2013-09-30 11:00:00
2013	9	30	8	LGA	RDU	N839MQ	MQ	60.98	51.08	69.86	NA	5.75390	NA	0	1018.6	10	2013-09-30 08:00:00

A character vector, by = "x". This is like a natural join, but uses only some of the common variables. For example, flights and planes have year variables, but they mean different things so we only want to join by tailnum.

In [18]:

            
                Copied!
                
flights2 %>% 
  left_join(planes, by = "tailnum")
flights2 %>% 
  left_join(planes, by = "tailnum")

A tibble: 336776 × 16
year.x	month	day	hour	origin	dest	tailnum	carrier	year.y	type	manufacturer	model	engines	seats	speed	engine
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>	<chr>	<int>	<chr>	<chr>	<chr>	<int>	<int>	<int>	<chr>
2013	1	1	5	EWR	IAH	N14228	UA	1999	Fixed wing multi engine	BOEING	737-824	2	149	NA	Turbo-fan
2013	1	1	5	LGA	IAH	N24211	UA	1998	Fixed wing multi engine	BOEING	737-824	2	149	NA	Turbo-fan
2013	1	1	5	JFK	MIA	N619AA	AA	1990	Fixed wing multi engine	BOEING	757-223	2	178	NA	Turbo-fan
2013	1	1	5	JFK	BQN	N804JB	B6	2012	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	LGA	ATL	N668DN	DL	1991	Fixed wing multi engine	BOEING	757-232	2	178	NA	Turbo-fan
2013	1	1	5	EWR	ORD	N39463	UA	2012	Fixed wing multi engine	BOEING	737-924ER	2	191	NA	Turbo-fan
2013	1	1	6	EWR	FLL	N516JB	B6	2000	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	LGA	IAD	N829AS	EV	1998	Fixed wing multi engine	CANADAIR	CL-600-2B19	2	55	NA	Turbo-fan
2013	1	1	6	JFK	MCO	N593JB	B6	2004	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	LGA	ORD	N3ALAA	AA	NA	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	JFK	PBI	N793JB	B6	2011	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	JFK	TPA	N657JB	B6	2007	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	JFK	LAX	N29129	UA	1998	Fixed wing multi engine	BOEING	757-224	2	178	NA	Turbo-jet
2013	1	1	6	EWR	SFO	N53441	UA	NA	Fixed wing multi engine	BOEING	737-924ER	2	191	NA	Turbo-fan
2013	1	1	6	LGA	DFW	N3DUAA	AA	NA	NA	NA	NA	NA	NA	NA	NA
2013	1	1	5	JFK	BOS	N708JB	B6	2008	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	EWR	LAS	N76515	UA	2008	Fixed wing multi engine	BOEING	737-824	2	149	NA	Turbo-fan
2013	1	1	6	LGA	FLL	N595JB	B6	2004	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	LGA	ATL	N542MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	EWR	PBI	N644JB	B6	2006	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	LGA	MSP	N971DL	DL	1991	Fixed wing multi engine	MCDONNELL DOUGLAS AIRCRAFT CO	MD-88	2	142	NA	Turbo-fan
2013	1	1	6	LGA	DTW	N730MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	EWR	MIA	N633AA	AA	1990	Fixed wing multi engine	BOEING	757-223	2	178	NA	Turbo-fan
2013	1	1	6	JFK	ATL	N3739P	DL	2000	Fixed wing multi engine	BOEING	737-832	2	189	NA	Turbo-jet
2013	1	1	6	EWR	MIA	N53442	UA	2009	Fixed wing multi engine	BOEING	737-924ER	2	191	NA	Turbo-fan
2013	1	1	6	EWR	ORD	N9EAMQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	JFK	SFO	N532UA	UA	NA	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	JFK	RSW	N635JB	B6	2006	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	JFK	SJU	N794JB	B6	2011	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	1	1	6	EWR	ATL	N326NB	DL	2001	Fixed wing multi engine	AIRBUS INDUSTRIE	A319-114	2	145	NA	Turbo-fan
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	LGA	CHO	N712EV	EV	2002	Fixed wing multi engine	BOMBARDIER INC	CL-600-2C10	2	80	NA	Turbo-fan
2013	9	30	21	EWR	CLT	N16546	EV	2002	Fixed wing multi engine	EMBRAER	EMB-145LR	2	55	NA	Turbo-fan
2013	9	30	21	JFK	DEN	N807JB	B6	2012	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	9	30	20	LGA	RIC	N751EV	EV	2004	Fixed wing multi engine	BOMBARDIER INC	CL-600-2C10	2	80	NA	Turbo-fan
2013	9	30	21	JFK	DCA	N807MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	9	30	21	JFK	LAX	N335AA	AA	1987	Fixed wing multi engine	BOEING	767-223	2	255	NA	Turbo-fan
2013	9	30	21	EWR	PWM	N12957	EV	1998	Fixed wing multi engine	EMBRAER	EMB-145LR	2	55	NA	Turbo-fan
2013	9	30	21	JFK	SJU	N633JB	B6	2006	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	9	30	21	LGA	FLL	N627JB	B6	2005	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	9	30	21	EWR	BOS	N813UA	UA	1998	Fixed wing multi engine	AIRBUS INDUSTRIE	A319-131	2	179	NA	Turbo-jet
2013	9	30	21	EWR	MHT	N10575	EV	2002	Fixed wing multi engine	EMBRAER	EMB-145LR	2	55	NA	Turbo-fan
2013	9	30	18	JFK	BUF	N906XJ	9E	2007	Fixed wing multi engine	BOMBARDIER INC	CL-600-2D24	2	95	NA	Turbo-fan
2013	9	30	22	LGA	BGR	N722EV	EV	2003	Fixed wing multi engine	BOMBARDIER INC	CL-600-2C10	2	80	NA	Turbo-fan
2013	9	30	21	LGA	BNA	N532MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	9	30	20	EWR	STL	N12145	EV	2003	Fixed wing multi engine	EMBRAER	EMB-145XR	2	55	NA	Turbo-fan
2013	9	30	22	JFK	PWM	N193JB	B6	2005	Fixed wing multi engine	EMBRAER	ERJ 190-100 IGW	2	20	NA	Turbo-fan
2013	9	30	21	EWR	SFO	N578UA	UA	1993	Fixed wing multi engine	BOEING	757-222	2	178	NA	Turbo-jet
2013	9	30	20	JFK	MCO	N804JB	B6	2012	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	9	30	22	JFK	BTV	N318JB	B6	2010	Fixed wing multi engine	EMBRAER	ERJ 190-100 IGW	2	20	NA	Turbo-fan
2013	9	30	22	JFK	SYR	N354JB	B6	2013	Fixed wing multi engine	EMBRAER	ERJ 190-100 IGW	2	20	NA	Turbo-fan
2013	9	30	22	JFK	BUF	N281JB	B6	2007	Fixed wing multi engine	EMBRAER	ERJ 190-100 IGW	2	20	NA	Turbo-fan
2013	9	30	22	JFK	ROC	N346JB	B6	2011	Fixed wing multi engine	EMBRAER	ERJ 190-100 IGW	2	20	NA	Turbo-fan
2013	9	30	22	JFK	BOS	N565JB	B6	2003	Fixed wing multi engine	AIRBUS	A320-232	2	200	NA	Turbo-fan
2013	9	30	23	JFK	PSE	N516JB	B6	2000	Fixed wing multi engine	AIRBUS INDUSTRIE	A320-232	2	200	NA	Turbo-fan
2013	9	30	18	LGA	BNA	N740EV	EV	2004	Fixed wing multi engine	BOMBARDIER INC	CL-600-2C10	2	80	NA	Turbo-fan
2013	9	30	14	JFK	DCA	NA	9E	NA	NA	NA	NA	NA	NA	NA	NA
2013	9	30	22	LGA	SYR	NA	9E	NA	NA	NA	NA	NA	NA	NA	NA
2013	9	30	12	LGA	BNA	N535MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	9	30	11	LGA	CLE	N511MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA
2013	9	30	8	LGA	RDU	N839MQ	MQ	NA	NA	NA	NA	NA	NA	NA	NA

Note that the year variables (which appear in both input data frames, but are not constrained to be equal) are disambiguated in the output with a suffix.

A named character vector: by = c("a" = "b"). This will match variable a in table x to variable b in table y. The variables from x will be used in the output.

For example, if we want to draw a map we need to combine the flights data with the airports data which contains the location (lat and lon) of each airport. Each flight has an origin and destination airport, so we need to specify which one we want to join to:

In [20]:

            
                Copied!
                
flights2 %>% 
  left_join(airports, c("dest" = "faa"))
flights2 %>% 
  left_join(airports, c("dest" = "faa"))

A tibble: 336776 × 15
year	month	day	hour	origin	dest	tailnum	carrier	name	lat	lon	alt	tz	dst	tzone
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<chr>	<chr>
2013	1	1	5	EWR	IAH	N14228	UA	George Bush Intercontinental	29.98443	-95.34144	97	-6	A	America/Chicago
2013	1	1	5	LGA	IAH	N24211	UA	George Bush Intercontinental	29.98443	-95.34144	97	-6	A	America/Chicago
2013	1	1	5	JFK	MIA	N619AA	AA	Miami Intl	25.79325	-80.29056	8	-5	A	America/New_York
2013	1	1	5	JFK	BQN	N804JB	B6	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	LGA	ATL	N668DN	DL	Hartsfield Jackson Atlanta Intl	33.63672	-84.42807	1026	-5	A	America/New_York
2013	1	1	5	EWR	ORD	N39463	UA	Chicago Ohare Intl	41.97860	-87.90484	668	-6	A	America/Chicago
2013	1	1	6	EWR	FLL	N516JB	B6	Fort Lauderdale Hollywood Intl	26.07258	-80.15275	9	-5	A	America/New_York
2013	1	1	6	LGA	IAD	N829AS	EV	Washington Dulles Intl	38.94453	-77.45581	313	-5	A	America/New_York
2013	1	1	6	JFK	MCO	N593JB	B6	Orlando Intl	28.42939	-81.30899	96	-5	A	America/New_York
2013	1	1	6	LGA	ORD	N3ALAA	AA	Chicago Ohare Intl	41.97860	-87.90484	668	-6	A	America/Chicago
2013	1	1	6	JFK	PBI	N793JB	B6	Palm Beach Intl	26.68316	-80.09559	19	-5	A	America/New_York
2013	1	1	6	JFK	TPA	N657JB	B6	Tampa Intl	27.97547	-82.53325	26	-5	A	America/New_York
2013	1	1	6	JFK	LAX	N29129	UA	Los Angeles Intl	33.94254	-118.40807	126	-8	A	America/Los_Angeles
2013	1	1	6	EWR	SFO	N53441	UA	San Francisco Intl	37.61897	-122.37489	13	-8	A	America/Los_Angeles
2013	1	1	6	LGA	DFW	N3DUAA	AA	Dallas Fort Worth Intl	32.89683	-97.03800	607	-6	A	America/Chicago
2013	1	1	5	JFK	BOS	N708JB	B6	General Edward Lawrence Logan Intl	42.36435	-71.00518	19	-5	A	America/New_York
2013	1	1	6	EWR	LAS	N76515	UA	Mc Carran Intl	36.08006	-115.15225	2141	-8	A	America/Los_Angeles
2013	1	1	6	LGA	FLL	N595JB	B6	Fort Lauderdale Hollywood Intl	26.07258	-80.15275	9	-5	A	America/New_York
2013	1	1	6	LGA	ATL	N542MQ	MQ	Hartsfield Jackson Atlanta Intl	33.63672	-84.42807	1026	-5	A	America/New_York
2013	1	1	6	EWR	PBI	N644JB	B6	Palm Beach Intl	26.68316	-80.09559	19	-5	A	America/New_York
2013	1	1	6	LGA	MSP	N971DL	DL	Minneapolis St Paul Intl	44.88196	-93.22177	841	-6	A	America/Chicago
2013	1	1	6	LGA	DTW	N730MQ	MQ	Detroit Metro Wayne Co	42.21244	-83.35339	645	-5	A	America/New_York
2013	1	1	6	EWR	MIA	N633AA	AA	Miami Intl	25.79325	-80.29056	8	-5	A	America/New_York
2013	1	1	6	JFK	ATL	N3739P	DL	Hartsfield Jackson Atlanta Intl	33.63672	-84.42807	1026	-5	A	America/New_York
2013	1	1	6	EWR	MIA	N53442	UA	Miami Intl	25.79325	-80.29056	8	-5	A	America/New_York
2013	1	1	6	EWR	ORD	N9EAMQ	MQ	Chicago Ohare Intl	41.97860	-87.90484	668	-6	A	America/Chicago
2013	1	1	6	JFK	SFO	N532UA	UA	San Francisco Intl	37.61897	-122.37489	13	-8	A	America/Los_Angeles
2013	1	1	6	JFK	RSW	N635JB	B6	Southwest Florida Intl	26.53617	-81.75517	30	-5	A	America/New_York
2013	1	1	6	JFK	SJU	N794JB	B6	NA	NA	NA	NA	NA	NA	NA
2013	1	1	6	EWR	ATL	N326NB	DL	Hartsfield Jackson Atlanta Intl	33.63672	-84.42807	1026	-5	A	America/New_York
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	LGA	CHO	N712EV	EV	Charlottesville-Albemarle	38.13864	-78.45286	639	-5	A	America/New_York
2013	9	30	21	EWR	CLT	N16546	EV	Charlotte Douglas Intl	35.21400	-80.94314	748	-5	A	America/New_York
2013	9	30	21	JFK	DEN	N807JB	B6	Denver Intl	39.86166	-104.67318	5431	-7	A	America/Denver
2013	9	30	20	LGA	RIC	N751EV	EV	Richmond Intl	37.50517	-77.31967	167	-5	A	America/New_York
2013	9	30	21	JFK	DCA	N807MQ	MQ	Ronald Reagan Washington Natl	38.85208	-77.03772	15	-5	A	America/New_York
2013	9	30	21	JFK	LAX	N335AA	AA	Los Angeles Intl	33.94254	-118.40807	126	-8	A	America/Los_Angeles
2013	9	30	21	EWR	PWM	N12957	EV	Portland Intl Jetport	43.64616	-70.30928	77	-5	A	America/New_York
2013	9	30	21	JFK	SJU	N633JB	B6	NA	NA	NA	NA	NA	NA	NA
2013	9	30	21	LGA	FLL	N627JB	B6	Fort Lauderdale Hollywood Intl	26.07258	-80.15275	9	-5	A	America/New_York
2013	9	30	21	EWR	BOS	N813UA	UA	General Edward Lawrence Logan Intl	42.36435	-71.00518	19	-5	A	America/New_York
2013	9	30	21	EWR	MHT	N10575	EV	Manchester Regional Airport	42.93256	-71.43567	266	-5	A	America/New_York
2013	9	30	18	JFK	BUF	N906XJ	9E	Buffalo Niagara Intl	42.94053	-78.73217	724	-5	A	America/New_York
2013	9	30	22	LGA	BGR	N722EV	EV	Bangor Intl	44.80744	-68.82814	192	-5	A	America/New_York
2013	9	30	21	LGA	BNA	N532MQ	MQ	Nashville Intl	36.12447	-86.67819	599	-6	A	America/Chicago
2013	9	30	20	EWR	STL	N12145	EV	Lambert St Louis Intl	38.74870	-90.37003	618	-6	A	America/Chicago
2013	9	30	22	JFK	PWM	N193JB	B6	Portland Intl Jetport	43.64616	-70.30928	77	-5	A	America/New_York
2013	9	30	21	EWR	SFO	N578UA	UA	San Francisco Intl	37.61897	-122.37489	13	-8	A	America/Los_Angeles
2013	9	30	20	JFK	MCO	N804JB	B6	Orlando Intl	28.42939	-81.30899	96	-5	A	America/New_York
2013	9	30	22	JFK	BTV	N318JB	B6	Burlington Intl	44.47186	-73.15328	335	-5	A	America/New_York
2013	9	30	22	JFK	SYR	N354JB	B6	Syracuse Hancock Intl	43.11119	-76.10631	421	-5	A	America/New_York
2013	9	30	22	JFK	BUF	N281JB	B6	Buffalo Niagara Intl	42.94053	-78.73217	724	-5	A	America/New_York
2013	9	30	22	JFK	ROC	N346JB	B6	Greater Rochester Intl	43.11887	-77.67239	559	-5	A	America/New_York
2013	9	30	22	JFK	BOS	N565JB	B6	General Edward Lawrence Logan Intl	42.36435	-71.00518	19	-5	A	America/New_York
2013	9	30	23	JFK	PSE	N516JB	B6	NA	NA	NA	NA	NA	NA	NA
2013	9	30	18	LGA	BNA	N740EV	EV	Nashville Intl	36.12447	-86.67819	599	-6	A	America/Chicago
2013	9	30	14	JFK	DCA	NA	9E	Ronald Reagan Washington Natl	38.85208	-77.03772	15	-5	A	America/New_York
2013	9	30	22	LGA	SYR	NA	9E	Syracuse Hancock Intl	43.11119	-76.10631	421	-5	A	America/New_York
2013	9	30	12	LGA	BNA	N535MQ	MQ	Nashville Intl	36.12447	-86.67819	599	-6	A	America/Chicago
2013	9	30	11	LGA	CLE	N511MQ	MQ	Cleveland Hopkins Intl	41.41169	-81.84979	791	-5	A	America/New_York
2013	9	30	8	LGA	RDU	N839MQ	MQ	Raleigh Durham Intl	35.87764	-78.78747	435	-5	A	America/New_York

In [21]:

            
                Copied!
                
flights2 %>% 
  left_join(airports, c("origin" = "faa"))

flights2 %>% 
  left_join(airports, c("origin" = "faa"))

A tibble: 336776 × 15
year	month	day	hour	origin	dest	tailnum	carrier	name	lat	lon	alt	tz	dst	tzone
<int>	<int>	<int>	<dbl>	<chr>	<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<chr>	<chr>
2013	1	1	5	EWR	IAH	N14228	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	5	LGA	IAH	N24211	UA	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	5	JFK	MIA	N619AA	AA	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	5	JFK	BQN	N804JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	LGA	ATL	N668DN	DL	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	5	EWR	ORD	N39463	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	EWR	FLL	N516JB	B6	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	LGA	IAD	N829AS	EV	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	6	JFK	MCO	N593JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	LGA	ORD	N3ALAA	AA	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	6	JFK	PBI	N793JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	JFK	TPA	N657JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	JFK	LAX	N29129	UA	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	EWR	SFO	N53441	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	LGA	DFW	N3DUAA	AA	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	5	JFK	BOS	N708JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	EWR	LAS	N76515	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	LGA	FLL	N595JB	B6	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	6	LGA	ATL	N542MQ	MQ	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	6	EWR	PBI	N644JB	B6	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	LGA	MSP	N971DL	DL	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	6	LGA	DTW	N730MQ	MQ	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	1	1	6	EWR	MIA	N633AA	AA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	JFK	ATL	N3739P	DL	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	EWR	MIA	N53442	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	EWR	ORD	N9EAMQ	MQ	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	1	1	6	JFK	SFO	N532UA	UA	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	JFK	RSW	N635JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	JFK	SJU	N794JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	1	1	6	EWR	ATL	N326NB	DL	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	21	LGA	CHO	N712EV	EV	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	21	EWR	CLT	N16546	EV	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	9	30	21	JFK	DEN	N807JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	20	LGA	RIC	N751EV	EV	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	21	JFK	DCA	N807MQ	MQ	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	21	JFK	LAX	N335AA	AA	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	21	EWR	PWM	N12957	EV	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	9	30	21	JFK	SJU	N633JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	21	LGA	FLL	N627JB	B6	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	21	EWR	BOS	N813UA	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	9	30	21	EWR	MHT	N10575	EV	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	9	30	18	JFK	BUF	N906XJ	9E	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	LGA	BGR	N722EV	EV	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	21	LGA	BNA	N532MQ	MQ	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	20	EWR	STL	N12145	EV	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	9	30	22	JFK	PWM	N193JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	21	EWR	SFO	N578UA	UA	Newark Liberty Intl	40.69250	-74.16867	18	-5	A	America/New_York
2013	9	30	20	JFK	MCO	N804JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	JFK	BTV	N318JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	JFK	SYR	N354JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	JFK	BUF	N281JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	JFK	ROC	N346JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	JFK	BOS	N565JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	23	JFK	PSE	N516JB	B6	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	18	LGA	BNA	N740EV	EV	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	14	JFK	DCA	NA	9E	John F Kennedy Intl	40.63975	-73.77893	13	-5	A	America/New_York
2013	9	30	22	LGA	SYR	NA	9E	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	12	LGA	BNA	N535MQ	MQ	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	11	LGA	CLE	N511MQ	MQ	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York
2013	9	30	8	LGA	RDU	N839MQ	MQ	La Guardia	40.77725	-73.87261	22	-5	A	America/New_York

Exercises¶

Compute the average delay by destination, then join on the airports data frame so you can show the spatial distribution of delays. Here's an easy way to draw a map of the United States:
```
airports %>%
  semi_join(flights, c("faa" = "dest")) %>%
  ggplot(aes(lon, lat)) +
    borders("state") +
    geom_point() +
    coord_quickmap()
```
(Don't worry if you don't understand what semi_join() does --- you'll learn about it next.)

You might want to use the size or colour of the points to display the average delay for each airport.

Add the location of the origin and destination (i.e. the lat and lon) to flights.
Is there a relationship between the age of a plane and its delays?
What weather conditions make it more likely to see a delay?
What happened on June 13 2013? Display the spatial pattern of delays, and then use Google to cross-reference with the weather.

Other implementations¶

base::merge() can perform all four types of mutating join:

dplyr	merge
`inner_join(x, y)`	`merge(x, y)`
`left_join(x, y)`	`merge(x, y, all.x = TRUE)`
`right_join(x, y)`	`merge(x, y, all.y = TRUE)`,
`full_join(x, y)`	`merge(x, y, all.x = TRUE, all.y = TRUE)`

The advantages of the specific dplyr verbs is that they more clearly convey the intent of your code: the difference between the joins is really important but concealed in the arguments of merge(). dplyr's joins are considerably faster and don't mess with the order of the rows.

SQL is the inspiration for dplyr's conventions, so the translation is straightforward:

dplyr	SQL
`inner_join(x, y, by = "z")`	`SELECT * FROM x INNER JOIN y USING (z)`
`left_join(x, y, by = "z")`	`SELECT * FROM x LEFT OUTER JOIN y USING (z)`
`right_join(x, y, by = "z")`	`SELECT * FROM x RIGHT OUTER JOIN y USING (z)`
`full_join(x, y, by = "z")`	`SELECT * FROM x FULL OUTER JOIN y USING (z)`

Note that "INNER" and "OUTER" are optional, and often omitted.

Joining different variables between the tables, e.g. inner_join(x, y, by = c("a" = "b")) uses a slightly different syntax in SQL: SELECT * FROM x INNER JOIN y ON x.a = y.b. As this syntax suggests, SQL supports a wider range of join types than dplyr because you can connect the tables using constraints other than equality (sometimes called non-equijoins).

Filtering joins¶

Filtering joins match observations in the same way as mutating joins, but affect the observations, not the variables. There are two types:

semi_join(x, y) keeps all observations in x that have a match in y.
anti_join(x, y) drops all observations in x that have a match in y.

Semi-joins are useful for matching filtered summary tables back to the original rows. For example, imagine you've found the top ten most popular destinations:

In [22]:

            
                Copied!
                
top_dest <- flights %>%
  count(dest, sort = TRUE) %>%
  head(10)
top_dest
top_dest <- flights %>%
  count(dest, sort = TRUE) %>%
  head(10)
top_dest

A tibble: 10 × 2
dest	n
<chr>	<int>
ORD	17283
ATL	17215
LAX	16174
BOS	15508
MCO	14082
CLT	14064
SFO	13331
FLL	12055
MIA	11728
DCA	9705

Now you want to find each flight that went to one of those destinations. You could construct a filter yourself:

In [23]:

            
                Copied!
                
flights %>% 
  filter(dest %in% top_dest$dest)
flights %>% 
  filter(dest %in% top_dest$dest)

A tibble: 141145 × 19
year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
2013	1	1	623	610	13	920	915	5	AA	1837	N3EMAA	LGA	MIA	153	1096	6	10	2013-01-01 06:00:00
2013	1	1	628	630	-2	1016	947	29	UA	1665	N33289	EWR	LAX	366	2454	6	30	2013-01-01 06:00:00
2013	1	1	629	630	-1	824	810	14	AA	303	N3CYAA	LGA	ORD	140	733	6	30	2013-01-01 06:00:00
2013	1	1	629	630	-1	824	833	-9	US	1019	N426US	EWR	CLT	91	529	6	30	2013-01-01 06:00:00
2013	1	1	637	645	-8	930	935	-5	B6	389	N709JB	LGA	MCO	144	950	6	45	2013-01-01 06:00:00
2013	1	1	639	640	-1	739	749	-10	B6	1002	N805JB	JFK	BOS	41	187	6	40	2013-01-01 06:00:00
2013	1	1	643	645	-2	837	848	-11	US	926	N178US	EWR	CLT	91	529	6	45	2013-01-01 06:00:00
2013	1	1	644	636	8	931	940	-9	UA	1701	N75435	EWR	FLL	151	1065	6	36	2013-01-01 06:00:00
2013	1	1	655	700	-5	1037	1045	-8	DL	1865	N705TW	JFK	SFO	362	2586	7	0	2013-01-01 07:00:00
2013	1	1	655	700	-5	1002	1020	-18	DL	2003	N997DL	LGA	MIA	161	1096	7	0	2013-01-01 07:00:00
2013	1	1	656	700	-4	854	850	4	AA	305	N4WNAA	LGA	ORD	143	733	7	0	2013-01-01 07:00:00
2013	1	1	656	659	-3	949	959	-10	AA	1815	N5FMAA	JFK	MCO	142	944	6	59	2013-01-01 06:00:00
2013	1	1	657	700	-3	959	1013	-14	DL	1879	N318NB	LGA	FLL	164	1076	7	0	2013-01-01 07:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	2017	2022	-5	2132	2155	-23	B6	105	N298JB	JFK	ORD	112	740	20	22	2013-09-30 20:00:00
2013	9	30	2017	2017	0	2253	2320	-27	UA	771	N510UA	JFK	LAX	304	2475	20	17	2013-09-30 20:00:00
2013	9	30	2022	2025	-3	2127	2140	-13	AA	2314	N3CUAA	JFK	BOS	37	187	20	25	2013-09-30 20:00:00
2013	9	30	2023	2025	-2	2315	2350	-35	B6	915	N712JB	JFK	SFO	327	2586	20	25	2013-09-30 20:00:00
2013	9	30	2026	2028	-2	2317	2340	-23	UA	1680	N31412	EWR	MIA	141	1085	20	28	2013-09-30 20:00:00
2013	9	30	2028	1910	78	2255	2215	40	AA	21	N338AA	JFK	LAX	294	2475	19	10	2013-09-30 19:00:00
2013	9	30	2030	2045	-15	2141	2153	-12	B6	2680	N266JB	EWR	BOS	38	200	20	45	2013-09-30 20:00:00
2013	9	30	2030	2035	-5	2324	2340	-16	UA	374	N484UA	EWR	FLL	141	1065	20	35	2013-09-30 20:00:00
2013	9	30	2032	2045	-13	2147	2225	-38	AA	371	N434AA	LGA	ORD	105	733	20	45	2013-09-30 20:00:00
2013	9	30	2035	2035	0	2142	2211	-29	9E	3395	N602LR	JFK	DCA	43	213	20	35	2013-09-30 20:00:00
2013	9	30	2041	2045	-4	2147	2208	-21	DL	985	N359NB	JFK	BOS	37	187	20	45	2013-09-30 20:00:00
2013	9	30	2048	2100	-12	2147	2214	-27	US	2197	N722US	LGA	DCA	39	214	21	0	2013-09-30 21:00:00
2013	9	30	2050	2100	-10	2312	2344	-32	B6	499	N606JB	LGA	MCO	122	950	21	0	2013-09-30 21:00:00
2013	9	30	2051	2054	-3	2348	2359	-11	B6	523	N605JB	JFK	LAX	304	2475	20	54	2013-09-30 20:00:00
2013	9	30	2052	2059	-7	2315	2358	-43	UA	1416	N68453	EWR	MCO	123	937	20	59	2013-09-30 20:00:00
2013	9	30	2053	1815	158	2310	2054	136	EV	5292	N600QX	EWR	ATL	91	746	18	15	2013-09-30 18:00:00
2013	9	30	2054	2100	-6	2240	2245	-5	MQ	3384	N528MQ	LGA	CLT	70	544	21	0	2013-09-30 21:00:00
2013	9	30	2055	2059	-4	2158	2211	-13	UA	1066	N16701	EWR	BOS	40	200	20	59	2013-09-30 20:00:00
2013	9	30	2057	2100	-3	2149	2211	-22	US	2164	N953UW	LGA	BOS	35	184	21	0	2013-09-30 21:00:00
2013	9	30	2121	2100	21	2349	14	-25	DL	2363	N193DN	JFK	LAX	296	2475	21	0	2013-09-30 21:00:00
2013	9	30	2122	2127	-5	2226	2237	-11	EV	4372	N14902	EWR	DCA	35	199	21	27	2013-09-30 21:00:00
2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00

But it's difficult to extend that approach to multiple variables. For example, imagine that you'd found the 10 days with highest average delays. How would you construct the filter statement that used year, month, and day to match it back to flights?

Instead you can use a semi-join, which connects the two tables like a mutating join, but instead of adding new columns, only keeps the rows in x that have a match in y:

In [24]:

            
                Copied!
                
flights %>% 
  semi_join(top_dest)
flights %>% 
  semi_join(top_dest)

Joining, by = "dest"

A tibble: 141145 × 19
year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
2013	1	1	623	610	13	920	915	5	AA	1837	N3EMAA	LGA	MIA	153	1096	6	10	2013-01-01 06:00:00
2013	1	1	628	630	-2	1016	947	29	UA	1665	N33289	EWR	LAX	366	2454	6	30	2013-01-01 06:00:00
2013	1	1	629	630	-1	824	810	14	AA	303	N3CYAA	LGA	ORD	140	733	6	30	2013-01-01 06:00:00
2013	1	1	629	630	-1	824	833	-9	US	1019	N426US	EWR	CLT	91	529	6	30	2013-01-01 06:00:00
2013	1	1	637	645	-8	930	935	-5	B6	389	N709JB	LGA	MCO	144	950	6	45	2013-01-01 06:00:00
2013	1	1	639	640	-1	739	749	-10	B6	1002	N805JB	JFK	BOS	41	187	6	40	2013-01-01 06:00:00
2013	1	1	643	645	-2	837	848	-11	US	926	N178US	EWR	CLT	91	529	6	45	2013-01-01 06:00:00
2013	1	1	644	636	8	931	940	-9	UA	1701	N75435	EWR	FLL	151	1065	6	36	2013-01-01 06:00:00
2013	1	1	655	700	-5	1037	1045	-8	DL	1865	N705TW	JFK	SFO	362	2586	7	0	2013-01-01 07:00:00
2013	1	1	655	700	-5	1002	1020	-18	DL	2003	N997DL	LGA	MIA	161	1096	7	0	2013-01-01 07:00:00
2013	1	1	656	700	-4	854	850	4	AA	305	N4WNAA	LGA	ORD	143	733	7	0	2013-01-01 07:00:00
2013	1	1	656	659	-3	949	959	-10	AA	1815	N5FMAA	JFK	MCO	142	944	6	59	2013-01-01 06:00:00
2013	1	1	657	700	-3	959	1013	-14	DL	1879	N318NB	LGA	FLL	164	1076	7	0	2013-01-01 07:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	2017	2022	-5	2132	2155	-23	B6	105	N298JB	JFK	ORD	112	740	20	22	2013-09-30 20:00:00
2013	9	30	2017	2017	0	2253	2320	-27	UA	771	N510UA	JFK	LAX	304	2475	20	17	2013-09-30 20:00:00
2013	9	30	2022	2025	-3	2127	2140	-13	AA	2314	N3CUAA	JFK	BOS	37	187	20	25	2013-09-30 20:00:00
2013	9	30	2023	2025	-2	2315	2350	-35	B6	915	N712JB	JFK	SFO	327	2586	20	25	2013-09-30 20:00:00
2013	9	30	2026	2028	-2	2317	2340	-23	UA	1680	N31412	EWR	MIA	141	1085	20	28	2013-09-30 20:00:00
2013	9	30	2028	1910	78	2255	2215	40	AA	21	N338AA	JFK	LAX	294	2475	19	10	2013-09-30 19:00:00
2013	9	30	2030	2045	-15	2141	2153	-12	B6	2680	N266JB	EWR	BOS	38	200	20	45	2013-09-30 20:00:00
2013	9	30	2030	2035	-5	2324	2340	-16	UA	374	N484UA	EWR	FLL	141	1065	20	35	2013-09-30 20:00:00
2013	9	30	2032	2045	-13	2147	2225	-38	AA	371	N434AA	LGA	ORD	105	733	20	45	2013-09-30 20:00:00
2013	9	30	2035	2035	0	2142	2211	-29	9E	3395	N602LR	JFK	DCA	43	213	20	35	2013-09-30 20:00:00
2013	9	30	2041	2045	-4	2147	2208	-21	DL	985	N359NB	JFK	BOS	37	187	20	45	2013-09-30 20:00:00
2013	9	30	2048	2100	-12	2147	2214	-27	US	2197	N722US	LGA	DCA	39	214	21	0	2013-09-30 21:00:00
2013	9	30	2050	2100	-10	2312	2344	-32	B6	499	N606JB	LGA	MCO	122	950	21	0	2013-09-30 21:00:00
2013	9	30	2051	2054	-3	2348	2359	-11	B6	523	N605JB	JFK	LAX	304	2475	20	54	2013-09-30 20:00:00
2013	9	30	2052	2059	-7	2315	2358	-43	UA	1416	N68453	EWR	MCO	123	937	20	59	2013-09-30 20:00:00
2013	9	30	2053	1815	158	2310	2054	136	EV	5292	N600QX	EWR	ATL	91	746	18	15	2013-09-30 18:00:00
2013	9	30	2054	2100	-6	2240	2245	-5	MQ	3384	N528MQ	LGA	CLT	70	544	21	0	2013-09-30 21:00:00
2013	9	30	2055	2059	-4	2158	2211	-13	UA	1066	N16701	EWR	BOS	40	200	20	59	2013-09-30 20:00:00
2013	9	30	2057	2100	-3	2149	2211	-22	US	2164	N953UW	LGA	BOS	35	184	21	0	2013-09-30 21:00:00
2013	9	30	2121	2100	21	2349	14	-25	DL	2363	N193DN	JFK	LAX	296	2475	21	0	2013-09-30 21:00:00
2013	9	30	2122	2127	-5	2226	2237	-11	EV	4372	N14902	EWR	DCA	35	199	21	27	2013-09-30 21:00:00
2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00

Graphically, a semi-join looks like this:

Only the existence of a match is important; it doesn't matter which observation is matched. This means that filtering joins never duplicate rows like mutating joins do:

The inverse of a semi-join is an anti-join. An anti-join keeps the rows that don't have a match:

Anti-joins are useful for diagnosing join mismatches. For example, when connecting flights and planes, you might be interested to know that there are many flights that don't have a match in planes:

In [25]:

            
                Copied!
                
flights %>%
  anti_join(planes, by = "tailnum") %>%
  count(tailnum, sort = TRUE)
flights %>%
  anti_join(planes, by = "tailnum") %>%
  count(tailnum, sort = TRUE)

A tibble: 722 × 2
tailnum	n
<chr>	<int>
NA	2512
N725MQ	575
N722MQ	513
N723MQ	507
N713MQ	483
N735MQ	396
N0EGMQ	371
N534MQ	364
N542MQ	363
N531MQ	349
N6EAMQ	348
N528MQ	346
N532MQ	340
N523MQ	334
N738MQ	333
N720MQ	331
N721MQ	328
N518MQ	319
N527MQ	317
N525MQ	314
N520MQ	306
N530MQ	302
N522MQ	300
N510MQ	299
N8EGMQ	299
N508MQ	298
N526MQ	297
N521MQ	296
N537MQ	289
N539MQ	288
⋮	⋮
N5ESAA	2
N628AW	2
N646MQ	2
N7BGAA	2
N14628	1
N32626	1
N3LDAA	1
N3LFAA	1
N451UW	1
N456UW	1
N502SW	1
N509AA	1
N5DJAA	1
N5ECAA	1
N5ERAA	1
N5FCAA	1
N5FDAA	1
N647MQ	1
N7AAAA	1
N7ACAA	1
N7AEAA	1
N7ALAA	1
N7ASAA	1
N7AXAA	1
N7AYAA	1
N7BFAA	1
N7BKAA	1
N7BMAA	1
N7BVAA	1
N7CAAA	1

Exercises¶

What does it mean for a flight to have a missing tailnum? What do the tail numbers that don't have a matching record in planes have in common? (Hint: one variable explains ~90% of the problems.)
Filter flights to only show flights with planes that have flown at least 100 flights.
Combine fueleconomy::vehicles and fueleconomy::common to find only the records for the most common models.
Find the 48 hours (over the course of the whole year) that have the worst delays. Cross-reference it with the weather data. Can you see any patterns?
What does anti_join(flights, airports, by = c("dest" = "faa")) tell you? What does anti_join(airports, flights, by = c("faa" = "dest")) tell you?
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 above.

Join problems¶

The data you've been working with in this chapter has been cleaned up so that you'll have as few problems as possible. Your own data is unlikely to be so nice, so there are a few things that you should do with your own data to make your joins go smoothly.

Start by identifying the variables that form the primary key in each table. You should usually do this based on your understanding of the data, not empirically by looking for a combination of variables that give a unique identifier. If you just look for variables without thinking about what they mean, you might get (un)lucky and find a combination that's unique in your current data but the relationship might not be true in general.

For example, the altitude and longitude uniquely identify each airport, but they are not good identifiers!

In [26]:

            
                Copied!
                
airports %>% count(alt, lon) %>% filter(n > 1)
airports %>% count(alt, lon) %>% filter(n > 1)

A tibble: 0 × 3
alt	lon	n
<dbl>	<dbl>	<int>

Check that none of the variables in the primary key are missing. If a value is missing then it can't identify an observation!
Check that your foreign keys match primary keys in another table. The best way to do this is with an anti_join(). It's common for keys not to match because of data entry errors. Fixing these is often a lot of work.

If you do have missing keys, you'll need to be thoughtful about your use of inner vs. outer joins, carefully considering whether or not you want to drop rows that don't have a match.

Be aware that simply checking the number of rows before and after the join is not sufficient to ensure that your join has gone smoothly. If you have an inner join with duplicate keys in both tables, you might get unlucky as the number of dropped rows might exactly equal the number of duplicated rows!

Set operations¶

The final type of two-table verb are the set operations. Generally, I use these the least frequently, but they are occasionally useful when you want to break a single complex filter into simpler pieces. All these operations work with a complete row, comparing the values of every variable. These expect the x and y inputs to have the same variables, and treat the observations like sets:

intersect(x, y): return only observations in both x and y.
union(x, y): return unique observations in x and y.
setdiff(x, y): return observations in x, but not in y.

Given this simple data:

In [27]:

            
                Copied!
                
                    
                    
                
                

        
df1 <- tribble(
  ~x, ~y,
   1,  1,
   2,  1
)
df2 <- tribble(
  ~x, ~y,
   1,  1,
   1,  2
)
df1 <- tribble(
  ~x, ~y,
   1,  1,
   2,  1
)
df2 <- tribble(
  ~x, ~y,
   1,  1,
   1,  2
)

The four possibilities are:

In [28]:

            
                Copied!
                
intersect(df1, df2)
intersect(df1, df2)

A tibble: 1 × 2
x	y
<dbl>	<dbl>
1	1

In [29]:

            
                Copied!
                
# Note that we get 3 rows, not 4
union(df1, df2)
# Note that we get 3 rows, not 4
union(df1, df2)

A tibble: 3 × 2
x	y
<dbl>	<dbl>
1	1
2	1
1	2

In [30]:

            
                Copied!
                
setdiff(df1, df2)
setdiff(df1, df2)

A tibble: 1 × 2
x	y
<dbl>	<dbl>
2	1

In [31]:

            
                Copied!
                
setdiff(df2, df1)
setdiff(df2, df1)

A tibble: 1 × 2
x	y
<dbl>	<dbl>
1	2