Excel数据处理异常：两子集合长度之和大于原集合长度

我有一个包含email_address、first_name、last_name、took_action列的Excel文件，前3列对应个人信息，第4列为已行动人员姓名列表。想要提取未行动人员姓名，编写了如下Python代码：

import openpyxl

csv_file = r'.\list.xlsx'
dataframe = openpyxl.load_workbook(csv_file)
dataframe_reader = dataframe.active

emails = []
first_names = []
last_names = []
took_action = []
neutral = []

for row in range(2, dataframe_reader.max_row+1):
  emails.append(dataframe_reader.cell(row = row, column = 1).value)
  first_names.append(dataframe_reader.cell(row = row, column = 2).value)
  last_names.append(dataframe_reader.cell(row = row, column = 3).value)
  if dataframe_reader.cell(row = row, column = 4).value != None:
    took_action.append(dataframe_reader.cell(row = row, column = 4).value)

first_names_set = set(first_names)
took_action_set = set(took_action)

for name in first_names_set:
  if name not in took_action_set:
    neutral.append(name)

print(f'neutral + took action = {len(neutral) + len(took_action_set)}')
print(f'first_names_set len = {len(first_names_set)}')

预期输出 #

neutral + took action = 100
first_names_set len = 100

实际输出 #

neutral + took action = 101
first_names_set len = 100

请问为何两个子集合的长度之和会大于原集合的长度？

原因分析 #

核心问题是**took_action_set中存在不属于first_names_set的姓名**。

你的代码逻辑默认took_action列的所有姓名都来自first_names列，但实际情况是：

• took_action列表提取的是Excel第4列的非空值，这些值可能包含不在first_names列里的名字
• 当你将took_action转为集合后，这部分额外的姓名会被保留
• 计算len(neutral) + len(took_action_set)时，相当于(原集合 - 两者交集) + (交集 + 额外元素)，最终结果等于原集合长度 + 额外元素数量，所以会比原集合长1（这里额外元素数量为1）

举个简单例子：

• first_names_set = {"张三", "李四", "王五"}（长度3）
• took_action_set = {"张三", "赵六"}（"赵六"不在原集合）
• neutral = {"李四", "王五"}（长度2）
• 此时2+2=4 > 3，和你的场景完全匹配

解决方法 #

构建took_action_set时，过滤掉不在first_names_set中的元素：

took_action_set = set(name for name in took_action if name in first_names_set)

或者在提取took_action列表时就做过滤，避免引入无关数据。

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