Merge and Join Data Frames in R (merge, left_join)

Two Tables, One Key

Real data rarely lives in one table. Customers sit in one data frame, their orders in another, and the question - "how much did each customer spend?" - needs both. What connects them is a key: a column present in each table that identifies the same entity. Combining tables on a key is a join (SQL's word) or a merge (base R's word); same operation.

Here is the pair of tables the rest of this page uses. Note that customer 5 placed an order but isn't in the customer table, and customers 2 and 4 never ordered:

customers <- data.frame(
    id   = c(1, 2, 3, 4),
    name = c("Ana", "Ben", "Chen", "Dana")
)
orders <- data.frame(
    id     = c(1, 1, 3, 5),
    amount = c(250, 40, 120, 60)
)

print(customers)
print(orders)

The mismatches are deliberate - what a join does with unmatched rows is precisely what distinguishes the join types.

merge(): Inner Join by Default

merge(x, y, by = "key") matches rows with equal keys and glues their columns together. By default it keeps only the keys present in both tables - an inner join:

customers <- data.frame(
    id   = c(1, 2, 3, 4),
    name = c("Ana", "Ben", "Chen", "Dana")
)
orders <- data.frame(
    id     = c(1, 1, 3, 5),
    amount = c(250, 40, 120, 60)
)

print(merge(customers, orders, by = "id"))

Three rows come back. Ana appears twice - she has two orders, and a join produces one output row per matching pair. Ben and Dana are gone (no orders), and so is the mystery order from customer 5 (no customer). An inner join silently discards non-matching rows from both tables - exactly right when you only care about complete pairs, and a silent data-loss bug when you don't.

Left, Right, and Full Joins: all.x, all.y, all

To keep unmatched rows, say which table's rows are sacred. all.x = TRUE keeps every row of the first table - the left join, the most used join in practice:

customers <- data.frame(
    id   = c(1, 2, 3, 4),
    name = c("Ana", "Ben", "Chen", "Dana")
)
orders <- data.frame(
    id     = c(1, 1, 3, 5),
    amount = c(250, 40, 120, 60)
)

print(merge(customers, orders, by = "id", all.x = TRUE))

Now Ben and Dana survive, with NA in amount - "this customer exists; no order matched." Those NAs are informative, not junk: is.na(result$amount) is exactly the list of customers who never ordered (the missing values doc covers working with them). The remaining variants are the same idea pointed elsewhere: all.y = TRUE keeps every row of the second table (right join - the order from unknown customer 5 would survive with NA in name), and all = TRUE keeps everything from both sides (full join).

Choose by asking: whose rows am I not allowed to lose? Enriching a master table with lookup data - left join, master table first. Auditing both directions - full join.

Different Key Names: by.x and by.y

Tables rarely agree on naming - id in one, customer_id in the other. Don't rename; tell merge() both names:

customers <- data.frame(
    id   = c(1, 2, 3),
    name = c("Ana", "Ben", "Chen")
)
orders <- data.frame(
    customer_id = c(1, 3, 3),
    amount      = c(250, 120, 60)
)

print(merge(customers, orders, by.x = "id", by.y = "customer_id"))

The output keeps the first table's name for the key. Related: if the two tables share non-key column names (both have a date, say), merge suffixes them date.x and date.y - rename them promptly, they read terribly.

The dplyr Joins

dplyr gives each join type its own verb, so the intent is in the function name rather than in flag arguments (static; the sandbox runs base R only):

library(dplyr)

left_join(customers, orders, by = "id")
inner_join(customers, orders, by = "id")
full_join(customers, orders, by = "id")
left_join(customers, orders, by = c("id" = "customer_id"))  # different names

Beyond readability, left_join() preserves the first table's row order (merge() re-sorts by key) and warns loudly about many-to-many matches - see the dplyr intro for the verb family.

The underrated member is anti_join(): it returns the rows of the first table that have no match in the second - no columns added, just the leftovers:

anti_join(customers, orders, by = "id")   # customers who never ordered

That question - "which rows failed to match?" - comes up constantly in data cleaning (unmatched IDs, orphaned records, failed lookups), and anti_join() answers it in one call where base R needs customers[!(customers$id %in% orders$id), ].

Stacking Rows: rbind()

Joining combines columns of two tables. When you instead have two batches of the same kind of rows - January's orders and February's - you stack them with rbind():

jan <- data.frame(id = c(1, 2), amount = c(250, 40))
feb <- data.frame(id = c(3, 4), amount = c(120, 60))

print(rbind(jan, feb))

The requirement is strict: both data frames must have the same column names (any order - rbind matches by name). A missing or extra column is an error, not an NA fill. When batches have drifted columns, dplyr's bind_rows() is more forgiving - it aligns by name and fills gaps with NA.

The Duplicate-Key Explosion

The classic join accident: keys you believed unique, but aren't - in both tables. Every match pairs with every match, multiplying rows:

a <- data.frame(id = c(1, 1), x = c("a1", "a2"))
b <- data.frame(id = c(1, 1), y = c("b1", "b2"))

merged <- merge(a, b, by = "id")
print(merged)
cat("rows:", nrow(merged), "\n")

Two rows joined to two rows yields four - each x paired with each y. On real data this is how a 10,000-row table becomes 3 million rows and a doubled revenue total. merge() does this silently. The defense is a habit: before joining, check the key you assume is unique - anyDuplicated(customers$id) should be 0 - and after joining, sanity-check nrow() against what you expected.

What You Take Away

• merge(x, y, by = "key") is an inner join - unmatched rows vanish silently.
• all.x = TRUE (left), all.y = TRUE (right), all = TRUE (full) keep unmatched rows, filling with NA.
• Different key names: by.x / by.y in base, by = c("a" = "b") in dplyr.
• dplyr names each join; anti_join() - the rows that didn't match - is the underrated one.
• rbind() stacks same-shaped tables; columns must match by name.
• Duplicate keys multiply rows silently - check anyDuplicated() before, nrow() after.

Next up: reshaping between wide and long formats with pivot_longer() and pivot_wider().