Pandas Transformation-CodePudding

I have a pandas dataframe like -

id  name1   name2    date     type  usage1   usage2

1    abc     def   12-09-21    a    100.01    109
2    xyz     wer   13-09-21    b     45        51
1    xyz     def   14-09-21    b     34       100
1    abc     def   13-09-21    a     50        60
1    abc     def   14-09-21    a     45        10

I want it to be transformed to a format like-

                               12-09-21          13-09-21            14-09-21
id    name1   name2  type   usage1  usage2    usage1  usage2     usage1  usage2
1      abc     def    a      100.01  109        50      60         45      10
1      xyz     wer    b       NA      NA        NA      NA         34      100
2      xyz     wer    b       NA      NA        45      51         NA      NA

What would be the best way to do that considering the date column to in ascending order from left to right in the output.

CodePudding user response：

This is essentially a pivot, but then you need to clean up the column axis with swaplevel and sort_index. This will leave you with a MultiIndex on the rows with ['id', 'name1', 'name2', 'type'] as the levels and a MultiIndex on the columns.

import pandas as pd
#df['date'] = pd.to_datetime(df['date'])

(df.pivot(index=['id', 'name1', 'name2', 'type'], columns='date')
   .swaplevel(0,1, axis=1)
   .rename_axis(columns=[None, None])
   .sort_index(axis=1)
)


                      12-09-21        13-09-21        14-09-21       
                      usage1 usage2   usage1 usage2   usage1 usage2
id name1 name2 type                                                
1  abc   def   a      100.01  109.0     50.0   60.0     45.0   10.0
   xyz   def   b         NaN    NaN      NaN    NaN     34.0  100.0
2  xyz   wer   b         NaN    NaN     45.0   51.0      NaN    NaN