CODEWITHSUNDEEP
pythonpandasdataframe
user9397006
user9397006

Reputation: 65

Restructuring Pandas DataFrame rows to columns

I have the following df:

itemname participant s0  s1  s2  s3
E1       1           no  no  no  yes
E1       2           no  no  yes no
E1       3           no  no  no  yes
A1       1           no  no  no  yes
A1       2           no  no  no  yes
A1       3           yes no  no  no
Foo      1           no  no  no  yes
Foo      2           yes no  no  no
Foo      3           no  yes yes yes

which I need to convert to the following format:

itemname  participant_1  participant_2  participant_3 
E1_s0     no             no             no 
E1_s1     no             no             no 
E1_s2     no             yes            no 
E1_s3     yes            no             yes 
A1_s0     no             no             yes 
A1_s1     no             no             no 
A1_s2     no             no             no 
A1_s3     yes            yes            no 
Foo_s0    no             yes            no      
Foo_s1    no             no             yes
Foo_s2    no             no             yes
Foo_s3    yes            no             yes

How can I combine each itemname with s0, s1, s2 and s3 as rows while differentiating among participants (where each participant should become a separate column)?

Upvotes: 1

Views: 72

Answers (1)

Sayandip Dutta
Sayandip Dutta

Reputation: 15872

You can try the following:

>>> df.pivot(
         columns='itemname',index='participant').T
         .swaplevel(axis='index')
         .sort_index(0).reset_index()
         .assign(itemname=lambda x:(x.pop('itemname')+'_' + x.pop('level_1'))
       ).set_index('itemname').add_prefix('participant_')

participant participant_1 participant_2 participant_3
itemname                                             
A1_s0                  no            no           yes
A1_s1                  no            no            no
A1_s2                  no            no            no
A1_s3                 yes           yes            no
E1_s0                  no            no            no
E1_s1                  no            no            no
E1_s2                  no           yes            no
E1_s3                 yes            no           yes
Foo_s0                 no           yes            no
Foo_s1                 no            no           yes
Foo_s2                 no            no           yes
Foo_s3                yes            no           yes

Upvotes: 1

Related Questions