Custom Sorting Columns In Multi Level Pandas Dataframe

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 pan

Solution 1:

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

Solution 2:

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