40

I have a dataframe that has auction IDs and bid prices. The dataframe is sorted by auction id (ascending) and bid price (descending):

Auction_ID    Bid_Price
123           9
123           7
123           6
123           2
124           3
124           2
124           1
125           1

I'd like to add a column called 'Auction_Rank' that ranks auction id's by bid prices:

Auction_ID    Bid_Price    Auction_Rank
123           9            1
123           7            2
123           6            3
123           2            4
124           3            1
124           2            2
124           1            3
125           1            1
Christopher Jenkins
  • 825
  • 3
  • 12
  • 16
  • Why do bid prices 9 and 1 both have a rank of 1? And why auction_id 124 and 125, both bid price 1, have different ranks? – jbuddy_13 Aug 30 '21 at 21:12

1 Answers1

62

Here's one way to do it in Pandas-way

You could groupby on Auction_ID and take rank() on Bid_Price with ascending=False

In [68]: df['Auction_Rank'] = df.groupby('Auction_ID')['Bid_Price'].rank(ascending=False)

In [69]: df
Out[69]:
   Auction_ID  Bid_Price  Auction_Rank
0         123          9             1
1         123          7             2
2         123          6             3
3         123          2             4
4         124          3             1
5         124          2             2
6         124          1             3
7         125          1             1
Cai
  • 1,726
  • 2
  • 15
  • 24
Zero
  • 74,117
  • 18
  • 147
  • 154
  • 1
    What if you wanted to groupby only `Auction_ID==124`? Similar to dpylr's filter function in R – Gabriel Fair May 02 '18 at 05:16
  • `df['Auction_Rank'] = df.loc[df['Auction_ID'] == 124, :].groupby('Auction_ID')['Bid_Price'].rank(ascending=False)` – moto Aug 04 '18 at 14:14
  • What if need to rank except one particular column? – Yog Sep 07 '18 at 04:14
  • 3
    @Zero This answer doesn't work for me. the code: df = pd.DataFrame({'Auction_ID':[1,2,3,4], 'Bid_Price':[7, 10, 5, 3]}); df['Auction_Rank'] = df.groupby('Auction_ID')['Bid_Price'].rank(ascending=False); df yields [1,1,1,1] at Auction_Rank column. – shahar_m May 14 '19 at 08:13
  • what if all the columns are in string – ashish Jan 07 '21 at 17:52