Convert a dataset with a single level header to a dataset with multiple level headers

Question

For the dataset df with one single-level header as follows, I hope to convert it into a dataset with a three-level header.

How to achieve this? Thanks.

   year       cpi       ppi Country code Country name
0  1995  0.107797  0.085297          AUS    Australia
1  1996  0.110997  0.082973          AUS    Australia
2  1997  0.110098  0.083651          AUS    Australia
3  1998  0.107635  0.086049          AUS    Australia
4  1999  0.104969  0.089700          AUS    Australia
5  1995  0.120071  0.129769          MEX       Mexico
6  1996  0.119249  0.142606          MEX       Mexico
7  1997  0.124866  0.151372          MEX       Mexico
8  1998  0.127448  0.153303          MEX       Mexico
9  1999  0.134342  0.159876          MEX       Mexico

The expected output:

     Country name Australia   Australia    Mexico    Mexico
     Country code       AUS         AUS       MEX       MEX
   Indicator Name       cpi         ppi       cpi       ppi
0            1995  0.107797    0.085297  0.120071  0.129769
1            1996  0.110997    0.082973  0.119249  0.142606
2            1997  0.110098    0.083651  0.124866  0.151372
3            1998  0.107635    0.086049  0.127448  0.153303
4            1999  0.104969      0.0897  0.134342  0.159876

You've incorrect expected output. "MEX" does not have "cpi" value from "1995-1999". Similarly, for "AUS". — Azhar Khan, Oct 27 '22 at 08:51

score 1 · Accepted Answer · edited Oct 27 '22 at 13:45

Use DataFrame.pivot with DataFrame.reorder_levels and DataFrame.sort_index:

df = (df.pivot(index=['year'], 
              columns=['Country code','Country name'])
        .reorder_levels([2, 1, 0],axis=1)
        .sort_index(axis=1))
print(df)
Country name Australia              Mexico          
Country code       AUS                 MEX          
                   cpi       ppi       cpi       ppi
year                                                
1995          0.107797  0.085297  0.120071  0.129769
1996          0.110997  0.082973  0.119249  0.142606
1997          0.110098  0.083651  0.124866  0.151372
1998          0.107635  0.086049  0.127448  0.153303
1999          0.104969  0.089700  0.134342  0.159876

Convert a dataset with a single level header to a dataset with multiple level headers

1 Answers1