Merging Datasets
Almost no project lives in a single file. Demographics are in one table, survey responses in another, lab results in a third, and at some point you need them side by side, one row per person. Combining them is a join (a "merge"). It looks trivial and usually is, until a mismatched key silently drops half your sample or a repeated key doubles it.
Keys line the rows up
A join matches rows across two tables using a shared key: a column both tables carry that says which row belongs with which, most often a participant ID. Get the key right and everything follows; get it wrong and the merge is nonsense.
The property that decides how a join behaves is uniqueness. If the key is unique in a table (each ID appears once), that table contributes at most one row per match. If it repeats, every copy matches, which is sometimes what you want (long-format repeated measures) and sometimes an accident that inflates your data. Below, the sessions table has ID 3 twice; watch what that does.
🔗 Visual Join Explorer
Two little tables sharing the key id. Pick a join type and watch which rows match up, which fall away, and what the row count does. Rows kept but unmatched fill the missing side with NA.
participants
sessions
result
Four joins in plain language
Every join answers one question: when a key exists in only one table, do we keep that row?
- inner: keep only rows whose key appears in both tables. The strictest join: anyone missing from either side is dropped. Use it when you want complete cases only.
- left: keep every row of the left table; fill in from the right where a match exists, leave NA where it doesn't. The one you will use most: "attach lab results to my participant list, and don't lose participants who never came in."
- right: the mirror image: keep every row of the right table. Rarely needed, since you can always swap the tables and use a left join instead.
- full: keep everyone from both sides, matching where possible and padding with NA elsewhere. Nothing is lost; use it when you need to see exactly who is missing from where.
Two habits that save you
Two cheap checks catch almost every merge error:
- Check your keys are unique first. If the same ID appears more than once in a table you meant to be one-row-per-person, the output multiplies, because every copy of a key matches. If the key repeats on both sides, the join is many-to-many, and two matching rows on each side of one key produce four rows. A clean 200-person dataset can become 380 rows this way.
- Count rows before and after. Know your expected row count going in (usually the size of your "spine" table) and compare it to what you got. A left join should return exactly as many rows as the left table unless the right key repeats. A drop means a key mismatch: trailing spaces,
"01"vs1, upper vs lower case. Clean the key like any other field first, the way the cleaning workflow does. A jump up means a duplicate key.
Make the software do the checking
Counting rows by hand works until the day you forget, so modern join functions can do the check for you if you tell them what to expect. In R, dplyr takes a relationship argument that states the shape you expect the merge to have, and stops with an error when the data don't match it:
left_join(participants, sessions, by = "id", relationship = "one-to-one")
#> Error: Each row in `x` must match at most 1 row in `y`.Pandas has the same check, spelled validate="1:1". Either way the check costs nothing and a failure produces an error message. A merge that quadruples your sample should stop the script, not reach the analysis.
You cannot rely on the default warnings alone. dplyr does warn when a join is many-to-many ("Detected an unexpected many-to-many relationship"). But the sessions table above is one-to-many (unique on the left, repeated on the right), and that case gives no warning. The four participants above joined to that table return five rows and no message at all.
When the count comes out wrong, the fastest way to find the unmatched rows is an anti-join: the rows of one table whose key has no match in the other. On these two tables it returns Ann and Dan in one direction and the orphaned session 5 in the other. Those three rows are the whole mismatch. Run it both ways before you start guessing whether the cause is a trailing space or a participant with no session. A full join shows that someone is missing, and an anti-join tells you who.
A join is only as good as its key. Match on a shared, cleaned, ideally unique key. Pick inner for complete cases and left to protect your main table, and always compare the row count before and after. If it changed when you didn't expect it to, stop — a mismatch dropped rows or a duplicate key multiplied them.
Why it matters: a silent join error doesn't crash. It gives you a plausible-looking table with the wrong people in it, and the error affects every statistic you compute from it. The same uniqueness question governs reshaping: keys that don't uniquely identify a row break pivots and joins alike. Do the merge in a script and assert the row count you expect, so that a wrong count stops the script with an error.
Common questions
Why did my merge create duplicate rows?
Because the key isn't unique in one of the tables. A join matches every copy of a key on one side to every copy on the other. If an ID appears twice in the table you're joining to, each matching row on the other side comes out twice. If it repeats in both tables, the copies multiply, a many-to-many join. It's the classic silent bug: nothing errors, you just end up with more rows (and inflated statistics) than you have participants. Before joining, check each key is unique where you expect it to be (duplicated() in R, .is_unique in pandas), and compare the row count before and after.
Can I join on more than one column?
Yes, and repeated-measures data usually requires it: by = c("id", "session") in dplyr, on=["id", "session"] in pandas. The key is whatever combination of columns makes a row unique, so if your design is one row per person per session, the key is that pair. Joining on the ID alone matches each of a person's sessions in one table to all of their sessions in the other, so a 200-row table can become 800 rows. Two practical notes. Clean the whole composite key, not just the ID, since a stray space in the session label breaks a match as thoroughly as one in the ID. And when the two tables name the same thing differently, map the names in the join itself (by = c("id" = "participant_id")) instead of renaming a column upstream, where the reason for the rename disappears.
My merge dropped half my rows — what went wrong?
Almost always a key mismatch. The join is comparing keys that look the same to you but not to the computer: a trailing space ("P01 " vs "P01"), a number stored as text on one side (1 vs "01"), or different capitalization. Because those keys never match, an inner join silently discards them. Clean and standardize the key column on both sides first (trim whitespace, fix the type, unify the case) then rejoin and confirm the row count is what you expected.