Merging & Joining

Relax. We'll talk through this in plain words — no textbook voice.

Merging & Joining

In data analysis, you'll rarely get by with just one table. You might have a separate sales table, a product info table, and a customer table. pd.merge() works like a SQL join — it combines two DataFrames based on a common column.

python

import pandas as pd

sales = pd.DataFrame({
    "ProductID": [1, 2, 1, 3],
    "Qty": [3, 2, 1, 4]
})

products = pd.DataFrame({
    "ProductID": [1, 2, 3],
    "ProductName": ["Tea", "Coffee", "Cake"],
    "Price": [1200, 1800, 2500]
})

merged = pd.merge(sales, products, on="ProductID", how="left")
merged["Total"] = merged["Qty"] * merged["Price"]

print(merged)

ProductID is the common key that exists in both tables. how="left" keeps every row from the sales table and fills in matching data from the products table. After merging, we multiply price by quantity to calculate the total.

You should see

ProductID Qty ProductName Price Total 0 1 3 Tea 1200 3600 1 2 2 Coffee 1800 3600 2 1 1 Tea 1200 1200 3 3 4 Cake 2500 10000

Easy traps

If the merge key column names don't match, on="ProductID" won't work — you'll need left_on and right_on instead. Also watch out for duplicate keys, which can make your row count balloon unexpectedly.