Search code examples
pythonpandasmulti-index

Reorganise Columns in MultiIndex Matrix Pandas

I have a pandas data frame where every row has a some readings from a sensor at different altitudes. I managed to get these readings in a matrix using the df.pivot function, where the index the identifier is, the columns the different altitude and three values of data at those altitudes.

My matrix looks like this (Data is level 0 column header, Alt is level 1)

Data_1. Data_2 Data_3.
Alt_1 - Alt_2 - Alt_3 - ... Alt-1 - Alt_2 - Alt_3 - ... Alt_1 - Alt_2 - Alt_3 - ...

What I want is to get the multi-index columns rearranged so that the level 0 as the altitudes and the level 1 is the data.

I tried various ways of changing levels, renaming tuples etc.. but found nothing useful. I expect the table to look like this:

Alt 1. Alt_2 Alt_3 ...
Data_1-Data_2-Data_3 Data_1-Data_2-Data_3 Data_1-Data_2-Data_3 Data_1-Data_2-Data_3
Solution

  • I think this is where swaplevel shines. Try with:

    df.swaplevel(axis=1)

    Example:

    df = pd.DataFrame(
    {"Grade": ['A','B','C','D','E','F','G','H','I']},
    index=[
        ['Data_1']*3 + ['Data_2']*3 + ['Data_3']*3,
        ['Alt_1','Alt_2','Alt_3']*3]).T

    Which looks as follows:

          Data_1             Data_2             Data_3            
       Alt_1 Alt_2 Alt_3  Alt_1 Alt_2 Alt_3  Alt_1 Alt_2 Alt_3
Grade      A     B     C      D     E     F      G     H     I

    After the transformation:

    df.swaplevel(axis=1)

    We get:

           Alt_1  Alt_2  Alt_3  Alt_1  Alt_2  Alt_3  Alt_1  Alt_2  Alt_3
      Data_1 Data_1 Data_1 Data_2 Data_2 Data_2 Data_3 Data_3 Data_3
Grade      A      B      C      D      E      F      G      H      I