求优化：Pandas按组匹配列值保留行，无匹配则留组首行

需求说明：
将df2左连接至df1，按market组执行以下规则：

• 组内underlying值匹配时，保留df2中对应的client值
• 组内无匹配行时，用该组在df2中的首行client值填充

现有代码可实现需求但步骤冗余，以下是更简洁高效的解决方案。

数据初始化（用于测试） #

import pandas as pd
import numpy as np

# DF1
market = ['SP', 'SP', 'SP']
underlying = ['TSLA', 'GOOG', 'MSFT']
df = pd.DataFrame(list(zip(market, underlying)), columns=['market', 'underlying'])

# DF2
market2 = ['SP', 'SP', 'SP', 'SP', 'SP']
underlying2 = [None, 'TSLA', 'GBX', 'GBM', 'GBS']
client2 = [17, 12, 100, 21, 10]
df2 = pd.DataFrame(list(zip(market2, underlying2, client2)), columns=['market', 'underlying', 'client'])

# 目标结果DF3
market3 = ['SP', 'SP', 'SP']
underlying3 = ['TSLA', 'GOOG', 'MSFT']
client3 = [12, 17, 17]
df3_target = pd.DataFrame(list(zip(market3, underlying3, client3)), columns=['market', 'underlying', 'client'])

优化解决方案 #

分步实现（逻辑清晰）

# 1. 提取每个market组的首行client作为兜底填充值
default_clients = df2.groupby('market')['client'].first().reset_index(name='default_client')

# 2. 左连接匹配market+underlying对应的client
result = df.merge(df2, on=['market', 'underlying'], how='left')

# 3. 合并兜底值并填充空client
result = result.merge(default_clients, on='market', how='left')
result['client'] = result['client'].combine_first(result['default_client'])

# 4. 清理多余列得到最终结果
df3 = result[['market', 'underlying', 'client']]

链式调用（简洁紧凑）

如果喜欢更简洁的写法，可以用链式操作一步完成：

df3 = (df
       .merge(df2, on=['market', 'underlying'], how='left')
       .merge(df2.groupby('market')['client'].first().reset_index(name='default_client'), 
              on='market', how='left')
       .assign(client=lambda x: x['client'].combine_first(x['default_client']))
       .filter(['market', 'underlying', 'client']))

验证结果 #

运行后df3与目标df3_target完全一致：

market underlying  client
0     SP        TSLA    12.0
1     SP        GOOG    17.0
2     SP        MSFT    17.0

内容的提问来源于stack exchange，提问作者Guyon Van Rooij