Top-selling product in every city

Question

Category managers want the single best-selling product per city by revenue — the pandas equivalent of a `ROW_NUMBER()` window function.

Write a function `top_product(df)` that returns a dictionary mapping each `city` to the `product` with the highest total `amount` in that city. When two products t

Accepted Answer

Step 1: Aggregate first. `groupby(["city", "product"])` with `as_index=False` gives a tidy frame of one row per city-product pair and its total revenue. Step 2: Sort by `city` ascending, `amount` descending, then `product` ascending. That third key is what makes ties deterministic instead of dependent on row order. Step 3: `drop_duplicates(subset=["city"], keep="first")` keeps the top row of each city block — the pandas idiom for `ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) = 1`. Step 4: `dict(zip(...))` reshapes the two winner columns into the requested mapping. Step 5: `idxmax()` looks tempting but silently picks an arbitrary winner on ties, which is why the explicit sort is safer.

Top-selling product in every city

More Pandas — GroupBy questions

Asked at Flipkart