基于周组合转换Pandas DataFrame：客户购物篮数据扩展需求

需求说明 #

现有记录客户每周购物篮的Pandas DataFrame，每条数据对应一位客户某一周购买的一件商品。需要为每个客户生成以最早消费周为起始，与所有后续周的非单周组合，每个组合下包含该客户在起始周到结束周范围内的全部购物记录，并添加组合标识列。

输入示例 #

import pandas as pd

data = [['1011', 1, 'Week-1','item1'],
        ['1011', 2, 'Week-2','item1'],
        ['1011', 3, 'Week-3','item2'],
        ['1011', 3, 'Week-3','item30'],
        ['1011', 5, 'Week-5','item12'],
        ['1022', 10, 'Week-10','item8'],
        ['1022', 10, 'Week-10','item12'],
        ['1022', 16, 'Week-16','item1'],
        ['1022', 17, 'Week-17','item2'],
       ]
df = pd.DataFrame(data, columns=['CustomerID', 'WeekID', 'Weekname','ProductID'])

实现代码 #

# 按客户分组处理
result_dfs = []
for cust_id, group in df.groupby('CustomerID'):
    # 获取当前客户的所有唯一WeekID并排序
    sorted_weeks = sorted(group['WeekID'].unique())
    # 生成以最早周为起始，与后续各周的组合
    if len(sorted_weeks) >= 2:
        first_week = sorted_weeks[0]
        week_pairs = [(first_week, to_week) for to_week in sorted_weeks[1:]]
        for from_week, to_week in week_pairs:
            # 筛选组合覆盖的所有周的记录
            filtered = group[group['WeekID'].between(from_week, to_week)]
            # 添加组合标识列
            filtered['CustomerID-FromWeekID-ToWeekID'] = f"{cust_id}-{from_week}-{to_week}"
            result_dfs.append(filtered)

# 合并结果并调整列顺序
final_df = pd.concat(result_dfs, ignore_index=True)
final_df = final_df[['CustomerID-FromWeekID-ToWeekID', 'CustomerID', 'WeekID', 'Weekname', 'ProductID']]

# 打印结果
print(final_df.to_string(index=False))

输出结果 #

CustomerID-FromWeekID-ToWeekID  CustomerID  WeekID Weekname ProductID
                     1011-1-2        1011       1   Week-1     item1
                     1011-1-2        1011       2   Week-2     item1
                     1011-1-3        1011       1   Week-1     item1
                     1011-1-3        1011       2   Week-2     item1
                     1011-1-3        1011       3   Week-3     item2
                     1011-1-3        1011       3   Week-3    item30
                     1011-1-5        1011       1   Week-1     item1
                     1011-1-5        1011       2   Week-2     item1
                     1011-1-5        1011       3   Week-3     item2
                     1011-1-5        1011       3   Week-3    item30
                     1011-1-5        1011       5   Week-5    item12
                    1022-10-16        1022      10  Week-10     item8
                    1022-10-16        1022      10  Week-10    item12
                    1022-10-16        1022      16  Week-16     item1
                    1022-10-17        1022      10  Week-10     item8
                    1022-10-17        1022      10  Week-10    item12
                    1022-10-17        1022      16  Week-16     item1
                    1022-10-17        1022      17  Week-17     item2

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