基于姓名映射字典优化Pandas DataFrame项目团队统计列的实现方案

原代码通过嵌套循环处理每个项目的成员列表，虽能得到预期结果，但在数据量较大时效率偏低。以下两种方案利用Pandas的矢量化操作和内置方法，既能简化代码，又能提升执行效率：

方案一：apply结合集合推导式（简洁直观，适合中小数据集） #

直接对Members列使用apply，借助集合自动去重的特性，快速生成唯一团队集合，再衍生所需统计列：

import pandas as pd
from io import StringIO

dict_name = {
"William":  "A",
"James":    "C",
"Ava":  "A",
"Elijah":   "A",
"Mason":    "B",
"Ethan":    "B",
"Noah": "B",
"Benjamin": "B",
"Lucas":    "B",
"Oliver":   "B",
"Olivia":   "C",
"Emma": "C"}

csvfile = StringIO(
"""
Project ID  Members
A58 Noah, Oliver
A34 William, Elijah, James, Benjamin
A157    Lucas, Mason, Ethan, Olivia
A49 Emma, Ava""")

df = pd.read_csv(csvfile, sep = '\t', engine='python')

# 生成每个项目对应的唯一团队集合
df['team_set'] = df['Members'].apply(lambda x: {dict_name[name] for name in x.split(', ')})
# 衍生团队数量列
df['How many teams?'] = df['team_set'].str.len()
# 将集合转为列表，生成涉及团队列
df['Which teams?'] = df['team_set'].apply(list)
# 移除中间辅助列（可选）
df = df.drop('team_set', axis=1)

print(df)

方案二：explode+groupby聚合（性能优先，适合大数据集） #

通过explode拆分成员列表为多行，再用groupby做聚合统计，完全规避Python层面的循环，数据量越大性能优势越明显：

import pandas as pd
from io import StringIO

dict_name = {
"William":  "A",
"James":    "C",
"Ava":  "A",
"Elijah":   "A",
"Mason":    "B",
"Ethan":    "B",
"Noah": "B",
"Benjamin": "B",
"Lucas":    "B",
"Oliver":   "B",
"Olivia":   "C",
"Emma": "C"}

csvfile = StringIO(
"""
Project ID  Members
A58 Noah, Oliver
A34 William, Elijah, James, Benjamin
A157    Lucas, Mason, Ethan, Olivia
A49 Emma, Ava""")

df = pd.read_csv(csvfile, sep = '\t', engine='python')

# 拆分成员列表为单行单成员的结构
df_expanded = df.assign(Members=df['Members'].str.split(', ')).explode('Members')
# 映射成员对应的团队
df_expanded['Team'] = df_expanded['Members'].map(dict_name)
# 按项目ID聚合，统计唯一团队及数量
df_agg = df_expanded.groupby('Project ID')['Team'].agg(
    Which_teams=lambda x: list(set(x)),
    How_many_teams='nunique'
).reset_index()

# 合并回原DataFrame，保持原有结构
df_final = df.merge(df_agg, on='Project ID')
# 调整列名与顺序（可选）
df_final = df_final.rename(columns={'How_many_teams': 'How many teams?', 'Which_teams': 'Which teams?'})
df_final = df_final[['Project ID', 'Members', 'How many teams?', 'Which teams?']]

print(df_final)

方案对比 #

• 方案一代码最紧凑，可读性高，适合中小规模数据场景；
• 方案二利用Pandas内置聚合逻辑，完全矢量化处理，大数据量下性能远超循环实现。

两种方案均能输出与原代码一致的结果，且代码更简洁、执行效率更高。

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