Reorder Multiindex Pandas Dataframe

Question

I would like to reorder the columns in a dataframe, and keep the underlying values in the right columns.

For example this is the dataframe I have

cols = [ ['Three', 'Two'],['A', 'D', 'C', 'B']]
header = pd.MultiIndex.from_product(cols)
df = pd.DataFrame([[1,4,3,2,5,8,7,6]]*4,index=np.arange(1,5),columns=header)                  
df.loc[:,('One','E')] = 9
df.loc[:,('One','F')] = 10

>>> df

And I would like to change it as follows:

header2 = pd.MultiIndex(levels=[['One', 'Two', 'Three'], ['E', 'F', 'A', 'B', 'C', 'D']],
       labels=[[0, 0, 0, 0, 1, 1, 1, 1, 2, 2], [0, 1, 2, 3, 4, 5, 2, 3, 4, 5]])

df2 = pd.DataFrame([[9,10,1,2,3,4,5,6,7,8]]*4,index=np.arange(1,5), columns=header2)
>>>>df2

seperate question - which syntax do I use in order for the dataframe to show up here — Jelmerd
– Jelmerd, Commented Aug 30, 2018 at 20:02
Your MultiIndex in the first and second DataFrames aren't the same... what exactly are you trying to do? — cs95
– cs95, Commented Aug 30, 2018 at 20:05
Basically I am trying to re-order the columns, change the top headers to 'One', 'Two', Three' and for the 'Two' and 'Three' headers, re-order the bottom headers to 'A','B','C','D' — Jelmerd
– Jelmerd, Commented Aug 30, 2018 at 20:08
Can you explain why the final ordering is "One Two Three" and not "One Three Two"? — user3483203
– user3483203, Commented Aug 30, 2018 at 20:11
This is just an example, the point is that I am looking for a way to put the columns to be in any particular order (not necessarily alphabetical) — Jelmerd
– Jelmerd, Commented Aug 30, 2018 at 20:48

cs95 · Accepted Answer · 2018-08-30 20:50:00Z

5

First, define a categorical ordering on the top level. Then, call sort_index on the first axis with both levels.

v = pd.Categorical(df.columns.get_level_values(0), 
                   categories=['One', 'Two', 'Three'], 
                   ordered=True)
v2 = pd.Categorical(df.columns.get_level_values(1), 
                    categories=['E', 'F', 'C', 'B', 'A', 'D'],
                    ordered=True)
df.columns = pd.MultiIndex.from_arrays([v, v2]) 

df = df.sort_index(axis=1, level=[0, 1])

df
  One     Two          Three         
    E   F   C  B  A  D     C  B  A  D
1   9  10   7  6  5  8     3  2  1  4
2   9  10   7  6  5  8     3  2  1  4
3   9  10   7  6  5  8     3  2  1  4
4   9  10   7  6  5  8     3  2  1  4

edited Aug 30, 2018 at 20:50

answered Aug 30, 2018 at 20:09

cs95

406k106 gold badges745 silver badges798 bronze badges

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

4 Comments

Jelmerd Over a year ago

Thank you, but the top headers are now not in the right order (One, Three, Two), I presume it works on the bottom headers because in this example the required order is in alphabetical order, but I am looking for a solution where I can do this in any required order

cs95 Over a year ago

@Jelmerd It is ordered Lexicographically. I've edited my answer with what you want. You can do the same thing with the second level.

Jelmerd Over a year ago

Thank you, although it is still not quite clear how I would sort the bottom level, let's say for example that I would like the bottom level to be E F C B A D C B A D, can I pass a list with this order for the bottom level?

cs95 Over a year ago

@Jelmerd No problem, you can also upvote the answer if it was helpful. Thanks!

Collectives™ on Stack Overflow

Reorder Multiindex Pandas Dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related