如何匹配DataFrame索引与另一DataFrame列并替换为邻列值（解决KeyError）

问题背景 #

需求：当sym DataFrame的Composite_Element_REF列值与df_normal_symbol的索引匹配时，将df_normal_symbol的索引替换为sym中对应的Gene_Symbol列值。

尝试执行以下代码时触发KeyError：

df_normal_symbol.index = df_normal.loc[sym["Composite_Element_REF"], df_normal.index].values

报错回溯信息：

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Input In [38], in <cell line: 1>()
----> 1 df_normal_symbol.index = df_normal.loc[sym["Composite_Element_REF"], df_normal.index].values

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexing.py:961, in _LocationIndexer.__getitem__(self, key)
    959     if self._is_scalar_access(key):
    960         return self.obj._get_value(*key, takeable=self._takeable)
--> 961     return self._getitem_tuple(key)
    962 else:
    963     # we by definition only have the 0th axis
    964     axis = self.axis or 0

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexing.py:1147, in _LocIndexer._getitem_tuple(self, tup)
   1145 # ugly hack for GH #836
   1146 if self._multi_take_opportunity(tup):
--> 1147     return self._multi_take(tup)
   1149 return self._getitem_tuple_same_dim(tup)

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexing.py:1098, in _LocIndexer._multi_take(self, tup)
   1082 """
   1083 Create the indexers for the passed tuple of keys, and
   1084 executes the take operation. This allows the take operation to be
   (...)
   1095 values: same type as the object being indexed
   1096 """
   1097 # GH 836
--> 1098 d = {
   1099     axis: self._get_listlike_indexer(key, axis)
   1100     for (key, axis) in zip(tup, self.obj._AXIS_ORDERS)
   1101 }
   1102 return self.obj._reindex_with_indexers(d, copy=True, allow_dups=True)

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexing.py:1099, in <dictcomp>(.0)
   1082 """
   1083 Create the indexers for the passed tuple of keys, and
   1084 executes the take operation. This allows the take operation to be
   (...)
   1095 values: same type as the object being indexed
   1096 """
   1097 # GH 836
   1098 d = {
--> 1099     axis: self._get_listlike_indexer(key, axis)
   1100     for (key, axis) in zip(tup, self.obj._AXIS_ORDERS)
   1101 }
   1102 return self.obj._reindex_with_indexers(d, copy=True, allow_dups=True)

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexing.py:1327, in _LocIndexer._get_listlike_indexer(self, key, axis)
   1324 ax = self.obj._get_axis(axis)
   1325 axis_name = self.obj._get_axis_name(axis)
--> 1327 keyarr, indexer = ax._get_indexer_strict(key, axis_name)
   1329 return keyarr, indexer

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexes/base.py:5782, in Index._get_indexer_strict(self, key, axis_name)
   5779 else:
   5780     keyarr, indexer, new_indexer = self._reindex_non_unique(keyarr)
--> 5782 self._raise_if_missing(keyarr, indexer, axis_name)
   5784 keyarr = self.take(indexer)
   5785 if isinstance(key, Index):
   5786     # GH 42790 - Preserve name from an Index

File /scg/apps/software/jupyter/python_3.9/lib/python3.9/site-packages/pandas/core/indexes/base.py:5845, in Index._raise_if_missing(self, key, indexer, axis_name)
   5842     raise KeyError(f"None of [{key}] are in the [{axis_name}]")
   5844 not_found = list(ensure_index(key)[missing_mask.nonzero()[0]].unique())
--> 5845 raise KeyError(f"{not_found} not in index")

问题分析 #

原代码存在三个核心问题：

1. 错误地操作无关的df_normal，而非直接基于sym的映射关系修改目标df_normal_symbol的索引
2. sym["Composite_Element_REF"]中存在部分值不在df_normal的索引中，触发KeyError
3. 逻辑混淆了目标DataFrame和无关数据源

解决方案 #

直接基于sym构建索引映射关系，替换df_normal_symbol的索引，无需涉及df_normal。

方法1：使用map方法（推荐） #

构建映射字典后，用map替换索引，不匹配的索引将保留原值：

# 构建Composite_Element_REF到Gene_Symbol的映射字典
symbol_map = sym.set_index("Composite_Element_REF")["Gene_Symbol"].to_dict()

# 替换df_normal_symbol的索引
df_normal_symbol.index = df_normal_symbol.index.map(lambda x: symbol_map.get(x, x))

方法2：使用replace方法 #

如果不需要保留不匹配的索引（或希望将其设为NaN），可以用replace：

symbol_map = sym.set_index("Composite_Element_REF")["Gene_Symbol"].to_dict()
df_normal_symbol.index = df_normal_symbol.index.replace(symbol_map)

方法3：合并后重置索引 #

通过合并操作确保仅替换匹配项，适合需要严格校验的场景：

# 将df_normal_symbol转为带索引列的DataFrame
temp_df = df_normal_symbol.reset_index().rename(columns={"index": "Composite_Element_REF"})
# 与sym合并获取对应Gene_Symbol
temp_df = temp_df.merge(sym[["Composite_Element_REF", "Gene_Symbol"]], on="Composite_Element_REF", how="left")
# 无匹配项时保留原索引值
temp_df["Gene_Symbol"] = temp_df["Gene_Symbol"].fillna(temp_df["Composite_Element_REF"])
# 重置索引并清理冗余列
df_normal_symbol = temp_df.set_index("Gene_Symbol").drop(columns="Composite_Element_REF")

以上方法均避免了原代码的错误逻辑，同时处理了可能存在的不匹配值，不会触发KeyError。

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