How to preserve the column ordering when accessing a multi-index dataframe using `.loc`?

user_3388962 0
Viewed 697

Let's be given the following dataframe with multi-index columns

import numpy as np
import pandas as pd 

a = ['i', 'ii']
b = list('abc')
mi = pd.MultiIndex.from_product([a,b])
df = pd.DataFrame(np.arange(100,100+len(mi)*3).reshape([-1,len(mi)]), 
                  columns=mi)
print(df)
#     i             ii
#     a    b    c    a    b    c
# 0  100  101  102  103  104  105
# 1  106  107  108  109  110  111
# 2  112  113  114  115  116  117

Using .loc[] and pd.IndexSlice I try to select the columns 'c' and 'b', in that very ordering.

idx = pd.IndexSlice
df.loc[:, idx[:, ['c','b']]]

However, if I look at the output, the requested ordering is not respected!

#     i        ii
#     b    c    b    c
# 0  101  102  104  105
# 1  107  108  110  111
# 2  113  114  116  117

Here are my questions:

  1. Why is the ordering not preserved by pandas? I consider this pretty dangerous, because the list ['c', 'b'] implies an ordering from a user point of view.
  2. How to access the columns via loc[] while preserving the ordering at the same time?

Update: (02.02.2020)

The issue has been identified as pandas bug. In the process of fixing it, this related issue has been identified, which addresses a semantic ambiguity for expressions like df.loc[:, pd.IndexSlice[:, ['c','b']]].

In the meantime, the problem can be circumvented using the approach described in the accepted answer.

multi-index pandas python
1 Answers

Quoting from this link:

I don't think we make guarantees about the order of returned values from a .loc operation so I am inclined to say this is not a bug but let's see what others say

So we should be using reindex instead:

df.reindex(columns=pd.MultiIndex.from_product([a,['c','b']]))
     i        ii     
     c    b    c    b
0  102  101  105  104
1  108  107  111  110
2  114  113  117  116
user_79645270
Related