Custom column sorts in pandas layered file system

Question

Background

I have a large data frame with 2 column levels, but 1 row level, and I'm trying to sort it as follows: level 0: in alphabetical order; Level 1: custom view.

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']) 

Then i have

 # ABC # FLMFLMFLM # 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 column order as A, B, C at level 0 and F, M, L at level 1?

 ### OUT # ABC # FMLFMLFML 

I tried with pd.IndexSlice and .loc , but I still get only letter order.

Answer 1

You can achieve this using reindex_axis , it takes arg labels, axis and level:

 In [20]: df = df.reindex_axis(list('FML'), axis=1, level=1) df Out[20]: ABCFMLFMLFML 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]: ABCFMLFMLFML 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 

Answer 2

Setting an index when creating frames

If you later do not want to change the data framework, you can provide the pd.DataFrame constructor pd.DataFrame index in which you have already defined the order.

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 give

  ABCFMLFMLFML 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