CODEWITHSUNDEEP
pythonpandassortingdataframe
Eksana Stasis
Eksana Stasis

Reputation: 183

Appending column in dataframe based on rank of values in preceding columns

My dataframe looks like this:

      Date       AAPL      NFLX       INTC    
20 2008-01-31  27.834286  3.764286  25.350000            
40 2008-02-29  27.847143  3.724286  24.670000   
60 2008-03-31   27.834286  3.764286  25.350000

Imagine that these are % returns above. How can I rank the values in the 3 columns so that the dataframe now looks like:

      Date       AAPL      NFLX       INTC      Rank_Max  Rank_Min
20 2008-01-31  27.834286  3.764286  25.350000     AAPL      NFLX        
40 2008-02-29  27.847143  33.724286  24.670000    NFLX      INTC
60 2008-03-31   27.834286  3.764286  25.350000    etc

Thank you.

Upvotes: 1

Views: 48

Answers (2)

DYZ
DYZ

Reputation: 57033

First, find the ranks (this function incidentally filters out any non-numeric columns):

ranks = df.rank(axis=1, numeric_only=True)

Next, find the indexes of the smallest and the largest ranks:

df['Rank_Max'] = ranks.idxmax(axis=1)
df['Rank_Min'] = ranks.idxmin(axis=1)
df
#          Date       AAPL       NFLX   INTC Rank_Max Rank_Min
#20  2008-01-31  27.834286   3.764286  25.35     AAPL     NFLX
#40  2008-02-29  27.847143  33.724286  24.67     NFLX     INTC
#60  2008-03-31  27.834286   3.764286  25.35     AAPL     NFLX

Upvotes: 2

jpp
jpp

Reputation: 164673

Using idxmax and idxmin:

df['Rank_Max'] = df[['AAPL', 'NFLX', 'INTC']].idxmax(axis=1)
df['Rank_Min'] = df[['AAPL', 'NFLX', 'INTC']].idxmin(axis=1)

print(df)

          Date       AAPL      NFLX   INTC Rank_Max Rank_Min
20  2008-01-31  27.834286  3.764286  25.35     AAPL     NFLX
40  2008-02-29  27.847143  3.724286  24.67     AAPL     NFLX
60  2008-03-31  27.834286  3.764286  25.35     AAPL     NFLX

Upvotes: 0

Related Questions