PYTHON / PANDAS
Merging, joining, and concatenating
Combine pandas objects with concat, merge, and join, pick the right join type, and diagnose rows that vanish or multiply.
What you will learn
- Stack frames with pd.concat and control the index with ignore_index
- Match rows on key columns with df.merge and choose how= inner/left/outer
- Align on index with df.join and know its default is a left join
- Spot unmatched keys with indicator=True and duplicated keys with validate=
Understanding Merging, joining, and concatenating
There are two distinct operations hiding behind the word 'combine'. pd.concat glues objects together along one axis and aligns them on the other: concat along axis=0 stacks rows and lines columns up by name, concat along axis=1 puts frames side by side and lines rows up by index label. Nothing is matched on values, so a column that exists in only one frame is filled with NaN, and index labels are carried through unchanged, which is why stacking two frames that both start at 0 gives you two rows labelled 0.
merge is the relational operation: you name one or more key columns, and pandas pairs every left row with every right row that shares the same key. The how argument decides what happens to keys that appear on only one side. inner keeps only keys present in both, left keeps every left row and fills the right-hand columns with NaN when there is no match, outer keeps the union. merge defaults to how='inner', so rows can disappear silently; that is the single most common surprise in this whole topic.
Because matching is done per key, row counts are not preserved. If a key appears twice on the right, each matching left row is emitted twice, so a many-to-many merge multiplies rows and quietly doubles any sum you compute afterwards. df.join is merge on the index with how='left' as its default, useful when both frames are already indexed by the same identifier. Filling with NaN also forces integer columns to float, since NaN is a float value and an int64 column has no way to store it.
import pandas as pd
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4],
"customer_id": [10, 11, 10, 99],
"amount": [250, 120, 80, 40],
})
customers = pd.DataFrame({
"customer_id": [10, 11, 12],
"name": ["Ada", "Grace", "Linus"],
})
inner = orders.merge(customers, on="customer_id")
left = orders.merge(customers, on="customer_id", how="left")
print(inner)
print()
print(left)
print()
print(left["name"].isna().sum())concat stacks blocks along an axis while merge and join pair rows by key value, and the join type decides which unmatched rows survive.
Worked examples
Stacking with concat and the duplicate index
Shows that concat keeps the original index labels, so the same label can appear twice unless you ask for a fresh one.
import pandas as pd
jan = pd.DataFrame({"city": ["Oslo", "Bergen"], "sales": [10, 20]})
feb = pd.DataFrame({"city": ["Oslo", "Tromso"], "sales": [15, 5]})
stacked = pd.concat([jan, feb])
print(stacked)
print(stacked.loc[0])
print(pd.concat([jan, feb], ignore_index=True).index.tolist())Example explained
Line 1pd.concat([jan, feb]) stacks rows and matches the two frames on column names, not on values.
Line 2The index labels 0 and 1 are copied from each input, so the result has each label twice.
Line 3stacked.loc[0] therefore returns a two-row DataFrame instead of a single row Series.
Line 4ignore_index=True discards the old labels and builds a clean 0..3 RangeIndex.
Different key names, outer join, and indicator
Merges on columns with different names and uses indicator=True to show which side each row came from.
import pandas as pd
left = pd.DataFrame({"sku": ["a", "b", "c"], "price": [5, 7, 9]})
right = pd.DataFrame({"product": ["b", "c", "d"], "price": [5.5, 9.0, 3.0]})
m = left.merge(right, left_on="sku", right_on="product",
how="outer", indicator=True, suffixes=("_list", "_sale"))
print(m)Example explained
Line 1left_on and right_on pair columns whose names differ, and both key columns stay in the result.
Line 2how='outer' keeps keys from either side, so 'a' and 'd' survive with NaN on the missing side.
Line 3suffixes renames the two clashing price columns; without it they would become price_x and price_y.
Line 4price_list is float, not int, because the unmatched row 3 needs a NaN in that column.
Joining on the index
Uses DataFrame.join to combine two frames indexed by the same identifier, contrasting its default left join with an outer join.
import pandas as pd
prices = pd.DataFrame({"price": [12.5, 8.0]}, index=["ISBN1", "ISBN2"])
stock = pd.DataFrame({"units": [3, 0, 7]}, index=["ISBN2", "ISBN3", "ISBN1"])
print(prices.join(stock))
print(prices.join(stock, how="outer"))Example explained
Line 1join matches on index labels, so the row order of stock is irrelevant: ISBN1 correctly gets 7 units.
Line 2The default how='left' keeps only the two labels in prices and drops ISBN3 entirely.
Line 3how='outer' adds ISBN3 and fills its unknown price with NaN, turning nothing about units into float since units has no gaps.
Line 4units stays int64 in both results because every kept label had a matching stock row.
Important notes
merge defaults to how='inner' but DataFrame.join defaults to how='left', so the same intent needs different arguments depending on which method you use.
Any join type that introduces NaN converts an int64 column to float64; cast back with astype('Int64') if you need nullable integers.
Common mistakes
Leaving merge at its default how='inner': every order whose customer is missing from the lookup table silently vanishes, and the totals come out too low with no error.
Using pd.concat(axis=1) to attach a lookup table: the frames are aligned by position-derived index labels, not by the key, so values end up on the wrong rows.
Merging on a key that is duplicated on the right side: the result has more rows than the left frame and later sums double-count, which validate='one_to_many' would have caught immediately.
Try it yourself
Change, predict, then run
Build an employees frame with dept_id values 1, 2, 2, 4 and a departments frame with dept_id 1, 2, 3, then merge them with how='left' and indicator=True and print only the rows where _merge is 'left_only'.
Open the Python workspaceCheck your understanding
A left merge of a 1000-row orders frame onto a customers lookup frame returns 1040 rows. What is the most likely explanation?
- how='left' also appends right rows that had no match
- Some customer_id values appear more than once in the customers frame, so those orders matched several rows
- Forty orders had no matching customer, and pandas added a NaN row for each
- The two frames had overlapping column names, so pandas duplicated the affected rows
Show answer
A left merge emits one output row per matching pair, so a key duplicated on the right multiplies the corresponding left rows and pushes the count above 1000. Unmatched left rows cannot explain it: they are still emitted exactly once, just with NaN in the right-hand columns, so they leave the row count at 1000.