When a Merge Goes Wrong
Overview
Duplicate keys multiply rows, and ids written two ways match nothing.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Predict the row count of a join before running it.
- Explain why a duplicate key multiplies rows.
- Tell a type mismatch, which raises, from text ids that silently match nothing.
- Have pandas check your assumption with
validate.
Why This Matters
These are the two failures that produce a wrong number rather than an error. A duplicated key inflates a total silently; the same id written two ways — "007" and "7", or with a trailing space — empties the result silently. Both are cheap to check for and expensive to discover later, which is the whole argument of this lesson.
Step 1: A key that repeats multiplies
Every matching row on the left pairs with every matching row on the right. Two and two make four, not two.
The multiplication
One person, two score rows, two output rows. Sum the scores now and the person counts twice.
import pandas as pd
people = pd.DataFrame({"id": [1], "name": ["Ada"]})
scores = pd.DataFrame({"id": [1, 1], "score": [90, 80]})
out = pd.merge(people, scores, on="id")
print(out)
print(len(people), "x duplicated key ->", len(out), "rows")Check the key before you join
One line, and it answers the question the row count would otherwise raise afterwards.
import pandas as pd
scores = pd.DataFrame({"id": [1, 1, 2], "score": [90, 80, 70]})
print("duplicated keys:", int(scores["id"].duplicated().sum()))
print(scores["id"].value_counts())Let pandas hold you to it
validate states the relationship you believe you have, and raises when the data disagrees — instead of silently multiplying.
import pandas as pd
people = pd.DataFrame({"id": [1], "name": ["Ada"]})
scores = pd.DataFrame({"id": [1, 1], "score": [90, 80]})
try:
pd.merge(people, scores, on="id", validate="one_to_one")
except Exception as exc:
print(type(exc).__name__ + ":", exc)"one_to_one", "one_to_many" and "many_to_one" are the useful three. Writing down which one you expect is also documentation for the next reader.
Mini Practice #1: Predict, then count
Try It YourselfWrite down how many rows you expect before you run this. The right-hand table has a duplicate.
Step 2: Keys have to be written the same way
A join compares key values exactly. When the types differ outright pandas refuses, which is the good case. The dangerous case is two text columns that look alike to a person and not to pandas.
A type mismatch is loud
An int key against a text key raises ValueError, and the message names the column. Read it: it is telling you the two files disagree about what an id is.
import pandas as pd
a = pd.DataFrame({"id": ["1"], "name": ["Ada"]})
b = pd.DataFrame({"id": [1], "score": [90]})
try:
pd.merge(a, b, on="id")
except ValueError as exc:
print("ValueError:", exc)
print(a["id"].dtype, "vs", b["id"].dtype)Make both sides the same type
Convert both id columns before joining, on copies. Converting only one side leaves the mismatch where it was.
import pandas as pd
a = pd.DataFrame({"id": ["1"], "name": ["Ada"]})
b = pd.DataFrame({"id": [1], "score": [90]})
a2, b2 = a.copy(), b.copy()
a2["id"] = a2["id"].astype(str)
b2["id"] = b2["id"].astype(str)
print(len(pd.merge(a2, b2, on="id")), "rows")Text that differs is silent
Once both sides are text, pandas compares them character by character and raises nothing. "1 " and "1" are different keys, and so are "007" and "7" — which is exactly what you get when one file was read as numbers and then converted back to text. No error, no warning, no rows.
import pandas as pd
a = pd.DataFrame({"id": ["1 "], "name": ["Ada"]})
b = pd.DataFrame({"id": ["1"], "score": [90]})
print(len(pd.merge(a, b, on="id")), "rows")
a["id"] = a["id"].str.strip()
print(len(pd.merge(a, b, on="id")), "rows")Mini Practice #2: Empty for no reason
Try It YourselfBoth id columns are text, so this join raises nothing — and returns nothing. Print the ids from each side to see why, then fix one side so they match.
Step 3: When to Use It
Before every merge
Check the key is unique where it should be and has the same dtype on both sides.
pd.merge(a, b, on="id", validate="many_to_one")After a surprising result
Too many rows means duplicate keys; too few means ids written differently on each side — padding, spaces or case.
When Not to Use It
Do not fix a doubled row count by dropping duplicates afterwards. Find the duplicate keys that caused it, or the numbers built on the merge are still wrong.
End-of-Lesson Exercises
Exercise 1: Join whatever type the ids arrived as
Write safe_merge(left, right) that joins on id after making both id columns text, and returns the number of rows in the result.
Convert both id columns with astype(str) on copies, then merge. Converting only one side leaves the mismatch exactly where it was.
Exercise 2: Count the duplicate keys first
Write key_report(left, right) returning a three-element list of [duplicated ids on the left, duplicated ids on the right, rows an inner join would produce]. Checking before joining is the point.
df["id"].duplicated().sum() counts the repeats on each side. Then merge and take len() — two duplicates each side give four rows, which is the multiplication this exercise is about.