基于多列匹配合并为DataFrame新增列赋值（多匹配场景）

问题背景 #

需基于Product和Price两列，为df2补充Store和Seller列。核心要求：同一Product+Price组合对应不同Store/Seller时，需按Amount的数量依次分配，用完一个来源的数量后再使用下一个。

示例数据 #

数据源df（包含可分配的Store、Seller及对应数量）

import pandas as pd

df = {'Product':['TV', 'iPhone', 'TV'], 'Seller': ['Pankaj', 'John', 'John'] , 'Amount': [15, 10, 20], 'Price' : [2.50, 3.50, 2.5], 'Store': ['Walmart', 'Amazon', 'Amazon']}
df = pd.DataFrame(df)

待补充的df2

df2 = {'Product':['TV', 'TV', 'iPhone', 'iPhone', 'TV'], 'Amount': [10, 5, 5, 5, 20], 'Price' : [2.50, 2.5, 3.50, 3.50, 2.5], 'ID':['GLOBAL', 'FLAGSHIP', 'GREEN', 'FLAGSHIP', 'GLOBAL']}
df2 = pd.DataFrame(df2)

预期结果

df2_result = {'Product':['TV', 'TV', 'iPhone', 'iPhone', 'TV'], 'Amount': [10, 5, 5, 5, 20], 'Price' : [2.50, 2.5, 3.50, 3.50, 2.5], 'ID':['GLOBAL', 'FLAGSHIP', 'GREEN', 'FLAGSHIP', 'GLOBAL'], 'Store': ['Walmart', 'Walmart', 'Amazon', 'Amazon', 'Amazon'], 'Seller': ['Pankaj', 'Pankaj', 'John', 'John', 'John']}
df2_result = pd.DataFrame(df2_result)

尝试方案的问题 #

将两个DataFrame的Amount拆分为单条记录后合并，结果不符合预期。原因是拆分后merge会随机匹配，无法按数量顺序分配Store/Seller。尝试代码：

df= df.loc[df.index.repeat(df['Amount'])].reset_index(drop=True)
df['Amount'] = 1

df2= df2.loc[df2.index.repeat(df2['Amount'])].reset_index(drop=True)
df2['Amount'] = 1

df2 = df2.merge(df, how='left', left_on=['Product', 'Price'])

正确解决方案 #

核心思路：按Product+Price分组，为每个组内的记录计算累计数量区间，再根据df2的需求数量匹配对应区间的Store/Seller。

步骤1：为df计算可分配数量的累计区间

# 按Product和Price分组排序，保证分配顺序
df = df.sort_values(['Product', 'Price'])
# 计算每个记录的数量起止区间
df['cum_start'] = df.groupby(['Product', 'Price'])['Amount'].cumsum() - df['Amount']
df['cum_end'] = df.groupby(['Product', 'Price'])['Amount'].cumsum()

步骤2：为df2计算需求数量的累计区间

df2 = df2.sort_values(['Product', 'Price'])
# 计算每个需求记录的数量起止区间
df2['cum_start'] = df2.groupby(['Product', 'Price'])['Amount'].cumsum() - df2['Amount']
df2['cum_end'] = df2.groupby(['Product', 'Price'])['Amount'].cumsum()

步骤3：区间匹配合并并整理结果

# 交叉合并后筛选需求区间落在可分配区间内的记录
merged = pd.merge(df2, df, on=['Product', 'Price'], how='left', suffixes=('_df2', '_df'))
merged = merged[(merged['cum_start_df2'] < merged['cum_end_df']) & (merged['cum_end_df2'] > merged['cum_start_df'])]

# 保留目标列并恢复原始顺序
df2_final = merged[['Product', 'Amount_df2', 'Price', 'ID', 'Store', 'Seller']].rename(columns={'Amount_df2': 'Amount'})
df2_final = df2_final.sort_index().reset_index(drop=True)

验证结果

执行print(df2_final)，输出将与预期结果完全一致。

方案说明

• 通过累计数量区间精准匹配需求与供给，避免随机匹配问题
• 分组处理确保每个Product+Price组合独立分配，互不干扰
• 排序操作保证分配顺序符合业务预期

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