Pandas透视表用cufflinks绘图时KeyError:'Supply'问题求助

问题描述 #

我有一个名为df_thd_funct_mode1_PVT的Pandas透视表，结构如下示例所示。使用cufflinks绘图时触发KeyError:'Supply'错误，相关代码、错误栈及透视表示例如下：

代码 #

THD_PVT_2V5 = df_thd_funct_mode1_PVT[df_thd_funct_mode1_PVT['Supply'] == 2.5].pivot_table(index='Temp', columns='xvalues', values=['94','100','110','115','120','124','128','129','130'])
# THD_PVT_2V5['SPEC_MIN']= 37
# THD_PVT_2V5['SPEC_TYP']= 38.3
# THD_PVT_2V5['SPEC_MAX']= 39.6
THD_PVT_2V5.iplot(title='THD vs TEMPERATURE @ 2.5V FUNCTIONAL RANGE', xaxis_title='TEMPERATURE', yaxis_title='THD',width=3)

错误信息 #

KeyError                                  Traceback (most recent call last)
File C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexes\base.py:3621, in Index.get_loc(self, key, method, tolerance)
   3620 try:
-> 3621     return self._engine.get_loc(casted_key)
   3622 except KeyError as err:

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\_libs\index.pyx:136, in pandas._libs.index.IndexEngine.get_loc()

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\_libs\index.pyx:163, in pandas._libs.index.IndexEngine.get_loc()

File pandas\_libs\hashtable_class_helper.pxi:5198, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas\_libs\hashtable_class_helper.pxi:5206, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Supply'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Input In [249], in <cell line: 1>()
----> 1 THD_PVT_2V5 = df_thd_funct_mode1_PVT[df_thd_funct_mode1_PVT['Supply'] == 2.5].pivot_table(index='Temp', columns='xvalues', values=['94','100','110','115','120','124','128','129','130'])
      2 # THD_PVT_2V5['SPEC_MIN']= 37
      3 # THD_PVT_2V5['SPEC_TYP']= 38.3
      4 # THD_PVT_2V5['SPEC_MAX']= 39.6
      5 THD_PVT_2V5.iplot(title='THD vs TEMPERATURE @ 2.5V FUNCTIONAL RANGE', xaxis_title='TEMPERATURE', yaxis_title='THD',width=3)

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py:3505, in DataFrame.__getitem__(self, key)
   3503 if self.columns.nlevels > 1:
   3504     return self._getitem_multilevel(key)
-> 3505 indexer = self.columns.get_loc(key)
   3506 if is_integer(indexer):
   3507     indexer = [indexer]

File C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexes\base.py:3623, in Index.get_loc(self, key, method, tolerance)
   3621     return self._engine.get_loc(casted_key)
   3622 except KeyError as err:
-> 3623     raise KeyError(key) from err
   3624 except TypeError:
   3625     # If we have a listlike key, _check_indexing_error will raise
   3626     #  InvalidIndexError. Otherwise we fall through and re-raise
   3627     #  the TypeError.
   3628     self._check_indexing_error(key)

KeyError: 'Supply'

透视表示例 #

94      100     110     115     120     124     128     129     130
Temp    xvalues Supply                                  
-40     MAIN_001    2.5  0.1644  0.0844  0.032   0.1202  0.3129  0.2177  2.9942  6.9715  9.9139
        MAIN_001    2.7  0.1591  0.08545 0.03065 0.12305 0.32265 0.22645 2.57495 6.54425 9.49495
        MAIN_001    3.6  0.15775 0.083   0.02825 0.112   0.32735 0.2325  2.7538  6.72915 9.57755
-60     MAIN_005    2.5  0.1868  0.0972  0.0521  0.6005  0.8448  0.7479  5.157   45.9854 0.7479
        MAIN_005    2.7  0.2047  0.1068  0.0532  0.603   0.8502  0.7521  5.1481  39.4838 0.7521
        MAIN_005    3.6  0.1909  0.0992  0.0567  0.5917  0.8612  0.7626  5.1195  42.9942 0.7626

问题原因 #

从透视表示例可以看到，Supply是多层索引（MultiIndex）的第三级，并非DataFrame的普通列。代码中用df_thd_funct_mode1_PVT['Supply']访问它，会被Pandas识别为访问普通列，但普通列中不存在Supply这个字段，因此触发KeyError。

修复方案 #

针对多层索引的特性，有三种可行的修复方式：

方法1：使用xs方法快速切片（最简洁） #

xs方法专门用于多层索引的层级提取，直接获取Supply=2.5的数据：

# 提取Supply=2.5的层级数据
THD_PVT_2V5 = df_thd_funct_mode1_PVT.xs(2.5, level='Supply', axis=0)
# 直接绘图
THD_PVT_2V5.iplot(title='THD vs TEMPERATURE @ 2.5V FUNCTIONAL RANGE', xaxis_title='TEMPERATURE', yaxis_title='THD',width=3)

方法2：使用IndexSlice精准筛选 #

通过切片对象定位多层索引的指定值：

import pandas as pd

# 创建多层索引切片对象
idx = pd.IndexSlice
# 筛选Supply=2.5的所有行
filtered_df = df_thd_funct_mode1_PVT.loc[idx[:, :, 2.5], :]
# 按需重新透视后绘图
THD_PVT_2V5 = filtered_df.pivot_table(index='Temp', columns='xvalues', values=['94','100','110','115','120','124','128','129','130'])
THD_PVT_2V5.iplot(title='THD vs TEMPERATURE @ 2.5V FUNCTIONAL RANGE', xaxis_title='TEMPERATURE', yaxis_title='THD',width=3)

方法3：将多层索引转为普通列后筛选 #

把索引层级转为列，再用常规方式筛选：

# 将多层索引转换为普通列
df_reset = df_thd_funct_mode1_PVT.reset_index()
# 筛选Supply=2.5的数据
filtered_df = df_reset[df_reset['Supply'] == 2.5]
# 重新透视后绘图
THD_PVT_2V5 = filtered_df.pivot_table(index='Temp', columns='xvalues', values=['94','100','110','115','120','124','128','129','130'])
THD_PVT_2V5.iplot(title='THD vs TEMPERATURE @ 2.5V FUNCTIONAL RANGE', xaxis_title='TEMPERATURE', yaxis_title='THD',width=3)

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