2

Suppose I have dataframe like 

id     p1 p2 p3 p4  
1      0  9  0  4
2      0  0  0  4
3      1  3 10  7
4      1  5  3  1
5      2  3  7 10

Want to find column names of top-n highest-value columns in each pandas data frame row and want to exclude zero value from top 3.

id top1 top2 top3
1  p2   p4   
2  p4      
3  p3   p4   p2
4  p2   p3   p4/p1
5  p4   p3   p2

The present solutions return column names which are having zero too. Is there way to exclude zero values. have this solution 

arank = df.apply(np.argsort, axis = 1)
ranked_cols = df.columns.to_series()[arank.values[:,::-1][:,:3]]
new_df = pd.DataFrame(ranked_cols, index=df.index)

there also other solutions such as Find names of top-n highest-value columns in each pandas dataframe row. Can these be modified to exclude columns with zero value?

Community
  • 1
  • 1
Sushant Kulkarni
  • 439
  • 1
  • 4
  • 11

1 Answers1

2

You need reorder values by column names, and where 0 replace by mask to empty strings:

df = df.set_index('id')

k = 3
vals = df.values
arr1 = np.argsort(-vals, axis=1)

print (vals[np.arange(len(df.index))[:,None], arr1][:,:k])
[[ 9  4  0]
 [ 4  0  0]
 [10  7  3]
 [ 5  3  1]
 [10  7  3]]

a = df.columns[arr1[:,:k]]
mask = vals[np.arange(len(df.index))[:,None], arr1][:,:k] == 0
print (mask)
[[False False  True]
 [False  True  True]
 [False False False]
 [False False False]
 [False False False]]

result = pd.DataFrame(a, columns=['top{}'.format(i) for i in range(1, k+1)],
                         index=df.index)

result = result.mask(mask, '')
print(result)
   top1 top2 top3
id               
1    p2   p4     
2    p4          
3    p3   p4   p2
4    p2   p3   p1
5    p4   p3   p2
Community
  • 1
  • 1
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252