Pandas高效实现分组去重后多列成员求和（适配百万级数据）

原始DataFrame #

import pandas as pd

df = pd.DataFrame({"week": [1, 1, 1, 1, 1],
                   "area1_code1": ["A", "A", "A", "A", "C"],
                   "area1_code2": ["A1", "A1", "A2", "A2", "C1"],
                   "area1_member": [10, 10, 8, 8, 2],
                   "area2_code1": ["B", "B", "B", "B", "D"],
                   "area2_code2": ["B1", "B2", "B1", "B2", "D1"],
                   "area2_member": [3, 3, 3, 3, 6]})

对应表格：

需求 #

按以下两种方式分组，计算分组内所有唯一area1_code2和area2_code2对应的area1_member与area2_member之和：

• 按area1_code1和area2_code1分组
• 按week分组

期望输出 #

按area1_code1和area2_code1分组 #

按week分组 #

尝试的代码及问题 #

以下代码仅在按week分组时得到正确结果，按area1_code1和area2_code1分组时无法得到期望输出：

area1 = df[["week", "area1_code1", "area1_code2", "area1_member"]].drop_duplicates(["week", "area1_code2"])
area1.rename(columns={"area1_code1": "area_code1",
                      "area1_code2": "area_code2",
                      "area1_member": "area_member"}, inplace=True)
area2 = df[["week", "area2_code1", "area2_code2", "area2_member"]].drop_duplicates(["week", "area2_code2"])
area2.rename(columns={"area2_code1": "area_code1",
                      "area2_code2": "area_code2",
                      "area2_member": "area_member"}, inplace=True)
result = pd.concat([area1, area2]).drop_duplicates().reset_index(drop=True)

# 按week分组得到正确结果
result_week = result.groupby("week")["area_member"].sum().reset_index()

问题在于合并area1和area2后，丢失了area1_code1与area2_code1的对应关联，无法按这两个字段组合分组求和。

针对百万级行的DataFrame，优先采用先去重、再分组求和、最后关联合并的思路，避免大表拼接带来的性能损耗：

1. 按area1_code1和area2_code1分组计算 #

# 提取area1的唯一记录并按week+area1_code1求和
area1_unique = df[['week', 'area1_code1', 'area1_code2', 'area1_member']].drop_duplicates()
sum_area1 = area1_unique.groupby(['week', 'area1_code1'])['area1_member'].sum().reset_index(name='sum_area1')

# 提取area2的唯一记录并按week+area2_code1求和
area2_unique = df[['week', 'area2_code1', 'area2_code2', 'area2_member']].drop_duplicates()
sum_area2 = area2_unique.groupby(['week', 'area2_code1'])['area2_member'].sum().reset_index(name='sum_area2')

# 获取原始数据中week+area1_code1+area2_code1的唯一组合
group_keys = df[['week', 'area1_code1', 'area2_code1']].drop_duplicates()

# 合并求和结果并计算总members
result_group = group_keys.merge(sum_area1, on=['week', 'area1_code1'])
result_group = result_group.merge(sum_area2, left_on=['week', 'area2_code1'], right_on=['week', 'area2_code1'])
result_group['members'] = result_group['sum_area1'] + result_group['sum_area2']

# 保留需要的列
result_group = result_group[['week', 'area1_code1', 'area2_code1', 'members']]

输出结果与期望一致。

2. 按week分组计算 #

基于上述求和结果，直接按week汇总即可：

sum_week_area1 = sum_area1.groupby('week')['sum_area1'].sum().reset_index()
sum_week_area2 = sum_area2.groupby('week')['sum_area2'].sum().reset_index()

result_week = sum_week_area1.merge(sum_week_area2, on='week')
result_week['members'] = result_week['sum_area1'] + result_week['sum_area2']
result_week = result_week[['week', 'members']]

方案优势 #

• 仅对必要字段操作，减少内存占用
• 先去重再分组，避免重复计算
• 合并的是小结果集而非原始大表，大幅提升处理速度，适配百万级数据场景

补充说明：该方案支持area2_code2对应不同area2_member的场景（如B1=3、B2=4）。

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