Data Science · मराठी आवृत्ती

23.3 Merge row explosion (many-to-many)

By Ravindra Bagale

या course चे सर्व chapters (py + ch)

हे का शिकायचं?

Join keys duplicate असतील तर rows गुणांकार होतात — totals फुगतात.

हे काय आहे?

Merge आधी दोन्ही बाजूंना key uniqueness तपासा. validate= वापरा. Shape आधी-नंतर पाहा.

कसं करायचं?

Step 1. value_counts on key.

Step 2. merge करण्याआधी shape लिहा.

Step 3. validate="one_to_one" किंवा one_to_many.

Step 4. नंतर shape आणि sum तुलना.

उदाहरणं: copy करा आणि चालवा

उदाहरण A — Explosion demo.

orders = pd.DataFrame({
    "order_id": ["BLK-20", "BLK-21", "BLK-22"],
    "city": ["Pune", "Pune", "Nashik"],
    "amount": [100, 120, 90],
})
stores = pd.DataFrame({
    "city": ["Pune", "Pune", "Nashik"],
    "store_id": ["PUN-01", "PUN-02", "NAS-01"],
})
boom = orders.merge(stores, on="city", how="left")
print("orders", len(orders), "after merge", len(boom))
print(boom)

What you should see. 3 → 5 rows. Pune orders duplicated per store.

उदाहरण B — Fix with store_id.

orders2 = pd.DataFrame({
    "order_id": ["BLK-20", "BLK-21", "BLK-22"],
    "store_id": ["PUN-01", "PUN-02", "NAS-01"],
    "amount": [100, 120, 90],
})
stores2 = pd.DataFrame({
    "store_id": ["PUN-01", "PUN-02", "NAS-01"],
    "city": ["Pune", "Pune", "Nashik"],
    "manager": ["Ruhi Bagale", "Amir", "Zoya"],
})
ok = orders2.merge(stores2, on="store_id", how="left", validate="m:1")
print(len(orders2), len(ok))
print(ok)

उदाहरण C — indicator report.

orphan = pd.DataFrame({
    "order_id": ["BLK-99"],
    "store_id": ["UNK-09"],
    "amount": [50],
})
chk = orphan.merge(stores2, on="store_id", how="left", indicator=True)
print(chk[["order_id", "store_id", "_merge"]])

left_only means the store master is incomplete — fix the dimension table, do not “fill city with Pune” silently.