如何筛选占净收入90%的SKU行并计算供应商门店库存平均覆盖率

需求说明 #

给定两个Pandas DataFrame：

1. 净收入表：按供应商（Supplier）、SKU统计净收入（Net Revenue）
2. 库存表：按门店（Store）、SKU记录库存数量（Stock）

需完成以下操作：

• 对每个供应商，筛选出**累计净收入占比≥90%**的SKU集合（若当前累计占比未达标，必须纳入下一个SKU）
• 基于筛选出的SKU，计算对应门店的库存平均覆盖率

数据示例 #

净收入表 #

库存表 #

实现代码（Python Pandas） #

1. 初始化数据 #

import pandas as pd

# 构建净收入表
df_rev = pd.DataFrame({
    'Supplier': ['UNILEVER', 'UNILEVER', 'UNILEVER', 'PEPSICO', 'PEPSICO', 'PEPSICO'],
    'SKU': ['1111', '2222', '3333', '1313', '2424', '2323'],
    'Net Revenue': [10000, 50000, 500, 680, 10000, 450]
})

# 构建库存表
df_stock = pd.DataFrame({
    'Store': ['1', '1', '1', '2', '2', '2'],
    'SKU': ['1111', '2222', '3333', '1111', '2222', '3333'],
    'Stock': [1, 2, 1, 1, 0, 1]
})

2. 筛选核心SKU #

# 计算每个供应商的总营收，以及单个SKU的营收占比
df_rev['Total Rev'] = df_rev.groupby('Supplier')['Net Revenue'].transform('sum')
df_rev['Rev Ratio'] = df_rev['Net Revenue'] / df_rev['Total Rev']

# 按供应商分组，将SKU按营收降序排序，计算累计占比
df_rev_sorted = df_rev.sort_values(['Supplier', 'Net Revenue'], ascending=[True, False])
df_rev_sorted['Cumulative Ratio'] = df_rev_sorted.groupby('Supplier')['Rev Ratio'].cumsum()

# 筛选累计占比首次达到90%的SKU及之前的所有SKU
def get_core_skus(group):
    # 找到第一个满足累计占比≥0.9的索引
    threshold_idx = group[group['Cumulative Ratio'] >= 0.9].index[0]
    return group.loc[:threshold_idx]

df_core = df_rev_sorted.groupby('Supplier', group_keys=False).apply(get_core_skus)

3. 计算库存平均覆盖率 #

提供两种常见的覆盖率计算逻辑，可根据实际需求选择：

逻辑1：所有核心SKU库存的整体平均值

# 合并核心SKU与库存数据
merged = df_core[['Supplier', 'SKU']].merge(df_stock, on='SKU')

# 计算每个供应商的平均覆盖率
coverage_result = merged.groupby('Supplier')['Stock'].mean().reset_index(name='Avg Stock Coverage')

逻辑2：按门店维度的平均（每个门店的核心SKU库存总和/核心SKU数量，再求门店间的平均）

merged = df_core[['Supplier', 'SKU']].merge(df_stock, on='SKU')

# 计算每个门店的核心SKU库存总和、核心SKU数量
merged['Store Total Stock'] = merged.groupby(['Supplier', 'Store'])['Stock'].transform('sum')
merged['Core SKU Count'] = merged.groupby('Supplier')['SKU'].transform('nunique')

# 计算单门店平均覆盖率，再求供应商整体平均
merged['Store Avg Coverage'] = merged['Store Total Stock'] / merged['Core SKU Count']
coverage_result = merged.groupby('Supplier')['Store Avg Coverage'].mean().reset_index(name='Avg Stock Coverage')

4. 输出结果 #

运行上述代码后，针对示例数据的输出（逻辑2）：

注：示例中UNILEVER的核心SKU为1111、2222，门店1的平均覆盖率为(1+2)/2=1.5，门店2为(1+0)/2=0.5，整体平均为1.0；PEPSICO的核心SKU为2424，两个门店的库存分别为2、0，平均为1.0

性能优化提示 #

针对150家供应商的数据集，Pandas原生操作已足够高效，若SKU量级极大，可优化点：

• 用transform替代apply减少内存占用
• 提前完成数据排序，避免重复排序操作
• 超大数据集可考虑用Dask进行分布式计算

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