如何在Pandas中按多列分组并基于条件匹配关联数据？

需求说明 #

现有两个DataFrame（自有品牌产品数据、竞品品牌产品数据），需完成以下操作：

• 按Yearweek、partner、stock_age分组，筛选出自有品牌中phonememory_own最小的行
• 将筛选后的自有品牌数据与竞品数据匹配，关联竞品的businessunit_ob、phonememory_other1、Price_other1等字段，得到指定格式的合并结果

原始数据 #

自有品牌DataFrame #

Yearweek partner   homebrand businessunit phonememory_own Price_own  stock_age
 1. 202201   abc       brandown  phone1       64GB            490$       eol
 2. 202201   abc       brandown  phone1       128GB           600$       eol
 3. 202201   xyz       brandown  phone2       256GB           900$       new
 4. 202201   qwe       brandown  phone2       128GB           790$       new
 5. 202202   xyz       brandown  phone1       128GB           550$       old

竞品品牌DataFrame #

Yearweek partner   otherbrand1 businessunit_ob phonememory_other1 Price_other1  stock_age
 1. 202201   abc       otherbrand  obphone1           64GB              390$       eol
 2. 202201   abc       otherbrand  obphone1           128GB             500$       eol
 3. 202201   xyz       otherbrand  obphone2           256GB             800$       new
 4. 202201   qwe       otherbrand  obphone2           128GB             590$       new
 5. 202202   xyz       otherbrand  obphone1           128GB             450$       old

期望输出 #

Yearweek partner homebrand   businessunit  phonememory_own  Price_own  stock_age  otherbrand1  businessunit_ob phonememory_other1 Price_other1  stock_age
 1. 202201   abc     brandown    phone1          64GB              490$          eol   otherbrand    obphone1           64GB               390$       eol
 2. 202201   xyz     brandown    phone2          256GB             900$          new   otherbrand    obphone2           256GB              800$       new
 3. 202201   qwe     brandown    phone2          128GB             790$          new   otherbrand    obphone2           128GB              590$       new
 4. 202202   xyz     brandown    phone1          128GB             550$          old   otherbrand    obphone1           128GB              450$       old

实现代码 #

import pandas as pd

# 构建自有品牌DataFrame
own_data = [
    [202201, 'abc', 'brandown', 'phone1', '64GB', '490$', 'eol'],
    [202201, 'abc', 'brandown', 'phone1', '128GB', '600$', 'eol'],
    [202201, 'xyz', 'brandown', 'phone2', '256GB', '900$', 'new'],
    [202201, 'qwe', 'brandown', 'phone2', '128GB', '790$', 'new'],
    [202202, 'xyz', 'brandown', 'phone1', '128GB', '550$', 'old']
]
own_df = pd.DataFrame(own_data, columns=['Yearweek', 'partner', 'homebrand', 'businessunit', 'phonememory_own', 'Price_own', 'stock_age'])

# 构建竞品品牌DataFrame
comp_data = [
    [202201, 'abc', 'otherbrand', 'obphone1', '64GB', '390$', 'eol'],
    [202201, 'abc', 'otherbrand', 'obphone1', '128GB', '500$', 'eol'],
    [202201, 'xyz', 'otherbrand', 'obphone2', '256GB', '800$', 'new'],
    [202201, 'qwe', 'otherbrand', 'obphone2', '128GB', '590$', 'new'],
    [202202, 'xyz', 'otherbrand', 'obphone1', '128GB', '450$', 'old']
]
comp_df = pd.DataFrame(comp_data, columns=['Yearweek', 'partner', 'otherbrand1', 'businessunit_ob', 'phonememory_other1', 'Price_other1', 'stock_age'])

# 步骤1：提取内存容量的数字部分，用于比较大小
own_df['mem_num'] = own_df['phonememory_own'].str.extract('(\d+)').astype(int)

# 步骤2：按分组键找出每组中内存最小的行
filtered_own = own_df.loc[own_df.groupby(['Yearweek', 'partner', 'stock_age'])['mem_num'].idxmin()]

# 步骤3：合并竞品数据，匹配键为Yearweek、partner、stock_age、内存容量
result = pd.merge(
    filtered_own.drop('mem_num', axis=1),
    comp_df[['Yearweek', 'partner', 'stock_age', 'otherbrand1', 'businessunit_ob', 'phonememory_other1', 'Price_other1']],
    left_on=['Yearweek', 'partner', 'stock_age', 'phonememory_own'],
    right_on=['Yearweek', 'partner', 'stock_age', 'phonememory_other1'],
    how='left'
)

# 调整列顺序，匹配期望输出格式
result = result[['Yearweek', 'partner', 'homebrand', 'businessunit', 'phonememory_own', 'Price_own', 'stock_age', 'otherbrand1', 'businessunit_ob', 'phonememory_other1', 'Price_other1']]
print(result)

代码说明 #

1. 提取内存数字：通过正则表达式提取phonememory_own中的数字，转为整数类型，方便比较大小
2. 分组筛选：使用groupby结合idxmin获取每组中内存最小的行索引，再通过loc筛选出目标行
3. 数据合并：以Yearweek、partner、stock_age、内存容量为匹配键，将自有品牌筛选后的数据与竞品数据合并，保留所需字段

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