Custom sorting columns in multi level pandas dataframe

Question

Background

I have a big data frame with 2 levels columns, but 1 level rows, and I am trying to sort it as follows: level 0: alphabetically; level 1: custom sort.

Example

import pandas as pd
dictionary = {'A' : {'M': [1,2,3,4,5],
                     'L': [6,7,8,9,1],
                     'F': [3,5,1,3,5]  },
              'C' : {'M': [2,3,4,5,6],
                     'L': [7,8,9,1,2],
                     'F': [0,1,6,3,5]  },
              'B' : {'M': [1,5,2,5,3],
                     'L': [9,5,6,3,4],
                     'F': [6,2,7,1,5] }
         }
reform = {(outerKey, innerKey): values for outerKey, innerDict in dictionary.iteritems() for innerKey, values in innerDict.iteritems()}
pd.DataFrame(reform,index=['g','h','i','j','k'])

What I have then is

#        A          B           C
#        F  L   M   F   L   M   F   L   M
#    g  3   6   1   6   9   1   0   7   2
#    h  5   7   2   2   5   5   1   8   3
#    i  1   8   3   7   6   2   6   9   4
#    j  3   9   4   1   3   5   3   1   5
#    k  5   1   5   5   4   3   5   2   6

Question

How can I specify the order of columns to be A, B, C on level 0 and F, M, L on level 1?

### OUT
#        A          B           C
#        F  M   L   F   M   L   F   M   L

I was trying with pd.IndexSlice and .loc, but I still get only alphabetic order.

EdChum · Accepted Answer · 2017-03-28 15:37:19Z

16

You can achieve this using reindex_axis, this accepts a labels arg, axis and level:

In [20]:
df = df.reindex_axis(list('FML'), axis=1, level=1)
df

Out[20]:
   A        B        C      
   F  M  L  F  M  L  F  M  L
g  3  1  6  6  1  9  0  2  7
h  5  2  7  2  5  5  1  3  8
i  1  3  8  7  2  6  6  4  9
j  3  4  9  1  5  3  3  5  1
k  5  5  1  5  3  4  5  6  2

Thanks to @Nickli Maveli you can also use reindex to achieve the same:

In [22]:
df = df.reindex(columns=list('FML'), level=1)
df

Out[22]:
   A        B        C      
   F  M  L  F  M  L  F  M  L
g  3  1  6  6  1  9  0  2  7
h  5  2  7  2  5  5  1  3  8
i  1  3  8  7  2  6  6  4  9
j  3  4  9  1  5  3  3  5  1
k  5  5  1  5  3  4  5  6  2

edited Mar 28, 2017 at 15:37

answered Mar 28, 2017 at 15:18

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

10 Comments

EdChum Over a year ago

@jezrael What problem, the OP want's the column order to change, not just re-label them is my understanding

jezrael Over a year ago

Sorry, I was bad.

Nickil Maveli Over a year ago

df.reindex(columns=list("FML"), level=1) should be enough. But anyways, nice answer.

EdChum Over a year ago

@jezrael no worries, originally I thought this was just a simple .columns.set_levels problem but I looked again

jezrael Over a year ago

Yes, I see original. And it was bad I think

|

firelynx · Accepted Answer · 2017-03-29 09:26:40Z

3

Setting index on dataframe creation

If you do not want to change the dataframe afterwards, you can give the pd.DataFrame constructor an index where you define the order already.

Explicit solution

columns = pd.Index([('A', 'F'), ('A', 'M'), ('A', 'L'), ('B', 'F'), ('B', 'M'), ('B', 'L'),('C', 'F'), ('C', 'M'), ('C', 'L')])
pd.DataFrame(reform,index=['g','h','i','j','k'], columns=columns)

Composite solution

columns = pd.Index([(level_0, level_1) for level_0 in "ABC" for level_1 in "FML"])
pd.DataFrame(reform,index=['g','h','i','j','k'], columns=columns)

Both gives

   A        B        C      
   F  M  L  F  M  L  F  M  L
g  3  1  6  6  1  9  0  2  7
h  5  2  7  2  5  5  1  3  8
i  1  3  8  7  2  6  6  4  9
j  3  4  9  1  5  3  3  5  1
k  5  5  1  5  3  4  5  6  2

edited Mar 29, 2017 at 9:26

answered Mar 29, 2017 at 9:14

firelynx

32.5k10 gold badges94 silver badges104 bronze badges

Collectives™ on Stack Overflow

Custom sorting columns in multi level pandas dataframe

Background

Example

Question

2 Answers 2

10 Comments

Setting index on dataframe creation

Explicit solution

Composite solution

Both gives

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Background

Example

Question

2 Answers 2

10 Comments

Setting index on dataframe creation

Explicit solution

Composite solution

Both gives

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related