Ranking & Row Numbers
Python · GroupBy & SQL-Style Analytics
Theory
s.rank() gives each value its position in sorted order, with ascending=False for 'biggest = 1'. The method argument controls ties, matching SQL's ranking functions: method='min' gives tied values the same lowest rank and leaves a gap (1, 1, 3), like RANK(); method='dense' gives the same rank without gaps (1, 1, 2), like DENSE_RANK(); method='first' breaks ties by order of appearance (1, 2, 3), like ROW_NUMBER(). The default 'average' gives ties the mean rank (1.5, 1.5) and returns floats.
To rank within groups, rank on a groupby: df.groupby('city')['amount'].rank(method='dense', ascending=False) restarts at 1 for each city - RANK() OVER (PARTITION BY city ORDER BY amount DESC). For a plain ROW_NUMBER in a chosen order, sort first and then use df.groupby('city').cumcount() + 1, which numbers rows 1, 2, 3 within each group. rank(pct=True) gives percentile ranks between 0 and 1.
Why we use it
Leaderboards, top performers per region, 'second highest salary' and de-duplication by row number all need ranks. pandas reproduces SQL's RANK, DENSE_RANK and ROW_NUMBER with one call, including per partition.
When to use it
Choose method='min' for competition-style ranks, 'dense' for 'Nth highest' questions, 'first' or cumcount for unique row numbers. Rank within groups with groupby(...).rank(). For only the top n rows, nlargest or head after sorting is simpler.
How it works
- Need ranks or row numbers
- Pick the order: column and ascending=False for biggest first
- Should ties share a rank? Yes: method='min' or 'dense'. No: method='first' or cumcount()+1.
- Ranking within groups? Yes: df.groupby(key)[col].rank(...). No: Rank the whole column.
- Rank column (float - use astype(int) if needed)
Syntax
s.rank(method='min' | 'dense' | 'first' | 'average', ascending=False)
df.groupby('key')['col'].rank(method='dense', ascending=False)
df.sort_values([...]).groupby('key').cumcount() + 1Example
df['rank'] = df.groupby('city')['amount'].rank(method='dense', ascending=False)
df['row_num'] = df.sort_values('amount', ascending=False).groupby('city').cumcount() + 1Key points
- method='min' = RANK(), 'dense' = DENSE_RANK(), 'first' = ROW_NUMBER()
- Default 'average' returns floats like 1.5 for ties
- Use ascending=False so the biggest value gets rank 1
- groupby('key')['col'].rank(...) restarts ranks per group (PARTITION BY)
- sort + groupby().cumcount() + 1 gives ROW_NUMBER in any order you choose