Merging and Joining
The sales table says "Pune" but not which manager runs Pune or how big the store is. That lives in a second table. Real data is spread across several tables, and answering most questions means stitching them together on a shared column.
The rule
Match rows on a key;
howdecides what happens to rows with no partner.
A second table describes each branch:
branches = pd.DataFrame({
"branch": ["Pune", "Delhi", "Mumbai"],
"manager": ["Asha", "Ravi", "Meera"],
})Notice: Chennai appears in sales but not here, and Mumbai appears here but has no sales.
merged = sales.merge(branches, on="branch", how="left")merge looks up each sales row's branch in branches and copies the matching manager across. The how argument sets what to do with unmatched rows:
| how | keeps | rows here |
|---|---|---|
inner (default) |
only keys found in both tables | 6 |
left |
every row of the left table | 8 |
right |
every row of the right table | 7 |
outer |
every row of both | 9 |
With how="left", the two Chennai sales stay, and their manager is NaN because there was no partner. With inner, those two rows vanish, which is easy to miss.
If the key columns have different names, name each side: sales.merge(stores, left_on="branch", right_on="city").
When two tables have the same columns and you just want to stack them, for example March sales on top of April sales, use pd.concat([march, april]) instead. merge goes sideways (adds columns); concat goes downward (adds rows).
The shortcut
Before merging, predict the row count. Count how many left rows find a partner, then add the kept unmatched rows for the chosen how. If the real count differs, something is off: usually a duplicated key.
Count matches
Pune has 3 sales and Delhi has 3, all with a manager: 6 matched rows.
The trap is a key that repeats on the right. If branches accidentally listed Pune twice, every Pune sale would be copied twice, and totals would silently inflate. Check branches["branch"].is_unique first.
Drills
Answer fast — trust the rule.
How many rows does sales.merge(branches, on="branch") return?
In the left merge, what is the manager value on Chennai rows?
How many rows does how="outer" give?
You have jan and feb DataFrames with identical columns. How do you make one table of both months?
Remember
- Match rows on a key;
howdecides what happens to rows with no partner. - Predict the row count before merging and compare.
mergeadds columns sideways,concatstacks rows; duplicate keys multiply rows.