Pandas删除DataFrame重复行时触发KeyError问题求助

背景 #

• 两个结构一致的DataFrame：uncatVulns（共982行）和vulnsWithNewSeverityOrFixable（共183行），数据均来自月度CSV报告
• 需求：删除uncatVulns中与vulnsWithNewSeverityOrFixable内容重复的行，已通过df.info()确认列结构完全一致

尝试过的代码 #

# 写法1：原地删除
uncatVulns.drop(vulnsWithNewSeverityOrFixable.index, inplace=True)

# 写法2：重新赋值
uncatVulns = uncatVulns.drop(vulnsWithNewSeverityOrFixable.index)

报错信息 #

2023-10-05 09:51:15,849:INFO: *** uncatVulns.info:
<class 'pandas.core.frame.DataFrame'>
Index: 982 entries, 0 to 1589
Data columns (total 12 columns):
 #   Column             Non-Null Count  Dtype 
---  ------             --------------  ----- 
 0   Cluster            982 non-null    object
 1   Namespace          982 non-null    object
 2   Deployment         982 non-null    object
 3   Image              982 non-null    object
 4   Component          982 non-null    object
 5   CVE                982 non-null    object
 6   Fixable            982 non-null    bool  
 7   Component Upgrade  368 non-null    object
 8   Severity           982 non-null    object
 9   Discovered At      982 non-null    object
 10  Reference          982 non-null    object
 11  Month              982 non-null    object
dtypes: bool(1), object(11)
memory usage: 93.0+ KB
2023-10-05 09:51:15,851:INFO: None
2023-10-05 09:51:15,851:INFO: 
2023-10-05 09:51:15,851:INFO: 
2023-10-05 09:51:15,851:INFO: *** vulnsWithNewSeverityOrFixable.info():
<class 'pandas.core.frame.DataFrame'>
Index: 183 entries, 908 to 1589
Data columns (total 12 columns):
 #   Column             Non-Null Count  Dtype 
---  ------             --------------  ----- 
 0   Cluster            183 non-null    object
 1   Namespace          183 non-null    object
 2   Deployment         183 non-null    object
 3   Image              183 non-null    object
 4   Component          183 non-null    object
 5   CVE                183 non-null    object
 6   Fixable            183 non-null    bool  
 7   Component Upgrade  77 non-null     object
 8   Severity           183 non-null    object
 9   Discovered At      183 non-null    object
 10  Reference          183 non-null    object
 11  Month              183 non-null    object
dtypes: bool(1), object(11)
memory usage: 17.3+ KB
2023-10-05 09:51:15,852:INFO: None
2023-10-05 09:51:15,852:INFO: 
2023-10-05 09:51:15,852:INFO: 
2023-10-05 09:51:15,852:ERROR:   *** len(uncatVulns) : 982
Traceback (most recent call last):
  File "/Users/robertharris/Security/POAnM/monthlyvulndelta/./POAM_Mixologist.py", line 466, in <module>
    main()
  File "/Users/robertharris/Security/POAnM/monthlyvulndelta/./POAM_Mixologist.py", line 320, in main
    uncatVulns = uncatVulns.drop(vulnsWithNewSeverityOrFixable.index)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/frame.py", line 5347, in drop
    return super().drop(
           ^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/generic.py", line 4711, in drop
    obj = obj._drop_axis(labels, axis, level=level, errors=errors)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/generic.py", line 4753, in _drop_axis
    new_axis = axis.drop(labels, errors=errors)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/indexes/base.py", line 6992, in drop
    raise KeyError(f"{labels[mask].tolist()} not found in axis")
KeyError: '[1045, 1054, 1056, 1060, 1069, 1071, 1075, 1084, 1086, 1090, 1099, 1101, 1105, 1114, 1116, 1120, 1129, 1131, 1135, 1139] not found in axis'

问题原因 #

你通过索引来删除行，但两个DataFrame的索引并不匹配：uncatVulns是经过筛选后的结果，索引范围是0到1589但实际只有982行，丢失了部分索引值；而vulnsWithNewSeverityOrFixable的索引包含uncatVulns中不存在的索引项，导致drop方法找不到对应索引，触发KeyError。

解决方案 #

不要依赖索引匹配，而是基于行内容来筛选删除重复行，以下是几种可行方法：

方法1：使用merge筛选差异行 #

通过merge的indicator参数标记行来源，保留仅存在于uncatVulns的行：

import pandas as pd

# 合并两个DataFrame并标记行来源
merged_df = uncatVulns.merge(vulnsWithNewSeverityOrFixable, how='left', indicator=True)
# 筛选仅在uncatVulns中存在的行，删除标记列
uncatVulns = merged_df[merged_df['_merge'] == 'left_only'].drop(columns=['_merge'])

方法2：基于唯一标识列筛选 #

如果存在唯一标识行的组合列（比如Cluster+Namespace+CVE），可以生成唯一ID后筛选：

# 为两个DataFrame生成唯一标识
uncatVulns['unique_key'] = uncatVulns['Cluster'] + '_' + uncatVulns['Namespace'] + '_' + uncatVulns['CVE']
target_keys = vulnsWithNewSeverityOrFixable['Cluster'] + '_' + vulnsWithNewSeverityOrFixable['Namespace'] + '_' + vulnsWithNewSeverityOrFixable['CVE']

# 保留不在目标集合中的行，删除临时列
uncatVulns = uncatVulns[~uncatVulns['unique_key'].isin(target_keys)].drop(columns=['unique_key'])

方法3：concat结合duplicated去重 #

如果两行内容完全一致，可合并后保留仅出现一次的行：

import pandas as pd

# 合并两个DataFrame
combined_df = pd.concat([uncatVulns, vulnsWithNewSeverityOrFixable])
# 保留只出现一次的行（即仅在uncatVulns中存在的行）
uncatVulns = combined_df[~combined_df.duplicated(keep=False)]

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