Module 5: Combining tables · Lesson 5.2 · about 25 min
Joining tables with merge
Bring columns from one table into another by matching a key with pd.merge: inner, left, right and outer joins, different key names, several keys, and the indicator that shows which rows matched.
Module 5 · about 25 minutes · uses Lesson 2.1. You’ll be able to join tables on a key and choose the right join type.
In everyday terms: a seating plan and a guest list. Match each seat number to a name; “inner” keeps only seats with a guest, “left” keeps every seat and leaves empty ones blank.
1. Why join
Data is split across tables so each fact is stored once: orders hold a product_id, the products table holds names and categories. To report revenue by category, you bring the category into the orders.
2. merge
Then any summary by the new columns is one groupby away:
orders.merge(products, ...) is the same as pd.merge(orders, products, ...). suffixes names clashing columns (both tables have a price).
3. Join types
how= |
Keeps | Use for |
|---|---|---|
'inner' |
rows with a match on both sides | only what links up |
'left' |
every left row; NaN where no match | adding details to a main table (the most common) |
'right' |
every right row | the same, from the other side |
'outer' |
everything from both | finding what’s missing on either side |
S09 has sales but no store record (an orphan); S03 is a store with no sales. The _merge column says which side each row came from.
4. Different names and several keys
- pd.merge(left, right, on='key')
- Matches rows where key is equal and puts their columns side by side.
- how='inner'
- Keeps only keys found in both tables (the default).
- how='left'
- Keeps every left row; missing partners give NaN.
- how='outer', indicator=True
- Keeps everything and adds _merge: both, left_only or right_only.
- Physical meaning
- A join is a lookup done for every row at once. With unique keys on the right, a left join keeps exactly the left table's row count; any change in rows means a key was missing or repeated.
“An inner join is the safe default.” It silently drops orders whose store is missing from the store table, so revenue shrinks without a warning. Use a left join from the table you are counting, and look at what failed to match.
Watch it happen, one step at a time:
| order | store_id | qty |
|---|---|---|
| 1 | S01 | 2 |
| 2 | S02 | 1 |
| 3 | S09 | 4 |
| store_id | city |
|---|---|
| S01 | Toronto |
| S02 | Ottawa |
| S03 | Halifax |
orders.merge(stores, on='store_id', how='left')| order | store_id | qty | city |
|---|---|---|---|
| 1 | S01 | 2 | Toronto |
| 2 | S02 | 1 | Ottawa |
| 3 | S09 | 4 | NaN |
how='outer', indicator=True shows S09 as left_only and S03 as right_only.5. Your turn
How it works
Principle: for each key value, merge pairs every left row with every right row that has the same key. With a unique key on one side (one row per product), each order line finds at most one partner, so a left join keeps the row count. If the key repeats on both sides, rows multiply (next lesson).
SQL: pd.merge(a, b, on='k', how='left') is SELECT * FROM a LEFT JOIN b ON a.k = b.k.
6. Practice
- Which join lists every store, including stores with no sales?
- After a left join, how do you find rows that didn’t match?
- Why check
len()before and after a join?
Answers
- Stores on the left with
how='left'(or stores on the right withhow='right'). - Look for NaN in a column from the right table, or use
indicator=Trueand keep'left_only'. - A change in row count after a left join means duplicate keys on the right; totals would be inflated.
New words: merge, join, key, inner / left / right / outer join, orphan, indicator, suffixes.
- merge matches rows on a key; on= names it (left_on/right_on if names differ).
- inner keeps matches only; left keeps every left row; outer keeps all.
- Use indicator=True to see which keys failed to match.
- Count rows before and after every join.
Check yourself
Sources
- pandas documentation, User Guide: Merge, join, concatenate and compare https://pandas.pydata.org/docs/user_guide/merging.html
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.