Python Pandas多级索引.loc查询无匹配键触发KeyError解决方法

在Python3环境中使用Pandas执行数据索引、切片操作计算空间统计指标时，针对设置了year、month、lat、lon四级索引的DataFrame，在遍历月份、纬度、经度范围的for循环中，通过.loc结合IndexSlice做索引查询时，若待查询的经纬度组合在输入文件中无对应记录，会抛出KeyError: (slice(None, None, None), )错误，代码直接终止，无法自动跳过无数据的坐标点。

原有代码 #

import numpy as np
import pandas as pd
from scipy import stats

filename='input.txt'
df = pd.read_csv(filename,delim_whitespace=True, header=None, names = ['year','month','lat','lon','aod'], index_col = ['year','month','lat','lon'])
idx=pd.IndexSlice

for i in range (1, 13):
    for lat0 in np.arange(0.,40.25,0.25,dtype=float):
        for lon0 in np.arange(20.0,75.25,0.25,dtype=float):
            tmp = df.loc[idx[:,i,lat0,lon0],:]
            if (len(tmp) <= 0):
                continue
            tmp2 = tmp.index.tolist()

注：原代码中N.arange为笔误，已修正为np.arange

复现现象 #

• 查询存在对应数据的索引组合tmp = df.loc[idx[:,1,0.0,34.0],:]时可正常返回结果，支持后续计算
• 查询无对应数据的索引组合tmp = df.loc[idx[:,1,0.0,32.75],:]时，抛出如下KeyError：

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3/dist-packages/pandas/core/indexing.py", line 925, in __getitem__
    return self._getitem_tuple(key)
  File "/usr/lib/python3/dist-packages/pandas/core/indexing.py", line 1100, in _getitem_tuple
    return self._getitem_lowerdim(tup)
  File "/usr/lib/python3/dist-packages/pandas/core/indexing.py", line 822, in _getitem_lowerdim
    return self._getitem_nested_tuple(tup)
  File "/usr/lib/python3/dist-packages/pandas/core/indexing.py", line 906, in _getitem_nested_tuple
    obj = getattr(obj, self.name)._getitem_axis(key, axis=axis)
  File "/usr/lib/python3/dist-packages/pandas/core/indexing.py", line 1157, in _getitem_axis
    locs = labels.get_locs(key)
  File "/usr/lib/python3/dist-packages/pandas/core/indexes/multi.py", line 3347, in get_locs
    indexer = _update_indexer(
  File "/usr/lib/python3/dist-packages/pandas/core/indexes/multi.py", line 3296, in _update_indexer
    raise KeyError(key)
KeyError: (slice(None, None, None), 1, 0.0, 32.75)

已尝试的无效方案：

• 将.loc替换为.iloc，触发too many indexers错误
• 使用.to_numpy()、.values、.as_matrix()等方法，均无法解决问题

首先执行df = df.sort_index()对多级索引排序，Pandas多级索引切片要求索引必须按字典序排序，否则即使索引值存在也可能抛出KeyError。之后可二选一实现跳过无数据点的逻辑：

方法1：try-except捕获异常 #

写法最简洁，适合无数据点占比不高的场景，异常捕获的开销可以忽略：

df = df.sort_index()
idx = pd.IndexSlice

for i in range(1, 13):
    for lat0 in np.arange(0.,40.25,0.25,dtype=float):
        for lon0 in np.arange(20.0,75.25,0.25,dtype=float):
            try:
                tmp = df.loc[idx[:,i,lat0,lon0],:]
            except KeyError:
                # 无对应数据直接跳过当前循环
                continue
            if len(tmp) == 0:
                continue
            tmp2 = tmp.index.tolist()
            # 后续空间统计计算逻辑写在这里

方法2：查询前预判索引是否存在 #

适合无数据点占比很高的场景，提前判断避免进入查询逻辑，减少不必要的开销：

df = df.sort_index()
idx = pd.IndexSlice
# 提前取出所有存在的year值，减少重复计算
all_years = df.index.levels[0]

for i in range(1, 13):
    for lat0 in np.arange(0.,40.25,0.25,dtype=float):
        for lon0 in np.arange(20.0,75.25,0.25,dtype=float):
            # 生成当前(month,lat,lon)对应的所有四级索引组合
            check_keys = [(y, i, lat0, lon0) for y in all_years]
            # 判断是否有任意一个组合存在于索引中
            if not df.index.isin(check_keys).any():
                continue
            tmp = df.loc[idx[:,i,lat0,lon0],:]
            tmp2 = tmp.index.tolist()
            # 后续空间统计计算逻辑写在这里

内容的提问来源于stack exchange，提问作者Piyushkumar Patel