CODEWITHSUNDEEP
pythonpandasdataframe
Bill Armstrong
Bill Armstrong

Reputation: 1777

Set order of mulitindexed columns in pandas dataframe

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

Upvotes: 2

Views: 752

Answers (2)

piRSquared
piRSquared

Reputation: 294278

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

Upvotes: 3

BENY
BENY

Reputation: 323266

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

Upvotes: 4

Related Questions