When a Merge Goes Wrong

Duplicate keys multiply rows, and mismatched types match nothing.

A key that repeats multiplies

If the right-hand key is not unique, every match on the left is repeated once per match on the right. Two rows becoming six is the classic sign.

import pandas as pd

left = pd.DataFrame({"id": [1], "name": ["Ada"]})
right = pd.DataFrame({"id": [1, 1], "score": [90, 80]})
print(pd.merge(left, right, on="id").shape)

Types have to match too

An id that is text on one side and a number on the other matches nothing at all, and the result is an empty table rather than an error.

import pandas as pd

left = pd.DataFrame({"id": ["1"], "name": ["Ada"]})
right = pd.DataFrame({"id": [1], "score": [90]})
print(pd.merge(left, right, on="id").shape)

Exercise

Try It Yourself

Write safe_merge(left, right) that joins on id after making both id columns text, and returns the number of rows in the result.

import pandas as pd

def safe_merge(left, right):
    # Match on id whatever type it arrived as.
    return 0

print(safe_merge(pd.DataFrame({"id": ["1"], "name": ["Ada"]}),
                 pd.DataFrame({"id": [1], "score": [90]})))

Press Run to see output

Check Your Understanding

← Previous Next