28. How to Merge and Join Tables in pandas for Blinkit Data

28.3 Multi-key Joins and Suffixes

By Ravindra Bagale, trainer · LinkedIn · Last updated: 24 October 2026

Why. Sometimes city alone is not enough — store_id + shift or two columns form the key. Overlapping column names need suffixes.

What.

• on=["store_id", "shift"] or left_on / right_on when names differ.
• suffixes=("_ord", "_str") when both sides share city or updated_at.

How.

Worked example A — left_on / right_on.

import pandas as pd

orders = pd.DataFrame({
    "order_id": ["BLK-10", "BLK-11"],
    "store_code": ["PUN-01", "NGP-01"],
    "amount_inr": [120, 150],
})
stores = pd.DataFrame({
    "store_id": ["PUN-01", "NGP-01"],
    "city": ["Pune", "Nagpur"],
})
m = orders.merge(stores, left_on="store_code", right_on="store_id", how="left")
print(m)

Worked example B — suffixes.

orders = pd.DataFrame({
    "order_id": ["BLK-20"],
    "store_id": ["PUN-01"],
    "city": ["Pune"],  # denormalized copy
    "amount_inr": [99],
})
stores = pd.DataFrame({
    "store_id": ["PUN-01"],
    "city": ["Pune"],
    "manager": ["Shraddha Bagale"],
})
m = orders.merge(stores, on="store_id", how="left", suffixes=("_ord", "_master"))
print(m.columns.tolist())

Worked example C — Two keys (fictional shift roster).

roster = pd.DataFrame({
    "store_id": ["PUN-01", "PUN-01"],
    "shift": ["morning", "evening"],
    "lead_rider": ["Salman", "Raja"],
})
orders = pd.DataFrame({
    "order_id": ["BLK-30", "BLK-31"],
    "store_id": ["PUN-01", "PUN-01"],
    "shift": ["morning", "evening"],
    "amount_inr": [80, 95],
})
print(orders.merge(roster, on=["store_id", "shift"], how="left"))