Pivot dataframe and get two value columns-CodePudding

I have a dataset there look like this:

Region	Country	ProductGroup	Category	ProductFamily	value	other info
DK	Europe	X	y	Z	1/2	X
DE	Europe	X1	y1	Z1	1/12	-
US	Americas	X	y	Z	6/9	-

I Would like to have a pivot like this

                                             Region
                                             Europe                                  Americas
                                             DK                   DE                 US
                                             Value Other info     Value Other info   Value Other info
ProductGroup    Category    ProductFamily
C               Y           Z                1/2  X                                  6/9  -
X1              Y1          Z1                                     1/12  -

I have tried this

x = df_out.pivot(index=['MATKL','ProductGroup','Category','ProductFamily'], 
                 columns=['Region','Country'], values = ['value','Eother info'])

It does nearly the job, but it give me two blocks on for value and then all region, countries and then the same for Other info.

CodePudding user response：

Use DataFrame.reorder_levels with DataFrame.sort_index:

x = (df_out.pivot(index=['ProductGroup','Category','ProductFamily'], 
                 columns=['Region','Country'], values = ['value','other info'])
     .reorder_levels([1,2,0], axis=1)
     .sort_index(axis=1, level=[0,1], sort_remaining=False))
print (x)
Region                                  DE                DK             \
Country                             Europe            Europe              
                                     value other info  value other info   
ProductGroup Category ProductFamily                                       
X            y        Z                NaN        NaN    1/2          X   
X1           y1       Z1              1/12          -    NaN        NaN   

Region                                    US             
Country                             Americas             
                                       value other info  
ProductGroup Category ProductFamily                      
X            y        Z                  6/9          -  
X1           y1       Z1                 NaN        NaN

CodePudding user response：

try this:

df.set_index([*df][:-2]).unstack([0,1]).swaplevel(0,-1, axis=1).sort_index(level=0, axis=1)