Lesson 4 of 8 · 7 min · Working with tables in pandas
Merging, joining and concatenating tables
What you will learn
- Combine two tables with
merge - Choose inner, left, right and outer joins
- Merge on columns with different names or on several columns
- Check that a merge did not change the row count unexpectedly
- Stack tables with
concat
In simple words
Your orders table knows only a customer number, and your customers table knows names and cities. To report "sales by city" you must attach each order's customer details. merge does that: it matches rows from two tables on a shared column, exactly like a SQL join or an Excel XLOOKUP for a whole column at once.
Words to know
- merge: combining two DataFrames by matching the values of key columns.
- Key: the column(s) used to match rows, such as
customer_id. - Inner join: keep only rows that match in both tables.
- Left join: keep every row of the left table, with missing values where nothing matches.
The two tables
customers = pd.DataFrame({
"customer_id": [1, 2, 3, 4, 5],
"name": ["Asha", "Ravi", "Meena", "Kiran", "Divya"],
"city": ["Hyderabad", "Pune", "Chennai", "Hyderabad", None],
})
with the orders table from earlier lessons (six orders for customers 1, 2 and 3).
merge
joined = orders.merge(customers, on="customer_id", how="left")
The result has all six orders, each with name and city added. The how argument chooses the join:
how |
Keeps | Like SQL |
|---|---|---|
"inner" (default) |
rows with a match in both | INNER JOIN |
"left" |
all rows of the left table | LEFT JOIN |
"right" |
all rows of the right table | RIGHT JOIN |
"outer" |
all rows of both | FULL OUTER JOIN |
"cross" |
every pairing | CROSS JOIN |
For customers with no orders:
all_customers = customers.merge(orders, on="customer_id", how="left")
all_customers[all_customers["order_id"].isna()] # Kiran and Divya
That gives the same "no match" list as the SQL left join with IS NULL.
Keys with different names, or several keys
orders.merge(customers, left_on="cust_id", right_on="customer_id") # different names
a.merge(b, on=["region", "month"]) # several keys
a.merge(b, left_index=True, right_index=True) # join on the index
When both tables have a column with the same name that is not a key, pandas adds suffixes _x and _y. Choose clearer ones with suffixes=("_order", "_customer"), or drop or rename columns before merging.
Check what happened
Merges are a classic source of silent errors. Always check the row count and the matches:
len(orders), len(joined) # 6, 6 : unchanged for a left join on a unique key
joined["name"].isna().sum() # how many orders found no customer
Two built-in safeguards:
orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
orders.merge(customers, on="customer_id", how="outer", indicator=True)
validate="many_to_one"raises an error ifcustomershas duplicatecustomer_idvalues. Other values:"one_to_one","one_to_many","many_to_many".indicator=Trueadds a_mergecolumn sayingleft_only,right_onlyorboth, so you can count each.
The duplicate-key trap
If the right table has two rows for the same key, every left row matching it is repeated. Suppose customers accidentally listed customer 1 twice. Then Asha's three orders would become six rows, and a later sum of amount would double those orders. This is the pandas twin of the SQL fan-out trap. Prevent it by:
- Checking
customers["customer_id"].is_uniquebefore the merge. - Using
validate="many_to_one". - Aggregating the many-side first, then merging.
Stacking tables: concat
merge adds columns by matching rows; concat stacks tables on top of each other (same columns) or side by side:
all_months = pd.concat([sept, october], ignore_index=True) # stack rows
wide = pd.concat([a, b], axis=1) # side by side by index
Typical use: combine monthly files.
from pathlib import Path
frames = [pd.read_csv(p) for p in Path("data").glob("orders_*.csv")]
orders_all = pd.concat(frames, ignore_index=True)
Columns that exist in only some tables are filled with missing values. After stacking, check for duplicates with duplicated() (cleaning lessons).
join
DataFrame.join merges on the index by default and is a shortcut when both tables are indexed by their key: orders.set_index("customer_id").join(customers.set_index("customer_id")). Most analysts use merge for everything because it names the keys explicitly.
Common mistakes
- Merging on columns of different types (integer in one, text in the other); you get no matches. Convert with
astype. - Keys with stray spaces or different capitalisation, so rows do not match.
- Not checking the row count after a join.
- Duplicate keys inflating rows and totals.
- Using the default inner join and silently losing rows you wanted to keep.
Try it yourself
- Merge orders with customers (left join) and compute total amount per city.
- List customers who have never ordered.
- Make
customershave a duplicate key on purpose, merge, and see how totals change. Then addvalidate. - Use
indicator=Truewith an outer merge and count the_mergecategories. - Create two small monthly DataFrames and combine them with
concat.
Check yourself
What is the difference between an inner and a left merge?
Inner keeps only rows matching in both tables. Left keeps all rows from the left table, filling missing values where there is no match.
What does validate="many_to_one" do?
It raises an error if the right table's key is not unique, which prevents accidental row duplication.
When would you use concat instead of merge?
To stack tables with the same columns (such as monthly files) or place tables side by side, rather than matching rows on a key.
Why might a merge return no matching rows even though the values look the same?
The key columns may have different types (number versus text) or hidden spaces and case differences.
Interview questions
How do you find rows in one DataFrame that have no match in another?
Merge with how="left", indicator=True and keep rows where _merge == "left_only", or use a left merge and filter for missing values in a right-hand column.
Your total revenue went up after a merge. What happened?
Probably a duplicate key in the other table repeated rows. Check key uniqueness, use validate, and aggregate the many-side before merging.
How do you combine 12 monthly CSV files?
Read each into a DataFrame in a list or generator and call pd.concat(frames, ignore_index=True).
Summary
mergejoins on key columns; choosehowas inner, left, right, outer or cross.- Check row counts, use
validateandindicator, and watch for duplicate keys. - Fix type and whitespace mismatches before merging.
concatstacks tables or places them side by side.
Going deeper
- Merge, join, concatenate and compare: the official guide.
- DataFrame.merge reference: all options.
- pandas.concat reference: axis and keys.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.