When a Join Goes Wrong
A join can run without an error and still give wrong answers, when a key that repeats in the lookup table copies rows (which validate= catches), when the key is a number in one table and text in the other, or when spaces hide in the keys, and each has a check and a fix.
Journeyman · Python · Database · Updated
After this lesson you can
- Explain how a repeated key multiplies the rows of a join, and catch it with validate
- Fix keys that don't match because of different types or extra spaces, using astype and str.strip
Key takeaways
- When a key appears more than once in the table you join to, every matching row is copied, so counts and totals grow without an error.
- validate="many_to_one" says the right table should have each key once, and stops the join with a MergeError when it doesn't.
- A key stored as numbers in one table and as text in the other won't join. Check the dtypes and convert with astype.
- A key typed as "C02 ", with a space, isn't "C02". str.strip() takes spaces off the ends, and indicator=True shows the keys that still don't match.
The worst join is the one that runs without an error and gives a wrong number, which then goes into a report. Three mistakes cause most of them, and each has a quick check. You'll use these checks every time you join tables someone else typed.
The example is Oakridge Middle School's records: fall_scores.csv (one row per student), students.csv, clubs.csv and memberships.csv (one row for each student in each club, so a student in two clubs has two rows).
A repeated key copies rows
The principal asks how many students scored 90 or more on fall's first quiz, and someone has already added each student's club to the scores:
import pandas as pd
students = pd.read_csv("students.csv")
fall = pd.read_csv("fall_scores.csv")
memberships = pd.read_csv("memberships.csv")
with_clubs = fall.merge(memberships, on="student_id", how="left")
print("rows before:", len(fall))
print("rows after:", len(with_clubs))
rows before: 240
rows after: 295
240 students went in and 295 rows came out. A left join keeps every score row, and a score row whose student_id appears twice in memberships matches both rows, so it's copied:
with_clubs[with_clubs["student_id"] == 1009]
student_id quiz_1 quiz_2 quiz_3 quiz_4 club_id
8 1009 92 75 79 75 C02
9 1009 92 75 79 75 C06
Student 1009 is in Robotics and Coding Club, so their scores now count twice:
print("90 or more, fall scores:", (fall["quiz_1"] >= 90).sum())
print("90 or more, after the join:", (with_clubs["quiz_1"] >= 90).sum())
90 or more, fall scores: 27
90 or more, after the join: 33
Six of the 33 are copies of students already counted. One student matched to many memberships is a one-to-many relationship. It's what you want for "how did each club's members do?", where a student belongs in both clubs' numbers, and it's wrong for any question about students.
validate=: say what you expect
validate= tells merge what each side's keys should look like, and stops the join when they don't. "many_to_one" means "the left table may repeat a key, the right one has each key once", the shape of a lookup:
fall.merge(memberships, on="student_id", how="left", validate="many_to_one")
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
(pandas lists some of the repeated keys under that line.) The same check passes on a real lookup: the year's 480 score rows, two per student, joined to the student list, where each student_id appears once:
spring = pd.read_csv("spring_scores.csv")
both = pd.concat(
[fall.assign(term="fall"), spring.assign(term="spring")],
ignore_index=True,
)
named = both.merge(students, on="student_id", how="left", validate="many_to_one")
print("rows before:", len(both))
print("rows after:", len(named))
rows before: 480
rows after: 480
The other values are "one_to_one", "one_to_many" and "many_to_many". Adding validate= to a join costs a few words, and turns a quiet mistake into an error you can't miss.
Keys of different types
Some people read every column as text, to be safe, with dtype=str. Then the same numbers can't be matched:
as_text = pd.read_csv("memberships.csv", dtype=str)
as_text.merge(students, on="student_id")
ValueError: You are trying to merge on str and int64 columns for key 'student_id'. If you wish to proceed you should use pd.concat
The text "1009" isn't the number 1009, so pandas refuses rather than match nothing. (Ignore its suggestion of pd.concat: stacking isn't joining.) Check both keys' types with .dtype, then convert one with astype, as Column Types: dtypes and astype shows:
as_text["student_id"] = as_text["student_id"].astype("int64")
print("rows:", len(as_text.merge(students, on="student_id")))
rows: 237
Spaces in keys
Three students signed up for clubs on a tablet at the club fair, and two club ids came with a space:
clubs = pd.read_csv("clubs.csv")
signups = pd.DataFrame({
"student_id": [1012, 1047, 1090],
"club_id": ["C06", "C02 ", " C04"],
})
check = signups.merge(clubs, on="club_id", how="left", indicator=True)
check[["student_id", "club_id", "club_name", "_merge"]]
student_id club_id club_name _merge
0 1012 C06 Coding Club both
1 1047 C02 NaN left_only
2 1090 C04 NaN left_only
They look right, but "C02 " isn't "C02". str.strip() takes the spaces off both ends of every value, and the indicator shows all three matching:
signups["club_id"] = signups["club_id"].str.strip()
fixed = signups.merge(clubs, on="club_id", how="left", indicator=True)
fixed["_merge"].value_counts()
_merge
both 3
left_only 0
right_only 0
Name: count, dtype: int64
Strip can't fix every key: CO3, typed with the letter O, still needs a person to decide it means C03. That's what the indicator is for: it shows you the rows to look at.
Check yourself
2 questions to try on the lesson page.
So what
A join that runs isn't a join that's right: compare the row counts, add validate= for the shape you expect, and check the keys' types and spaces, and the quiet mistakes become ones you can see.