Set order of mulitindexed columns in pandas dataframe

Question

Is there a way to reorder specific levels within the columns index in a Pandas dataframe based on my personal preference (e.g., by an ordered list)?

In [130]: frame = pd.DataFrame({
     ...: ('TWO','thing1'):[1,2,3,4],
     ...: ('TWO','thing4'):[1,2,3,4],
     ...: ('DARK','thing1'):[0.1,0.2,1,2],
     ...: ('ANTS','thing3'):['a','e','i','o'],
     ...: ('ANTS','thing1'):['a','e','i','o']})

In [131]: frame
Out[131]: 
    ANTS          DARK    TWO       
  thing1 thing3 thing1 thing1 thing4
0      a      a    0.1      1      1
1      e      e    0.2      2      2
2      i      i    1.0      3      3
3      o      o    2.0      4      4

My list is then based on a separately generated list. Important to note that I don't know either level 0 or level 1 index labels - they are variables.

In [132]: sort_list = ['DARK', 'ANTS', 'TWO']

If I then try and pass this list in the context of frame = frame[sort_list] or .reindex(columns=sort_list) it throws Expected tuple, got str for obvious reasons. Here are the solutions that worked on a single level index.

What I'd like to do is only sort on the top level and leave the second level as is. The final dataframe looking like this...

  DARK   ANTS           TWO       
thing1 thing1 thing3 thing1 thing4
   0.1      a      a      1      1
   0.2      e      e      2      2
   1.0      i      i      3      3
   2.0      o      o      4      4

Zero · Accepted Answer · 2018-06-28 04:04:45Z

4

You can using reindex

frame.reindex(sort_list, level=0, axis=1)
Out[126]: 
    DARK   ANTS           TWO       
  thing1 thing1 thing3 thing1 thing4
0    0.1      a      a      1      1
1    0.2      e      e      2      2
2    1.0      i      i      3      3
3    2.0      o      o      4      4

edited Jun 28, 2018 at 4:04

Zero

77.4k22 gold badges154 silver badges154 bronze badges

answered Jun 28, 2018 at 4:00

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Armstrong Over a year ago

I almost had it. ;-)

piRSquared · Accepted Answer · 2018-06-28 04:09:38Z

3

Option 1

You can sort the index then slice

frame.sort_index(axis=1, level=1)[['DARK', 'ANTS', 'TWO']]

    DARK   ANTS           TWO       
  thing1 thing1 thing3 thing1 thing4
0    0.1      a      a      1      1
1    0.2      e      e      2      2
2    1.0      i      i      3      3
3    2.0      o      o      4      4

Option 2

Set the first level of the columns to be categorical that is ordered

frame.columns = frame.columns.set_levels(
    pd.CategoricalIndex(
        frame.columns.levels[0],
        ['DARK', 'ANTS', 'TWO'],
        ordered=True
    ), level=0
)

frame.sort_index(axis=1)

    DARK   ANTS           TWO       
  thing1 thing1 thing3 thing1 thing4
0    0.1      a      a      1      1
1    0.2      e      e      2      2
2    1.0      i      i      3      3
3    2.0      o      o      4      4

edited Jun 28, 2018 at 4:09

answered Jun 28, 2018 at 4:01

piRSquared

296k68 gold badges509 silver badges654 bronze badges

1 Comment

Bill Armstrong Over a year ago

Thanks @piRSquared, another, previously unknown, method... :-) I don't think I've made it a day without seeing something new.

Collectives™ on Stack Overflow

Set order of mulitindexed columns in pandas dataframe

2 Answers 2

1 Comment

Option 1

Option 2

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Option 1

Option 2

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related