Pivoting first level of a multilevel index to be the first level of a multilevel column-CodePudding

I have a multilevel index dataframe like this:

indx = [('location', 'a'), ('location', 'b'), ('location', 'c'), ('location2', 'a'), ('location2', 'b'), ('location2', 'c')]
indx = pd.MultiIndex.from_tuples(indx)
col = ['S1','S2','S3']
df = pd.DataFrame(np.random.randn(6, 3), index=indx, columns=col)

df

                   S1        S2        S3
location  a -0.453549 -0.079797  0.581657
          b -0.458573 -0.732625 -2.277674
          c  0.874403  0.459590 -1.220271
location2 a -1.418821  0.847556  0.665808
          b  1.321825 -0.150274  0.507685
          c  0.894865 -0.502577  2.460591

and I'm trying to move the first level of the index to be the first level of a multilevel column to look like this:

header = [np.array(['location','location','location','location2','location2','location2']),
np.array(['S1','S2','S3','S1','S2','S3'])]
df = pd.DataFrame(np.random.randn(3, 6), index=['a','b','c'], columns = header )

df

   location                     location2                    
         S1        S2        S3        S1        S2        S3
a  1.225889 -0.547848 -0.413482 -1.038456 -1.055484 -0.143620
b -1.278012  0.403767 -0.298525  0.198157  0.327973 -0.088555
c -1.152128  0.209956  0.939142  0.458412  1.207760 -0.091029

Any ideas?

CodePudding user response：

You want unstack:

df.unstack(level=0).swaplevel(0,1, axis=1).sort_index(axis=1)

Output:

   location                     location2                    
         S1        S2        S3        S1        S2        S3
a  0.022553  0.485896 -0.421144  1.836187 -0.354247 -1.744599
b  0.099465 -0.531600  0.165415 -0.022424  0.286443 -0.356408
c  0.660913  1.071645  1.803176 -0.514787 -0.704810  0.797522