CODEWITHSUNDEEP
pythonpandasdataframe
flyingdutchman
flyingdutchman

Reputation: 346

How to transform a 2nd level category column with values to multiple columns

I'd like to transform a "category" index from a DataFrame with MultiIndex into multiple columns each holding the values of the row they belonged to before. My index columns are Index and Country.

 Index      Country   Count
     0      'Canada'     10
             'Italy'     20
     1   'Indonesia'      5
            'Canada'      2
     2       'Italy'     14

To:

   Canada  Indonesia  Italy
0      10          0     20
1       2          5      0
2       0          0     14

It is quite close to the get_dummies function but I need to keep the count values to their correct positions.

Upvotes: 1

Views: 208

Answers (1)

jezrael
jezrael

Reputation: 863361

If one column DataFrame with MultiIndex select column with Series.unstack:

df1 = df['Count'].unstack(fill_value=0)
print (df1)
Country  'Canada'  'Indonesia'  'Italy'
Index                                  
0              10            0       20
1               2            5        0
2               0            0       14

If MultiIndex Series:

df1 = s.unstack(fill_value=0)

Upvotes: 1

Related Questions