Joining Data

Data often lives in several tables: orders in one, customers in another. A join combines them by matching a key column that both share.

library(dplyr, warn.conflicts = FALSE)

customers <- tibble(
  customer_id = c(1, 2, 3),
  name = c("Ada", "Linus", "Grace")
)

orders <- tibble(
  order_id = c(101, 102, 103, 104),
  customer_id = c(1, 1, 3, 4),
  total = c(120, 45, 300, 80)
)

orders |> left_join(customers, by = "customer_id")

# A tibble: 4 × 4
  order_id customer_id total name
     <dbl>       <dbl> <dbl> <chr>
1      101           1   120 Ada
2      102           1    45 Ada
3      103           3   300 Grace
4      104           4    80 <NA>

left_join keeps every row of the left table (orders), adding matching columns from the right. Order 104 belongs to customer 4, who isn't in customers, so name is NA.

The main joins

library(dplyr, warn.conflicts = FALSE)

customers <- tibble(customer_id = c(1, 2, 3), name = c("Ada", "Linus", "Grace"))
orders <- tibble(order_id = c(101, 102, 103, 104), customer_id = c(1, 1, 3, 4))

inner_join(orders, customers, by = "customer_id")
anti_join(customers, orders, by = "customer_id")
semi_join(customers, orders, by = "customer_id")

# A tibble: 3 × 3
  order_id customer_id name
     <dbl>       <dbl> <chr>
1      101           1 Ada
2      102           1 Ada
3      103           3 Grace
# A tibble: 1 × 2
  customer_id name
        <dbl> <chr>
1           2 Linus
# A tibble: 2 × 2
  customer_id name
        <dbl> <chr>
1           1 Ada
2           3 Grace

When key columns have different names, use by = join_by(customer_id == id).

Stacking tables

To combine tables with the same columns (for example, one file per month), stack them with bind_rows().

Common mistakes

• Duplicate keys multiply rows. If a customer appears twice in the right table, each of their orders appears twice after the join. dplyr warns about unexpected many-to-many matches — always check the row count before and after joining.
• Keys with different types (number in one table, text in the other) don't match. Convert them first.