如何将广告成本分摊至各订单并补全无订单的广告记录？

现有数据 #

订单数据（orders） #

import pandas as pd

orders_data = {
    'date_order': ['2024-04-01', '2024-04-01', '2024-04-01', '2024-04-02'],
    'order_id': [1, 2, 3, 4],
    'article_id': ['A', 'A', 'B', 'A'],
    'quantity': [5, 1, 3, 2],
    'final_price': [50, 50, 30, 20],
    'logistics_costs': [10, 10, 5, 4],
    'revenue': [200, 100, 80, 40],
    'cost_price': [30, 30, 20, 15],
    'profit': [160, 70, 60, 25]
}

orders = pd.DataFrame(orders_data)

广告成本数据（adv） #

adv_data = {
    'adv_date': ['2024-04-01', '2024-04-02', '2024-04-03'],
    'article_id': ['A', 'B', 'C'],
    'costs': [24, 10, 15]
}

adv = pd.DataFrame(adv_data)

需求说明 #

• 给每个订单核算对应广告成本：如果某日期某商品既有订单又有广告成本，新增adv_cost列，值为当日该商品广告成本 ÷ 当日该商品总销量，按订单销量占比分摊；
• 如果某日期某商品有广告成本但无对应订单，新增一行记录，adv_cost列填对应成本，其余列填0或标识为adv_cost；

实现代码 #

1. 统计每日商品总销量 #

先算出每个日期、每个商品的总销量，用来分摊广告成本：

# 按日期和商品分组，计算总销量
daily_article_sales = orders.groupby(['date_order', 'article_id'])['quantity'].sum().reset_index()
daily_article_sales.rename(columns={'quantity': 'total_quantity'}, inplace=True)

2. 关联广告数据计算单位成本 #

把广告数据和总销量表关联，算出每单位销量要分摊的广告成本：

# 重命名广告表的日期列，方便后续关联
adv_renamed = adv.rename(columns={'adv_date': 'date_order'})
# 关联总销量数据
adv_with_sales = pd.merge(adv_renamed, daily_article_sales, on=['date_order', 'article_id'], how='left')
# 计算单位销量的广告成本，无销量时结果为NaN
adv_with_sales['unit_adv_cost'] = adv_with_sales['costs'] / adv_with_sales['total_quantity']

3. 给原订单添加广告成本列 #

把单位广告成本关联回订单表，算出每个订单的分摊成本：

# 把单位广告成本关联到原订单数据
orders_with_adv = pd.merge(orders, adv_with_sales[['date_order', 'article_id', 'unit_adv_cost']], 
                           on=['date_order', 'article_id'], how='left')
# 计算每个订单的广告成本，没有对应广告的填0
orders_with_adv['adv_cost'] = orders_with_adv['quantity'] * orders_with_adv['unit_adv_cost'].fillna(0)
# 删除临时列
orders_with_adv.drop(columns=['unit_adv_cost'], inplace=True)

4. 生成无订单的广告记录 #

筛选出有广告但没订单的记录，构造新增行：

# 筛选出没有对应销量的广告记录
no_sales_adv = adv_with_sales[adv_with_sales['total_quantity'].isna()].copy()
# 构造新增行的内容
no_sales_rows = pd.DataFrame({
    'date_order': no_sales_adv['date_order'],
    'order_id': 'adv_cost',
    'article_id': no_sales_adv['article_id'],
    'quantity': 0,
    'final_price': 0,
    'logistics_costs': 0,
    'revenue': 0,
    'cost_price': 0,
    'profit': 0,
    'adv_cost': no_sales_adv['costs']
})

5. 合并所有数据得到最终结果 #

把原订单数据和新增的广告记录合并，调整列顺序和示例一致：

# 合并两部分数据
final_result = pd.concat([orders_with_adv, no_sales_rows], ignore_index=True)
# 调整列顺序匹配期望结果
final_result = final_result[['date_order', 'order_id', 'article_id', 'quantity', 'final_price', 
                             'logistics_costs', 'revenue', 'cost_price', 'profit', 'adv_cost']]

运行后输出的final_result与期望结果完全一致。

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