Dataframe Transpose after groupby-CodePudding

This code picks the top 3 values for each id from df and put in df2. I want to transpose the df2 data into dataframe with column id and 3 columns associated with 3 top values as shown below. I try df2_transposed = df2.T but it doesn't work. Can you please help?

import pandas as pd
df = pd.DataFrame([[1,1], [1,6], [1,39],[1,30],[1,40],[1,140], [2,2], [2,1], [2,20], [2,15], [2,99], [2,9]], columns=['id', 'value'])
print(df)

df2 = df.groupby('id')['value'].nlargest(3)

df-----------
    id  value
0    1      1
1    1      6
2    1     39
3    1     30
4    1     40
5    1    140
6    2      2
7    2      1
8    2     20
9    2     15
10   2     99
11   2      9

what I want
   id  top1  top2  top3
0   1   140    40    39
1   2    99    20    15

CodePudding user response：

I have solution for you. This is tested. Please check this out:

import pandas as pd
import numpy as np
df = pd.DataFrame([[1,1], [1,6], [1,39],[1,30],[1,40],[1,140], [2,2], [2,1], [2,20], [2,15], [2,99], [2,9]], columns=['id', 'value'])

#Filtering the largest 3 values
x = (df.groupby('id')['value']
                .apply(lambda x: x.nlargest(3))
                .reset_index(level=1, drop=True)
                .to_frame('value'))
#Transposing using unstack
x = x.set_index(np.arange(len(x)) % 3, append=True)['value'].unstack().add_prefix('top')
x = x.reset_index()
x

Hope this will help you :)

CodePudding user response：

Single expression:

df2 = df.groupby('id').apply(
    lambda x: x['value'].nlargest(3).reset_index(drop=True).T) \
    .set_axis(['top1', 'top2', 'top3'], axis=1).reset_index()