pandas · Chapter 16 of 22
Joining tables with merge
By the end of this chapter you can join two tables on a key with pd.merge, choose which rows survive with how=, see what matched with indicator=True, spot and stop a merge that multiplies rows with validate=, handle clashing column names and keys with different names, and join on the index. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Grouping and combining
- 4 tasks
- pandas
- In the all labs plan
The first 2 chapters of pandas are free. This one comes with the all labs plan.
Why you need this
An order line knows only its sku. Its category, its fabric and the weaver who made it are in the catalogue, and the weaver's name and village are in a third table, weavers.csv. Shabnam's question "which fabrics sell?" needs the lines and the catalogue together; Harpreet's weaver payouts need all three. Today Harpreet does it with a lookup formula dragged down 14,128 rows.
With what you know so far, you could build a dict from sku to fabric and look every line up in a loop:
import pandas as pd
catalogue = pd.read_csv("/workspace/data/products.csv")
site = pd.read_csv("/workspace/data/orders_site.csv")
fabric_of = dict(zip(catalogue["sku"], catalogue["fabric"]))
fabrics = [fabric_of[s] for s in site["sku"]]
KeyError: 'kg-dup-010'
It stops at the first hand-typed sku. Patch that with .get and it runs, but then you need a second dict for the category, a third for the weaver, and a second hop from the weaver to the village, and nothing tells you how many lines found no product or whether a sku was in the catalogue twice. A merge does all of it in one call and lets you check each of those.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
products.py: every line with its category and fabric
Write with_products(lines, catalogue) in products.py. lines is the website's lines as read_csv reads orders_site.csv; catalogue is products.csv. Return every line with two more columns from its product: category and fabric.
The result has the lines' own columns, in their order, then category, then fabric, and no other column from the catalogue. It has one row for each line, every line kept, in the lines' order, labelled 0, 1, 2 and so on. The file has 141 skus typed by hand ("kg-stl-010", "KG-STL-010 " with a space) that match no product: those lines stay, with a missing value in category and fabric.
The Check tries the whole website file, and 5 small lines (one with a hand-typed sku, one product on two lines) with a small catalogue of 4 products.
- Task 2
weaver_revenue.py: every weaver's revenue
Write weaver_revenue(lines, catalogue, weavers) in weaver_revenue.py. lines is the website's delivered lines, with their skus already cleaned and no blank qty (columns order_id, sku, qty, price_rs and the file's others); catalogue is products.csv; weavers is weavers.csv. A line's amount is price_rs times qty, and it belongs to the weaver of its product (the catalogue's weaver_id).
Return a DataFrame with exactly the columns weaver_id, name and revenue_rs: one row for every weaver in weavers, in its order, labelled 0, 1, 2 and so on. name is the weaver's name (from weavers); revenue_rs is the sum of the amounts of their products' lines, a decimal number, and 0 for a weaver with no lines. W29 and W39 have no products and must be there with 0. KG-SAR-012 and KG-STL-006 are by W40, who is not in weavers.csv, so their lines count for nobody. Careful: the catalogue and the weavers both have a column called name.
The Check tries the real files, and 5 lines with 4 products (one by W40) and 3 weavers (one with no products).
- Task 3
matching.py: what did not match
Write two functions in matching.py; lines are website lines (order_id, sku and more columns) and catalogue is products.csv.
unmatched(lines, catalogue) returns a DataFrame with exactly the columns order_id and sku: one row for each line whose sku is in no product of the catalogue. When every sku matches, it is an empty DataFrame with those two columns.
never_sold(lines, catalogue) returns a DataFrame with exactly the columns sku and name: one row for each product of the catalogue that no line sold (a product sold on many lines is sold, and is not in it). When every product sold, it is an empty DataFrame with those two columns.
The order of the rows and their labels do not matter. The Check tries the whole website file, June 2026's delivered lines, lines whose skus all match, and 5 small lines with a small catalogue.
- Task 4 · interview question
Interview: category_revenue.py, revenue that doubled
Harpreet's category_revenue(lines, catalogue) returns each category's revenue: each line's price_rs times qty, summed by its product's category. With this month's catalogue export every category came out exactly twice as big, because the export had run twice into one file and every sku is in it two times. Fix it so every line counts once, however many times a sku is in the catalogue (the rows of a repeated sku have the same category).
It returns a Series labelled by category, in alphabetical order (dupatta, kurta, saree, stole), with each category's revenue as a decimal number. lines is the website's delivered lines with their skus cleaned and no blank qty. The Check tries a catalogue exported twice, one with only its stoles twice (with another mrp_rs on the second copy), the plain catalogue, and a small catalogue exported twice.
Devika will ask: why did the revenue double, and what does validate= do to catch it before a report goes out?
What you will learn
- pd.merge on a key: each row of one table gets the columns of the matching row of the other.
- how=: inner, left, right and outer, and which rows each keeps.
- indicator=True: a _merge column that says where each row came from.
- Duplicate keys multiply rows, and validate= stops it with a MergeError.
- suffixes= for column names both tables have; left_on and right_on for keys with different names.
- join: a merge onto another table's index.
More in Grouping and combining
Build "Joining tables with merge" yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of pandas are free. This one comes with the all labs plan.