In pandas, how to differentiate between two equal ranks based on the value of another column?

Question

I have a dataframe df:

Index	A	B	Rank
1	55	27	1.0
2	62	15	2.5
3	76	15	2.5
4	85	04	4.0

Where the 'Rank' column was created by ranking 'B' in descending order. with :

df['Rank'] = df['B'].rank(ascending = False)

In rows 2 and 3 the ranks assigned are the same. Whenever there are same ranks assigned to two different rows, I want to break the tie by using the value of column 'A' in descending order so that the row containing the higher value of 'A' gets a lower rank.

Desired output:

Index	A	B	Rank
1	55	27	1.0
2	62	15	3.0
3	76	15	2.0
4	85	04	4.0

Edit: I want to first rank it by 'B' first, and then only use 'A' to decide between the same ranked elements, such that all the other ranks are still decided only by 'B'.

As per the suggested duplicate, you could do: `df['Rank'] = df[['B','A']].apply(tuple,axis=1).rank(method='dense',ascending=False).astype(int)` — ouroboros1, Apr 21 '23 at 17:17
Note that the accepted answer in the dup link is not recommended. — Quang Hoang, Apr 21 '23 at 17:24
In the above code, does it give precedence to ranking by `'B'` first and then only using `'A'` to decide between the same ranked elements. such that all the other ranks are still decided only by `'B'`. ? — Abhishek Banerjee, Apr 21 '23 at 17:26
I don't think using aggregation as tuples (like in the linked answer) is a good idea: it is **slow**. — mozway, Apr 21 '23 at 17:31

score 1 · Accepted Answer · answered Apr 21 '23 at 17:28

1

One option using sort_values and numpy:

order = df.sort_values(by=['B', 'A'], ascending=[False, False]).index

df.loc[order, 'Rank'] = np.arange(len(df))+1

A variant combined with rank:

df['Rank'] = (df.sort_values(by=['B', 'A'], ascending=[False, False])['B']
                .rank(method='first', ascending=False)
             )

Output:

   Index   A   B  Rank
0      1  55  27     1
1      2  62  15     3
2      3  76  15     2
3      4  85   4     4

answered Apr 21 '23 at 17:28

mozway

194,879
13
39
75

COuld you please check the edit once and confirm? Thanks a lot for the help. – Abhishek Banerjee Apr 21 '23 at 17:31
1

@AbhishekBanerjee both my approaches should use B to rank and break ties based on A. – mozway Apr 21 '23 at 17:33

In pandas, how to differentiate between two equal ranks based on the value of another column?

1 Answers1