Find rank of column relative to other rows-CodePudding

df = pd.DataFrame({'Alice': [4,15,2], 'Bob': [9,3,5], 'Emma': [4,7,19]})

I can find who got the highest score in each round with

df.idxmax(1)
> 0      Bob
  1    Alice
  2     Emma
 dtype: object

But I would like to find in which place Bob finished in each round. Output should be:

> 0
  2
  1

Seems like something with argsort should work, but can't quite get it.

(Here is the same question, but in SQL Server.)

CodePudding user response：

You can use rank:

df.rank(axis=1, method='first', ascending=False)

NB. check the methods to find the one that better suits your need:

How to rank the group of records that have the same value (i.e. ties):

average: average rank of the group
min: lowest rank in the group
max: highest rank in the group
first: ranks assigned in order they appear in the array
dense: like ‘min’, but rank always increases by 1 between groups.

output:

   Alice  Bob  Emma
0    2.0  1.0   3.0
1    1.0  3.0   2.0
2    3.0  2.0   1.0

NB. note that the ranks start as 1, you can add sub(1) to get a rank from 0

df.rank(axis=1, method='first', ascending=False).sub(1).convert_dtypes()

output:

   Alice  Bob  Emma
0      1    0     2
1      0    2     1
2      2    1     0