Merging and joining

merge, join types, and validating that the merge did what you expected.

What you'll be able to do

Merge on one or more keys
Choose the right how=
Verify a merge with indicator and validate

merge

Shaded area shows which rows survive the join.

Check the result

The habit that catches merge bugs

print(len(orders))
merged = pd.merge(orders, customers, on='customer_id', how='left')
print(len(merged))

A left merge should not change the row count. If it grew, the right side has duplicate keys and your rows have fanned out.

indicator and validate

merged = pd.merge(orders, customers, on='customer_id',
                  how='left', indicator=True)
merged['_merge'].value_counts()
# both          987
# left_only      13    <- orders with no matching customer

pd.merge(orders, customers, on='customer_id',
         how='left', validate='many_to_one')

validate raises immediately if the relationship is not what you claimed. 'many_to_one', 'one_to_one' and 'one_to_many' are the useful values — this turns a silent fan-out into an error, which is exactly what you want.

Overlapping column names

pd.merge(a, b, on='id', suffixes=('_orders', '_customers'))

Without this you get revenue_x and revenue_y, which nobody can read a week later.

concat

pd.concat([jan, feb, mar], ignore_index=True)      # stack rows
pd.concat([left, right], axis=1)                   # side by side

concat stacks; merge matches on keys. ignore_index=True renumbers, avoiding duplicate index values that cause confusing behaviour later.

Key points

Check row counts before and after every merge
validate="many_to_one" turns a silent fan-out into an error
indicator=True shows which rows matched and which did not

Check yourself