All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

merge brings columns from one table into another by matching a key. The join type decides what happens to rows that find no partner.

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

Pythonorders.csv, products.csv

Then any summary by the new columns is one groupby away:

Pythonorders.csv, products.csv

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
Python

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

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

Orders.
orderstore_idqty
1S012
2S021
3S094
Stores.
store_idcity
S01Toronto
S02Ottawa
S03Halifax
Match keys. S01 and S02 are in both; S09 is only in orders; S03 only in stores.
Left join. orders.merge(stores, on='store_id', how='left')
orderstore_idqtycity
1S012Toronto
2S021Ottawa
3S094NaN
Inner join. Order 3 disappears: 2 rows, qty 3 instead of 7.
Find the gap. how='outer', indicator=True shows S09 as left_only and S03 as right_only.

5. Your turn

Your turnorders.csv, customers.csv
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

  1. Which join lists every store, including stores with no sales?
  2. After a left join, how do you find rows that didn’t match?
  3. Why check len() before and after a join?
Answers
  1. Stores on the left with how='left' (or stores on the right with how='right').
  2. Look for NaN in a column from the right table, or use indicator=True and keep 'left_only'.
  3. 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

1You want every order, plus the product name where known. Which join?
2The key is 'product_id' in orders and 'id' in products. How?
3What does indicator=True add?

Sources

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.