# 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 November 19, 2026

By Gregor Dzierzon

## 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:

```python
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))
```

```text
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:

```python
with_clubs[with_clubs["student_id"] == 1009]
```

```text
   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:

```python
print("90 or more, fall scores:", (fall["quiz_1"] >= 90).sum())
print("90 or more, after the join:", (with_clubs["quiz_1"] >= 90).sum())
```

```text
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:

```python
fall.merge(memberships, on="student_id", how="left", validate="many_to_one")
```

```text
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:

```python
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))
```

```text
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:

```python
as_text = pd.read_csv("memberships.csv", dtype=str)
as_text.merge(students, on="student_id")
```

```text
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:

```python
as_text["student_id"] = as_text["student_id"].astype("int64")
print("rows:", len(as_text.merge(students, on="student_id")))
```

```text
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:

```python
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"]]
```

```text
   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:

```python
signups["club_id"] = signups["club_id"].str.strip()
fixed = signups.merge(clubs, on="club_id", how="left", indicator=True)
fixed["_merge"].value_counts()
```

```text
_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

- [One-to-Many Relationship](https://www.ask-codey.com/glossary/one-to-many-relationship)
- [Data Type](https://www.ask-codey.com/glossary/data-type)
- [strip()](https://www.ask-codey.com/glossary/strip)

## Related

- [Inner, Left, Right and Outer Joins](https://www.ask-codey.com/lessons/pandas-join-types)
- [Finding and Removing Duplicates](https://www.ask-codey.com/lessons/pandas-duplicates)

Source: https://www.ask-codey.com/lessons/pandas-join-pitfalls
