如何基于列中子串筛选pandas groupby结果？员工入职培训判定场景

问题背景 #

现有员工已完成培训记录表，包含EmployeeID和CompletedCourseName两列，数据如下：

判定员工完成入职培训的条件：

• 必须完成onboarding part 1（无需完成part 2）
• 至少完成一门包含corporate compliance的培训

原实现使用groupby结合lambda，代码如下：

import pandas as pd

data = [
    {"EmployeeID": 1, "CompletedCourseName ": "onboarding part 1"},
    {"EmployeeID": 1, "CompletedCourseName ": "onboarding part 2"},
    {"EmployeeID": 1, "CompletedCourseName ": "corporate compliance training A"},
    {"EmployeeID": 2, "CompletedCourseName ": "corporate compliance training B"},
    {"EmployeeID": 2, "CompletedCourseName ": "Random other Training"}
]

# Create a DataFrame from the list
df = pd.DataFrame(data)

# return True if True is anywhere in the returned series of Trues and Falses that was return after checking if the required substring exists.
df.groupby(['EmployeeID'])['CompletedCourseName '].apply(lambda column: 
( True in list(column.str.lower().str.contains("onboarding part 1")) ) 
& 
( True in list(column.str.lower().str.contains("corporate compliance"))  )  )

优化实现方案 #

方案1：用groupby.agg结合any()简化判断 #

利用Pandas原生的any()方法直接判断分组内是否存在符合条件的记录，避免手动转换列表的冗余操作：

import pandas as pd

df = pd.DataFrame(data)
# 统一转为小写，消除大小写差异影响
df['lower_course'] = df['CompletedCourseName '].str.lower()

# 分组聚合两个条件的结果，再合并判断
result = df.groupby('EmployeeID').agg(
    has_onboarding=('lower_course', lambda x: x.str.contains('onboarding part 1').any()),
    has_compliance=('lower_course', lambda x: x.str.contains('corporate compliance').any())
).assign(completed=lambda x: x['has_onboarding'] & x['has_compliance'])['completed']

方案2：标记课程类型后用max()聚合 #

先给每条记录标记是否满足单个条件，再通过max()聚合（只要分组内有一条满足则结果为True），最后判断逻辑与：

import pandas as pd

df = pd.DataFrame(data)
df['lower_course'] = df['CompletedCourseName '].str.lower()

# 标记单条记录是否符合两个条件
df['is_onboarding'] = df['lower_course'].str.contains('onboarding part 1')
df['is_compliance'] = df['lower_course'].str.contains('corporate compliance')

# 分组取最大值（等价于判断是否存在满足条件的记录），再判断同时满足两个条件
result = df.groupby('EmployeeID')[['is_onboarding', 'is_compliance']].max().all(axis=1)

方案3：用transform保留原表结构并标记结果 #

如果需要在原DataFrame中为每条记录标记所属员工是否完成入职培训，可使用transform：

import pandas as pd

df = pd.DataFrame(data)
df['lower_course'] = df['CompletedCourseName '].str.lower()

# 为每个员工生成全局的条件满足标记
df['has_onboarding'] = df.groupby('EmployeeID')['lower_course'].transform(lambda x: x.str.contains('onboarding part 1').any())
df['has_compliance'] = df.groupby('EmployeeID')['lower_course'].transform(lambda x: x.str.contains('corporate compliance').any())
df['completed_onboarding'] = df['has_onboarding'] & df['has_compliance']

# 若仅需员工ID与结果的映射，去重提取即可
result = df[['EmployeeID', 'completed_onboarding']].drop_duplicates().set_index('EmployeeID')['completed_onboarding']

以上方案均贴合Pandas的API设计逻辑，代码可读性和执行效率优于原实现，避免了手动操作列表的冗余步骤。

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