Introduction to joining datasets using dplyr

This document is a minor adaptation of Michael Hallquist’s minor adaptation of Jenny Bryan’s dplyr joins tutorial (with a little help from animations by Garrick Aden-Buie): http://stat545.com/bit001_dplyr-cheatsheet.html. The goal is to develop an intuition of the four major types of two-table join operations: inner, left, right, and full. We’ll also get into using joins to identify areas of match or mismatch between two datasets (using semi- and anti-joins).

First, we import the data into data.frame objects. We’re generating the data from scratch using character strings. But the join functions below will work with other data types (e.g., numeric data).

superheroes <-"
name,     alignment, gender, publisher
Magneto,  bad,       male,   Marvel
Storm,    good,      female, Marvel
Mystique, bad,       female, Marvel
Batman,   good,      male,   DC
Joker,    bad,       male,   DC
Catwoman, bad,       female, DC
Hellboy,  good,      male,   Dark Horse Comics
"


superheroes <- read_csv(superheroes, trim_ws = TRUE, skip = 1) #"trim_ws" eliminates extra spaces in dataset; #skip=1 tells R not to read in the first line of data as data (instead leaving it as column names) 

publishers <- "
publisher, yr_founded
DC,        1934
Marvel,    1939
Image,     1992
"
publishers <- read_csv(publishers, trim_ws = TRUE, skip = 1)

superheroes
name alignment gender publisher
Magneto bad male Marvel
Storm good female Marvel
Mystique bad female Marvel
Batman good male DC
Joker bad male DC
Catwoman bad female DC
Hellboy good male Dark Horse Comics 
publishers
publisher yr_founded
DC 1934
Marvel 1939
Image 1992

Mutating joins: inner, left, right, full

“Mutating” joins combine variables from two datasets on the basis of one or more keys that match between datasets. In the case of these datasets, notice they share the “publisher” column.

NB: By default, dplyr will search for common columns across datsets as the matching keys (natural join). If you want to control the process, specify the key using by.

inner join

Require match in both datasets (non-matching rows are dropped)

For those of you who are visual learners, conceptually, imagine the following two simple datasets:

An inner join combines the two datasets and drops the non-matching rows like so:

Lets try it with our superhero data.

#*NB*: the extra parentheses enclosing the line of code tell R to both save the new dataframe as ijsp AND to print the result simultaneously
(ijsp <- inner_join(x=superheroes, y=publishers))
name alignment gender publisher yr_founded
Magneto bad male Marvel 1939
Storm good female Marvel 1939
Mystique bad female Marvel 1939
Batman good male DC 1934
Joker bad male DC 1934
Catwoman bad female DC 1934

Same idea, just explicit declaration of key (i.e., “publisher”)

inner_join(superheroes, publishers, by="publisher") #note that we've cut the x=, y= as this isn't necessary to specify
name alignment gender publisher yr_founded
Magneto bad male Marvel 1939
Storm good female Marvel 1939
Mystique bad female Marvel 1939
Batman good male DC 1934
Joker bad male DC 1934
Catwoman bad female DC 1934

Notice both Hellboy (from the superheroes dataset) and Image comics (from the publishers dataset) were dropped.

left join

Keep all rows in left-hand ‘x’ dataset (i.e., superheroes). Add columns from publishers where there is a match. Fill in NA for non-matching observations.

(ljsp <- left_join(superheroes, publishers))
name alignment gender publisher yr_founded
Magneto bad male Marvel 1939
Storm good female Marvel 1939
Mystique bad female Marvel 1939
Batman good male DC 1934
Joker bad male DC 1934
Catwoman bad female DC 1934
Hellboy good male Dark Horse Comics NA

right join

Keep all rows in right-hand ‘y’ dataset (i.e., publishers). Add columns from superheroes where there is a match. Fill in NA for non-matching observations.

(superheroes %>% right_join(publishers)->rjsp) #note the shift to using dplyr piping; just another way to do this code
name alignment gender publisher yr_founded
Batman good male DC 1934
Joker bad male DC 1934
Catwoman bad female DC 1934
Magneto bad male Marvel 1939
Storm good female Marvel 1939
Mystique bad female Marvel 1939
NA NA NA Image 1992

full join

Keep all rows in left-hand ‘x’ (superheroes) and right-hand ‘y’ (publishers) datasets. Resulting dataset will have all columns of both datasets, but filling in NA for any non-matches on either side (denoted as blank spaces below).

(superheroes %>% full_join(publishers) -> fjsp)
name alignment gender publisher yr_founded
Magneto bad male Marvel 1939
Storm good female Marvel 1939
Mystique bad female Marvel 1939
Batman good male DC 1934
Joker bad male DC 1934
Catwoman bad female DC 1934
Hellboy good male Dark Horse Comics NA
NA NA NA Image 1992

One-to-many join

Note that when there are non-unique matches, the join adds all possible combinations. This occurs in a one-to-many join.

Let’s say you wanted to see if students’ final grades were associated with how many extra credit points they recieved on multiple assignments during a course.

(extra_credit <- data_frame(x = c("Jeff", "Karla", "Jasmine", "Karla"), z = c(2, 5, 4, 4)))
## Warning: `data_frame()` is deprecated, use `tibble()`.
## This warning is displayed once per session.
x z
Jeff 2
Karla 5
Jasmine 4
Karla 4 
(final_grade <- data_frame(x = c("Jeff", "Karla", "Ping"), y = c(70, 80, 92)))
x y
Jeff 70
Karla 80
Ping 92 
final_grade %>% left_join(extra_credit)
x y z
Jeff 70 2
Karla 80 5
Karla 80 4
Ping 92 NA

Filtering joins: semi_join and anti_join

Filtering joins use specific criteria to identify observations (rows) from one table that exist or don’t exist in another table.

These joins are typically used for diagnosing mismatch between two overlapping datasets.

semi_join

retain observations (rows) in x that match in y

Notice that this is different from the left_join shown above as the data from y is not kept.

semi_join(superheroes, publishers)
name alignment gender publisher
Magneto bad male Marvel
Storm good female Marvel
Mystique bad female Marvel
Batman good male DC
Joker bad male DC
Catwoman bad female DC

Now, observations in publishers that match in superheroes

semi_join(publishers, superheroes)
publisher yr_founded
DC 1934
Marvel 1939

This can be useful if you have a dataset of your data of interest and another dataset that indicates which of your participants/observations you want to remove or filter out.

anti_join

observations in x that are not matched in y Note that this is similar to setdiff in base R

Observations in superheroes that don’t match in publishers

anti_join(superheroes, publishers)
name alignment gender publisher
Hellboy good male Dark Horse Comics

Now, observations in publishers that don’t match in superheroes

publishers %>% anti_join(superheroes)
publisher yr_founded
Image 1992

This can be useful if you are trying to identify extra participants/observations that may have snuck into one dataset (x) or been deleted in another (y).

Joining multiple datasets

Joining can be done repeatedly across multiple datasets. The following code, for instance, joins datasets two at a time from left to right in the list. The result of a two-table join becomes the ‘x’ dataset for the next join of a new dataset ‘y’.

df1 <- data.frame(id=1:10, x=rnorm(10), y=runif(10))
df2 <- data.frame(id=1:11, z=rnorm(11), a=runif(11))
df3 <- data.frame(id=2:10, b=rnorm(9), c=runif(9))

dftemp <- full_join(df1,df2)
dffinal <- full_join(dftemp,df3)

#alternative way to combine:
Reduce(function(...) { full_join(...) }, list(df1, df2, df3))
id x y z a b c
1 0.3276807 0.7646054 0.2964507 0.5742303 NA NA
2 0.4889132 0.9550850 0.3612697 0.0249259 0.8945863 0.7075377
3 0.0861085 0.6342025 0.7875112 0.8117101 -1.5767115 0.1824528
4 2.2449579 0.5770883 0.4870670 0.2037343 -1.0912737 0.2299864
5 0.5829925 0.8750095 -0.4561240 0.5817179 0.0970905 0.3350578
6 -0.0877355 0.4725206 -0.0545536 0.5571138 1.3192575 0.4462988
7 -0.3427714 0.3876312 0.4920803 0.1397631 -0.5883248 0.0350247
8 -1.2356293 0.3943900 0.6541769 0.5905771 2.4382461 0.9070495
9 0.2406862 0.7125592 -0.4482395 0.1206362 0.6497620 0.9832816
10 0.2420339 0.1053874 0.5375871 0.7008227 -0.1886147 0.6633109
11 NA NA -0.2702798 0.3510085 NA NA

Alternative using pipeline (less extensible)

(mergedf <- df1 %>% full_join(df2) %>% full_join(df3))
id x y z a b c
1 0.3276807 0.7646054 0.2964507 0.5742303 NA NA
2 0.4889132 0.9550850 0.3612697 0.0249259 0.8945863 0.7075377
3 0.0861085 0.6342025 0.7875112 0.8117101 -1.5767115 0.1824528
4 2.2449579 0.5770883 0.4870670 0.2037343 -1.0912737 0.2299864
5 0.5829925 0.8750095 -0.4561240 0.5817179 0.0970905 0.3350578
6 -0.0877355 0.4725206 -0.0545536 0.5571138 1.3192575 0.4462988
7 -0.3427714 0.3876312 0.4920803 0.1397631 -0.5883248 0.0350247
8 -1.2356293 0.3943900 0.6541769 0.5905771 2.4382461 0.9070495
9 0.2406862 0.7125592 -0.4482395 0.1206362 0.6497620 0.9832816
10 0.2420339 0.1053874 0.5375871 0.7008227 -0.1886147 0.6633109
11 NA NA -0.2702798 0.3510085 NA NA