Finding column header for the second highest value in a row of a dataframe-CodePudding

I have a data frame:

Patch_0  Patch_1  Patch_2   Patch_7  exp_patch  
0        0.0      70.0      70.0       3            
1        0.0      70.0      74.0       4

using the following code I was able to find the colum header for the maximum value in that row:

df3['Highest_Rew_patch'] = df3.max(axis=1)
s = df3.iloc[:, df3.columns.str.startswith('Patch')].apply(lambda s:s.index[s.eq(s.max())].tolist(), axis=1)
s = s.apply(lambda s: ','.join(s))

Patch_0  Patch_1  Patch_2   Patch_7  exp_patch  Highest_Rew_patch
0        0.0      70.0      70.0       3            Patch_2,Patch_7
1        0.0      70.0      74.0       4            Patch_7

How do I create another new column with Second_highest and Third_highest values? Desired output:

Patch_0     Patch_1    Patch_2   Patch_7   exp_patch     Highest_Rew_patch    Second_highest        
    0.0        0.0      70.0      70.0       3            Patch_2,Patch_7      Patch_0,Patch_1
    1.0        0.0      70.0      74.0       4            Patch_7              Patch_2

CodePudding user response：

Keep your code, but instead of s.max(), use s.nlargest(2)[-1].

CodePudding user response：

one step for all rank

s = pd.wide_to_long(df.reset_index(),['Patch'],i = 'index',j='a',suffix='\\w ',sep='_').reset_index()
s['new'] = s.groupby('index')['Patch'].rank('dense')

out = df.join(pd.crosstab(index=s['index'],columns = s['new'], values= s['a'].astype(str).radd('Patch'),aggfunc=','.join))

CodePudding user response：

You can get all ranks at once using rank and reshaping with melt and pivot_table:

df.join(df.filter(like='Patch')
   .rank(method='dense', ascending=False, axis=1)
   .astype(int)
   .reset_index()
   .melt('index')
   .pivot_table(index='index', columns='value', values='variable', aggfunc=','.join)
 .iloc[:,:3] # optional: keep only top 3
 )

Output:

   Patch_0  Patch_1  Patch_2  Patch_7  exp_patch                1                2        3
0        0      0.0     70.0     70.0          3  Patch_2,Patch_7  Patch_0,Patch_1      NaN
1        1      0.0     70.0     74.0          4          Patch_7          Patch_2  Patch_0