Pandas实现不同DataFrame列值匹配，返回多结果至新列

需求说明 #

对比两个DataFrame（Marks和claimed）的Car Brand列，为Marks添加新列Possible Owners，显示对应车型的所有匹配车主（类似Excel的XLOOKUP但支持返回多结果）：

• Marks存储登记在Mark名下的车辆（实际不属于Mark）
• claimed存储其他车主及其车辆信息

示例数据 #

DataFrame 1: Marks #

import pandas as pd

Marks = pd.DataFrame({
    'Car Brand': ['Jeep','Jeep','BMW','Volvo'],
    'Owner Name': ['Mark', 'Mark', 'Mark', 'Mark']
})

输出结构：

Car Brand Owner Name
0      Jeep        Mark
1      Jeep        Mark
2       BMW        Mark
3     Volvo        Mark

DataFrame 2: claimed #

claimed = pd.DataFrame({
    'Car Brand': ['Dodge', 'Jeep', 'BMW', 'Merc', 'Volvo', 'Jeep', 'Volvo'],
    'Owner Name': ['Chris', 'Frank','Rob','Kelly','John','Chris','Kelly']
})

输出结构：

Car Brand Owner Name
0     Dodge       Chris
1      Jeep       Frank
2       BMW         Rob
3      Merc       Kelly
4     Volvo        John
5      Jeep       Chris
6     Volvo       Kelly

期望输出 #

Car Brand Owner Name Possible Owners
0      Jeep        Mark    [Frank, Chris]
1      Jeep        Mark    [Frank, Chris]
2       BMW        Mark              Rob
3     Volvo        Mark     [John, Kelly]

用户尝试的错误代码及问题 #

用户用嵌套循环实现时触发ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().错误：

possible_owners = list()
for cars in Marks['Car Brand']:
  for car_brands in claimed['Car Brand']:
     if Marks.loc[Marks['Car Brand'].isin(claimed['Car Brand'])]:
        sub = list()
        sub.append()
        possible_owners.append(sub)
    else:
        not_found = 'No possible Owners Identified'
        possible_owners.append(not_found)
# 后续打算将possible_owners作为新列加入Marks

解决方案 #

用分组聚合+映射的方式高效实现，避免低效嵌套循环：

步骤1：生成品牌-车主映射字典 #

先对claimed按Car Brand分组，把每个品牌对应的车主聚合成列表，转成字典方便快速匹配：

owner_map = claimed.groupby('Car Brand')['Owner Name'].apply(list).to_dict()

生成的字典示例：{'Jeep': ['Frank', 'Chris'], 'BMW': ['Rob'], 'Volvo': ['John', 'Kelly'], ...}

步骤2：为Marks添加匹配列 #

定义函数处理单个/多个车主的显示逻辑，再用apply映射到Marks的每一行：

def get_possible_owners(brand):
    # 找不到对应品牌时返回提示文本
    owners = owner_map.get(brand, ['No possible Owners Identified'])
    # 单个车主直接返回字符串，多个返回列表
    return owners[0] if len(owners) == 1 else owners

Marks['Possible Owners'] = Marks['Car Brand'].apply(get_possible_owners)

完整代码 #

import pandas as pd

# 初始化数据
Marks = pd.DataFrame({
    'Car Brand': ['Jeep','Jeep','BMW','Volvo'],
    'Owner Name': ['Mark', 'Mark', 'Mark', 'Mark']
})

claimed = pd.DataFrame({
    'Car Brand': ['Dodge', 'Jeep', 'BMW', 'Merc', 'Volvo', 'Jeep', 'Volvo'],
    'Owner Name': ['Chris', 'Frank','Rob','Kelly','John','Chris','Kelly']
})

# 生成品牌-车主映射字典
owner_map = claimed.groupby('Car Brand')['Owner Name'].apply(list).to_dict()

# 定义匹配函数
def get_possible_owners(brand):
    owners = owner_map.get(brand, ['No possible Owners Identified'])
    return owners[0] if len(owners) == 1 else owners

# 添加新列
Marks['Possible Owners'] = Marks['Car Brand'].apply(get_possible_owners)

# 打印结果
print(Marks)

错误原因说明 #

之前的代码问题：

• Marks.loc[Marks['Car Brand'].isin(claimed['Car Brand'])]返回的是DataFrame，不能直接放在if条件中（DataFrame的布尔值无法被Python直接判断真假）
• 嵌套循环逻辑冗余，没有针对当前遍历的车型匹配对应车主，效率极低

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