Introduction
Real data lives in several tables: orders in one, customers in another, products in a third. To answer "how much did each city spend?" you need to combine them. pandas does this with merge, which works like a SQL join, and with concat, which stacks tables. Merges are also where silent mistakes happen: a duplicated key can double a total without any error, so this lesson covers the checks as well as the mechanics.
How it works
merge. pd.merge(left, right, on="id", how="inner") matches rows whose id values are equal and puts their columns side by side. The how argument decides which keys survive:
"inner"(the default): keys in both tables, in the order of the left table;"left": every left row, with NaN where the right table has no match;"right": every right row;"outer": every key from both, with the keys sorted.
people = pd.DataFrame({"id": [1, 2, 3], "name": ["Ana", "Ben", "Cy"]})
scores = pd.DataFrame({"id": [3, 1, 4], "score": [70, 90, 50]})
pd.merge(people, scores, on="id")["name"].tolist() # ['Ana', 'Cy']
len(pd.merge(people, scores, on="id", how="outer")) # 4
When the key columns have different names, use left_on="customer_id", right_on="id". Columns with the same name on both sides get suffixes, _x and _y by default.
Duplicate keys. If a key appears twice on the right, every matching left row is repeated twice. Sometimes that is right (one customer, many orders), often it is a bug. validate="many_to_one" raises a MergeError when the right keys are not unique, and validate="one_to_one" checks both sides. indicator=True adds a _merge column saying both, left_only or right_only, the quickest way to see what did not match.
concat. pd.concat([jan, feb], ignore_index=True) stacks tables vertically. Columns are aligned by name, and a column missing from one table is filled with NaN in its rows. ignore_index=True renumbers the rows from 0; without it, the old row labels are kept and may repeat. axis=1 puts tables side by side, aligned by index.
join. df.join(other) is a shortcut that merges on the index, handy when both tables are indexed by the same key.
Example
# pd.merge modelled with lists of dicts: inner, left and outer joins on "id".
left = [{"id": 1, "name": "Ana"}, {"id": 2, "name": "Ben"}, {"id": 3, "name": "Cy"}]
right = [{"id": 3, "score": 70}, {"id": 1, "score": 90}, {"id": 4, "score": 50}]
scores = {r["id"]: r["score"] for r in right}
names = {r["id"]: r["name"] for r in left}
inner = [(r["id"], r["name"], scores[r["id"]]) for r in left if r["id"] in scores]
lft = [(r["id"], r["name"], scores.get(r["id"], "NaN")) for r in left]
keys = sorted(set(names) | set(scores))
outer = [(k, names.get(k, "NaN"), scores.get(k, "NaN")) for k in keys]
print("inner:", inner)
print("left: ", lft)
print("outer:", outer)Line by line
leftandrightshare the keyid; key 2 exists only on the left and key 4 only on the right.- The inner join keeps the left order and only keys with a score.
- The left join keeps every left row and fills the missing score with NaN.
- The outer join uses the sorted union of all keys.
Expected result
inner: [(1, 'Ana', 90), (3, 'Cy', 70)]
left: [(1, 'Ana', 90), (2, 'Ben', 'NaN'), (3, 'Cy', 70)]
outer: [(1, 'Ana', 90), (2, 'Ben', 'NaN'), (3, 'Cy', 70), (4, 'NaN', 50)]
In the real world
Analysts merge transactions with customer and product tables before almost every report. Monthly exports are concatenated into one table for a year. Data teams run merges with validate and indicator in their pipelines, because a merge that quietly drops or duplicates rows is one of the most common causes of wrong numbers.
Common mistakes
- Not checking the row count after a merge, missing duplicated or dropped rows.
- Using the default inner join when unmatched rows must be kept.
- Merging on keys of different types, such as the number 1 and the text "1", which never match.
- Forgetting ignore_index=True in concat and getting repeated row labels.
Best practices
- Print the shapes before and after every merge.
- State
howandvalidateexplicitly in every merge. - Use
indicator=Trueto investigate unmatched keys.
Summary
pd.merge matches rows by key: inner keeps common keys, left and right keep one side, outer keeps all. Duplicate keys multiply rows, so use validate, and inspect matches with indicator. pd.concat stacks tables and aligns columns by name, and join merges on the index.
Quick check
Key takeaways
- how decides which keys survive: inner, left, right or outer.
- Duplicate keys multiply rows; validate catches them early.
- concat stacks tables; merge matches them by key.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.