python - "Too many indexers" with DataFrame.loc

Question

I've read the docs about slicers a million times, but have never got my head round it, so I'm still trying to figure out how to use loc to slice a DataFrame with a MultiIndex.

I'll start with the DataFrame from this SO answer:

                           value
first second third fourth       
A0    B0     C1    D0          2
                   D1          3
             C2    D0          6
                   D1          7
      B1     C1    D0         10
                   D1         11
             C2    D0         14
                   D1         15
A1    B0     C1    D0         18
                   D1         19
             C2    D0         22
                   D1         23
      B1     C1    D0         26
                   D1         27
             C2    D0         30
                   D1         31
A2    B0     C1    D0         34
                   D1         35
             C2    D0         38
                   D1         39
      B1     C1    D0         42
                   D1         43
             C2    D0         46
                   D1         47
A3    B0     C1    D0         50
                   D1         51
             C2    D0         54
                   D1         55
      B1     C1    D0         58
                   D1         59
             C2    D0         62
                   D1         63

To select just A0 and C1 values, I can do:

In [26]: df.loc['A0', :, 'C1', :]
Out[26]: 
                           value
first second third fourth       
A0    B0     C1    D0          2
                   D1          3
      B1     C1    D0         10
                   D1         11

Which also works selecting from three levels, and even with tuples:

In [28]: df.loc['A0', :, ('C1', 'C2'), 'D1']
Out[28]: 
                           value
first second third fourth       
A0    B0     C1    D1          3
             C2    D1          5
      B1     C1    D1         11
             C2    D1         13

So far, intuitive and brilliant.

So why can't I select all values from the first index level?

In [30]: df.loc[:, :, 'C1', :]
---------------------------------------------------------------------------
IndexingError                             Traceback (most recent call last)
<ipython-input-30-57b56108d941> in <module>()
----> 1 df.loc[:, :, 'C1', :]

/usr/local/lib/python2.7/dist-packages/pandas/core/indexing.pyc in __getitem__(self, key)
   1176     def __getitem__(self, key):
   1177         if type(key) is tuple:
-> 1178             return self._getitem_tuple(key)
   1179         else:
   1180             return self._getitem_axis(key, axis=0)

/usr/local/lib/python2.7/dist-packages/pandas/core/indexing.pyc in _getitem_tuple(self, tup)
    694 
    695         # no multi-index, so validate all of the indexers
--> 696         self._has_valid_tuple(tup)
    697 
    698         # ugly hack for GH #836

/usr/local/lib/python2.7/dist-packages/pandas/core/indexing.pyc in _has_valid_tuple(self, key)
    125         for i, k in enumerate(key):
    126             if i >= self.obj.ndim:
--> 127                 raise IndexingError('Too many indexers')
    128             if not self._has_valid_type(k, i):
    129                 raise ValueError("Location based indexing can only have [%s] "

IndexingError: Too many indexers

Surely this is not intended behaviour?

Note: I know this is possible with df.xs('C1', level='third') but the current .loc behaviour seems inconsistent.

Answer 1

The reason this doesn't work is tied to the need to specify the axis of indexing (mentioned in http://pandas.pydata.org/pandas-docs/stable/advanced.html). An alternative solution to your problem is to simply do this:

df.loc(axis=0)[:, :, 'C1', :]

Pandas gets confused sometimes when indexes are similar or contain similar values. If you were to have a column named 'C1' or something you would also need to do this under this style of slicing/selecting.