将字典映射至含带引号多元素列的Pandas DataFrame

需要将给定的院校-ID字典映射到大型Pandas DataFrame，核心问题在于college_name列被双引号包裹，且每个单元格可能包含单个或多个院校名称。以下是具体解决方案：

原始数据 #

院校-ID字典 #

dict_id = {
   'College1': ['1256511'],
   'College2': ['1200582'],
   'College3': ['1256618'], 
   'College10': ['1256621']
}

原始DataFrame #

id1  id2            college_name
0   01   01    "College1, College2"
1   01   02  "College10, College12"
2   01   03             "College19"

解决方案 #

方法1：自定义函数+Apply（适合中小数据集） #

逻辑直观，适合数据量不大的场景：

import pandas as pd

# 初始化数据
dict_id = {
   'College1': ['1256511'],
   'College2': ['1200582'],
   'College3': ['1256618'], 
   'College10': ['1256621']
}

df = pd.DataFrame({
    'id1': ['01', '01', '01'],
    'id2': ['01', '02', '03'],
    'college_name': ['"College1, College2"', '"College10, College12"', '"College19"']
})

# 1. 清理列：去除双引号，分割成院校列表
df['colleges_list'] = df['college_name'].str.strip('"').str.split(', ')

# 2. 定义映射函数：过滤字典内的院校，提取对应ID并拼接
def get_college_ids(colleges):
    matched_ids = [dict_id[college][0] for college in colleges if college in dict_id]
    return ', '.join(matched_ids) if matched_ids else ''

# 3. 生成目标列
df['id_college'] = df['colleges_list'].apply(get_college_ids)

# 4. 移除临时列
df = df.drop('colleges_list', axis=1)

print(df)

方法2：Explode+Merge（适合大型数据集） #

针对超大型DataFrame，避免apply的性能瓶颈，用向量化方法处理：

import pandas as pd

# 初始化数据
dict_id = {
   'College1': ['1256511'],
   'College2': ['1200582'],
   'College3': ['1256618'], 
   'College10': ['1256621']
}

df = pd.DataFrame({
    'id1': ['01', '01', '01'],
    'id2': ['01', '02', '03'],
    'college_name': ['"College1, College2"', '"College10, College12"', '"College19"']
})

# 1. 拆分多院校行为单行单院校
df_exploded = df.assign(college=df['college_name'].str.strip('"').str.split(', ')).explode('college')

# 2. 字典转Series用于匹配
id_mapper = pd.Series({k: v[0] for k, v in dict_id.items()}, name='id_college')

# 3. 关联ID值
df_exploded = df_exploded.merge(id_mapper, left_on='college', right_index=True, how='left')

# 4. 分组拼接ID，空值转为空字符串
df_result = df_exploded.groupby(['id1', 'id2', 'college_name'])['id_college'].agg(', '.join).reset_index()
df_result['id_college'] = df_result['id_college'].fillna('')

print(df_result)

输出结果 #

两种方法均会生成符合预期的目标DataFrame：

id1 id2            college_name         id_college
0  01  01    "College1, College2"  1256511, 1200582
1  01  02  "College10, College12"           1256621
2  01  03             "College19"

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