Real analysis almost always needs more than one table. pandas gives you three ways to combine DataFrames, and choosing the right one avoids some painful bugs.
The examples use a fictional pet-supplies shop, PawPantry, with orders, customers and monthly order files.
concat: stack or place side by side
Use concat when DataFrames have the same columns and you want more rows, such as monthly files:
import pandas as pd
all_orders = pd.concat([jan, feb, mar], ignore_index=True)ignore_index=True gives a fresh 0..n index instead of repeated ones. Add a column first if you need to know which file each row came from:
all_orders = pd.concat([jan.assign(month="Jan"), feb.assign(month="Feb")], ignore_index=True)axis=1 places DataFrames side by side, aligned on the index, which is less common.
merge: SQL-style joins on columns
df = orders.merge(customers, on="customer_id", how="left")how="inner": only matching rows (the default).how="left": all orders, customer details where found.how="right": all customers.how="outer": everything from both.
If the key columns have different names:
orders.merge(customers, left_on="cust_id", right_on="customer_id", how="left")join: merge on the index
orders.set_index("customer_id").join(customers.set_index("customer_id"), how="left")join is a convenient shortcut when the key is already the index. Most analysts simply use merge for everything.
Avoid duplicate-row surprises with validate
If the customers table accidentally has a customer twice, every order for that customer is duplicated after the merge, and revenue totals inflate. Catch it immediately:
orders.merge(customers, on="customer_id", how="left", validate="many_to_one")pandas raises an error if the right side is not unique on the key.
See what matched with indicator
check = orders.merge(customers, on="customer_id", how="outer", indicator=True)
check["_merge"].value_counts()The _merge column says both, left_only or right_only. Filter left_only to find orders with unknown customers, a quick data-quality check.
Overlapping column names
If both tables have a city column, pandas adds suffixes city_x and city_y. Choose meaningful ones:
orders.merge(stores, on="store_id", suffixes=("_customer", "_store"))Check row counts every time
print(len(orders), len(df))After a left merge onto a unique key, the row count should not change. If it grew, the right side has duplicate keys.
Quick guide
| Goal | Use |
|---|---|
| Append monthly files with the same columns | pd.concat |
| Add customer details to orders | merge(..., how="left") |
| Rows in both tables only | merge(..., how="inner") |
| Find unmatched rows | merge(..., how="outer", indicator=True) |
| Key is already the index | join |
Practise
Our merge, pivot and reshape set covers every join type and the common traps. The ideas are identical to SQL joins; see SQL joins explained for the row-level picture.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: