Search code examples
pandasdataframemultiple-columnstransformationmulti-index

Convert MultiIndex into columns Pandas

I would like to know how to perform the below transformation in a pandas dataframe. I have no idea how to tackle this. The idea is to take the index level 0 and set it as level 0 column with the rest of the columns place into the appropiated main column

initial dataframe

final dataframe

Solution

  • Try this reshaping the dataframe using set_index, unstack and swaplevel:

    df_out = df.set_index(df.groupby(level=0).cumcount()+1, append=True)\
           .reset_index(level=1)\
           .rename(columns={'level_1':'ident'})\
           .unstack(0)\
           .swaplevel(0,1, axis=1)\
           .sort_index(axis=1)
df_out

    Output:

              A                        B                         C                 
       city ident population    city ident population     city ident population
1        NY     1      57578  London     4     543534   Berlin     7    5257537
2        LA     2    8767867   Paris     5      25725   Madrid     8      53755
3  Valencia     3    8767678  Beijin     6     275275  Belfast     9     354354