Pivot Data with Uneven Groups Python Pandas

Question

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

Group	Assessment	Review
GroupA	No team spirit	Negative
GroupA	Good players	Positive
GroupA	They scored well	Positive
GroupB	Goal failed	Negative
GroupB	Bad weather	Negative
GroupB	Resilience	Positive
GroupB	Growth potential	Positive
GroupB	Bad technique	Negative

Resulting table should be:

Group	Positive	Negative
GroupA	Good players	No team spirit
GroupA	They scored well	NaN
GroupB	Resilience	Goal Failed
GroupB	Growth Potential	Bad weather
GroupB	NaN	Bad Technique

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

user7864386 · Accepted Answer · 2022-03-10 16:35:06Z

2

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

answered Mar 10, 2022 at 16:35

user7864386

Sign up to request clarification or add additional context in comments.

2 Comments

matt.aurelio Over a year ago

Thank you. Can you explain what the "num=" parameters serves for in the assign method?

user7864386 Over a year ago

@matt.aurelio it's creating a new column named "num", which we use to pivot

SergFSM · Accepted Answer · 2022-03-10 22:20:38Z

0

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]

answered Mar 10, 2022 at 22:20

SergFSM

1,4991 gold badge6 silver badges9 bronze badges

Collectives™ on Stack Overflow

Pivot Data with Uneven Groups Python Pandas

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related