如何在Pandas中使用apply方法从另一DataFrame匹配获取列？

首先，先构造你提供的两个DataFrame：

import pandas as pd

df_a = pd.DataFrame({
    'request_id': [1, 2],
    'type': ['OCR', 'Match'],
    'request_date': ['2023-06-01', '2023-01-01'],
    'entity_id': [20220401003, 20220401004]
})

df_b = pd.DataFrame({
    'entity_id': [20220401001, 20220401002, 20220401003, 20220401004, 20220401005],
    'status': ['rejected', 'cancel', 'approved', 'approved', 'pending'],
    'notes': ['rejected', 'cancel', 'approved', 'approved', 'waiting']
})

用apply方法实现匹配 #

定义一个函数，根据entity_id从DataFrame B中获取对应的status和notes，再通过apply将结果映射到DataFrame A中：

def get_entity_details(entity_id):
    match_row = df_b[df_b['entity_id'] == entity_id]
    if not match_row.empty:
        return match_row['status'].iloc[0], match_row['notes'].iloc[0]
    return None, None

# 对df_a的entity_id列应用函数，拆分结果为新列
df_a[['status', 'notes']] = df_a['entity_id'].apply(lambda x: pd.Series(get_entity_details(x)))

执行后，df_a的结果如下：

request_id   type request_date   entity_id    status     notes
0           1    OCR   2023-06-01  20220401003  approved  approved
1           2  Match   2023-01-01  20220401004  approved  approved

补充：更高效的替代方案 #

如果不强制要求使用apply，Pandas的merge方法在处理大规模数据时效率更高，代码也更简洁：

df_a = df_a.merge(df_b[['entity_id', 'status', 'notes']], on='entity_id', how='left')

内容的提问来源于stack exchange，提问作者Muhamad Ridwan Nurhakim