如何按agent external id分组统计各product_group的计数与周数总和并格式化输出

原始数据 #

agent external id,product_group,commission_rate,weeks_held
3000-29,Collection,0.85,1
3000-29,Collection,0.85,2
3000-29,Return,0.85,1
3000-12,Collection,0.85,1
3000-12,Collection,0.85,2
3000-12,Return,0.85,1
3000-34,Collection,0.8,2
3000-34,Collection,0.8,2
3000-34,Return,0.8,1
3022-29,Collection,0.75,1
3022-29,Collection,0.75,2
3022-29,Return,0.75,1

需求 #

为每个agent external id生成包含以下内容的DataFrame：

• agent external id（重命名为agent_external_id）
• 各product_group的计数（count_collection、count_return）
• 各product_group对应的weeks_held求和（sum_collection_weeks_held、sum_return_weeks_held）

要求每个agent external id对应两行：一行展示Collection的统计值（Return列设为0），另一行展示Return的统计值（Collection列设为0）。

期望输出 #

agent_external_id,count_collection,sum_collection_weeks_held,count_return,sum_return_weeks_held
3000-29,2,3,0,0
3000-29,0,0,1,1
3000-12,2,3,0,0
3000-12,0,0,1,1
3000-34,2,4,0,0
3000-34,0,0,1,1
3022-29,2,3,0,0
3022-29,0,0,1,1

解决方案 #

使用Pandas实现，步骤如下：

import pandas as pd

# 加载数据（实际使用时可替换为pd.read_csv("your_file.csv")）
data = """agent external id,product_group,commission_rate,weeks_held
3000-29,Collection,0.85,1
3000-29,Collection,0.85,2
3000-29,Return,0.85,1
3000-12,Collection,0.85,1
3000-12,Collection,0.85,2
3000-12,Return,0.85,1
3000-34,Collection,0.8,2
3000-34,Collection,0.8,2
3000-34,Return,0.8,1
3022-29,Collection,0.75,1
3022-29,Collection,0.75,2
3022-29,Return,0.75,1"""

df = pd.read_csv(pd.io.common.StringIO(data))

# 1. 按agent和product_group分组，计算计数和周数总和
grouped_stats = df.groupby(['agent external id', 'product_group']).agg(
    item_count=('product_group', 'count'),
    total_weeks=('weeks_held', 'sum')
).reset_index()

# 2. 生成所有agent与product_group的组合，确保每个agent都有两类记录
all_agent_product_pairs = pd.MultiIndex.from_product(
    [df['agent external id'].unique(), ['Collection', 'Return']],
    names=['agent external id', 'product_group']
).to_frame(index=False)

# 3. 合并统计数据，缺失值填充为0
combined_df = pd.merge(all_agent_product_pairs, grouped_stats, how='left', on=['agent external id', 'product_group']).fillna(0)

# 4. 构建目标列，根据当前product_group赋值对应统计值
combined_df['count_collection'] = combined_df.apply(
    lambda row: row['item_count'] if row['product_group'] == 'Collection' else 0, axis=1
)
combined_df['sum_collection_weeks_held'] = combined_df.apply(
    lambda row: row['total_weeks'] if row['product_group'] == 'Collection' else 0, axis=1
)
combined_df['count_return'] = combined_df.apply(
    lambda row: row['item_count'] if row['product_group'] == 'Return' else 0, axis=1
)
combined_df['sum_return_weeks_held'] = combined_df.apply(
    lambda row: row['total_weeks'] if row['product_group'] == 'Return' else 0, axis=1
)

# 5. 整理最终结果，保留需要的列并重命名agent列
final_result = combined_df[
    ['agent external id', 'count_collection', 'sum_collection_weeks_held', 'count_return', 'sum_return_weeks_held']
].rename(columns={'agent external id': 'agent_external_id'})

# 输出结果（或保存为csv）
print(final_result.to_csv(index=False))

逻辑说明 #

1. 分组统计：先获取每个agent对应每个product_group的基础统计值（计数、周数总和）。
2. 补全组合：生成所有agent与product_group的配对，避免某个agent缺少某类product_group的记录。
3. 填充缺失值：合并后将缺失的统计值填充为0，保证数据完整性。
4. 列赋值：根据当前行的product_group，将统计值填入对应列，其余列设为0，匹配期望的输出格式。
5. 整理输出：调整列名和列顺序，得到最终结果。

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