Navigate
Courses Tutorials Practice Pricing Tools Upgrade
Lesson 1 of 3

Joining Tables

You can already wrangle a single table: filter it, add columns, summarise it. But real data almost never arrives in one table. Maya's bakery keeps the week's sales in one place (what rang up at the till) and a product catalogue in another (each item's price and shelf). To answer something as simple as "how much revenue came from pastries?", she first has to stitch the two together.

That stitching is a join, and it is the whole of this lesson. Below are Maya's two tables. The key that links them is item, the column they share. Click through the join types and watch which rows survive.

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

  • Combine two tables with the mutating joins (inner, left, right, full) and predict what happens to unmatched rows
  • Keep or drop rows by whether a match exists, with the filtering joins (semi, anti)
  • Match on a range instead of an exact key with a non-equi join (join_by())

Prerequisites: you can run R, and you know the dplyr verbs and the pipe from The dplyr Verbs. A tibble and NA (a missing value) are all you need besides.

The key, and the first join

inner_join keeps only the matches

Every join needs a key: a column whose values identify the same thing in both tables. Here the key is item. Wherever the same item appears in both tables, a join can line the two rows up side by side.

Let us build Maya's two tables. Each lesson runs in a fresh R session, so we make the data right here, then never touch the disk again:

RInteractive R
library(dplyr) # the join verbs and the pipe library(tibble) # tibble() # What Maya's till rang up this week, one row per sale: sales <- tibble( item = c("Sourdough", "Bagel", "Croissant", "Pretzel"), units = c(20, 38, 15, 12) ) # Maya's standing catalogue: a price and a shelf for each item: products <- tibble( item = c("Sourdough", "Bagel", "Croissant", "Muffin"), price = c(4.5, 1.5, 4.0, 3.0), category = c("Bread", "Bread", "Pastry", "Pastry") )

  

  





Look closely and you will spot two odd ones out. Pretzel was a one-off special Maya tried, so it sold but is not in the catalogue. Muffin is in the catalogue but did not sell this week. Three items, Sourdough, Bagel and Croissant, appear in both. Those three are the matches.



The simplest join, inner_join(), keeps only the matches. It walks the left table (sales), and for every row whose key is also in the right table (products), it glues the two rows together; rows with no match on the other side are dropped.




  

    
RInteractive R

    
inner_join(sales, products, by = "item")
#> # A tibble: 3 x 4
#>   item      units price category
#>   <chr>     <dbl> <dbl> <chr>
#> 1 Sourdough    20   4.5 Bread
#> 2 Bagel        38   1.5 Bread
#> 3 Croissant    15   4   Pastry

    

      
      
    

    

  

  





Three rows, four columns: the two from sales plus the two new ones from products. Pretzel and Muffin both vanished, because neither had a partner on the other side. The by = "item" argument names the key; leave it out and dplyr guesses it from the column names the two tables share (and tells you which it picked).