基于参考DataFrame提取条件最大值的优化实现需求

数据准备 #

参考DataFrame tbl：

import pandas as pd

tbl = pd.DataFrame([['Afghanistan', 'AFN',  4],
                   ['Albania',  'ALL',  2],
                   ['France',   'EUR',  1]],
                     columns=['country',    'currency', 'score'])

工作DataFrame df：

df = pd.DataFrame(
[['France','AFN'],['France','ALL'],['France','EUR'],
['Albania','AFN'],['Albania','ALL'],['Albania','EUR'],
['Afghanistan','AFN'],['Afghanistan','ALL'],['Afghanistan','EUR']], 
columns=['country','currency'])

需求说明 #

给df新增score列，取值规则为：该行的country或currency在tbl中对应的score的最大值（比如France+AFN组合对应score为4）。

期望输出 #

country currency  score
0       France      AFN      4
1       France      ALL      2
2       France      EUR      1
3      Albania      AFN      4
4      Albania      ALL      2
5      Albania      EUR      2
6  Afghanistan      AFN      4
7  Afghanistan      ALL      4
8  Afghanistan      EUR      4

现有繁琐实现 #

df = pd.merge(df, tbl[['country', 'score']],
             how='left', on='country')
df['em_score'] = df['score']
df = df.drop('score', axis=1)

df = pd.merge(df, tbl[['currency', 'score']],
             how='left', on='currency')
df['em_score'] = df[['em_score', 'score']].max(axis=1)
df = df.drop('score', axis=1)

简洁实现方案 #

方案一：用map+combine_max（最简洁） #

直接通过映射获取两个字段对应的score，再合并取最大值：

# 构建映射关系
country_map = tbl.set_index('country')['score']
currency_map = tbl.set_index('currency')['score']

# 生成score列
df['score'] = df['country'].map(country_map).combine_max(df['currency'].map(currency_map))

方案二：链式merge+assign（可读性强） #

通过链式调用完成两次合并，直接计算最大值后清理冗余列：

df = df.merge(tbl[['country', 'score']], on='country', how='left')\
       .merge(tbl[['currency', 'score']], on='currency', how='left', suffixes=('_cntry', '_curr'))\
       .assign(score=lambda x: x[['score_cntry', 'score_curr']].max(axis=1))\
       .drop(['score_cntry', 'score_curr'], axis=1)

方案三：apply+字典映射（直观易懂） #

先把映射转成字典，再逐行计算最大值：

country_score = tbl.set_index('country')['score'].to_dict()
currency_score = tbl.set_index('currency')['score'].to_dict()

df['score'] = df.apply(lambda row: max(country_score[row['country']], currency_score[row['currency']]), axis=1)

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