Merging tables · Pandas

Data often lives in several tables: customers in one, orders in another. pd.merge joins them on a shared column, just like a SQL join.

Python

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "name": ["Asha", "Leo", "Mei", "Raj"],
    "city": ["Mumbai", "London", "Osaka", "Delhi"],
})
orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105],
    "customer_id": [1, 2, 1, 3, 9],
    "amount": [120.0, 75.5, 40.0, 210.0, 15.0],
})

print(pd.merge(orders, customers, on="customer_id"))

By default, merge is an inner join: only rows with a match in both tables survive. Order 105 belongs to customer 9, who isn't in customers, so it disappears, and so does Raj, who has no orders.

Choosing the kind of join

Python

import pandas as pd

customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["Asha", "Leo", "Mei", "Raj"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104, 105],
                       "customer_id": [1, 2, 1, 3, 9],
                       "amount": [120.0, 75.5, 40.0, 210.0, 15.0]})

print(pd.merge(customers, orders, on="customer_id", how="left"))     # every customer
print(pd.merge(customers, orders, on="customer_id", how="outer", indicator=True))

The green middle is the keys both tables share. inner keeps only those; left and right add their own side's unmatched rows; outer keeps everything.

`how=`	Keeps
`"inner"`	only rows that match in both (the default)
`"left"`	every row of the left table
`"right"`	every row of the right table
`"outer"`	every row of both tables

Unmatched rows get NaN in the other table's columns. indicator=True adds a _merge column saying where each row came from: both, left_only or right_only, which makes it easy to find orders with no customer or customers with no orders.

Keys with different names

Python

import pandas as pd

customers = pd.DataFrame({"id": [1, 2, 3], "name": ["Asha", "Leo", "Mei"]})
orders = pd.DataFrame({"order_id": [101, 102, 103], "cust": [2, 3, 1], "amount": [75.5, 210.0, 120.0]})

merged = pd.merge(orders, customers, left_on="cust", right_on="id")
print(merged.drop(columns=["id"]))

Clashing column names

Python

import pandas as pd

products = pd.DataFrame({"product_id": [1, 2], "name": ["Keyboard", "Monitor"]})
reviews = pd.DataFrame({"product_id": [1, 1, 2], "name": ["Asha", "Leo", "Mei"], "stars": [5, 4, 3]})

print(pd.merge(products, reviews, on="product_id", suffixes=("_product", "_reviewer")))

Both tables have a name column. pandas keeps both, adding suffixes: _x and _y by default, or the clearer ones you choose. Notice the keyboard appears twice: one product with two reviews is a one-to-many merge, and each match produces a row.

Stacking tables: concat

Python

import pandas as pd

june = pd.DataFrame({"product": ["Pen", "Bag"], "sold": [30, 4]})
july = pd.DataFrame({"product": ["Pen", "Lamp"], "sold": [25, 7]})

both = pd.concat([june, july], keys=["June", "July"])
print(both)
print(pd.concat([june, july], ignore_index=True))

concat stacks tables with the same columns on top of each other, such as one file per month. ignore_index=True renumbers the rows, and keys labels where each part came from.