Ravindra BagaleCourses & study guides मराठी Track your progress

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

28.7 Practice, Tip and Close

By , trainer LinkedIn Last updated:

Why. Joins must feel boring before the Chapter 16-style project refresh or interview (Chapter 18).

What. Lab: orders + stores + riders, validations, one summary CSV.

How.

Chapter practice lab

  1. Create fictional orders (≥10) with store_id and rider_id across Pune, Nashik, Kolhapur, Solapur, Nagpur (include one bad store_id).
  2. Create stores (unique store_id) and riders (unique rider_id).
  3. Left-merge with indicator for stores; list anti-join keys.
  4. Merge riders; assert lengths.
  5. Delivered GMV by city and manager; export outputs/blinkit_join_gmv.csv.
  6. Write three bullets: how choice, validation, one unmatched key story.
from pathlib import Path
import pandas as pd

Path("outputs").mkdir(exist_ok=True)
orders = pd.DataFrame({
    "order_id": [f"BLK-{i:03d}" for i in range(1, 11)],
    "store_id": ["PUN-01", "PUN-01", "NSK-01", "KOP-01", "SLP-01",
                 "NGP-01", "PUN-01", "NSK-01", "BAD-01", "KOP-01"],
    "rider_id": ["R-1", "R-2", "R-3", "R-1", "R-4", "R-5", "R-2", "R-3", "R-1", "R-4"],
    "status": ["Delivered"] * 8 + ["Delivered", "Cancelled"],
    "amount_inr": [64, 90, 58, 110, 70, 150, 200, 95, 80, 40],
})
stores = pd.DataFrame({
    "store_id": ["PUN-01", "NSK-01", "KOP-01", "SLP-01", "NGP-01"],
    "city": ["Pune", "Nashik", "Kolhapur", "Solapur", "Nagpur"],
    "manager": ["Ravindra Bagale", "Shraddha Bagale", "Salman", "Raja", "Rani"],
})
riders = pd.DataFrame({
    "rider_id": ["R-1", "R-2", "R-3", "R-4", "R-5"],
    "rider_name": ["Zoya", "Amir", "Ruhi", "Ravina", "Shahrukh"],
})
assert stores["store_id"].is_unique and riders["rider_id"].is_unique

m = orders.merge(stores, on="store_id", how="left", indicator=True)
assert len(m) == len(orders)
print("store match", m["_merge"].value_counts().to_dict())
m = m.merge(riders, on="rider_id", how="left")
assert len(m) == len(orders)

delivered = m.loc[m["status"] == "Delivered"]
gmv = (
    delivered.groupby(["city", "manager"], as_index=False)["amount_inr"]
    .sum()
    .sort_values("amount_inr", ascending=False)
)
gmv.to_csv("outputs/blinkit_join_gmv.csv", index=False)
print(gmv)
print("anti", m.loc[m["_merge"] == "left_only", ["order_id", "store_id"]])

Practice task

Amir: “Outer join orders and stores with indicator; count right_only stores that had zero orders in this extract.” Print the store_ids. No ML.

Ravindra Bagale's Tip

left + assert + indicator. GMV nantar. Samajla ka?

Thodkyaat sangaycha tar (quick recap)

  • Blinkit facts join to store/rider dimensions on IDs
  • Prefer explicit how='left' from orders; log drops if inner
  • Multi-keys and suffixes keep columns honest
  • Validate with length, unique keys, indicator, anti-join
  • Chain merges with a checkpoint each time
  • Default merge is inner — say how= every time

Samajla ka? Aata Blinkit-style joins validate karun GMV trustable — pudhe ek anti-join list / day.