merge, join and concat in pandas

All three combine DataFrames, but in different ways. merge matches rows by column values like a SQL join. join is a shorter way to merge on the index. concat stacks DataFrames on top of each other (or side by side) without matching anything.

merge

orders.merge(customers, on="customer_id", how="left")

how can be inner, left, right or outer, like SQL. If the key columns have different names, use left_on and right_on. Rows with a NULL key do not match in SQL, but in pandas NaN keys do match each other in a merge, which surprises people, so clean the keys first.

join

orders.set_index("customer_id").join(customers.set_index("customer_id"))

join is a convenience for index-based merges. Most people write merge and ignore join.

concat

all_orders = pd.concat([orders_jan, orders_feb], ignore_index=True)

This stacks rows (axis=0, the default) like UNION ALL. Columns are aligned by name, and a column missing in one frame becomes NaN. With axis=1, it puts frames side by side, aligned on the index, which is almost never what you want for tables with different keys.

Guarding against row explosions

The most common merge bug is a duplicated key on the right side, so each left row matches several rows and the result grows. Two features help:

orders.merge(customers, on="customer_id", how="left",
             validate="many_to_one",     # raises if customer_id is not unique in customers
             indicator=True)             # adds a _merge column: both / left_only / right_only

validate checks the assumption you are making about the relationship, and fails fast if it is wrong. indicator=True shows which rows found a partner, so a quick value_counts() reveals unmatched rows.

Always check row counts

assert len(result) == len(orders)

after a left merge that should not add rows. This one line catches most join mistakes.

Quick map to SQL

merge is JOIN, concat is UNION ALL, and join is a JOIN on index. Mention validate and indicator to show you have been bitten by this before.