Section 2.8

Merging Datasets

Q: 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 every one of a person's sessions to every one of them, which is exactly how a 200-row table becomes 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.

Real projects rarely live in one file. Demographics sit 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. That stitching-together is a join (a "merge"). It looks trivial and usually is, right up 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 one 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, cleanest join; anyone missing from either side vanishes. Reach for 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 everyday workhorse: "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

Almost every merge disaster is caught by two cheap checks:

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, a join becomes many-to-many and the output multiplies: two matching rows on each side of one key produce four rows. That's how a clean 200-person dataset becomes 380 rows nobody ordered.
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" vs 1, upper vs lower case — clean the key like any other field first, see the cleaning workflow). A jump up means a duplicate key.

Make the software do the checking

Counting rows by hand works right up until the day you forget, so modern join functions will do it for you if you tell them what to expect. In R, dplyr takes a relationship argument that states the shape you believe the merge has, and refuses to proceed when the data disagree:

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 idea spelled validate="1:1". Either way the check is free and the failure is loud. A merge that quadruples your sample should stop the script rather than survive into the analysis.

You cannot lean on the default warnings alone, and it is worth knowing exactly why. dplyr does warn on a genuine many-to-many join ("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 passes in complete silence. The four participants above joined to that table return five rows and no message at all. The loud disaster warns you; the quiet one does not.

When the count does come out wrong, the fastest way to find out who is responsible is an anti-join: the rows of one table whose key finds no partner in the other. On these two tables it returns Ann and Dan in one direction and the orphaned session 5 in the other, which is the entire mismatch, named. Run it both ways before you start guessing whether the cause is a trailing space or a genuinely absent participant. A full join shows you that someone is missing; an anti-join hands you the list.

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 hands you a plausible-looking table with the wrong people in it, and every statistic downstream inherits the error. 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, assert the row count you expect, and let the assertion fail loudly rather than the analysis fail silently.

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, so if an ID appears twice in the table you're joining to, each matching row on the other side is duplicated, 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 every one of a person's sessions to every one of them, which is exactly how a 200-row table becomes 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.