CODEWITHSUNDEEP
pythonpandasindexing
rpb
rpb

Reputation: 3299

How to create multi level index from multiindex column dataframe

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

Upvotes: 0

Views: 816

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24322

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

Upvotes: 2

Related Questions