基于交易数据按ID和季度获取累计销量最高的产品

原始数据 #

import pandas as pd

df_sample = pd.DataFrame({
    'ID': ['ID1','ID2','ID2','ID2','ID1','ID2','ID1','ID1'],
    "quarter": ['2016Q1','2016Q1','2016Q1','2017Q1','2017Q1','2018Q1','2018Q2','2018Q3'],
    "product": ['productA','productB','productA','productD','productA','productA','productD','ProductA'],
    "sales": [100,200,100,400,100,500,400,100]
})

需求说明 #

按每个ID，针对每个季度，计算截至该季度的所有历史数据中各产品的累计销量，选出销量最高的产品，最终返回每个ID-季度对应的产品名称。

预期输出 #

pd.DataFrame({
    'ID': ['ID1','ID1','ID1','ID1','ID2','ID2','ID2'],
    "quarter": ['2016Q1','2017Q1','2018Q2','2018Q3','2016Q1','2017Q1','2018Q1'],
    "product": ['productA','productA','productD','productD','productB','productD','productA']
})

解决方案 #

实现思路 #

1. 将季度字符串转换为时间格式，方便进行时间范围筛选
2. 遍历每个ID的所有唯一季度，对每个季度筛选出该ID下截至当前季度的所有记录
3. 按产品分组求和累计销量，选出销量最高的产品
4. 收集所有结果并整理成目标DataFrame

完整代码 #

import pandas as pd

# 原始数据
df_sample = pd.DataFrame({
    'ID': ['ID1','ID2','ID2','ID2','ID1','ID2','ID1','ID1'],
    "quarter": ['2016Q1','2016Q1','2016Q1','2017Q1','2017Q1','2018Q1','2018Q2','2018Q3'],
    "product": ['productA','productB','productA','productD','productA','productA','productD','ProductA'],
    "sales": [100,200,100,400,100,500,400,100]
})

# 转换季度为可比较的时间格式
df_sample['quarter_dt'] = pd.to_datetime(df_sample['quarter'], format='%YQ%q')

# 获取每个ID对应的所有唯一季度
id_quarter_map = df_sample.groupby('ID')['quarter_dt'].unique().reset_index()

result = []
for _, row in id_quarter_map.iterrows():
    current_id = row['ID']
    # 对当前ID的季度按时间排序，确保累计逻辑正确
    sorted_quarters = sorted(row['quarter_dt'])
    for q in sorted_quarters:
        # 筛选当前ID下截至当前季度的所有数据
        filter_mask = (df_sample['ID'] == current_id) & (df_sample['quarter_dt'] <= q)
        filtered_data = df_sample[filter_mask]
        # 按产品分组计算累计销量（可根据需求决定是否统一产品名称大小写）
        # 若需统一大小写，取消下面一行注释
        # filtered_data['product'] = filtered_data['product'].str.lower()
        product_sales = filtered_data.groupby('product')['sales'].sum().reset_index()
        # 获取销量最高的产品（若有多个并列，取第一个）
        top_product = product_sales.loc[product_sales['sales'].idxmax(), 'product']
        # 匹配回原始季度字符串
        original_quarter = df_sample.loc[df_sample['quarter_dt'] == q, 'quarter'].iloc[0]
        result.append({
            'ID': current_id,
            'quarter': original_quarter,
            'product': top_product
        })

# 转换为目标DataFrame
result_df = pd.DataFrame(result)
print(result_df)

注意事项 #

• 关于产品名称大小写：原始数据中存在productA和ProductA，若需将它们视为同一产品，可取消代码中filtered_data['product'] = filtered_data['product'].str.lower()的注释，统一为小写后再分组计算。
• 销量并列处理：代码中使用idxmax()取第一个销量最高的产品，若需处理并列情况，可根据需求调整逻辑（如返回所有并列产品）。

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