Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reorder levels of MultiIndex in a pandas DataFrame

I have a DataFrame that looks something like this:

>>> df = pd.DataFrame(index=pd.MultiIndex.from_tuples([(num,letter,color) 
                    for num in range(1,3) 
                    for letter in ['a','b','c'] for color in ['Red','Green']], 
                    names=['Number','Letter','Color']))
>>> df['Value'] = np.random.randint(1,100,len(df))
>>> df
                     Value
Number Letter Color       
1      a      Red       97
              Green     61
       b      Red       97
              Green     98
       c      Red       91
              Green     47
2      a      Red       17
              Green     63
       b      Red       26
              Green     73
       c      Red       34
              Green     68

But I actually want my index to be ordered 'Letter, Color, Number'.

I currently do this as follows:

>>> df.reset_index().set_index(['Letter','Color','Number'])
                     Value
Letter Color Number       
a      Red   1          97
       Green 1          61
b      Red   1          97
       Green 1          98
c      Red   1          91
       Green 1          47
a      Red   2          17
       Green 2          63
b      Red   2          26
       Green 2          73
c      Red   2          34
       Green 2          68

Is this the best approach?

like image 669
AJG519 Avatar asked Dec 01 '15 20:12

AJG519


2 Answers

It's better to use reorder_levels to manipulate the order of MultiIndex levels. Just pass in a list of the level names/numbers in the order you want:

>>> df.reorder_levels(['Letter','Color','Number'])
                     Value
Letter Color Number       
a      Red   1          41
       Green 1          56
b      Red   1          43
       Green 1          42
c      Red   1          89
       Green 1          18
a      Red   2          55
       Green 2          93
b      Red   2          64
       Green 2           9
c      Red   2          21
       Green 2          93

There's also swaplevel if you simply want to swap the positions of two levels.

like image 151
Alex Riley Avatar answered Oct 21 '22 22:10

Alex Riley


Inplace Modification

Call MultiIndex.reorder_levels, then assign the new index to your DataFrame.

df.index = df.index.reorder_levels(['Letter', 'Color', 'Number']) 
df

                     Value
Letter Color Number       
a      Red   1          41
       Green 1          56
b      Red   1          43
       Green 1          42
c      Red   1          89
       Green 1          18
a      Red   2          55
       Green 2          93
b      Red   2          64
       Green 2           9
c      Red   2          21
       Green 2          93

Since Index objects are immutable, you cannot get over creating a new Index, but you can avoid duplicating your data by otherwise calling df.reorder_levels.

like image 33
cs95 Avatar answered Oct 21 '22 21:10

cs95