Quick answer: Merging combines two data frames by a shared key column: merge(x, y, by = "id") in base R returns the rows whose keys appear in both tables (inner join) — add all.x = TRUE to keep every row of the first table (left join), all.y = TRUE for the second, or all = TRUE for everything (full join). The dplyr twins left_join(), inner_join(), full_join() do the same with a pipe-friendly syntax. Data frame basics first: data frames in R.
The four joins on a real example
Two tables: patients (id, age, group) and results (id, biomarker). 120 patients, 115 result rows — 5 patients never got assayed. The joins answer different questions:
merge(patients, results, by = "id") # inner: 115 rows, complete cases only
merge(patients, results, by = "id", all.x = TRUE) # left: 120 rows, NA biomarker for the 5
merge(patients, results, by = "id", all = TRUE) # full: everything from both sides
dplyr::left_join(patients, results, by = "id") # same as all.x = TRUE, pipe-friendlyClinical analysis almost always wants the left join: keep the full cohort and let missing assays be NA, so dropouts stay visible in the missingness summary instead of vanishing silently — which would inflate n and quietly change the sample. The power analysis that depends on an honest n is in the power guide.
The mistakes that corrupt merges
| Symptom | Cause | Fix |
|---|---|---|
| Result has more rows than either input | Duplicate keys — one id maps to several result rows | Deduplicate first: results[!duplicated(results$id), ] |
| Merge returns 0 rows | Key types differ — “101” (text) vs 101 (number), or trailing spaces | Harmonize: as.integer() / trimws() before merging |
| Columns appear twice with .x/.y suffixes | Same column name in both tables | Rename before, or use suffixes = c("_p", "_r") |
| Keys match by position, not value | Accidental by = c(1, 2) or mismatched names | Always name keys explicitly: by = c("study_id" = "pid") handles different names |
Vertical stacking is the other merge direction: rbind(patients_2025, patients_2026) appends rows — but only when column names match exactly, which makes dplyr::bind_rows() the safer default (it fills missing columns with NA instead of erroring). Where merges feed GUI-based analysis, R Commander consumes the merged frame through the same menus shown in the data import guide, and pipelines in genomics lean on these joins constantly — the BLAST workflow in what BLAST does is a real example of joining annotation tables to hits.
For one-to-one coaching on data wrangling in R — merges, reshapes and the tidyverse — Ampersand Academy teaches R programming one-to-one.
Frequently asked questions
What is the difference between merge with all.x and all.y?
all.x keeps every row of the first data frame (a left join), filling unmatched right rows with NA. all.y keeps every row of the second (a right join). Omitting both keeps only keys present in both – an inner join.
Why does my merge produce duplicate rows?
Your key column has duplicates in at least one table – each matching pair multiplies. Deduplicate or aggregate the many-side table first, then merge on the unique key.
How do I merge tables when key columns have different names?
Name the pair explicitly: merge(x, y, by = c(study_id = pid)) or in dplyr left_join(x, y, by = c(study_id = pid)). Rows join on matching values despite the different names.
When should I use rbind instead of merge?
rbind stacks rows vertically – appending this year’s data under last year’s – while merge joins columns horizontally via a key. bind_rows from dplyr is the tolerant version that survives mismatched columns.
Is dplyr’s left_join the same as merge with all.x = TRUE?
Functionally yes, with nicer syntax: the by argument is explicit, the result keeps a consistent column order, and bind_rows family errors are clearer. Performance differences are irrelevant at beginner-scale data.