Python for Data course menu
pandas merge Explained: Inner, Left, Right and Outer Joins
Combine pandas DataFrames with merge: inner, left, right and outer joins, joining on columns with different names, several key columns, finding unmatched rows with indicator, and avoiding duplicated rows from one-to-many joins.
On this page
Data is often split across tables: customers in one, orders in another, products in a third. Merging combines them by matching a shared key, exactly like a SQL JOIN.
import pandas as pd
customers = pd.DataFrame({
"customer_id": [1, 2, 3, 4],
"name": ["Asha", "Ravi", "Meera", "John"],
"city": ["Mumbai", "Delhi", "Pune", "Chennai"],
})
orders = pd.DataFrame({
"order_id": [101, 102, 103, 104],
"customer_id": [1, 1, 2, 5],
"amount": [250, 120, 560, 90],
})
print(customers)
print(orders) customer_id name city
0 1 Asha Mumbai
1 2 Ravi Delhi
2 3 Meera Pune
3 4 John Chennai
order_id customer_id amount
0 101 1 250
1 102 1 120
2 103 2 560
3 104 5 90Notice the mismatches: Meera and John have no orders, and order 104 belongs to customer 5, who is not in the customers table.
Inner join (the default)#
Keeps only rows whose key appears in both tables:
import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["Asha", "Ravi", "Meera", "John"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104], "customer_id": [1, 1, 2, 5], "amount": [250, 120, 560, 90]})
print(pd.merge(orders, customers, on="customer_id")) order_id customer_id amount name
0 101 1 250 Asha
1 102 1 120 Asha
2 103 2 560 RaviOrder 104 disappears (no customer 5), and so do Meera and John (no orders).
Left, right and outer joins#
import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["Asha", "Ravi", "Meera", "John"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104], "customer_id": [1, 1, 2, 5], "amount": [250, 120, 560, 90]})
print(pd.merge(customers, orders, on="customer_id", how="left")) # every customer
print(pd.merge(customers, orders, on="customer_id", how="outer")) # everything from both customer_id name order_id amount
0 1 Asha 101.0 250.0
1 1 Asha 102.0 120.0
2 2 Ravi 103.0 560.0
3 3 Meera NaN NaN
4 4 John NaN NaN
customer_id name order_id amount
0 1 Asha 101.0 250.0
1 1 Asha 102.0 120.0
2 2 Ravi 103.0 560.0
3 3 Meera NaN NaN
4 4 John NaN NaN
5 5 NaN 104.0 90.0The left join is the most common in analysis: "all customers, with their orders if any".
Finding unmatched rows#
indicator=True adds a _merge column saying where each row came from, which is perfect for data quality checks:
import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["Asha", "Ravi", "Meera", "John"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104], "customer_id": [1, 1, 2, 5], "amount": [250, 120, 560, 90]})
check = pd.merge(customers, orders, on="customer_id", how="outer", indicator=True)
print(check[["customer_id", "name", "order_id", "_merge"]])
print("Customers with no orders:", check.loc[check["_merge"] == "left_only", "name"].tolist())
print("Orders with unknown customer:", check.loc[check["_merge"] == "right_only", "order_id"].tolist()) customer_id name order_id _merge
0 1 Asha 101.0 both
1 1 Asha 102.0 both
2 2 Ravi 103.0 both
3 3 Meera NaN left_only
4 4 John NaN left_only
5 5 NaN 104.0 right_only
Customers with no orders: ['Meera', 'John']
Orders with unknown customer: [104.0]order_id now shows as 101.0, 104.0 and so on: the outer join put NaN in that column, and NaN forces numbers to floats. Convert back with .astype("Int64") if whole numbers matter.
Different column names and several keys#
import pandas as pd
products = pd.DataFrame({"sku": ["P1", "P2"], "product": ["Pen", "Book"]})
order_lines = pd.DataFrame({"product_code": ["P1", "P2", "P1"], "qty": [3, 1, 5]})
print(pd.merge(order_lines, products, left_on="product_code", right_on="sku").drop(columns="sku"))
targets = pd.DataFrame({"region": ["North", "North", "South"], "month": ["Jan", "Feb", "Jan"], "target": [500, 450, 300]})
actual = pd.DataFrame({"region": ["North", "South", "North"], "month": ["Jan", "Jan", "Feb"], "revenue": [520, 260, 470]})
print(pd.merge(targets, actual, on=["region", "month"])) product_code qty product
0 P1 3 Pen
1 P2 1 Book
2 P1 5 Pen
region month target revenue
0 North Jan 500 520
1 North Feb 450 470
2 South Jan 300 260When both tables have other columns with the same name, pandas adds the suffixes _x and _y; choose clearer ones with suffixes=("_target", "_actual").
One-to-many joins multiply rows#
Joining customers to orders gives one row per order, so customer columns are repeated. Summing a customer column afterwards double counts. Aggregate first, then join:
import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2, 3], "name": ["Asha", "Ravi", "Meera"], "credit_limit": [1000, 500, 800]})
orders = pd.DataFrame({"customer_id": [1, 1, 2], "amount": [250, 120, 560]})
joined = pd.merge(customers, orders, on="customer_id")
print(joined["credit_limit"].sum(), "<- wrong: Asha's limit is counted twice")
totals = orders.groupby("customer_id", as_index=False)["amount"].sum()
summary = pd.merge(customers, totals, on="customer_id", how="left").fillna({"amount": 0})
print(summary)2500 <- wrong: Asha's limit is counted twice
customer_id name credit_limit amount
0 1 Asha 1000 370.0
1 2 Ravi 500 560.0
2 3 Meera 800 0.0validate="one_to_many" (or "one_to_one") makes merge raise an error if the keys are not as unique as you expect, which catches duplicate keys early.
Common mistakes#
Practice#
Practice exercise
Using customers (ids 1–4) and orders from the start of this lesson, print every customer with their number of orders and total spent, including customers with no orders (show 0), sorted by total spent from highest to lowest.
Show hint
Group the orders by customer_id with named aggregation (count and sum), left-join that onto customers, then fillna(0).
Show solution
import pandas as pd
customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["Asha", "Ravi", "Meera", "John"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104], "customer_id": [1, 1, 2, 5], "amount": [250, 120, 560, 90]})
stats = orders.groupby("customer_id", as_index=False).agg(orders=("order_id", "count"), spent=("amount", "sum"))
report = pd.merge(customers, stats, on="customer_id", how="left").fillna({"orders": 0, "spent": 0})
report[["orders", "spent"]] = report[["orders", "spent"]].astype(int)
print(report.sort_values("spent", ascending=False)) customer_id name orders spent
1 2 Ravi 1 560
0 1 Asha 2 370
2 3 Meera 0 0
3 4 John 0 0Key takeaway#
Next: Concatenating and Reshaping.
Finished this lesson?