Top-N per Group
Python · GroupBy & SQL-Style Analytics
Theory
'Top 2 orders in each city' is a classic window-function question. The most readable pandas answer is sort, then take the first n rows of each group: df.sort_values('amount', ascending=False).groupby('city').head(2). groupby().head(n) keeps the original columns and rows in sorted order; groupby().nth(0) picks a single position, and .tail(n) gives the bottom n.
Alternatives: df.groupby('city')['amount'].nlargest(2) returns just the column with a (city, original index) MultiIndex; and a rank filter, df[df.groupby('city')['amount'].rank(method='dense', ascending=False) <= 2], decides explicitly how ties behave - with 'dense' or 'min', tied values are all kept, so a city can return more than n rows. The single best row per group can also come from df.loc[df.groupby('city')['amount'].idxmax()]. In SQL this is QUALIFY ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) <= 2.
Why we use it
Best sellers per category, top customers per region and latest events per user all need 'the first n rows of each group'. pandas does it in one chain instead of a loop over groups.
When to use it
Use sort + groupby().head(n) for exactly n rows per group, a rank filter when ties must be kept, and idxmax for the single best row. For top n overall, nlargest(n, col) is enough.
How it works
- Need top n rows per group
- Sort by the measure, highest first
- Group by the key (sorted order is kept inside groups)
- Keep ties beyond n? Yes: Filter rank(method='dense') <= n. No: Take groupby().head(n).
- n best rows for each group
Syntax
df.sort_values('col', ascending=False).groupby('key').head(n)
df[df.groupby('key')['col'].rank(method='dense', ascending=False) <= n]
df.loc[df.groupby('key')['col'].idxmax()]Example
top2 = (df.sort_values('amount', ascending=False)
.groupby('city').head(2)
.sort_values(['city', 'amount'], ascending=[True, False]))Key points
- sort_values + groupby().head(n) gives exactly n rows per group
- A rank filter (method='dense' or 'min') keeps all tied rows
- idxmax() gives the row label of each group's maximum - pass it to .loc
- groupby()['col'].nlargest(n) returns only that column, with a MultiIndex
- SQL equivalent: QUALIFY ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) <= 2