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

28.4 Validate: shape, indicator, anti-join

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

Why. A merge that doubles rows looks “successful” until GMV is 2×. Validation is part of the join — not optional homework.

What.

• Compare len before/after.
• indicator=True adds _merge column: both, left_only, right_only.
• Anti-join — rows on the left with no match: cleaning list for bad keys.
• duplicated() on dimension keys before merge (Chapter 23).

How.

Worked example A — indicator.

import pandas as pd

orders = pd.DataFrame({
    "order_id": ["BLK-001", "BLK-002", "BLK-004"],
    "store_id": ["PUN-01", "NSK-01", "PUN-99"],
    "amount_inr": [64, 90, 110],
})
stores = pd.DataFrame({
    "store_id": ["PUN-01", "NSK-01", "KOP-01"],
    "city": ["Pune", "Nashik", "Kolhapur"],
})
m = orders.merge(stores, on="store_id", how="left", indicator=True)
print(m["_merge"].value_counts())
anti = m.loc[m["_merge"] == "left_only", ["order_id", "store_id"]]
print(anti)

Worked example B — Duplicate dimension key danger.

bad_stores = pd.DataFrame({
    "store_id": ["PUN-01", "PUN-01"],
    "city": ["Pune", "Pune"],
    "manager": ["Ravindra", "Shraddha"],
})
print("dupes", bad_stores["store_id"].duplicated().sum())
exploded = orders.merge(bad_stores, on="store_id", how="left")
print(len(orders), len(exploded))  # explosion if orders hit PUN-01

Worked example C — WhatsApp one-liner.

“Left join orders→stores: 412 orders in, 412 out, 3 left_only store_ids — list attached.”