Ravindra BagaleCourses & study guides

8. Combining Data

8.2 merge — Orders ⨝ Stores

By Last updated: 

orders = pd.DataFrame({
    "order_id": ["BLK-001", "BLK-002", "AMN-003", "BLK-004"],
    "store_id": ["PUN-01", "NSK-01", "KOP-01", "PUN-99"],
    "amount": [64, 90, 58, 110],
    "customer": ["Ruhi Bagale", "Amir", "Zoya", "Ravina"],
})
stores = pd.DataFrame({
    "store_id": ["PUN-01", "NSK-01", "KOP-01", "SLP-01"],
    "city": ["Pune", "Nashik", "Kolhapur", "Solapur"],
    "manager": ["Ravindra Bagale", "Shraddha Bagale", "Salman", "Raja"],
})

left = orders.merge(stores, on="store_id", how="left")
inner = orders.merge(stores, on="store_id", how="inner")
print(left)
print(len(orders), len(left), len(inner))
how Keeps
left All orders; missing store → NaN (PUN-99)
inner Only matching keys
right / outer Less common in class; know they exist

Steps in Jupyter

  1. Create orders and stores.
  2. left = orders.merge(stores, on="store_id", how="left").
  3. Find unmatched: left[left["city"].isna()].
  4. Confirm len(left) == len(orders) for a correct many-to-one left join.

What you should see. BLK-004 / PUN-99 has NaN city — bad store_id in the export. That is a cleaning signal.

Ravindra Bagale's Tip

Khup students merge nantar row count double zala tar ignore kartat — te many-to-many duplicate key mule hota. Join chya aadhi stores["store_id"].duplicated().sum() check kara. Duplicate keys = danger. Dhyan rakho!