Capstone: an analysis, end to end

The question: which stall is doing best, and how does trade vary by day? You have orders and stalls. Everything you need is in the previous twenty-five lessons.

1. Clean first

Reuse the pipeline from the last capstone. Analysing before deduplicating means the duplicate Delhi order counts twice in every total — and a 130-cup order is enough to change which city looks best.

2. Enrich — and mind the join

clean.merge(stalls, on="city", how="left")

This is the decision that matters most in the whole capstone. The two tables don't cover the same cities: Pune has orders but no stall record, and Jaipur has a stall but no orders.

An inner join — the default — would silently drop every Pune order. Since Pune has the single biggest order in the table, your "best stall" would be chosen from an incomplete list, with no error and no warning.

So: how="left", and then check.

before = len(clean)
enriched = clean.merge(stalls, on="city", how="left", validate="many_to_one")
assert len(enriched) == before          # no rows lost, no rows multiplied
enriched["manager"].isna().sum()        # how many found no stall record

validate="many_to_one" states your assumption — many orders, at most one stall per city — and raises immediately if it's wrong. One argument, and it turns a silent row explosion into an exception.

3. Derive what you need

.assign(
    price=lambda d: d["revenue"] / d["cups"],
    weekday=lambda d: d["date"].dt.day_name(),
)

date has to be a real datetime for that second one — which is why fixing types happened during cleaning rather than here.

4. Aggregate

.groupby("city").agg(
    total_cups=("cups", "sum"),
    revenue=("revenue", "sum"),
    orders=("cups", "count"),
    avg_rating=("rating", "mean"),
)

Named aggregation, so the output columns say what they are. And note revenue skips the missing value — twelve rows contribute, not thirteen. That's the right default and it's worth saying out loud in the report, because a reader will assume otherwise.

5. Reshape for reading

.pivot_table(index="city", columns="weekday", values="cups",
             aggfunc="sum", fill_value=0)

Long form to compute, wide form to present — lesson 19's rule. And aggfunc="sum" explicitly, because the default is mean.

6. Rank and report

summary.sort_values("total_cups", ascending=False)

What makes this a capstone

Not the number of methods — the two judgement calls. Which join, because the wrong one silently answers a different question. And what the gaps mean, because a total computed from twelve of thirteen rows is correct and misleading at the same time unless you say so.

Everything else here is mechanical. Those two are the analysis.