12

I have the following dataframe:

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

I need to reshape the data frame in a way that for each id it will have the top 3 columns with the highest values. The result would be like this:

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

It shows the top 3 best sellers for every user_id. I have already done it using the dplyr package in R, but I am looking for the pandas equivalent.

smci
  • 32,567
  • 20
  • 113
  • 146
chessosapiens
  • 3,159
  • 10
  • 36
  • 58
  • 1
    Site policy is against pinging particular users, regardless of whether or not they've helped you in the past. – DSM Aug 15 '16 at 12:52

2 Answers2

20

You could use np.argsort to find the indices of the n largest items for each row:

import numpy as np
import pandas as pd

df = pd.DataFrame({'id': [1, 2, 3, 4, 5],
 'p1': [0, 0, 1, 1, 2],
 'p2': [9, 2, 3, 5, 3],
 'p3': [1, 3, 10, 3, 7],
 'p4': [4, 4, 7, 1, 10]})
df = df.set_index('id')

nlargest = 3
order = np.argsort(-df.values, axis=1)[:, :nlargest]
result = pd.DataFrame(df.columns[order], 
                      columns=['top{}'.format(i) for i in range(1, nlargest+1)],
                      index=df.index)

print(result)

yields

   top1 top2 top3
id               
1    p2   p4   p3
2    p4   p3   p2
3    p3   p4   p2
4    p2   p3   p1
5    p4   p3   p2
jpaugh
  • 6,634
  • 4
  • 38
  • 90
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
8

You can use:

df = df.set_index('id').apply(lambda x: pd.Series(x.sort_values(ascending=False)
       .iloc[:3].index, 
      index=['top1','top2','top3']), axis=1).reset_index()
print (df)
   id top1 top2 top3
0   1   p2   p4   p3
1   2   p4   p3   p2
2   3   p3   p4   p2
3   4   p2   p3   p4
4   5   p4   p3   p2
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252