How do I see which rows merge() dropped?

Anti-join: x[!x$id %in% y$id, ] lists rows of x with no match in y, and dplyr::anti_join(x, y, by = "id") does the same.

How do I keep all rows from the first data frame in R?

Use merge(x, y, by = "key", all.x = TRUE). Rows in x with no match in y are kept, and y's columns are filled with NA.

Does merge() keep the original row order?

No. By default sort = TRUE sorts the result by the key columns. Pass sort = FALSE to avoid sorting, but don't rely on the original order being preserved; add a row number column first if order matters.

Is dplyr left_join safer than merge()?

It has a clearer name and keeps x's row order, but it fails in the same way when keys don't match exactly. Recent dplyr versions can also warn about unexpected many-to-many matches.

Why does merge() return more rows instead of fewer?

Because a key appears more than once in both data frames, so each pair is returned. Check duplicated() on the key columns of each side.

Why R merge() drops rows unexpectedly, and how to keep them

By the Analistable team · Updated 2026-10-10 · 5 min read

merge() in base R performs an inner join unless you say otherwise, so any row without a match in the other data frame disappears. Use merge(x, y, by = "id", all.x = TRUE) to keep every row of x. If rows still go missing, the keys don't match exactly: check types, whitespace, case, leading zeros and NA values.

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

The default is an inner join

merge(x, y) has all = FALSE by default, which means only keys present in both data frames survive. This catches out people coming from Excel, where a lookup keeps every row and shows #N/A for the misses. The full syntax is covered in merging data frames in R; here we focus on finding where rows went.

Which rows merge() keeps
Argument	Join type	Rows kept
(default) all = FALSE	Inner	Keys in both x and y
all.x = TRUE	Left	Every row of x, NA where y has no match
all.y = TRUE	Right	Every row of y, NA where x has no match
all = TRUE	Full outer	Every row of both

customers <- read.csv("customers.csv")   # 1,200 rows
orders    <- read.csv("orders.csv")      # 5,430 rows

nrow(merge(customers, orders, by = "customer_id"))               # 5,102
nrow(merge(customers, orders, by = "customer_id", all.y = TRUE)) # 5,430

Here 328 orders (5,430 minus 5,102) have a customer_id that isn't in the customer file. The inner join silently discarded them. Before deciding whether that is right, find out why they didn't match.

Find the rows that didn't match

Use anti-joins to list the unmatched keys from each side. In base R, %in% is enough:

missing <- orders[!orders$customer_id %in% customers$customer_id, ]
nrow(missing)                 # 328
head(unique(missing$customer_id))

# dplyr equivalent
library(dplyr)
anti_join(orders, customers, by = "customer_id")

Look at a handful of the unmatched keys next to their nearest match in the other table. The cause is nearly always one of the issues below.

Cause 1: key types or formats differ

If one file stores IDs as numbers and another as text with leading zeros, "00123" and 123 will never match. read.csv converts a column of digits to integer and drops the zeros, while another file might keep them as character. Check with str() and convert both sides to the same type.

str(customers$customer_id)   # int
str(orders$customer_id)      # chr  "00123"

orders$customer_id    <- as.integer(orders$customer_id)
# or keep text and pad the other side:
customers$customer_id <- sprintf("%05d", customers$customer_id)

Read files with colClasses = "character" for ID columns if you need to preserve leading zeros from the start.

Cause 2: whitespace and case

Exports from CRMs and web forms often carry trailing spaces or mixed case. "ACME01 " and "acme01" are different strings to merge(). Normalise on both sides before joining:

clean_key <- function(x) toupper(trimws(x))
customers$code <- clean_key(customers$code)
orders$code    <- clean_key(orders$code)

Cause 3: NA keys

Rows with NA in the key are a common source of confusion. By default merge() has incomparables = NULL, which means NA values are treated as matchable and an NA in x can pair with an NA in y. That can create spurious matches rather than dropping rows. If you want NA keys never to match, pass incomparables = NA, or filter them out first and handle them separately. With dplyr joins, NA keys also match each other by default; set na_matches = "never" to stop that.

Count NA keys on both sides with sum(is.na(x$id)) before merging so the result doesn't surprise you either way.

Cause 4: factors and multi-column keys

In R versions before 4.0, read.csv turned text into factors by default (stringsAsFactors = TRUE). Merging factor keys generally works because merge() compares the values, but factor levels with stray spaces or different encodings still won't match, and converting a factor of numbers with as.numeric() gives level codes rather than the numbers. Convert with as.character() first if you see odd results.

With a composite key such as by = c("store", "date"), every column must match. A date read as character in one file ("2026-03-01") and as Date in another won't match. Convert both with as.Date().

A worked example

Unmatched orders by cause, after anti-joining 5,430 orders to customers
Cause	Unmatched rows	Fix
Leading zeros lost in customers file	214	Pad to 5 digits
Trailing space in orders export	71	trimws()
NA customer_id (guest checkout)	43	Keep with all.x, label 'Guest'
Total	328

After fixing types and whitespace, the inner join returns 5,387 rows and the remaining 43 guest orders are kept with all.x = TRUE from the orders side. Check the totals: 5,387 + 43 = 5,430, every order accounted for. The same reasoning applies in other languages; compare pandas merge duplicates for the opposite problem, and the pandas, R and SQL merge guide for side-by-side syntax.

If you'd rather not script it, Analistable can load both CSVs in your browser and answer "which orders have no matching customer?" directly, showing the SQL it ran so you can check the join type.

Make the check part of your script

Rather than discovering missing rows later, assert the row count right after every merge. A line such as stopifnot(nrow(result) == nrow(orders)) after a left join from the orders side fails loudly when keys are duplicated or a later step filtered rows away. Pair it with a count of unmatched keys so you know whether a drop is expected: printing sum(!orders$customer_id %in% customers$customer_id) each run turns a silent change in the source data into a number you can watch.

Frequently asked questions

How do I see which rows merge() dropped?: Anti-join: x[!x$id %in% y$id, ] lists rows of x with no match in y, and dplyr::anti_join(x, y, by = "id") does the same.
How do I keep all rows from the first data frame in R?: Use merge(x, y, by = "key", all.x = TRUE). Rows in x with no match in y are kept, and y's columns are filled with NA.
Does merge() keep the original row order?: No. By default sort = TRUE sorts the result by the key columns. Pass sort = FALSE to avoid sorting, but don't rely on the original order being preserved; add a row number column first if order matters.
Is dplyr left_join safer than merge()?: It has a clearer name and keeps x's row order, but it fails in the same way when keys don't match exactly. Recent dplyr versions can also warn about unexpected many-to-many matches.
Why does merge() return more rows instead of fewer?: Because a key appears more than once in both data frames, so each pair is returned. Check duplicated() on the key columns of each side.