How to Merge Two Pandas DataFrames on Multiple Columns

Merging on a single column works fine until two records need more than one field to uniquely match — a student identified by student_id and term, a sale identified by region and date. This covers three ways to merge on multiple columns, and what to do when the two DataFrames don't fully agree on which rows exist.

The short version, before the detail: an inner merge drops any row that doesn't match on both sides — silently. A left merge keeps every row from one side and can show you which ones didn't match. An outer merge keeps everything from both sides, so you see every gap at once. The rest of this article is really just that three-way distinction, worked through in detail.

Problem Statement

Given two DataFrames that share more than one key column, combine them so rows match only when all key columns agree.

Example

Input:

# df_scores
   student_id term  score
0         101   T1     85
1         101   T2     90
2         102   T1     78
3         103   T1     92

# df_attendance
   student_id term  attendance
0         101   T1     95
1         101   T2     88
2         102   T1     80
3         104   T1     99

Notice the two DataFrames don't fully overlap: student 103 has a score but no attendance record, and student 104 has an attendance record but no score. That mismatch is the actual problem this article is about — not just the merge syntax.

Using merge() with on=[...]

The default case — match rows only where every key column agrees, and drop anything that doesn't have a match on both sides:

import pandas as pd

df_scores = pd.DataFrame({
    "student_id": [101, 101, 102, 103],
    "term": ["T1", "T2", "T1", "T1"],
    "score": [85, 90, 78, 92]
})

df_attendance = pd.DataFrame({
    "student_id": [101, 101, 102, 104],
    "term": ["T1", "T2", "T1", "T1"],
    "attendance": [95, 88, 80, 99]
})

result = pd.merge(df_scores, df_attendance, on=["student_id", "term"])
print(result)

   student_id term  score  attendance
0         101   T1     85          95
1         101   T2     90          88
2         102   T1     78          80

This is an inner merge by default — only (101, T1), (101, T2), and (102, T1) exist in both DataFrames, so those are the only rows that survive. Student 103's score and student 104's attendance both silently disappear. That's fine if missing data should be dropped — dangerous if you didn't realize you were dropping it.

If the key columns have different names on each side — say the second DataFrame uses id and term_code instead of student_id and term — use left_on/right_on instead of on:

pd.merge(df_scores, df_attendance_alt,
         left_on=["student_id", "term"],
         right_on=["id", "term_code"])

Two more things worth knowing before you rely on this:

• A NaN in a key column never matches anything, including another NaN — a row with a missing key is silently treated as having no match, on either side.
• Non-key columns that happen to share a name across both DataFrames (like created_at) get renamed automatically with _x/_y suffixes. Use suffixes=("_scores", "_attendance") if you want to control what they're called instead.

Using how='left' with indicator=True

When you need to keep every row from one side — and see exactly which ones didn't find a match — a left merge with the indicator flag on gives you both:

result = pd.merge(
    df_scores, df_attendance,
    on=["student_id", "term"],
    how="left",
    indicator=True
)
print(result)

   student_id term  score  attendance     _merge
0         101   T1     85        95.0       both
1         101   T2     90        88.0       both
2         102   T1     78        80.0       both
3         103   T1     92         NaN  left_only

Every row from df_scores is kept, whether or not it matched. Student 103 shows up with attendance as NaN and _merge flagged as left_only — pandas is telling you directly, row by row, which side of the merge each row actually came from. Filtering result[result["_merge"] == "left_only"] gives you exactly the rows an inner merge would have thrown away without telling you. (If you want to keep that column for further analysis under a clearer name, pass indicator="match_status" instead of True.)

Common Pitfall: Duplicate Keys

This is the gotcha that causes the most confusing production bugs in multi-column merges, and it's worth seeing once instead of just being warned about. If your key columns aren't actually unique on one side, merge doesn't error — it silently multiplies rows:

df_scores_dup = pd.DataFrame({
    "student_id": [101, 101, 101],
    "term": ["T1", "T1", "T2"],
    "score": [85, 87, 90]
})

result = pd.merge(df_scores_dup, df_attendance, on=["student_id", "term"])
print(result)

   student_id term  score  attendance
0         101   T1     85          95
1         101   T1     87          95
2         101   T2     90          88

Two different scores for the same (101, T1) key both matched the single attendance row — the attendance value 95 now appears twice, once per duplicate. In a small example it's obvious. On a real dataset with thousands of rows, this is exactly how a merge quietly inflates your row count and nobody notices until a downstream aggregate is mysteriously too high.

The fix is validate, which turns this from a silent bug into an explicit error:

pd.merge(df_scores_dup, df_attendance, on=["student_id", "term"], validate="1:1")

MergeError: Merge keys are not unique in left dataset; not a one-to-one merge

Use validate="1:1" when you expect exactly one match per key pair on each side, or "1:m"/"m:1" if one side is genuinely expected to repeat. It costs nothing to add and catches this class of bug before it reaches production.

Using how='outer' to Reconcile Both Sides

When neither DataFrame should lose rows — you want the full picture, gaps and all — an outer merge keeps everything from both sides:

result = pd.merge(df_scores, df_attendance, on=["student_id", "term"], how="outer")
print(result)

   student_id term  score  attendance
0         101   T1   85.0        95.0
1         101   T2   90.0        88.0
2         102   T1   78.0        80.0
3         103   T1   92.0         NaN
4         104   T1    NaN        99.0

Now both mismatches show up: student 103 with no attendance, student 104 with no score. Neither an inner merge nor a plain left merge would have shown you both at once.

Notice score and attendance became floats (85.0 instead of 85) — pandas can't represent a missing value in an integer column, so introducing any NaN forces the whole column to float. This is worth knowing before it surprises you downstream in code that expects integers.

From here, don't reach for fillna(0) without thinking about what 0 actually means for your data — a missing attendance record isn't the same fact as "attended zero classes," and filling it with 0 quietly turns "we don't know" into a false claim. If a specific default genuinely makes sense for your case, say so explicitly and comment why:

# 0 here means "no record exists yet," not "confirmed zero attendance" —
# only fill this way if your downstream logic treats them the same.
result["attendance"] = result["attendance"].fillna(0)
result["score"] = result["score"].fillna(0)

# now safe to cast back to int, since there are no NaNs left
result[["score", "attendance"]] = result[["score", "attendance"]].astype(int)

Often the better move is to leave the NaNs as-is and handle the two mismatched rows deliberately — a separate lookup, a flag for manual review, whatever fits — rather than picking a placeholder number that might get misread as real data later.

Performance note: on large DataFrames, multi-column merges are one of the more expensive pandas operations. If you're merging millions of rows, setting sort=False (the default since recent pandas versions) and making sure both DataFrames are already sorted on the key columns beforehand can meaningfully speed things up.

Which One Should You Use?

• Only care about rows that exist in both DataFrames? Use the default merge(on=[...]) — inner join.
• Need to keep every row from one side and see what didn't match? Use how='left' with indicator=True.
• Need to see and resolve mismatches from both sides? Use how='outer', then decide how to handle the NaNs explicitly.
• Not sure whether your keys are actually unique? Add validate="1:1" before you trust any of the above.

Conclusion

Don't default to the inner merge just because it's the shortest call — default to it only once you've confirmed that dropping unmatched rows is actually what you want. If you haven't confirmed that yet, run how='left' with indicator=True first and see what an inner merge would have silently thrown away. Add validate any time you're not certain your keys are unique — it's one keyword argument standing between you and a duplicated-row bug that's much harder to spot after the fact than before it.