Joining tables

Combine information from two data frames through a shared key

Understand keys and the mutating joins left_join(), inner_join(), full_join(), right_join(), the filtering joins semi_join() and anti_join(), join_by() for different key names, duplicate keys and the many-to-many warning, and base R merge().

Level 1 · Foundations ⏱ ~60 min Prerequisites: Data manipulation with dplyr

Learning objectives

By the end of this lesson you will be able to:

identify the key that links two tables and check whether it is unique;
choose between left_join(), inner_join(), full_join() and right_join();
use semi_join() and anti_join() to keep or find rows with or without a match;
join on columns with different names using join_by(a == b);
predict and check the number of rows after a join, and handle duplicate keys and the many-to-many warning;
translate each join into base R merge().

library(dplyr)
library(tibble)

1. Why join?

Real data rarely arrive in a single table. A hospital keeps patients in one table and visits in another; a shop keeps products in one table and sales in another. Information about one statistical unit is spread over several tables, and each table may have a different unit (a patient vs a visit).

To analyse it we must join the tables: for each row of one table, look up the matching rows of the other, using a column that both tables share — the key.

2. Toy example: film-makers and films

producers <- tibble(
  surname     = c("Spielberg", "Scorsese", "Hitchcock", "Tarantino",
                  "Polanski", "Lucas"),
  nationality = c("US", "US", "UK", "US", "Poland", "US")
)

movies <- tibble(
  surname = c("Spielberg", "Scorsese", "Hitchcock", "Hitchcock",
              "Spielberg", "Tarantino", "Polanski", "Kubrick"),
  title   = c("Super 8", "Taxi Driver", "Psycho", "North by Northwest",
              "Catch Me If You Can", "Reservoir Dogs", "Chinatown",
              "The Shining")
)

producers

#> # A tibble: 6 × 2
#>   surname   nationality
#>   <chr>     <chr>      
#> 1 Spielberg US         
#> 2 Scorsese  US         
#> 3 Hitchcock UK         
#> 4 Tarantino US         
#> 5 Polanski  Poland     
#> 6 Lucas     US

movies

#> # A tibble: 8 × 2
#>   surname   title              
#>   <chr>     <chr>              
#> 1 Spielberg Super 8            
#> 2 Scorsese  Taxi Driver        
#> 3 Hitchcock Psycho             
#> 4 Hitchcock North by Northwest 
#> 5 Spielberg Catch Me If You Can
#> 6 Tarantino Reservoir Dogs     
#> 7 Polanski  Chinatown          
#> 8 Kubrick   The Shining

The key is surname. Notice three things that make the example realistic:

in producers each surname appears once (the key is unique: one row per film-maker);
in movies a surname can appear several times (one row per film);
“Lucas” has no film in movies, and “Kubrick” has no row in producers.

Is the key unique?

Always check before joining. count() + filter() lists duplicated keys:

producers |> count(surname) |> filter(n > 1)

#> # A tibble: 0 × 2
#> # ℹ 2 variables: surname <chr>, n <int>

movies    |> count(surname) |> filter(n > 1)

#> # A tibble: 2 × 2
#>   surname       n
#>   <chr>     <int>
#> 1 Hitchcock     2
#> 2 Spielberg     2

producers has no duplicates; movies has two film-makers with two films each. The relationship is one-to-many: one producer, many films.

3. Mutating joins: adding columns

A mutating join adds the columns of y to x, matching rows by the key. The four types differ only in what happens to rows without a match.

Join	Rows of `x` without a match	Rows of `y` without a match
`inner_join(x, y)`	dropped	dropped
`left_join(x, y)`	kept (new columns `NA`)	dropped
`right_join(x, y)`	dropped	kept (columns of `x` `NA`)
`full_join(x, y)`	kept	kept

`inner_join()`: only matches

inner <- producers |> inner_join(movies, by = join_by(surname))
inner

#> # A tibble: 7 × 3
#>   surname   nationality title              
#>   <chr>     <chr>       <chr>              
#> 1 Spielberg US          Super 8            
#> 2 Spielberg US          Catch Me If You Can
#> 3 Scorsese  US          Taxi Driver        
#> 4 Hitchcock UK          Psycho             
#> 5 Hitchcock UK          North by Northwest 
#> 6 Tarantino US          Reservoir Dogs     
#> 7 Polanski  Poland      Chinatown

Lucas (no film) and Kubrick (no producer row) disappear. Each Hitchcock and Spielberg film gets its own row, with the nationality repeated.

`left_join()`: keep everything in `x`

producers |> left_join(movies, by = join_by(surname))

#> # A tibble: 8 × 3
#>   surname   nationality title              
#>   <chr>     <chr>       <chr>              
#> 1 Spielberg US          Super 8            
#> 2 Spielberg US          Catch Me If You Can
#> 3 Scorsese  US          Taxi Driver        
#> 4 Hitchcock UK          Psycho             
#> 5 Hitchcock UK          North by Northwest 
#> 6 Tarantino US          Reservoir Dogs     
#> 7 Polanski  Poland      Chinatown          
#> 8 Lucas     US          <NA>

All producers are kept; Lucas gets NA as title. left_join() is the join you will use most: you have a main table and want to add information to it, without losing any of its rows.

`right_join()` and `full_join()`

producers |> right_join(movies, by = join_by(surname))

#> # A tibble: 8 × 3
#>   surname   nationality title              
#>   <chr>     <chr>       <chr>              
#> 1 Spielberg US          Super 8            
#> 2 Spielberg US          Catch Me If You Can
#> 3 Scorsese  US          Taxi Driver        
#> 4 Hitchcock UK          Psycho             
#> 5 Hitchcock UK          North by Northwest 
#> 6 Tarantino US          Reservoir Dogs     
#> 7 Polanski  Poland      Chinatown          
#> 8 Kubrick   <NA>        The Shining

producers |> full_join(movies, by = join_by(surname))

#> # A tibble: 9 × 3
#>   surname   nationality title              
#>   <chr>     <chr>       <chr>              
#> 1 Spielberg US          Super 8            
#> 2 Spielberg US          Catch Me If You Can
#> 3 Scorsese  US          Taxi Driver        
#> 4 Hitchcock UK          Psycho             
#> 5 Hitchcock UK          North by Northwest 
#> 6 Tarantino US          Reservoir Dogs     
#> 7 Polanski  Poland      Chinatown          
#> 8 Lucas     US          <NA>               
#> 9 Kubrick   <NA>        The Shining

right_join(x, y) keeps all rows of y (Kubrick appears with NA nationality); it is the same as left_join(y, x) with the columns in a different order. full_join() keeps everyone.

How to read this output: count the rows

Join	Rows	Why
`inner_join`	7	the 7 films whose maker is in `producers`
`left_join`	8	those 7 + Lucas
`right_join`	8	all 8 films (Kubrick with `NA`)
`full_join`	9	7 + Lucas + Kubrick

Comparing nrow() before and after a join is the quickest sanity check you can do.

4. Filtering joins: keeping rows, not columns

Sometimes you do not want new columns: you want to know which rows have (or do not have) a match.

# producers that have at least one film
producers |> semi_join(movies, by = join_by(surname))

#> # A tibble: 5 × 2
#>   surname   nationality
#>   <chr>     <chr>      
#> 1 Spielberg US         
#> 2 Scorsese  US         
#> 3 Hitchcock UK         
#> 4 Tarantino US         
#> 5 Polanski  Poland

# producers with no film
producers |> anti_join(movies, by = join_by(surname))

#> # A tibble: 1 × 2
#>   surname nationality
#>   <chr>   <chr>      
#> 1 Lucas   US

# films whose maker is missing from producers
movies |> anti_join(producers, by = join_by(surname))

#> # A tibble: 1 × 2
#>   surname title      
#>   <chr>   <chr>      
#> 1 Kubrick The Shining

semi_join() never duplicates rows (Spielberg appears once even though he has two films) and adds no columns. anti_join() is an excellent data-quality tool: before a join, it shows you exactly which keys will fail to match — often because of typos, different capitalisation or trailing spaces (see Strings and dates).

5. Keys with different names: `join_by(a == b)`

Often the same key has different names in the two tables.

movies2 <- movies |> rename(director = surname)

producers |> inner_join(movies2, by = join_by(surname == director))

#> # A tibble: 7 × 3
#>   surname   nationality title              
#>   <chr>     <chr>       <chr>              
#> 1 Spielberg US          Super 8            
#> 2 Spielberg US          Catch Me If You Can
#> 3 Scorsese  US          Taxi Driver        
#> 4 Hitchcock UK          Psycho             
#> 5 Hitchcock UK          North by Northwest 
#> 6 Tarantino US          Reservoir Dogs     
#> 7 Polanski  Poland      Chinatown

Read surname == director as “match surname in the left table with director in the right table”. The result keeps the left name. You can join on several keys at once, e.g. join_by(id, year) or join_by(id == patient_id, year).

Going deeper: by as a character vector

Before dplyr 1.1 joins were written by = "surname" or by = c("surname" = "director"). These still work and you will see them often. join_by() is the modern form, and it also allows non-equality joins such as join_by(id, date >= start) (rolling and interval joins), which are beyond this lesson.

6. Duplicate keys and the many-to-many warning

Suppose someone adds a second row for Hitchcock, who became a US citizen later in life:

producers_dup <- producers |>
  add_row(surname = "Hitchcock", nationality = "US")

producers_dup |> count(surname) |> filter(n > 1)

#> # A tibble: 1 × 2
#>   surname       n
#>   <chr>     <int>
#> 1 Hitchcock     2

Now Hitchcock appears twice in both tables. A join pairs every matching row on the left with every matching row on the right: 2 × 2 = 4 Hitchcock rows.

producers_dup |> inner_join(movies, by = join_by(surname))

#> Warning in inner_join(producers_dup, movies, by = join_by(surname)): Detected an unexpected many-to-many relationship between `x` and `y`.
#> ℹ Row 1 of `x` matches multiple rows in `y`.
#> ℹ Row 3 of `y` matches multiple rows in `x`.
#> ℹ If a many-to-many relationship is expected, set `relationship =
#>   "many-to-many"` to silence this warning.

#> # A tibble: 9 × 3
#>   surname   nationality title              
#>   <chr>     <chr>       <chr>              
#> 1 Spielberg US          Super 8            
#> 2 Spielberg US          Catch Me If You Can
#> 3 Scorsese  US          Taxi Driver        
#> 4 Hitchcock UK          Psycho             
#> 5 Hitchcock UK          North by Northwest 
#> 6 Tarantino US          Reservoir Dogs     
#> 7 Polanski  Poland      Chinatown          
#> 8 Hitchcock US          Psycho             
#> 9 Hitchcock US          North by Northwest

dplyr warns about an unexpected many-to-many relationship, because this is usually a mistake: the rows multiply and every statistic computed afterwards (counts, means, totals) is distorted. What to do:

Fix the data if the duplicate is an error (here: decide which nationality to keep, or keep both in one row).
If many-to-many is really what you want, say so: inner_join(..., relationship = "many-to-many") silences the warning.
If you expect a unique key on one side, make dplyr check it for you:

producers_dup |>
  inner_join(movies, by = join_by(surname), relationship = "one-to-many")

#> Error in `inner_join()`:
#> ! Each row in `y` must match at most 1 row in `x`.
#> ℹ Row 3 of `y` matches multiple rows in `x`.

With relationship = "one-to-many" dplyr throws an error as soon as a key in x is duplicated — the safest option in scripts.

7. A realistic example: adding species information to penguins

penguins stores the species by its common name. A small lookup table adds the scientific name and a label we want to use in plots:

penguins <- as_tibble(penguins)   # shorter printing

species_info <- tibble(
  species    = c("Adelie", "Chinstrap", "Gentoo"),
  scientific = c("Pygoscelis adeliae", "Pygoscelis antarcticus",
                 "Pygoscelis papua")
)

penguins_plus <- penguins |>
  left_join(species_info, by = join_by(species))

nrow(penguins)

#> [1] 344

nrow(penguins_plus)

#> [1] 344

penguins_plus |> count(species, scientific)

#> # A tibble: 3 × 3
#>   species   scientific                 n
#>   <chr>     <chr>                  <int>
#> 1 Adelie    Pygoscelis adeliae       152
#> 2 Chinstrap Pygoscelis antarcticus    68
#> 3 Gentoo    Pygoscelis papua         124

The number of rows is unchanged (344): each penguin matched exactly one species. This is the pattern many-to-one, the most common in practice.

A second very common use is to attach group summaries back to the units. For example, the mean mass of each species:

species_mass <- penguins |>
  summarise(mean_mass = mean(body_mass, na.rm = TRUE), .by = species)

penguins |>
  select(species, body_mass) |>
  left_join(species_mass, by = join_by(species)) |>
  mutate(diff_from_mean = body_mass - mean_mass)

#> # A tibble: 344 × 4
#>    species body_mass mean_mass diff_from_mean
#>    <fct>       <int>     <dbl>          <dbl>
#>  1 Adelie       3750     3701.          49.34
#>  2 Adelie       3800     3701.          99.34
#>  3 Adelie       3250     3701.        -450.7 
#>  4 Adelie         NA     3701.          NA   
#>  5 Adelie       3450     3701.        -250.7 
#>  6 Adelie       3650     3701.         -50.66
#>  7 Adelie       3625     3701.         -75.66
#>  8 Adelie       4675     3701.         974.3 
#>  9 Adelie       3475     3701.        -225.7 
#> 10 Adelie       4250     3701.         549.3 
#> # ℹ 334 more rows

(The same result can be obtained in one step with a grouped mutate(), as shown in Grouped summaries.)

8. Base R: `merge()`

merge() does the same jobs. Its default is an inner join; the all* arguments add unmatched rows.

merge(producers, movies, by = "surname")                # inner

#>     surname nationality               title
#> 1 Hitchcock          UK              Psycho
#> 2 Hitchcock          UK  North by Northwest
#> 3  Polanski      Poland           Chinatown
#> 4  Scorsese          US         Taxi Driver
#> 5 Spielberg          US             Super 8
#> 6 Spielberg          US Catch Me If You Can
#> 7 Tarantino          US      Reservoir Dogs

merge(producers, movies, by = "surname", all.x = TRUE)  # left

#>     surname nationality               title
#> 1 Hitchcock          UK              Psycho
#> 2 Hitchcock          UK  North by Northwest
#> 3     Lucas          US                <NA>
#> 4  Polanski      Poland           Chinatown
#> 5  Scorsese          US         Taxi Driver
#> 6 Spielberg          US             Super 8
#> 7 Spielberg          US Catch Me If You Can
#> 8 Tarantino          US      Reservoir Dogs

merge(producers, movies2, by.x = "surname", by.y = "director")

#>     surname nationality               title
#> 1 Hitchcock          UK              Psycho
#> 2 Hitchcock          UK  North by Northwest
#> 3  Polanski      Poland           Chinatown
#> 4  Scorsese          US         Taxi Driver
#> 5 Spielberg          US             Super 8
#> 6 Spielberg          US Catch Me If You Can
#> 7 Tarantino          US      Reservoir Dogs

dplyr	base R `merge()`
`inner_join(x, y, by = join_by(k))`	`merge(x, y, by = "k")`
`left_join(x, y, by = join_by(k))`	`merge(x, y, by = "k", all.x = TRUE)`
`right_join(x, y, by = join_by(k))`	`merge(x, y, by = "k", all.y = TRUE)`
`full_join(x, y, by = join_by(k))`	`merge(x, y, by = "k", all = TRUE)`
`join_by(a == b)`	`by.x = "a", by.y = "b"`
`semi_join(x, y, ...)`	`x[x$k %in% y$k, ]`
`anti_join(x, y, ...)`	`x[!x$k %in% y$k, ]`

One practical difference: merge() sorts the result by the key, while dplyr joins keep the row order of x.

Common pitfalls

Not checking that the key is unique on the side where it should be. Use count(key) |> filter(n > 1) before joining.
Rows that multiply silently. Always compare nrow() before and after a join; a left_join() should not increase the rows of x if the key of y is unique.
Keys that look equal but are not: "Hitchcock" vs "hitchcock" vs "Hitchcock ". Use anti_join() to find them and clean with stringr.
Keys of different types: a numeric id in one table and a character id in the other cause an error. Convert one of them first.
Using inner_join() when you meant left_join(). Units without a match vanish, and the sample you analyse is no longer the one you think.

9. Check your understanding

Q1. Table x has 100 patients (unique id), table y has 250 visits with id. How many rows can left_join(x, y) have?

At least 100 (every patient is kept) and possibly more: each patient with k visits produces k rows. Patients without visits produce one row with NAs. If all 250 visits belong to patients in x, the result has 250 rows plus one row for each patient with no visit.

Q2. What is the difference between semi_join(x, y) and inner_join(x, y)?

Both keep only the rows of x that have a match. inner_join() adds the columns of y and can duplicate rows of x (one per match); semi_join() adds nothing and never duplicates.

Q3. When does dplyr warn about a many-to-many relationship?

When at least one row of x matches several rows of y and at least one row of y matches several rows of x. It is usually a sign of an unintended duplicate key.

10. Exercises

Interactive (run in your browser)

★ Exercise 1 — left or inner? The table species_info (already created) has only Adelie and Gentoo. Join it to penguins with left_join() and with inner_join(), and compare the number of rows.

a <- penguins |> left_join(species_info, by = join_by(species))
b <- penguins |> inner_join(species_info, by = join_by(species))
nrow(a)   # 344: all penguins, Chinstrap with scientific = NA
nrow(b)   # 276: the 68 Chinstrap penguins are dropped

★★ Exercise 2 — different key names and filtering joins. students has the key student_id, exams has id. (a) Add the student names to exams, keeping only exams of known students. (b) Which students took no exam? (c) Which exam rows belong to an unknown student?

exams |> inner_join(students, by = join_by(id == student_id))
students |> anti_join(exams, by = join_by(student_id == id))
exams |> anti_join(students, by = join_by(id == student_id))

gives 6 rows; (b) Chiara and Eli took no exam; (c) the exam of student 6 has no matching student — a data-quality problem to investigate.

★★★ Exercise 3 — predict the rows. Before running the code, predict how many rows the join returns and whether dplyr will warn. Then run it. Is the result useful?

shops |>
  left_join(weather, by = join_by(city), relationship = "many-to-many")

5 rows (4 for Rome + 1 for Milan), with a many-to-many warning. Here the result is meaningful — every shop gets the weather of every day in its city — so we declare relationship = "many-to-many". If instead we expected one weather row per city, the warning would have revealed a mistake.

Practice on your own computer

★★ Exercise 4 — base R translation. Using producers and movies from section 2, reproduce left_join(), full_join() and anti_join() with base R only. Check with nrow() that you get 8, 9 and 1 rows.

Solution

nrow(merge(producers, movies, by = "surname", all.x = TRUE))   # 8
nrow(merge(producers, movies, by = "surname", all = TRUE))     # 9
producers[!producers$surname %in% movies$surname, ]            # Lucas

★★ Exercise 5 — interpret. A colleague joins a table of 1,000 customers (unique customer_id) with a table of orders and gets 3,412 rows. She then computes mean(age) on the result and reports it as “the mean age of our customers”. What is wrong, and how would you fix it?