How to swap indexes in a multiple indexes dataframe with pandas ?

September 17, 2021    /    Viewed: 138    /    Comments: 0    /    Edit


Examples of how to swap indexes in a multiple indexes dataframe with pandas:

Create a multiple indexes dataframe with pandas

Let's first create a multiple indexes dataframe with pandas using MultiIndex / advanced indexing,see also how to create a dataframe with multiple indexes with pandas

import pandas as pd

iterables = [["Land", "Ocean", "Snow/Ice"], ["Liquid", "Ice"]]

index = pd.MultiIndex.from_product(iterables, names=["Id1", "Id2"])

df = pd.DataFrame({'Count A': [12., 70., 30., 20., 17.0, 45.0], 'Count B': [12., 70., 30., 20., 17.0, 45.0]}, index=index)

returns

                                 Count A  Count B
Id1      Id2                     
Land     Liquid     12.0     12.0
                 Ice        70.0     70.0
Ocean    Liquid     30.0     30.0
                 Ice        20.0     20.0
Snow/Ice Liquid     17.0     17.0
                 Ice        45.0     45.0

Swap indexes of a dataframe

To swap id1 and id2, a solution is to use pandas.DataFrame.swaplevel

df = df.swaplevel()

returns then

                                 Count A  Count B
Id2    Id1                       
Liquid Land         12.0     12.0
Ice    Land         70.0     70.0
Liquid Ocean        30.0     30.0
Ice    Ocean        20.0     20.0
Liquid Snow/Ice     17.0     17.0
Ice    Snow/Ice     45.0     45.0

Note: to make the dataframe more easiest to read, a solution is to use [pandas.DataFrame.sort_index](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_index.html)

    df = df.sort_index()

gives

                                 Count A  Count B
Id2    Id1                       
Ice    Land         70.0     70.0
             Ocean        20.0     20.0
             Snow/Ice     45.0     45.0
Liquid Land         12.0     12.0
             Ocean        30.0     30.0
             Snow/Ice     17.0     17.0

Another example with 3 levels of index

Another example with a dataframe with 3 indexes:

import pandas as pd

iterables = [["Land", "Ocean"], ["Liquid", "Ice"], ["Successful", "Unsuccessful"]]

index = pd.MultiIndex.from_product(iterables, names=["Id1", "Id2", "Id3"])

df = pd.DataFrame({'Count A': [12., 70., 30., 20., 17.0, 45.0, 42., 7.], 'Count B': [12., 70., 30., 20., 17.0, 45.0, 42., 7.]}, index=index)

                           Count A  Count B
Id1   Id2    Id3                           
Land  Liquid Successful       12.0     12.0
             Unsuccessful     70.0     70.0
      Ice    Successful       30.0     30.0
             Unsuccessful     20.0     20.0
Ocean Liquid Successful       17.0     17.0
             Unsuccessful     45.0     45.0
      Ice    Successful       42.0     42.0
             Unsuccessful      7.0      7.0

Note: with more than 2 levels, by default swaplevel() will swap the last two levels (e.g. id2 and id3 here):

df = df.swaplevel()

returns

                           Count A  Count B
Id1   Id3          Id2                     
Land  Successful   Liquid     12.0     12.0
      Unsuccessful Liquid     70.0     70.0
      Successful   Ice        30.0     30.0
      Unsuccessful Ice        20.0     20.0
Ocean Successful   Liquid     17.0     17.0
      Unsuccessful Liquid     45.0     45.0
      Successful   Ice        42.0     42.0
      Unsuccessful Ice         7.0      7.0

    df = df.sort_index()

gives

                           Count A  Count B
Id1   Id3          Id2                     
Land  Successful   Ice        30.0     30.0
                   Liquid     12.0     12.0
      Unsuccessful Ice        20.0     20.0
                   Liquid     70.0     70.0
Ocean Successful   Ice        42.0     42.0
                   Liquid     17.0     17.0
      Unsuccessful Ice         7.0      7.0
                   Liquid     45.0     45.0

Choose wich indexes to swap

Let's re-create the dataframe:

import pandas as pd

iterables = [["Land", "Ocean"], ["Liquid", "Ice"], ["Successful", "Unsuccessful"]]

index = pd.MultiIndex.from_product(iterables, names=["Id1", "Id2", "Id3"])

df = pd.DataFrame({'Count A': [12., 70., 30., 20., 17.0, 45.0, 42., 7.], 'Count B': [12., 70., 30., 20., 17.0, 45.0, 42., 7.]}, index=index)

                                                     Count A  Count B
Id1   Id2    Id3                           
Land  Liquid Successful       12.0     12.0
                         Unsuccessful     70.0     70.0
            Ice    Successful       30.0     30.0
                         Unsuccessful     20.0     20.0
Ocean Liquid Successful       17.0     17.0
                         Unsuccessful     45.0     45.0
            Ice    Successful       42.0     42.0
                         Unsuccessful      7.0      7.0

To swap id1 and id2, a solution is to do

df = df.swaplevel(0,1)

returns then

                                                     Count A  Count B
Id2    Id1   Id3                           
Liquid Land  Successful       12.0     12.0
                         Unsuccessful     70.0     70.0
Ice    Land  Successful       30.0     30.0
                         Unsuccessful     20.0     20.0
Liquid Ocean Successful       17.0     17.0
                         Unsuccessful     45.0     45.0
Ice    Ocean Successful       42.0     42.0
                         Unsuccessful      7.0      7.0

and

df = df.sort_index()

will give

                                                     Count A  Count B
Id2    Id1   Id3                           
Ice    Land  Successful       30.0     30.0
                         Unsuccessful     20.0     20.0
             Ocean Successful       42.0     42.0
                         Unsuccessful      7.0      7.0
Liquid Land  Successful       12.0     12.0
                         Unsuccessful     70.0     70.0
             Ocean Successful       17.0     17.0
                         Unsuccessful     45.0     45.0

References


Card image cap
profile-image
Daidalos

Hi, I am Ben.

I have developed this web site from scratch with Django to share with everyone my notes. If you have any ideas or suggestions to improve the site, let me know ! (you can contact me using the form in the welcome page). Thanks!