These are the notes for Data Analytics with Python, episode 26. Real data never lives in one table: each stall sends its own file, prices live in a menu, stock lives somewhere else. Here you invent the three ways to put tables together: stacking them with concat, matching rows by a key with merge, and matching on the index with DataFrame.join, plus lookup tables, the join types (inner, left, right, outer, cross), left_on / right_on, suffixes, indicator, and validate against the many-to-many row explosion. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.
The data: two stall files and a menu
pune and nashik have the same columns (an order id, the item, the quantity). menu has each item's price. Note that Poha is sold in Pune but is not on the menu, and Vada Pav is on the menu but nobody ordered it.
import sys; sys.tracebacklimit = 0
import pandas as pd
pune = pd.DataFrame({"order_id": [1, 2, 3, 4],
"item": ["Chai", "Samosa", "Chai", "Poha"],
"qty": [2, 3, 1, 2]})
nashik = pd.DataFrame({"order_id": [1, 2, 3],
"item": ["Coffee", "Chai", "Samosa"],
"qty": [1, 4, 2]})
menu = pd.DataFrame({"item": ["Chai", "Samosa", "Coffee", "Vada Pav"],
"price": [20, 15, 30, 25]})
Stacking tables: concat
pd.concat (concatenate: join end to end) takes a list of tables and puts them one under the other, like stapling two pages of a register together.
both = pd.concat([pune, nashik])
print(both)
print(both.loc[1])
order_id item qty
0 1 Chai 2
1 2 Samosa 3
2 3 Chai 1
3 4 Poha 2
0 1 Coffee 1
1 2 Chai 4
2 3 Samosa 2
order_id item qty
1 2 Samosa 3
1 2 Chai 4
Seven rows, but concat keeps each table's own index, so labels 0–2 appear twice and loc[1] returns two different orders. Duplicate labels make lookups and later merges wrong.
Fix 1: ignore_index=True throws away the old labels and numbers the result 0 … n−1:
print(pd.concat([pune, nashik], ignore_index=True))
order_id item qty
0 1 Chai 2
1 2 Samosa 3
2 3 Chai 1
3 4 Poha 2
4 1 Coffee 1
5 2 Chai 4
6 3 Samosa 2
Fix 2: keys gives each input a name that becomes an outer index level (a MultiIndex, episode 25), so you always know where a row came from. names labels the levels; reset_index(level="city") turns the outer level into a normal column:
both = pd.concat([pune, nashik], keys=["Pune", "Nashik"],
names=["city", "row"])
print(both.reset_index(level="city").head(5))
city order_id item qty
row
0 Pune 1 Chai 2
1 Pune 2 Samosa 3
2 Pune 3 Chai 1
3 Pune 4 Poha 2
0 Nashik 1 Coffee 1
You can also pass a dictionary {"Pune": pune, "Nashik": nashik}: its keys become the keys (used in the mini project).
concat matches columns by name
print(pd.concat([pune, menu]))
order_id item qty price
0 1.0 Chai 2.0 NaN
1 2.0 Samosa 3.0 NaN
2 3.0 Chai 1.0 NaN
3 4.0 Poha 2.0 NaN
0 NaN Chai NaN 20.0
1 NaN Samosa NaN 15.0
2 NaN Coffee NaN 30.0
3 NaN Vada Pav NaN 25.0
Columns that exist in only one input are filled with NaN in the other. Stacking is for tables with the same columns. Rename columns first if they're spelled differently.
axis: rows or columns
axis=0 (the default) stacks rows. axis=1 puts tables side by side as extra columns, pairing rows by their index label, which for default indexes means by position.
Why side by side goes wrong
To price each Pune order, the obvious attempt glues the menu next to the orders:
print(pd.concat([pune, menu], axis=1))
order_id item qty item price
0 1 Chai 2 Chai 20
1 2 Samosa 3 Samosa 15
2 3 Chai 1 Coffee 30
3 4 Poha 2 Vada Pav 25
Row 2 is a Chai order next to Coffee's price; row 3 is Poha next to Vada Pav. Rows 0–1 were right only by luck. Why: the menu is not in the same order as the orders (and never will be). We need to pair rows by matching the item name. A column used for matching is called a key (join key).
Lookup tables with map
Series.map (episode 17) replaces each value by looking it up. Passing the menu's price column fails:
pune["price"] = pune["item"].map(menu["price"])
print(pune)
order_id item qty price
0 1 Chai 2 NaN
1 2 Samosa 3 NaN
2 3 Chai 1 NaN
3 4 Poha 2 NaN
map looks values up in the index of the Series it receives, and menu["price"] has the index 0–3, not item names.
A lookup table is a Series indexed by the key: give it a key, get the value, like a price list on the wall. Build it with set_index:
price = menu.set_index("item")["price"]
print(price)
pune["price"] = pune["item"].map(price)
print(pune)
item
Chai 20
Samosa 15
Coffee 30
Vada Pav 25
Name: price, dtype: int64
order_id item qty price
0 1 Chai 2 20.0
1 2 Samosa 3 15.0
2 3 Chai 1 20.0
3 4 Poha 2 NaN
map is perfect for adding one column. For bringing over several columns, or for more control, use merge.
merge: match rows by a key
Real-life picture: a school has an attendance register and a marks register; both list the roll number. Combining them means matching rows by roll number. The join type (how=) decides what happens to keys that have no match:
how= |
Keeps | Unmatched keys | School picture |
|---|---|---|---|
"inner" (default) |
keys in both tables | dropped | only students in both registers |
"left" |
all left rows | right columns NaN | every student, marks blank if missing |
"right" |
all right rows | left columns NaN | every marks entry |
"outer" |
everything from both | NaN on either side | everyone from both registers |
"cross" |
every left row × every right row | (no key) | every student × every subject |
pd.merge(left, right, on="key") and left.merge(right, on="key") are the same.
Inner join: the silent row loss
print(pd.merge(pune, menu, on="item"))
order_id item qty price
0 1 Chai 2 20
1 2 Samosa 3 15
2 3 Chai 1 20
Four orders went in, three came out: Poha has no menu row, so the inner join dropped it without a warning. In a revenue report that's lost money.
Left join: keep every order
print(pune.merge(menu, on="item", how="left"))
order_id item qty price
0 1 Chai 2 20.0
1 2 Samosa 3 15.0
2 3 Chai 1 20.0
3 4 Poha 2 NaN
For "orders + lookup table", a left join is almost always what you want: every order is kept, and NaN shows exactly which keys are missing from the lookup.
Right, outer and indicator
print(pune.merge(menu, on="item", how="right"))
print(pune.merge(menu, on="item", how="outer", indicator=True))
order_id item qty price
0 1.0 Chai 2.0 20
1 3.0 Chai 1.0 20
2 2.0 Samosa 3.0 15
3 NaN Coffee NaN 30
4 NaN Vada Pav NaN 25
order_id item qty price _merge
0 1.0 Chai 2.0 20.0 both
1 3.0 Chai 1.0 20.0 both
2 NaN Coffee NaN 30.0 right_only
3 4.0 Poha 2.0 NaN left_only
4 2.0 Samosa 3.0 15.0 both
5 NaN Vada Pav NaN 25.0 right_only
indicator=True adds a column _merge that says where each row came from: both, left_only or right_only. It's the best way to audit a merge: count the left_only rows before trusting a report. An outer merge sorts the result by the key.
Cross join: every combination
A cross join has no key: it pairs every row of one table with every row of the other (a Cartesian product). 2 items × 2 sizes = 4 rows, handy for building a full price grid or a calendar of every (store, day) pair:
sizes = pd.DataFrame({"size": ["Small", "Large"]})
print(menu.head(2).merge(sizes, how="cross"))
item price size
0 Chai 20 Small
1 Chai 20 Large
2 Samosa 15 Small
3 Samosa 15 Large
left_on / right_on and suffixes
When the key has different names in the two tables, use left_on for the left table's key column and right_on for the right's. Both key columns are kept; drop one afterwards if you like.
menu2 = menu.rename(columns={"item": "name"})
print(pune.merge(menu2, left_on="item", right_on="name"))
order_id item qty name price
0 1 Chai 2 Chai 20
1 2 Samosa 3 Samosa 15
2 3 Chai 1 Chai 20
A key that doesn't exist:
print(pune.merge(menu, on="Item"))
KeyError: 'Item'
When both tables have other columns with the same name, pandas can't keep two columns called item, so it adds the default suffixes _x (left) and _y (right):
print(pune.merge(nashik, on="order_id"))
order_id item_x qty_x item_y qty_y
0 1 Chai 2 Coffee 1
1 2 Samosa 3 Chai 4
2 3 Chai 1 Samosa 2
Choose clear endings with suffixes=:
print(pune.merge(nashik, on="order_id",
suffixes=("_pune", "_nashik")))
order_id item_pune qty_pune item_nashik qty_nashik
0 1 Chai 2 Coffee 1
1 2 Samosa 3 Chai 4
2 3 Chai 1 Samosa 2
How many rows does a merge make? The row explosion
The rule behind every surprise: each left row is repeated once for every matching right row.
| Relationship | Meaning | Example | Rows |
|---|---|---|---|
| one-to-one | each key once on both sides | student ↔ ID card | ≤ min of both |
| one-to-many / many-to-one | a key once on one side, repeated on the other | many orders → one menu row | the "many" side's count |
| many-to-many | a key repeated on both sides | 3 × 2 | multiplied |
If the menu accidentally has Chai twice, every Chai order is silently doubled:
extra = pd.DataFrame({"item": ["Chai"], "price": [25]})
menu_dup = pd.concat([menu, extra], ignore_index=True)
m = pune.merge(menu_dup, on="item")
print(m)
print(len(pune), "->", len(m))
order_id item qty price
0 1 Chai 2 20
1 1 Chai 2 25
2 2 Samosa 3 15
3 3 Chai 1 20
4 3 Chai 1 25
4 -> 5
And when both sides repeat a key, the rows multiply (3 × 2 = 6). On real data this can turn thousands of rows into millions:
a = pd.DataFrame({"k": ["x", "x", "x"], "a": [1, 2, 3]})
b = pd.DataFrame({"k": ["x", "x"], "b": [10, 20]})
print(len(a.merge(b, on="k")))
6
validate: make merge check the relationship
validate= tells merge what relationship you expect; it raises an error instead of silently multiplying rows. Options: "one_to_one" ("1:1"), "one_to_many" ("1:m"), "many_to_one" ("m:1"), "many_to_many" ("m:m", no check).
extra = pd.DataFrame({"item": ["Chai"], "price": [25]})
menu_dup = pd.concat([menu, extra], ignore_index=True)
pune.merge(menu_dup, on="item", validate="many_to_one")
pandas.errors.MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
Duplicates in right:
item
Chai ...
A loud error beats a silently wrong report. Two habits: validate every lookup merge, and after a left join to a lookup table, check the row count didn't change (assert len(merged) == len(orders)).
DataFrame.join: match on the index
DataFrame.join is a shortcut that matches on the index of the right table. It's a left join by default. With on= the left side uses a column:
m = menu.set_index("item")
print(pune.join(m, on="item"))
order_id item qty price
0 1 Chai 2 20.0
1 2 Samosa 3 15.0
2 3 Chai 1 20.0
3 4 Poha 2 NaN
Without on=, it matches index to index. Overlapping column names are refused:
print(pune.join(nashik))
ValueError: columns overlap but no suffix specified: Index(['order_id', 'item', 'qty'], dtype='str')
Pass lsuffix= / rsuffix=, or use merge(..., suffixes=...). join shines when both tables are indexed by the same thing:
stock = pd.DataFrame({"stock": [50, 20]}, index=["Chai", "Coffee"])
print(menu.set_index("item").join(stock))
price stock
item
Chai 20 50.0
Samosa 15 NaN
Coffee 30 20.0
Vada Pav 25 NaN
| Tool | Matches on | Default | Use it for |
|---|---|---|---|
concat |
column names (axis 0) / index (axis 1) | stack rows | files with the same columns |
map |
a lookup Series' index | – | one column from a lookup table |
merge |
columns (on, left_on/right_on) or indexes |
inner | the general tool |
join |
the right table's index | left | tables indexed by the same key |
Mini project: city_revenue()
Revenue per city from separate stall files and a menu, with every step checked: concat a dictionary of stalls (keys become the city), a left merge validated many-to-one with an indicator, a warning for items missing from the menu, then qty × price summed per city (min_count=1 from episode 24 keeps an all-missing city honest).
def city_revenue(stalls, menu):
orders = pd.concat(stalls, names=["city", "row"]).reset_index(level="city")
m = orders.merge(menu, on="item", how="left",
validate="many_to_one", indicator=True)
print("not on menu:", m.loc[m["_merge"] == "left_only", "item"].tolist())
m["revenue"] = m["qty"] * m["price"]
return m.groupby("city", sort=False)["revenue"].sum(min_count=1)
print(city_revenue({"Pune": pune, "Nashik": nashik}, menu))
not on menu: ['Poha']
city
Pune 105.0
Nashik 140.0
Name: revenue, dtype: float64
Poha is reported instead of hidden; Pune earned ₹105 from priced items (2×20 + 3×15 + 1×20) and Nashik ₹140 (1×30 + 4×20 + 2×15).
Common mistakes
| Code / habit | Real error / symptom | Why | Fix |
|---|---|---|---|
pd.concat([pune, nashik]) then loc[1] |
two rows for label 1 | each input keeps its index | ignore_index=True or keys= |
pd.concat([pune, menu]) |
NaN-filled columns | concat matches column names | stack only same-column tables |
pd.concat([pune, menu], axis=1) |
no error; Chai next to Coffee's price | pairs rows by position | merge(..., on="item") |
pune["item"].map(menu["price"]) |
all NaN | lookup Series indexed 0..3 | map(menu.set_index("item")["price"]) |
pune.merge(menu, on="item") |
no error; 4 orders → 3 rows | inner join drops unmatched keys | how="left", indicator=True |
merge(menu, on="Item") |
KeyError: 'Item' |
key not in both tables | exact name, or left_on/right_on |
| merge with a duplicate key | 4 orders → 5 rows; doubled revenue | many-to-many explosion | validate="many_to_one" → MergeError: Merge keys are not unique in right dataset; not a many-to-one merge |
pune.join(nashik) |
ValueError: columns overlap but no suffix specified: Index(['order_id', 'item', 'qty'], dtype='str') |
same column names on both sides | lsuffix=/rsuffix= or merge(suffixes=) |
item_x, item_y in a report |
confusing columns | default suffixes | suffixes=("_pune", "_nashik") |
Practice
All practice questions use these tables:
import sys; sys.tracebacklimit = 0
import pandas as pd
students = pd.DataFrame({"roll": [1, 2, 3, 4],
"name": ["Asha", "Ravi", "Meena", "Kiran"],
"section": ["A", "A", "B", "B"]})
marks = pd.DataFrame({"roll": [1, 2, 3, 5],
"maths": [78, 64, 91, 70]})
teachers = pd.DataFrame({"section": ["A", "B"],
"teacher": ["Mrs Rao", "Mr Iyer"]})
1. Stack two days of attendance (day1, day2, same columns) into one table with a day column, using keys.
Solution
day1 = pd.DataFrame({"roll": [1, 2], "present": [True, False]})
day2 = pd.DataFrame({"roll": [1, 2], "present": [True, True]})
att = pd.concat({"Mon": day1, "Tue": day2}, names=["day", "row"]).reset_index(level="day")
print(att.reset_index(drop=True))
day roll present
0 Mon 1 True
1 Mon 2 False
2 Tue 1 True
3 Tue 2 True
2. Every student with their maths marks (blank if missing). How many rows? Who has no marks?
Solution
sm = students.merge(marks, on="roll", how="left", indicator=True)
print(sm)
print(sm.loc[sm["_merge"] == "left_only", "name"].tolist())
roll name section maths _merge
0 1 Asha A 78.0 both
1 2 Ravi A 64.0 both
2 3 Meena B 91.0 both
3 4 Kiran B NaN left_only
['Kiran']
3. Which marks belong to a roll number that isn't in the student list?
Solution
o = students.merge(marks, on="roll", how="outer", indicator=True)
print(o[o["_merge"] == "right_only"])
roll name section maths _merge
4 5 NaN NaN 70.0 right_only
4. Add each student's teacher with map and with merge; validate the merge.
Solution
t = teachers.set_index("section")["teacher"]
print(students["section"].map(t).tolist())
st = students.merge(teachers, on="section", how="left", validate="many_to_one")
print(st)
assert len(st) == len(students)
['Mrs Rao', 'Mrs Rao', 'Mr Iyer', 'Mr Iyer']
roll name section teacher
0 1 Asha A Mrs Rao
1 2 Ravi A Mrs Rao
2 3 Meena B Mr Iyer
3 4 Kiran B Mr Iyer
5. The teacher table accidentally lists section A twice. Show the row explosion, then catch it with validate.
Solution
bad = pd.concat([teachers, pd.DataFrame({"section": ["A"], "teacher": ["Mr Das"]})], ignore_index=True)
print(len(students.merge(bad, on="section")))
students.merge(bad, on="section", validate="many_to_one")
6
pandas.errors.MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
Duplicates in right:
section
A ...
6. Build a timetable grid of every section × every period (1–3) with a cross join.
Solution
periods = pd.DataFrame({"period": [1, 2, 3]})
print(teachers.merge(periods, how="cross"))
section teacher period
0 A Mrs Rao 1
1 A Mrs Rao 2
2 A Mrs Rao 3
3 B Mr Iyer 1
4 B Mr Iyer 2
5 B Mr Iyer 3
7. Use join to add maths marks to students indexed by roll.
Solution
print(students.set_index("roll").join(marks.set_index("roll")))
name section maths
roll
1 Asha A 78.0
2 Ravi A 64.0
3 Meena B 91.0
4 Kiran B NaN
Quick quiz
a = pd.DataFrame({"k": ["x", "x", "y"]})
b = pd.DataFrame({"k": ["x", "x", "z"],
"v": [1, 2, 3]})
print(len(a.merge(b, on="k", how="left")))
A. 3 B. 4 C. 5 D. 6
Answer
C. 5. Each of the two x rows matches two x rows (2 × 2 = 4); y has no match but a left join keeps it once; z is dropped.
Interview questions
concatvsmerge?concatstacks tables (rows or columns) by position/labels;mergematches rows by key values like a SQL JOIN.- Explain inner, left, right and outer joins. Inner keeps keys in both; left keeps all left rows; right keeps all right rows; outer keeps all keys from both, filling NaN.
- What is a cross join? Every row of one table paired with every row of the other (Cartesian product); no key.
- Why did my row count increase after a merge? Duplicate keys in the right table (many-to-many); each left row repeats per matching right row. Check with
validateandduplicated(). - How do you find rows that didn't match?
indicator=Trueand filter_merge == "left_only"/"right_only". - Keys with different names?
left_on="item", right_on="name". - What are
_xand_y? Default suffixes for overlapping non-key columns; setsuffixes=. mergevsjoin?joinis a convenience method matching on the index (left join by default);mergeis the general column-based tool.- Fastest way to add one column from a lookup table?
df["col"].map(lookup_series), with the lookup indexed by the key. - What does
validate="one_to_one"check? That the key is unique in both tables; otherwiseMergeError. - SQL equivalent?
SELECT * FROM orders LEFT JOIN menu ON orders.item = menu.item.
Glossary
| Term | Meaning | Example |
|---|---|---|
Concatenate (concat) |
Join tables end to end (stack rows, or side by side) | Pune + Nashik orders |
axis |
0 = along rows (stack), 1 = along columns (side by side) | axis=1 |
ignore_index |
Throw away input labels and number the result 0..n−1 | 0..6 |
keys |
Names for the inputs of concat, kept as an outer index level |
keys=["Pune", "Nashik"] |
names |
Names for the index levels created by keys |
names=["city", "row"] |
| Key (join key) | Column whose values are matched between tables | item |
| Lookup table | A table/Series indexed by a key that returns a value | price list |
map |
Replace each value via a lookup Series or dict | item.map(price) |
merge |
Combine tables by matching key values (a database join) | pune.merge(menu, on="item") |
Join type (how) |
What to do with unmatched keys | "left" |
| Inner join | Only keys present in both tables | Poha dropped |
| Left join | All rows of the left table | Poha kept, price NaN |
| Right join | All rows of the right table | Vada Pav kept |
| Outer join | All keys from both tables | everything |
| Cross join | Every left row × every right row | 2 × 2 = 4 |
| Cartesian product | All combinations of two sets | items × sizes |
on |
The key column(s) present in both tables | on="item" |
left_on / right_on |
Key columns with different names | left_on="item", right_on="name" |
suffixes |
Endings added to overlapping column names | ("_pune", "_nashik") |
indicator |
Adds _merge: both / left_only / right_only |
audit a merge |
| One-to-one / one-to-many / many-to-many | How often a key appears on each side | orders → menu = many-to-one |
| Row explosion | A merge creating more rows than expected because of repeated keys | 4 → 5 rows |
validate |
Make merge check the expected relationship | "many_to_one" |
MergeError |
The error raised when validation fails | duplicate Chai |
DataFrame.join |
Merge on the right table's index (left join by default) | pune.join(m, on="item") |
lsuffix / rsuffix |
Suffixes for join when columns overlap |
lsuffix="_p" |
set_index |
Turn a column into the index | menu.set_index("item") |
Summary
concatstacks tables with the same columns; useignore_indexorkeysso labels stay unique and the source is remembered.- Side by side (
axis=1) pairs rows by position: wrong for lookups. - A lookup table (Series indexed by the key) +
mapadds one column. mergematches by key: inner drops unmatched rows silently, left keeps every left row, outer keeps everything, cross makes every combination. Useindicator=Trueto audit.left_on/right_onfor different key names;suffixesfor overlapping columns.- Repeated keys multiply rows: always
validatelookup merges and check row counts. joinmatches on the index.- Next episode: reshaping with pivot, pivot_table, melt and crosstab.