SyntaxPilot
Courses/Data Analytics with Python/Lesson 26

Data Analytics with Python · Lesson 26 of 90 · 13 min

Combining data: concat, merge and join

concataxisignore_indexkeysmergejoin types (inner, left, right, outer, cross)onleft_on / right_onsuffixesindicatorvalidate (one_to_one, one_to_many, many_to_many)many-to-many row explosionDataFrame.joinlookup tables

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

  1. concat vs merge? concat stacks tables (rows or columns) by position/labels; merge matches rows by key values like a SQL JOIN.
  2. 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.
  3. What is a cross join? Every row of one table paired with every row of the other (Cartesian product); no key.
  4. 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 validate and duplicated().
  5. How do you find rows that didn't match? indicator=True and filter _merge == "left_only" / "right_only".
  6. Keys with different names? left_on="item", right_on="name".
  7. What are _x and _y? Default suffixes for overlapping non-key columns; set suffixes=.
  8. merge vs join? join is a convenience method matching on the index (left join by default); merge is the general column-based tool.
  9. Fastest way to add one column from a lookup table? df["col"].map(lookup_series), with the lookup indexed by the key.
  10. What does validate="one_to_one" check? That the key is unique in both tables; otherwise MergeError.
  11. 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

  • concat stacks tables with the same columns; use ignore_index or keys so 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) + map adds one column.
  • merge matches by key: inner drops unmatched rows silently, left keeps every left row, outer keeps everything, cross makes every combination. Use indicator=True to audit.
  • left_on/right_on for different key names; suffixes for overlapping columns.
  • Repeated keys multiply rows: always validate lookup merges and check row counts.
  • join matches on the index.
  • Next episode: reshaping with pivot, pivot_table, melt and crosstab.