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.)
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