How to create multi level index from multiindex column dataframe

Question

The main goal is to set multi-index row from existing df.

Say we have multilevel column as below

        level1            
        level2            
      sub_name   A   B   C
ONE         TT  11  12  13
TWO         TT  21  22  23
THREE       TT  31  32  33

then, the intended output (1)

           level1        
           level2        
           A   B   C
TT ONE       11  12  13
   TWO       21  22  23
   THREE     31  32  33

I have the impression this can be achieved by group OR directly using set_index, as below

df = df.groupby((slice ( None ), slice ( None ),'sub_name'),as_index = False)

OR

df=df.set_index([(slice ( None ), slice ( None ),'sub_name')])

However, this is not working.

Such that the first approach return an error

TypeError: unhashable type: 'slice'

and second approach

TypeError: The parameter "keys" may be a column key, one-dimensional array, or a list containing only valid column keys and one-dimensional arrays.. Received column of type <class 'tuple'>

May I know what is the proper way of achieving the intended goal.

The full code to reproduce the above error

import pandas as pd

df = pd.DataFrame ( {'A': [11, 21, 31],
                     'B': [12, 22, 32],
                     'C': [13, 23, 33]},
                    index=['ONE', 'TWO', 'THREE'] )

df.columns = pd.MultiIndex.from_product ( [['level1'], ['level2'], df.columns] )

df2 = pd.DataFrame ( ['TT'] * len ( df ), index=df.index,
                     columns=pd.MultiIndex.from_product ( [df.columns.levels [0],
                                                           df.columns.levels [1],
                                                           ['sub_name']] ) ) 
# Im just curios whether there is simpler way of creating constant column like this


df = pd.concat ( [df2,df ], axis=1 )

# df = df.groupby((slice ( None ), slice ( None ),'sub_name'),as_index = False)

# df=df.set_index([(slice ( None ), slice ( None ),'sub_name')])

With support from contributor.

The bonus question was whether it is possible to add another level on top of TT without the need of creating constant column (i.e.,df2)

               level1
               level2
               A   B   C
main_level TT  ONE     11  12  13
               TWO     21  22  23
               THREE   31  32  33

Anurag Dabas · Accepted Answer · 2021-08-07 15:15:44Z

2

You can try set_index()+xs()+swaplevel()+drop():

df=(df.set_index(df.xs('sub_name',axis=1,level=2).values.squeeze(),append=True)
      .swaplevel(0).drop('sub_name',axis=1,level=2))

output:

            level1
            level2
            A   B   C
TT  ONE     11  12  13
    TWO     21  22  23
    THREE   31  32  33

Note: you can also use df.loc[:,(slice(None),slice(None),'sub_name')] in place of df.xs('sub_name',axis=1,level=2)

Update:

you can try pd.MultiIndex.from_product():

df.index=pd.MultiIndex.from_product([['main_level'],['TT'],df.index.unique()])

output:

                        level1
                        level2
                        A   B   C
main_level TT   ONE     11  12  13
                TWO     21  22  23
                THREE   31  32  33

edited Aug 7, 2021 at 15:15

answered Aug 7, 2021 at 11:25

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

rpb Over a year ago

Thanks @Anurag. btw Im just curios whether it is possible to create another level ('main_level) on top of TT without creating the constant column(i.e., df2). For example, the expected output will be something: level1 level2 A B C main_level TT ONE 11 12 13 TWO 21 22 23 THREE 31 32 33

rpb Over a year ago

Sorry, the comment is not well formatted.But im not sure whether this curiosity worth new post or not.

Anurag Dabas Over a year ago

@balandongiv you can add that in question

rpb Over a year ago

Rename index column: df=df.rename_axis ( index=['main_level', None] ) # edit the naming

Anurag Dabas Over a year ago

@balandongiv you don't need to run set_index() 2 times..updated answer ..kindly have a look :)

|

Collectives™ on Stack Overflow

How to create multi level index from multiindex column dataframe

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related