I have a table and I want to convert it using Python 3x Pandas into the following:

Resulting table should be:

Is there any neat, Pythonic way of doing it using Pandas or other method?

CodePudding user response：

You can't pivot as is, but you could add a group number using groupby cumcount and pivot using the newly created numbering as index:

out = (df.assign(num=df.groupby(['Group','Review']).cumcount())
       .pivot(['num','Group'],'Review','Assessment')
       .droplevel(0).sort_index()
       [['Positive','Negative']]
       .reset_index()
       .rename_axis(columns=[None]))

Output:

    Group          Positive        Negative
0  GroupA      Good players  No team spirit
1  GroupA  They scored well             NaN
2  GroupB        Resilience     Goal failed
3  GroupB  Growth potential     Bad weather
4  GroupB               NaN   Bad technique

CodePudding user response：

it is not what you exactly want, but it seems this table structure has more sence:

res = (df.groupby(['Group','Review']).
       apply(lambda x:x['Assessment'].tolist()).
       unstack())
print(res)
'''
Review                                   Negative                          Positive
Group                                                                              
GroupA                           [No team spirit]  [Good players, They scored well]
GroupB  [Goal failed, Bad weather, Bad technique]    [Resilience, Growth potential]