Analistable

How to merge data frames in R

By the Analistable team · Updated · 5 min read

In base R, merge(x, y, by = "id") does an inner join; add all.x = TRUE for a left join or all = TRUE for a full join. In dplyr, use left_join(x, y, by = join_by(id)), inner_join, full_join or anti_join. To stack data frames with the same columns, use rbind() or dplyr::bind_rows().

Part of our guide: How to merge data in pandas, R and SQL

Example data

customers <- data.frame(
  customer_id = c("C-101", "C-102", "C-103"),
  name = c("Bakery Lune", "Hart & Co", "Nordic Supply")
)
orders <- data.frame(
  order_id = c("O-9001", "O-9002", "O-9003", "O-9004"),
  customer_id = c("C-101", "C-103", "C-101", "C-104"),
  amount = c(120, 75, 80, 60)
)

Base R: merge()

merge() arguments by join type
JoinCode
Innermerge(orders, customers, by = "customer_id")
Leftmerge(orders, customers, by = "customer_id", all.x = TRUE)
Rightmerge(orders, customers, by = "customer_id", all.y = TRUE)
Fullmerge(orders, customers, by = "customer_id", all = TRUE)
Different key namesmerge(x, y, by.x = "cust", by.y = "customer_id")

merge() sorts the result by the key columns by default; pass sort = FALSE to keep the original order.

dplyr joins

library(dplyr)

orders |> left_join(customers, by = join_by(customer_id))
customers |> anti_join(orders, by = join_by(customer_id))   # never ordered
orders |> inner_join(customers, by = join_by(customer_id),
                     relationship = "many-to-one")

join_by() (dplyr 1.1+) also handles different names (join_by(cust == customer_id)) and inequality joins. relationship = "many-to-one" raises an error if customers has duplicate keys.

left_join result
order_idcustomer_idamountname
O-9001C-101120Bakery Lune
O-9002C-10375Nordic Supply
O-9003C-10180Bakery Lune
O-9004C-10460NA

Stacking data frames

  • rbind(jan, feb) — columns must have the same names (order can differ).
  • dplyr::bind_rows(jan, feb, .id = "source") — fills missing columns with NA and adds a source column.
  • Many files: bind_rows(lapply(list.files("sales", full.names = TRUE), read.csv)).

The same joins in pandas and SQL are mapped side by side in merging data in pandas, R and SQL.

Common problems

Problems when merging in R
SymptomCauseFix
More rows than expectedDuplicate keys in the lookup tableCheck with `anyDuplicated(customers$customer_id)`; set relationship in dplyr
Columns named name.x and name.yBoth tables have a non-key column with the same nameRename first, or set suffixes = c("_order", "_cust")
No matchesKey is a factor in one table and character in the other, or has spacesConvert with as.character() and trimws()
Row order changedmerge() sorts by key by defaultUse sort = FALSE or dplyr joins

Full join and finding unmatched rows

To reconcile both lists at once, use a full join and look for NA on either side. In dplyr:

full <- full_join(orders, customers, by = join_by(customer_id))

filter(full, is.na(name))       # O-9004: customer C-104 not in customers
filter(full, is.na(order_id))   # Hart & Co: no orders

# Or ask each side directly
anti_join(orders, customers, by = join_by(customer_id))
anti_join(customers, orders, by = join_by(customer_id))

The full join returns 5 rows: the 3 matched orders, O-9004 with name NA, and Hart & Co with order_id and amount NA. semi_join() is the opposite of anti_join(): it keeps rows of the first table that have a match, without adding any columns.

A second example: two key columns

When a single column isn't unique, join on several. Stock is recorded per store and SKU:

stock <- data.frame(store = c("London", "London", "Leeds"),
                    sku = c("D-1", "C-2", "D-1"), on_hand = c(5, 12, 3))
sales <- data.frame(store = c("London", "Leeds", "Leeds"),
                    sku = c("D-1", "D-1", "C-2"), sold = c(2, 1, 4))

left_join(sales, stock, by = join_by(store, sku))
# base R: merge(sales, stock, by = c("store", "sku"), all.x = TRUE)
Result
storeskusoldon_hand
LondonD-125
LeedsD-113
LeedsC-24NA

With base merge() the rows come back sorted by the keys (Leeds before London) unless you add sort = FALSE.

Base R or dplyr?

Choosing a join function
SituationUse
No packages allowed, simple joinsbase `merge()`
Anti or semi joinsdplyr `anti_join()` / `semi_join()`
You want an error on unexpected duplicatesdplyr with `relationship = "many-to-one"`
Range or inequality joins (dates in a period)dplyr `join_by()` with `>=` / `between()`
Very large tablesdata.table joins, or DuckDB from R

dplyr joins keep the row order of the first table and never sort, which makes before-and-after comparisons easier. By default dplyr 1.1+ also warns when a many-to-many match occurs, which base merge() does silently.

Checking the result

  • Compare nrow(result) with nrow(orders) after a left join; a higher number means duplicate keys in the lookup table.
  • sum(is.na(result$name)) counts orders with no customer; investigate them before dropping anything.
  • count(customers, customer_id) |> filter(n > 1) lists duplicate keys.
  • Read IDs as text: read.csv("orders.csv", colClasses = c(customer_id = "character")) keeps leading zeros.

Joining several data frames and different key names

Pipes make a chain of joins readable, and join_by() handles columns that are named differently in each table:

invoices |>
  left_join(customers, by = join_by(cust == customer_id)) |>
  left_join(regions,   by = join_by(country)) |>
  left_join(fx_rates,  by = join_by(currency, invoice_date == rate_date))

Each step adds columns to the running result, so start from the table whose rows you want to keep. If a column such as name exists in two tables, dplyr adds .x and .y suffixes; set suffix = c("_invoice", "_customer") to get readable names. With base R only, Reduce(function(a, b) merge(a, b, by = "customer_id", all.x = TRUE), list(orders, customers, segments)) does the same for a shared key.

To repeat the job every month, keep these steps in a script that reads the new exports, runs the joins with relationship = set, and prints the anti-join counts, so a change in the source files shows up as a number you notice rather than a silent gap.

Frequently asked questions

How do I do a left join in R?
Use merge(x, y, by = "key", all.x = TRUE) in base R, or left_join(x, y, by = join_by(key)) in dplyr.
How do I merge more than two data frames in R?
Chain joins with the pipe, or use Reduce(function(a, b) merge(a, b, by = "key"), list(df1, df2, df3)).
What's the difference between merge and rbind?
merge matches rows on key columns; rbind stacks rows of data frames with the same columns.
How do I find rows that don't match between two data frames in R?
Use dplyr's anti_join(x, y, by = join_by(key)) to list rows of x with no match in y, and swap the arguments for the other direction.
Why does dplyr warn about a many-to-many relationship?
Some key values repeat in both data frames, so each pair of rows is combined. Remove the duplicates or set relationship explicitly once you've confirmed the result is intended.

Related guides