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

28.7 Practice, tip आणि close

By Ravindra Bagale

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

दोन छोट्या tables left merge + indicator + एक anti-join यादी.

हे काय आहे?

Practice + quiz. या chapter नंतर joins बद्दल आत्मविश्वास.

कसं करायचं?

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

हा code / output:

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"]])