How to change the order / grouping / level of pandas MultiIndex columns?

Question

I am trying to reorder / swaplevel / pivot / something columns in a pandas dataframe. Columns are MultiIndex, but I can't find the sauce to do what I want.

The fastest variable column in my multi-index is a month, but I would like it to be the slowest different column.

I have an nbviewer laptop if you want to try it yourself: http://nbviewer.ipython.org/gist/flamingbear/4cfac24c80fe34a67474

What I have:

+-------------------------------------------------------------------+ |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | || |weight |extent |rank || |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | ||month|'1Jan'|'Feb' |'Mar'|'1Jan'|'Feb'|'Mar'|'1Jan'|'Feb'|'Mar'| | |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | ||year | | | | | | | | | | | |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | ||2000 |45.1 |46.1 |25.1 |13.442|14.94|15.02|13 |17 |14 | | |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | ||2001 |85.0 |16.0 |49.0 |13.380|14.81|15.14|12 |15 |17 | | |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | ||2002 |90.0 |33.0 |82.0 |13.590|15.13|14.88|15 |22 |10 | | |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | ||2003 |47.0 |34.0 |78.0 |13.640|14.83|15.27|17 |16 |22 | | |+-----+------+------+-----+------+-----+-----+------+-----+-----+ | +-------------------------------------------------------------------+ 

What I want

 +------------------------------------------------------------------+ |+-----+------+------+----+------+------+-----+------+------+----+ | ||month|1Jan |Feb |Mar || |+-----+------+------+----+------+------+-----+------+------+----+ | || |weight|extent|rank|weight|extent|rank |weight|extent|rank| | |+-----+------+------+----+------+------+-----+------+------+----+ | ||year | | | | | | | | | | | |+-----+------+------+----+------+------+-----+------+------+----+ | ||2000 |45.1 |13.442|13 |46.1 |14.94 |17 | 25.1 |15.02 |14 | | |+-----+------+------+----+------+------+-----+------+------+----+ | ||2001 |85.0 |13.380|12 |16.0 |14.81 |15 | 49.0 |15.14 |17 | | |+-----+------+------+----+------+------+-----+------+------+----+ | ||2002 |90.0 |13.590|15 |33.0 |15.13 |22 | 82.0 |14.88 |10 | | |+-----+------+------+----+------+------+-----+------+------+----+ | ||2003 |47.0 |13.640|17 |34.0 |14.83 |16 | 78.0 |15.27 |22 | | |+-----+------+------+-----------+------+-----+------+------+----+ | +------------------------------------------------------------------+ 

Any help would be greatly appreciated. I can work with my original DataFrame, but writing in CSV with the desired order would be fantastic.

Thanks in advance, Matt

Answer 1

Your columns are MultiIndex. You need to reassign the DataFrame columns with a new MultiIndex created from the swap levels of the existing one:

 df.columns = df.columns.swaplevel(0, 1) df.sortlevel(0, axis=1, inplace=True) >>> df month '1Jan' 'Feb' 'Mar' weight extent rank weight extent rank weight extent rank year 2000 45.1 13.442 13 46.1 14.94 17 25.1 15.02 14 2001 85.0 13.380 12 16.0 14.81 15 49.0 15.14 17 2002 90.0 13.590 15 33.0 15.13 22 82.0 14.88 10 2003 47.0 13.640 17 34.0 14.83 16 78.0 15.27 22 

Then you can export to csv:

 df.to_csv(filename)