Joining: merge

In short: merge in pandas joins two tables on a shared key, like a JOIN in SQL, and how decides which rows stay.

Problem

“Which category brings in the most money?”

The manager asks which category sells the most. The order lines say how many units of which product were bought, and at what price:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
products = pd.read_csv('products.csv')
print(items.columns.tolist())
print(products.columns.tolist())
items.head(3)

import pandas as pd items = pd.read_csv('order_items.csv') products = pd.read_csv('products.csv') print(items.columns.tolist()) print(products.columns.tolist()) items.head(3)

▶ RunRestore the code

There is no category there, though. There is only product_id, the product number. The name and the category are in products.csv, and the name of the category is in a third table, categories.csv.

Shop data is almost always split like this: each thing is written down in one place, and the other tables point to it with a number. To answer, you have to join the tables into one.

The idea

Joining on a key: merge

merge takes each row of the left table, looks in the right table for the row with the same value in the key column, and glues the two rows into one. This is called a join, and the column it looks things up by is the key. In SQL the same thing is JOIN.

Here the key has a different name in each table: product_id in the lines, id in the products. left_on and right_on say which column is on which side:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
products = pd.read_csv('products.csv')
lines = items.merge(products, left_on='product_id', right_on='id')
print(items.shape, lines.shape)
lines[['order_id', 'product_id', 'id', 'name', 'quantity', 'unit_price']].head(3)

import pandas as pd items = pd.read_csv('order_items.csv') products = pd.read_csv('products.csv') lines = items.merge(products, left_on='product_id', right_on='id') print(items.shape, lines.shape) lines[['order_id', 'product_id', 'id', 'name', 'quantity', 'unit_price']].head(3)

▶ RunRestore the code

The rows stay 152, and the columns become 10: the four from items and the six from products. The columns product_id and id hold the same number, now twice.

So more often you first rename the key so that it has the same name, and join with on. You also take only the columns you need:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
lines = items.merge(products[['product_id', 'name', 'category_id']], on='product_id')
lines.head(3)

import pandas as pd items = pd.read_csv('order_items.csv') products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) lines = items.merge(products[['product_id', 'name', 'category_id']], on='product_id') lines.head(3)

▶ RunRestore the code

See it

How merge finds the pairs

Here is the same thing on small tables: 5 order lines and a price list with 4 rows. In the price list coffee (17) appears twice, with the old and the new price, and product 26 is not there yet. With how="left" every order line stays:

Pythondata: the shop tables

import pandas as pd

lines = pd.DataFrame({
    'order_id': [7, 7, 8, 8, 9],
    'product_id': [1, 17, 5, 1, 26],
    'quantity': [2, 1, 3, 1, 2],
})
catalog = pd.DataFrame({
    'product_id': [1, 5, 17, 17],
    'name': ['White bread', 'Fresh milk', 'Coffee beans', 'Coffee beans'],
    'price': [1.2, 1.6, 13.9, 14.9],
})
both = lines.merge(catalog, on='product_id', how='left')
print(len(lines), len(both))
both

import pandas as pd lines = pd.DataFrame({ 'order_id': [7, 7, 8, 8, 9], 'product_id': [1, 17, 5, 1, 26], 'quantity': [2, 1, 3, 1, 2], }) catalog = pd.DataFrame({ 'product_id': [1, 5, 17, 17], 'name': ['White bread', 'Fresh milk', 'Coffee beans', 'Coffee beans'], 'price': [1.2, 1.6, 13.9, 14.9], }) both = lines.merge(catalog, on='product_id', how='left') print(len(lines), len(both)) both

▶ RunRestore the code

import pandas as pd

lines = pd.DataFrame({
    'order_id': [7, 7, 8, 8, 9],
    'product_id': [1, 17, 5, 1, 26],
    'quantity': [2, 1, 3, 1, 2],
})
catalog = pd.DataFrame({
    'product_id': [1, 5, 17, 17],
    'name': ['White bread', 'Fresh milk', 'Coffee beans', 'Coffee beans'],
    'price': [1.2, 1.6, 13.9, 14.9],
})
both = lines.merge(catalog, on='product_id', how='left')
print(len(lines), len(both))
both

Step by step, in words

1. lines are 5 order lines. product_id says which product was bought.
2. catalog is the price list: 4 rows. Product 17 is in it twice, with the old and the new price.
3. Line 1: product_id 1. merge looks for every row with 1 in catalog: White bread. The pair becomes a row in both.
4. Line 2: product_id 17 is in catalog twice, at €13.90 and €14.90. The two pairs become two rows.
5. Line 3: product_id 5, that is Fresh milk.
6. Line 4: product_id 1, White bread again. One catalog row can pair with many lines.
7. Line 5: product 26 is not in catalog. With how='left' the line stays, and name and price are NaN.
8. 5 lines went in, 6 rows came out: the repeated key doubled a line. That is why you count len before and after.

• Every order line looks for all the rows in the price list with the same product_id.
• The coffee line finds two and becomes two rows. Five lines become 6.
• Product 26 finds nothing. With how="left" the line stays, and the columns from the price list are NaN.

Try it

How many rows?

1. If coffee (17) were in catalog three times, with three prices, how many rows would both have for the same 5 order lines?

785

Check

The idea

Which rows stay: how

Not every row has a pair. Two of the 20 customers have not ordered anything yet, and there are 60 orders. The how parameter decides what happens to the rows without a pair:

Pythondata: the shop tables

import pandas as pd

customers = pd.read_csv('customers.csv')
orders = pd.read_csv('orders.csv')
people = customers[['id', 'name']]
buys = orders[['customer_id', 'status']]
print(len(people), len(buys))
for how in ['inner', 'left', 'right', 'outer']:
    both = people.merge(buys, left_on='id', right_on='customer_id', how=how)
    print(how, len(both))

import pandas as pd customers = pd.read_csv('customers.csv') orders = pd.read_csv('orders.csv') people = customers[['id', 'name']] buys = orders[['customer_id', 'status']] print(len(people), len(buys)) for how in ['inner', 'left', 'right', 'outer']: both = people.merge(buys, left_on='id', right_on='customer_id', how=how) print(how, len(both))

▶ RunRestore the code

• inner (the default): only the pairs. 60 rows, one for each order. The customers without orders are gone.
• left: every row on the left, with NaN where there is no pair. 62 rows: 60 orders and the two customers without orders.
• right: every row on the right. Here it is 60, because every order has a customer.
• outer: every row from both sides.

indicator=True tells you where each row came from. It adds a _merge column with the values both, left_only and right_only:

Pythondata: the shop tables

import pandas as pd

customers = pd.read_csv('customers.csv')
orders = pd.read_csv('orders.csv')
people = customers[['id', 'name']]
buys = orders[['customer_id', 'status']]
both = people.merge(buys, left_on='id', right_on='customer_id', how='outer', indicator=True)
print(both['_merge'].value_counts())
both[both['_merge'] == 'left_only']

import pandas as pd customers = pd.read_csv('customers.csv') orders = pd.read_csv('orders.csv') people = customers[['id', 'name']] buys = orders[['customer_id', 'status']] both = people.merge(buys, left_on='id', right_on='customer_id', how='outer', indicator=True) print(both['_merge'].value_counts()) both[both['_merge'] == 'left_only']

▶ RunRestore the code

The two customers without orders have ids 19 and 20. right_only is 0: no order belongs to a customer missing from the table. If one did, that would be an error in the data, and indicator would show it.

More precisely, if you are curious

indicator is the quickest check after a join. value_counts() on _merge tells you at once how many rows found no pair. If you expect 0 and there are 300, the keys do not match: for example one is the text '17' and the other is the number 17.

Try it

Products without a review

Which products have no review at all yet? Join products with the reviews so that every product stays, and store in no_review only the rows without a pair.

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
reviews = pd.read_csv('reviews.csv')
both = products.merge(reviews[['product_id', 'rating']], left_on='id', right_on='product_id', how=..., indicator=...)
no_review = ...
print(len(no_review))

import pandas as pd products = pd.read_csv('products.csv') reviews = pd.read_csv('reviews.csv') both = products.merge(reviews[['product_id', 'rating']], left_on='id', right_on='product_id', how=..., indicator=...) no_review = ... print(len(no_review))

Check▶ Run

The idea

When the key repeats

Now the manager wants the product rating next to every order line. The reviews are in reviews.csv, and it seems natural to join them on product_id:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
reviews = pd.read_csv('reviews.csv')
with_rating = items.merge(reviews[['product_id', 'rating']], on='product_id', how='left')
print(len(items), len(with_rating))
print((items['quantity'] * items['unit_price']).sum().round(2))
print((with_rating['quantity'] * with_rating['unit_price']).sum().round(2))
print(reviews['product_id'].value_counts().head(3))

import pandas as pd items = pd.read_csv('order_items.csv') reviews = pd.read_csv('reviews.csv') with_rating = items.merge(reviews[['product_id', 'rating']], on='product_id', how='left') print(len(items), len(with_rating)) print((items['quantity'] * items['unit_price']).sum().round(2)) print((with_rating['quantity'] * with_rating['unit_price']).sum().round(2)) print(reviews['product_id'].value_counts().head(3))

▶ RunRestore the code

The rows became 167 instead of 152, and the total of the orders jumped from €1,963.50 to €2,485.60. Coffee has 3 reviews, so every coffee line paired with all three and became three rows. The frying pan (22) has 2 reviews. Nothing crashes, but the numbers are now wrong.

So you count the rows before and after every join. If the right table should have one row per key, tell pandas with validate:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
reviews = pd.read_csv('reviews.csv')
items.merge(reviews[['product_id', 'rating']], on='product_id', how='left', validate='many_to_one')

import pandas as pd items = pd.read_csv('order_items.csv') reviews = pd.read_csv('reviews.csv') items.merge(reviews[['product_id', 'rating']], on='product_id', how='left', validate='many_to_one')

▶ RunRestore the code

validate="many_to_one" means “on the left the key may repeat, on the right it must be unique”. If it is not, merge stops with MergeError and tells you which key repeats. There are also "one_to_one" and "one_to_many".

Try it

A rating on every line

Put the mean rating of the product next to every order line. The rows must stay as many as they are (152), and products without a review should get NaN.

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
reviews = pd.read_csv('reviews.csv')
avg = ...
with_rating = items.merge(..., on='product_id', how='left', validate='many_to_one')
print(len(items), len(with_rating))

import pandas as pd items = pd.read_csv('order_items.csv') reviews = pd.read_csv('reviews.csv') avg = ... with_rating = items.merge(..., on='product_id', how='left', validate='many_to_one') print(len(items), len(with_rating))

Check▶ Run

The idea

Two name columns: suffixes

Next comes the category. Both the products and the categories have id and name columns. After the join pandas keeps both and adds the endings _x (left) and _y (right). With suffixes you choose them yourself:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
categories = pd.read_csv('categories.csv')
both = products.merge(categories, left_on='category_id', right_on='id', how='left')
print(both.columns.tolist())
both = products.merge(categories[['id', 'name']], left_on='category_id', right_on='id', how='left', suffixes=('', '_category'))
print(both.columns.tolist())
both[['id', 'name', 'name_category']].tail(3)

import pandas as pd products = pd.read_csv('products.csv') categories = pd.read_csv('categories.csv') both = products.merge(categories, left_on='category_id', right_on='id', how='left') print(both.columns.tolist()) both = products.merge(categories[['id', 'name']], left_on='category_id', right_on='id', how='left', suffixes=('', '_category')) print(both.columns.tolist()) both[['id', 'name', 'name_category']].tail(3)

▶ RunRestore the code

suffixes=("", "_category") leaves the product columns as they are and adds _category to the category ones. The gift card has no category, so name_category is NaN.

Here is the trap of the lesson. If you forget how="left", the inner join drops the gift card without a single warning:

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})
lines = items.merge(products[['product_id', 'name', 'category_id']], on='product_id')
inner = lines.merge(categories[['category_id', 'category']], on='category_id')
left = lines.merge(categories[['category_id', 'category']], on='category_id', how='left')
print(len(lines), len(inner), len(left))
left[left['category'].isna()]

import pandas as pd items = pd.read_csv('order_items.csv') products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) lines = items.merge(products[['product_id', 'name', 'category_id']], on='product_id') inner = lines.merge(categories[['category_id', 'category']], on='category_id') left = lines.merge(categories[['category_id', 'category']], on='category_id', how='left') print(len(lines), len(inner), len(left)) left[left['category'].isna()]

▶ RunRestore the code

152 rows become 148. Four gift card sales, €240 in total, vanish from the report by category. With how="left" the rows stay 152, and the missing category shows up as NaN.

The idea

One under the other: concat

Sometimes tables should not be glued side by side but one under the other. The shop changed its system: the orders up to the end of 2025 were exported from the old one, and those from 2026 from the new one, with the same columns. pd.concat stacks them into one table:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('orders.csv')
# Two exports with the same columns: the old system (to the end of 2025) and the new one (from 2026).
old = orders[orders['ordered_at'] < '2026-01-01']
new = orders[orders['ordered_at'] >= '2026-01-01']
print(len(old), len(new))
together = pd.concat([old, new], ignore_index=True)
print(len(together), together.columns.tolist())
together.tail(3)

import pandas as pd orders = pd.read_csv('orders.csv') # Two exports with the same columns: the old system (to the end of 2025) and the new one (from 2026). old = orders[orders['ordered_at'] < '2026-01-01'] new = orders[orders['ordered_at'] >= '2026-01-01'] print(len(old), len(new)) together = pd.concat([old, new], ignore_index=True) print(len(together), together.columns.tolist()) together.tail(3)

▶ RunRestore the code

21 + 39 = 60 rows. ignore_index=True numbers the rows again from 0. Without it the indexes of both tables are kept and may repeat.

The dates here are text like “2025-09-01”, so comparing < '2026-01-01' in alphabetical order matches the order in time. Real dates come in the lesson “Dates and time series”.

More precisely, if you are curious

merge looks for pairs by a key and puts the columns side by side. concat looks for nothing: it stacks the rows (or, with axis=1, the columns) in the order of the tables. If the columns do not match, concat still works, but the missing cells become NaN, so compare df.columns of both tables first.

Exercise

The report by category

Answer the manager: what is the revenue of each category, without the cancelled orders? Join the lines with the orders (for status), with the products (for category_id) and with the categories, so that after every join there are still 152 rows. Products without a category go under “No category”. Store the result in by_category.

Pythondata: the shop tables

import pandas as pd

items = pd.read_csv('order_items.csv')
orders = pd.read_csv('orders.csv').rename(columns={'id': 'order_id'})
products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'})
categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'})

lines = items.merge(orders[['order_id', 'status']], on='order_id', how=..., validate=...)
lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how=..., validate=...)
lines = lines.merge(categories[['category_id', 'category']], on='category_id', how=..., validate=...)
print(len(items), len(lines))

lines['category'] = lines['category'].fillna(...)
sold = ...
by_category = ...

import pandas as pd items = pd.read_csv('order_items.csv') orders = pd.read_csv('orders.csv').rename(columns={'id': 'order_id'}) products = pd.read_csv('products.csv').rename(columns={'id': 'product_id'}) categories = pd.read_csv('categories.csv').rename(columns={'id': 'category_id', 'name': 'category'}) lines = items.merge(orders[['order_id', 'status']], on='order_id', how=..., validate=...) lines = lines.merge(products[['product_id', 'category_id']], on='product_id', how=..., validate=...) lines = lines.merge(categories[['category_id', 'category']], on='category_id', how=..., validate=...) print(len(items), len(lines)) lines['category'] = lines['category'].fillna(...) sold = ... by_category = ...

Check▶ Run

Test

Check yourself

1. What happens to a row of the left table that has no row with the same key on the right, if you do not set how?

It stays, with NaN in the columns from the right tableIt disappears from the result: how is "inner" by defaultmerge raises an error

2. 152 order lines joined with the reviews on product_id became 167. Why?

merge adds one row for each review without an orderA product with several reviews pairs with each of them, and the line repeatsThe reviews have empty values

3. What does validate="many_to_one" do?

It removes the repeated keys from the right tableIt checks that the key in the right table is unique, and raises MergeError if it is notIt keeps only the first pair for each row

4. Both products and categories have a name column. Which columns does joining them give?

Only name, from the left tablename_x and name_y, unless you set suffixesAn error: columns cannot share a name

5. You have the orders from the old system and from the new one, with the same columns. How do you make them one table?

old.merge(new, on="id")old.merge(new, how="outer")pd.concat([old, new], ignore_index=True)

Check

Start the lesson over

Related lessons

• Builds on: Filters and sorting · New columns · Types and missing values · Groups: groupby
• Continues in: Wide and long: pivot_table and melt · Dates and time series · The first chart · Which chart for which question
• Tasks on this topic: Wrangling data