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

By

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.

Key terms

Related