How Can I Reorder Multi-indexed Dataframe Columns At A Specific Level

I have a multi-indexed DataFrame with names attached to the column levels. I'd like to be able to easily shuffle the columns around so that they match the order specified by the u

Solution 1:

There is a very simple way: just create a new dataframe based on the original, with the correct order of multiindex columns:

multi_tuples = [('IWWGCW',24), ('IWWGCW',48), ('IWWGDW',24), ('IWWGDW',48)
    , ('BASE',24), ('BASE',48)]

multi_cols = pd.MultiIndex.from_tuples(multi_tuples, names=['Experiment', 'Lead Time'])

df_ordered_multi_cols = pd.DataFrame(df_ori, columns=multi_cols)

Solution 2:

This is the simplest one that worked for me:

1. for your selected level, create a list with columns in desired order;

2. reindex your columns and create a MultiIndex object from that list, keep in mind this returns a tuple;

3. use the MultiIndex object to reorder your DataFrame.

cols = ['IWWGCW', 'IWWGDW', 'BASE']
new_cols = df.columns.reindex(cols, level=0)
df.reindex(columns=new_cols[0]) #new_cols is a single item tuple

In one line:

df.reindex(columns=df.columns.reindex(['IWWGCW', 'IWWGDW', 'BASE'], level=0)[0])

voilá

Solution 3:

I don't know of anything off-hand. Created an enhancement ticket about it:

http://github.com/pydata/pandas/issues/1864

Solution 4:

A solution from my comment above, using pandas 1.3.2:

df.reindex(columns=['IWWGCW', 'IWWGDW', 'BASE'], level='Experiment')

Solution 5:

The comment by andrew_reece should be the accepted answer. Simply use reindex().

Copy & pasting from the github issue:

>>> df
                     vals
first second third       
mid   3rd    9921.9656212.06
      1st    73     -6.46818   -15.756585.90
btm   2nd    9159.75474    -1.47905    -6.03
      1st    7178.01909   -21.12
      3rd    61611.916751.06579    -4.01
top   1st    2411.793631.71
      3rd    67713.38238   -16.7740717.19
      2nd    728   -21.55368.09>>> df.reindex(['top', 'mid', 'btm'], level='first')
                     vals
first second third       
top   1st    2411.793631.71
      3rd    67713.38238   -16.7740717.19
      2nd    728   -21.55368.09
mid   3rd    9921.9656212.06
      1st    73     -6.46818   -15.756585.90
btm   2nd    9159.75474    -1.47905    -6.03
      1st    7178.01909   -21.12
      3rd    61611.916751.06579    -4.01>>> df.reindex(['1st', '2nd', '3rd'], level='second')
                     vals
first second third       
mid   1st    73     -6.46818   -15.756585.90
      3rd    9921.9656212.06
btm   1st    7178.01909   -21.12
      2nd    9159.75474    -1.47905    -6.03
      3rd    61611.916751.06579    -4.01
top   1st    2411.793631.71
      2nd    728   -21.55368.09
      3rd    67713.38238   -16.7740717.19>>> df.reindex(['top', 'btm'], level='first').reindex(['1st', '2nd'], level='second')
                     vals
first second third       
top   1st    2411.793631.71
      2nd    728   -21.55368.09
btm   1st    7178.01909   -21.12
      2nd    9159.75474    -1.47905    -6.03