pyeR_biz
pyeR_biz

Reputation: 1044

KeyError: Key length exceeds index depth - pandas MultiIndex

I parsed large sets of xml files to get a pandas dataframe. I need to now remove some columns for data analysis. I am unable to find the exact error in previous questions. I used -

data = data["Rig Mode","Bit on Bottom","Block Position","Block Velocity",..]

And got an error message (complete error message is at the end of post) -

KeyError: 'Key length (22) exceeds index depth (2)'

So I researched and went to this post , this mentions lexsort depth related error, while mine is precisely as posted above. I sorted Index according to above post -

`data = data.sort_index(level=1)`
 pd.__version__
 '0.22.0' 
 Python version - 3.6.4

And got the exact same error. Below I fetch my multiindex details -

data.columns

#MultiIndex(levels=[['Bit on Bottom','Block Position', 'Block Velocity',  'Rig Mode',...], ['', '1/min', 'L/min', 'dega', ...]],
           labels=[[38, 0, 2, 22, ...]],
           names=['Description', 'Unit'])

This is how I built my multiindex while preparing the dataframe, the now column headers were parsed as rows into the dataset -

data.columns = pd.MultiIndex.from_arrays([data.iloc[0],data.iloc[1]], names = ['Description','Unit'])
data=data.iloc[2:]

#### complete error message: 

>     --------------------------------------------------------------------------- KeyError                                  Traceback (most recent call
> last) <ipython-input-119-60ad57c2383f> in <module>()
>       3                               "Continuous Survey Depth","Pump 1 Stroke Rate","Pump 2 Stroke Rate","Pump 3 Stroke Rate",
>       4                               "Average Standpipe Pressure","Slips stat (1=Out,0=In)", "Weight on Bit","Mud Flow
> In","Time","Average Surface Torque",
> ----> 5                               "MWD Turbine RPM"]
> 
> ~\Anaconda3\lib\site-packages\pandas\core\frame.py in
> __getitem__(self, key)    2135             return self._getitem_frame(key)    2136         elif is_mi_columns:
> -> 2137             return self._getitem_multilevel(key)    2138         else:    2139             return self._getitem_column(key)
> 
> ~\Anaconda3\lib\site-packages\pandas\core\frame.py in
> _getitem_multilevel(self, key)    2179     2180     def _getitem_multilevel(self, key):
> -> 2181         loc = self.columns.get_loc(key)    2182         if isinstance(loc, (slice, Series, np.ndarray, Index)):    2183          
> new_columns = self.columns[loc]
> 
> ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in
> get_loc(self, key, method)    2076         if self.nlevels < keylen:  
> 2077             raise KeyError('Key length ({0}) exceeds index depth
> ({1})'
> -> 2078                            ''.format(keylen, self.nlevels))    2079     2080         if keylen == self.nlevels and self.is_unique:
> 
> KeyError: 'Key length (22) exceeds index depth (2)'

Upvotes: 3

Views: 4862

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375635

To select a subset of columns you must use [[ ]]:

data = data[["Rig Mode","Bit on Bottom","Block Position","Block Velocity",..]]

The __getindex__ is overloaded quite a bit.


In [11]: df = pd.DataFrame([[1, 2], [3, 4], [5, 6]], columns=["A", "B"])

In [12]: df
Out[12]:
   A  B
0  1  2
1  3  4
2  5  6

In [13]: df["A"]
Out[13]:
0    1
1    3
2    5
Name: A, dtype: int64

In [14]: df["A", "B"]
KeyError: ('A', 'B')

with the MultiIndex it's trying to select the column:

In [21]: df = pd.DataFrame([[1, 2], [3, 4], [5, 6]], columns=[["A", "AA"], ["B", "BB"]])

In [22]: df
Out[22]:
   A AA
   B BB
0  1  2
1  3  4
2  5  6

In [23]: df["A"]
Out[23]:
   B
0  1
1  3
2  5

In [24]: df["A", "B"]
Out[24]:
0    1
1    3
2    5
Name: (A, B), dtype: int64

Upvotes: 4

Related Questions