Merging on a Key

Bringing two tables together on the column they share.

An inner join keeps the matches

merge lines rows up by a shared key. The default keeps only keys present on both sides, so the result can be smaller than either input.

import pandas as pd

people = pd.DataFrame({"id": [1, 2], "name": ["Ada", "Grace"]})
scores = pd.DataFrame({"id": [1], "score": [92]})
print(pd.merge(people, scores, on="id").shape)

how= decides what happens to the rest

left keeps every row on the left and leaves gaps where the right had nothing. That is usually what you want when the left table is your population.

import pandas as pd

people = pd.DataFrame({"id": [1, 2], "name": ["Ada", "Grace"]})
scores = pd.DataFrame({"id": [1], "score": [92]})
out = pd.merge(people, scores, on="id", how="left")
print(out.shape, int(out["score"].isna().sum()))

Exercise

Try It Yourself

Write with_scores(people, scores) that joins on id, keeping every person even when they have no score, and returns the number of people with no score.

import pandas as pd

def with_scores(people, scores):
    # Keep everyone. Return how many have no score.
    return 0

print(with_scores(pd.DataFrame({"id": [1, 2], "name": ["Ada", "Bo"]}),
                  pd.DataFrame({"id": [1], "score": [92]})))

Press Run to see output

Check Your Understanding

← Previous Next