pandas DataFrame如何删除多余汇总重复行 按指定ID生成对应汇总行

核心问题原因 #

• 原代码聚合时使用全局DataFrame而非传入的当前ID切片，导致聚合数据包含其他ID的内容
• 未建立汇总标识（b65/b66/b67）和业务ID（G-00001/G-00002）的绑定关系，每次循环都会生成全部三类汇总行
• 每次循环重复生成所有汇总行，导致结果出现重复数据

调整后代码 #

import pandas as pd
import numpy as np

# 原始数据构造
d = {'id1': ['85643', '85644','85643','8564312','8564314','85645','8564316','85646','8564318','85647','85648','85649','85655'],
     'ID': ['G-00001', 'G-00001','G-00002','G-00002','G-00002','G-00001','G-00002','G-00001','G-00002','G-00001','G-00001','G-00001','G-00001'],
     'col1': [671, 2,5,3,4,5,60,0,0,6,3,2,4],
     'Goal': [np.nan, 56,78,np.nan,89,73,np.nan ,np.nan ,np.nan, np.nan, np.nan, 34,np.nan ],
     'col2': [793, 4,8,32,43,55,610,0,0,16,23,72,48],
     'col3': [500, 22,89,33,44,55,60,1,5,6,3,2,4],
     'Name': ['aasd', 'aasd','aabsd','aabsd','aabsd','aasd','aabsd','aasd','aabsd','aasd','aasd','aasd','aasd'],
     'Date': ['2021-06-13', '2021-06-13','2021-06-14','2021-06-13','2021-06-14','2021-06-15','2021-06-15','2021-06-13','2021-06-16','2021-06-13','2021-06-13','2021-06-13','2021-06-16']}
dff = pd.DataFrame(data=d)

# 业务ID与对应汇总规则的绑定映射
id_to_summary_map = {
    'G-00001': {
        'b65': ['85643','85645', '85655','85646'],
        'b66': ['85643','85645','85647','85648','85649','85644']
    },
    'G-00002': {
        'b67': ['85643','8564312','8564314','8564316','8564318']
    }
}

def sumarizeValues(current_id, filter_df, original_df):
    summary_rules = id_to_summary_map.get(current_id, {})
    agg_results = []
    for summary_id, id1_collection in summary_rules.items():
        # 仅取当前ID切片下符合id1条件的行做聚合
        matched_data = filter_df[filter_df['id1'].isin(id1_collection)]
        if matched_data.empty:
            continue
        # 按要求聚合字段
        agg_row = matched_data.agg({'col1': sum, 'col2': sum, 'col3': 'mean', 'Name': min})
        agg_row['ID'] = summary_id
        agg_results.append(agg_row)
    if agg_results:
        summary_df = pd.DataFrame(agg_results)
        original_df = pd.concat([original_df, summary_df], ignore_index=True)
    return original_df

def abcFunction(dff):
    ID_list = ['G-00001','G-00002']
    for current_id in ID_list:
        # 取当前业务ID的所有行
        current_id_slice = dff[dff['ID'] == current_id]
        dff = sumarizeValues(current_id, current_id_slice, dff)
    return dff

# 执行得到最终结果
final_df = abcFunction(dff)
print(final_df)

调整说明 #

1. 新增映射表把业务ID和对应的汇总规则绑定，不会生成不属于当前业务ID的汇总行
2. 聚合仅使用当前业务ID的切片数据，避免混入其他ID的数值
3. 增加空校验，仅当存在匹配数据时才生成汇总行
4. 移除重复生成逻辑，不会出现重复汇总行

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