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()
| Join | Code |
|---|---|
| Inner | merge(orders, customers, by = "customer_id") |
| Left | merge(orders, customers, by = "customer_id", all.x = TRUE) |
| Right | merge(orders, customers, by = "customer_id", all.y = TRUE) |
| Full | merge(orders, customers, by = "customer_id", all = TRUE) |
| Different key names | merge(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.
| order_id | customer_id | amount | name |
|---|---|---|---|
| O-9001 | C-101 | 120 | Bakery Lune |
| O-9002 | C-103 | 75 | Nordic Supply |
| O-9003 | C-101 | 80 | Bakery Lune |
| O-9004 | C-104 | 60 | NA |
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
| Symptom | Cause | Fix |
|---|---|---|
| More rows than expected | Duplicate keys in the lookup table | Check with `anyDuplicated(customers$customer_id)`; set relationship in dplyr |
| Columns named name.x and name.y | Both tables have a non-key column with the same name | Rename first, or set suffixes = c("_order", "_cust") |
| No matches | Key is a factor in one table and character in the other, or has spaces | Convert with as.character() and trimws() |
| Row order changed | merge() sorts by key by default | Use 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)| store | sku | sold | on_hand |
|---|---|---|---|
| London | D-1 | 2 | 5 |
| Leeds | D-1 | 1 | 3 |
| Leeds | C-2 | 4 | NA |
With base merge() the rows come back sorted by the keys (Leeds before London) unless you add sort = FALSE.
Base R or dplyr?
| Situation | Use |
|---|---|
| No packages allowed, simple joins | base `merge()` |
| Anti or semi joins | dplyr `anti_join()` / `semi_join()` |
| You want an error on unexpected duplicates | dplyr with `relationship = "many-to-one"` |
| Range or inequality joins (dates in a period) | dplyr `join_by()` with `>=` / `between()` |
| Very large tables | data.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)withnrow(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.