Level 1 · Add product names to order lines

Why tables are split

order_items stores a product_id on each line instead of the product’s name, category and price. That’s deliberate: the product’s details live in one place, products, so renaming a product means changing one row, not 30,000. The cost is that you have to join the tables back together when you need both.

merge

order_items.merge(products, on="product_id")

For each order line, pandas finds the product row with the same product_id and puts their columns side by side. The column you match on is the key. If the key has different names in the two tables, use left_on= and right_on=. For example, each employee’s manager_id points at another employee’s employee_id, so this puts every employee next to their manager’s details:

employees.merge(employees, left_on="manager_id", right_on="employee_id", suffixes=("", "_manager"))

That gives 59 rows, not 60: the CEO has no manager, so the inner merge leaves that row out.

The how= argument decides what happens to rows without a match:

how	keeps
"inner" (default)	only rows with a match in both tables
"left"	every row from the left table; NaN where the right has no match
"right"	every row from the right table
"outer"	everything from both

Every order line here has a valid product, so inner and left give the same result. Write how= explicitly anyway; it tells the reader you thought about it.

Overlapping column names

Both tables have a unit_price column, and they mean different things: the price paid on the line versus today’s list price. pandas keeps both and adds suffixes, unit_price_x and unit_price_y. Those names are easy to mix up, so set your own:

order_items.merge(products, on="product_id", suffixes=("_paid", "_list"))

Or select only the columns you need from the right table before merging, which also keeps the result narrow:

order_items.merge(products[["product_id", "name", "category"]], on="product_id")

Check the row count

The single most useful habit with merges: compare row counts before and after.

len(order_items)                                   # lines before
len(order_items.merge(products, on="product_id"))  # should be the same

Each line has exactly one product, so the count must not change. If it grew, the right table had duplicate keys and some lines were copied. If it shrank, some lines had no match and an inner join dropped them. You’ll see both problems later in this chapter.

pandas can check this for you. validate="many_to_one" says “many lines may share a product, but each product appears once in products”, and raises an error if that’s not true:

order_items.merge(products, on="product_id", how="inner", validate="many_to_one")

Other options are "one_to_one" and "one_to_many". Adding validate costs nothing and turns a silent mistake into a loud one.

Common mistakes

• Merging without on=. pandas then joins on every column name the tables share, here product_id and unit_price, and most lines fail to match because prices changed.
• Using unit_price_x later without remembering which table it came from.
• Not checking the row count after a merge.

If you know SQL

This is SELECT ... FROM order_items JOIN products USING (product_id), and how= is the join type.