Sorting on multiindex level in pandas dataframe

Question

I have the following pivot table

                       MQW             MSND           Grand Total      
                   Amount($m)       Amount($m)         Amount($m)      
                  Total Count      Total Count        Total Count
Margin Call Date                                                     
2016-12-06            16.99     4       8.50     6        25.50    10
2016-12-07            11.24     4       8.55     6        19.79    10
2016-12-08             4.21     5       8.28     6        12.49    11
2016-12-09            23.29     7       8.08     6        31.37    13
2016-12-12             0.29     1       8.73     6         9.02     7
 Total                56.03    21      42.14    30        98.18    51

with the structure

MultiIndex(levels=[[u' Grand Total', u'MSND', u'MQW'], [u'Amount($m)'], [u'Count', u'Total']],labels=[[2, 2, 1, 1, 0, 0], [0, 0, 0, 0, 0, 0], [1, 0, 1, 0, 1, 0]])

and for the life of me I can't get the 'Count' and 'Total' columns to switch places using the .sortlevel method without also reversing the order of 'MQW','MSND', and 'Grand Total'. I've also tried setting 'sort_remaining' = False but it isn't working. This is what I'm trying to get.

                       MQW             MSND           Grand Total      
                   Amount($m)       Amount($m)         Amount($m)      
                  Count Total      Count Total        Count Total
Margin Call Date                                                     
2016-12-06            13.99     4       7.50     6        35.50    10
2016-12-07             1.24     4       16.55    6         9.79    10
2016-12-08             7.21     5       0.28     6        22.49    11
2016-12-09            33.29     7       9.08     6        21.37    13
2016-12-12             0.29     1       8.73     6         9.02     7
 Total                56.03    21      42.14    30        98.18    51

Any help would be much appreciated!

Try doing df.sort_index(level=[1,0], axis=1) and if this doesn't work you can result to brute force multiindex selection. No sorting just selecting each column by hand. — Ted Petrou
– Ted Petrou, Commented Dec 19, 2016 at 15:45

ptrj · Accepted Answer · 2016-12-20 04:48:20Z

The following solution works. However, I believe some easier alternative should be possible.

First, create a new index inverting the level 2 labels like this:

idx = df.columns
new_idx1 = idx.set_levels(idx.levels[2][::-1], level=2)

# or, equivalently,
# new_idx1 = idx.set_levels(['Total', 'Count'], level=2)

or maybe better to change the codes of the labels:

new_idx2 = idx.set_labels(labels=[0, 1] * 3, level=2)

Note that the inner structure of new_idx2 is different than new_idx1, even though they seem to be the same. (The results of sortlevel applied on them will be different.)

You can also create a new_idx from scratch with pd.MultiIndex, pd.MultiIndex.from_arrays or pd.MultiIndex.from_tuples.

And now reindex, for example:

df_sorted = df.reindex(columns=new_idx2)
df_sorted
Out[337]: 
                  MQW              MSND         Grand Total       
           Amount($m)        Amount($m)          Amount($m)       
                Count  Total      Count  Total        Count  Total
2016-12-06          4  13.99          6   7.50           10  35.50
2016-12-07          4   1.24          6  16.55           10   9.79
2016-12-08          5   7.21          6   0.28           11  22.49
2016-12-09          7  33.29          6   9.08           13  21.37
2016-12-12          1   0.29          6   8.73            7   9.02
Total              21  56.03         30  42.14           51  98.18

Collectives™ on Stack Overflow

Sorting on multiindex level in pandas dataframe

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related