Merging on a Key
Overview
Bringing two tables together on the column they share.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Join two tables on a shared column.
- Choose between inner, left, right and outer.
- Say what happens to a row that matches nothing.
- Check the row count against what you expected.
Why This Matters
A join is how two files become one answer, and the choice of how is a decision about whose rows matter. Getting it wrong does not raise — it silently drops the people you were asked about, and the summary that follows is computed over the wrong population.
Step 1: An inner join keeps the matches
The default keeps only the rows whose key appears in both tables.
Joining on a shared column
The key column appears once in the result; the other columns come from both sides.
import pandas as pd
people = pd.DataFrame({"id": [1, 2], "name": ["Ada", "Bo"]})
scores = pd.DataFrame({"id": [1], "score": [92]})
print(pd.merge(people, scores, on="id"))What an inner join costs you
Bo is gone. If the question was "what did each person score", an inner join has quietly answered a different one: "what did each person who has a score score".
import pandas as pd
people = pd.DataFrame({"id": [1, 2], "name": ["Ada", "Bo"]})
scores = pd.DataFrame({"id": [1], "score": [92]})
inner = pd.merge(people, scores, on="id")
print(len(people), "people ->", len(inner), "rows")Mini Practice #1: Inner against left
Try It YourselfJoin these two both ways and print the row count each gives.
Step 2: how= decides what happens to the rest
Four choices, and the right one depends on whose rows the question is about.
left keeps everyone on the left
Rows with no match get NaN in the columns that came from the right. This is the one you want when the left table is the population you were asked about.
import pandas as pd
people = pd.DataFrame({"id": [1, 2], "name": ["Ada", "Bo"]})
scores = pd.DataFrame({"id": [1], "score": [92]})
print(pd.merge(people, scores, on="id", how="left"))And the other two
right is the mirror image; outer keeps everything from both sides and fills the gaps on each.
import pandas as pd
a = pd.DataFrame({"id": [1, 2], "x": ["a", "b"]})
b = pd.DataFrame({"id": [2, 3], "y": ["c", "d"]})
print(pd.merge(a, b, on="id", how="outer"))Counting what did not match
After a left join, the rows with NaN in a right-hand column are exactly the ones that found no partner. That number is usually worth reporting, not just fixing.
import pandas as pd
people = pd.DataFrame({"id": [1, 2, 3], "name": ["a", "b", "c"]})
scores = pd.DataFrame({"id": [1], "score": [92]})
out = pd.merge(people, scores, on="id", how="left")
print(int(out["score"].isna().sum()), "of", len(people), "had no score")Mini Practice #2: Who is missing
Try It YourselfLeft join, then print the names of the people who had no score.
Step 3: When to Use It
Adding columns from another table
Names from a people table onto a scores table, matched on the id they share.
pd.merge(scores, people, on="id", how="left")Choosing which rows survive
inner keeps matches only; left keeps every row of the main table.
When Not to Use It
Do not merge without checking the row count before and after. An inner join drops unmatched rows silently, and duplicate keys multiply them.
End-of-Lesson Exercises
Exercise 1: Keep everyone, count the gaps
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.
how="left" keeps every person. The people who matched nothing have a missing score, and isna().sum() counts them.
Exercise 2: Inner against left, as a pair
Write join_counts(left, right) returning a two-element list of [rows in an inner join on id, rows in a left join]. The pair is what tells you whether every left row found a partner.
Two merges, one with the default how and one with how="left", and len() on each. When the two numbers differ, the difference is how many left rows matched nothing.