Why merges matter
Most real analytics uses multiple tables:
- Customers table
- Orders table
- Products table
You typically need to combine them to answer questions.
Example tables
import pandas as pd
customers = pd.DataFrame({
"customer_id": [1, 2, 3],
"name": ["Asha", "Ravi", "Meera"],
})
orders = pd.DataFrame({
"order_id": [101, 102, 103, 104],
"customer_id": [1, 2, 2, 4],
"amount": [250, 180, 90, 300],
})
print(customers)
print(orders)merge: SQL-style joins
Inner join (only matching keys)
inner = customers.merge(orders, on="customer_id", how="inner")
print(inner)Left join (keep all left rows)
left = customers.merge(orders, on="customer_id", how="left")
print(left)Outer join (keep everything)
outer = customers.merge(orders, on="customer_id", how="outer", indicator=True)
print(outer)indicator=True adds a _merge column so you can see where each row came from.
join: align by index
join is convenient when keys are indices.
customers_idx = customers.set_index("customer_id")
orders_idx = orders.set_index("customer_id")
joined = customers_idx.join(orders_idx, how="left")
print(joined)concat: stack DataFrames
Append rows (same columns)
jan = pd.DataFrame({"customer_id": [1, 2], "amount": [100, 200]})
feb = pd.DataFrame({"customer_id": [2, 3], "amount": [150, 50]})
all_orders = pd.concat([jan, feb], ignore_index=True)
print(all_orders)Add columns side-by-side
df1 = pd.DataFrame({"A": [1, 2]})
df2 = pd.DataFrame({"B": [10, 20]})
combined = pd.concat([df1, df2], axis=1)
print(combined)Merge tips
- Use
validate=to enforce expected relationships (e.g., one-to-many). - Watch for duplicate column names; use
suffixes=("_left", "_right")when needed. - After a left join, missing matches become
NaN—handle them deliberately.
Patching data with combine_first
merge, join, and concat all combine different rows or columns from separate
tables. Sometimes you instead have two versions of the same table, and you want
to patch holes in one using values from the other — wherever the first is missing,
fall back to the second.
numpy.where(pd.isna(a), b, a) can do this, but it ignores index labels entirely: it
just lines values up by position, so it silently produces the wrong answer if the two
objects aren't already sorted the same way. combine_first does the same "fall back to
the other value" logic, but it aligns by label first, the same way arithmetic between
two Series does.
import pandas as pd
import numpy as np
primary = pd.DataFrame({
"customer_id": [1, 2, 3, 4],
"email": ["asha@mail.com", np.nan, "meera@mail.com", np.nan],
"phone": [np.nan, "9876500001", np.nan, "9876500004"],
}).set_index("customer_id")
backup = pd.DataFrame({
"customer_id": [1, 2, 3, 4],
"email": ["asha@old.com", "ravi@mail.com", "meera@old.com", "kabir@mail.com"],
"phone": ["9876500011", "9876500002", "9876500003", "9876500004"],
}).set_index("customer_id")
patched = primary.combine_first(backup)
print(patched)Every NaN cell in primary gets filled in from backup's matching row and column —
customer 1 keeps their own email (it wasn't missing) but borrows a phone number from
backup, while customer 2 does the opposite. Where both sides have a value, primary's
value always wins.
With DataFrames, combine_first works column by column, and the result has the
union of both objects' columns — think of it as "patching," not merging: it fills
gaps in your main table using a secondary source, rather than joining rows on a key.
Visualize it
An inner join keeps only matching keys; a left join keeps every row from the left table
and fills in NaN where there's no match on the right.
flowchart LR
A["customers"] --> M{"merge on customer_id"}
B["orders"] --> M
M -->|"how='inner'"| I["Only matching keys"]
M -->|"how='left'"| L["All of customers + matches"]
M -->|"how='outer'"| O["Everything, NaN where missing"]
🧪 Try It Yourself
Exercise 1 – Inner Join Two Tables
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Exercise 2 – Left Join Keeps Everything on the Left
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Exercise 3 – Stack Two DataFrames With concat
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Check your understanding
customers has ids 1, 2, 3 and orders has customer ids 1, 2, 2, 4. How many rows does an inner merge on customer_id return?
Only ids 1 and 2 match: customer 1 has one order and customer 2 has two, so the inner join gives 3 rows.
Show answer
B — 3 — Only ids 1 and 2 match: customer 1 has one order and customer 2 has two, so the inner join gives 3 rows.
For the same tables, how many rows does a left merge (customers on the left) return and what happens to Meera?
A left join keeps every customer. Meera has no orders, so her order_id and amount are NaN, and Ravi appears twice.
Show answer
A — 4 rows, and Meera gets NaN for the order columns — A left join keeps every customer. Meera has no orders, so her order_id and amount are NaN, and Ravi appears twice.
What does indicator=True add to a merge?
The _merge column tells you whether a row was left_only, right_only or both.
Show answer
A — A _merge column showing where each row came from — The _merge column tells you whether a row was left_only, right_only or both.
What does pd.concat([jan, feb], ignore_index=True) do when jan and feb each have 2 rows?
concat on the default axis appends rows, and ignore_index=True rebuilds the index.
Show answer
A — Stacks them into 4 rows with a fresh index 0 to 3 — concat on the default axis appends rows, and ignore_index=True rebuilds the index.
Next
With tables combined, you're ready to work with time-based data — continue to Working with Dates and Times (to_datetime, dt accessor).