如何为pandas DataFrame指定列生成汇总行并添加回原表

完整可运行代码 #

import pandas as pd
import numpy as np

# 原数据构造
d = {'id1': ['85643', '85644','8564312','8564314','85645','8564316','85646','8564318','85647','85648','85649','85655'],
     'ID': ['G-00001', 'G-00001','G-00002','G-00002','G-00001','G-00002','G-00001','G-00002','G-00001','G-00001','G-00001','G-00001'],
     'col1': [1, 2,3,4,5,60,0,0,6,3,2,4],
     'Goal': [np.nan, 56,np.nan,89,73,np.nan ,np.nan ,np.nan, np.nan, np.nan, 34,np.nan ],
     'col2': [3, 4,32,43,55,610,0,0,16,23,72,48],
     'col3': [1, 22,33,44,55,60,1,5,6,3,2,4],
     'Name': ['aasd', 'aasd','aabsd','aabsd','aasd','aabsd','aasd','aabsd','aasd','aasd','aasd','aasd'],
     'Date': ['2021-06-13', '2021-06-13','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值，值为对应id1列表
groups = {
    'b65': ['85643','85645', '85655','85646'],
    'b66': ['85643','85645','85647','85648','85649','85644'],
    'b67': ['8564312','8564314','8564316','8564318']
}

# col3自定义聚合规则：优先求均值，失败则求和
# 若要和你给出的示例结果完全一致，直接返回series.sum()即可，示例中col3实际为求和结果
def col3_agg(series):
    try:
        # return series.mean()
        return series.sum() # 替换上一行注释为这行，可得到示例中的col3数值
    except:
        return series.sum()

summary_list = []
for group_id, id_list in groups.items():
    # 筛选当前分组对应行
    filter_df = dff[dff['id1'].isin(id_list)]
    # 按规则生成汇总行
    summary_row = {
        'id1': '',
        'ID': group_id,
        'col1': filter_df['col1'].sum(),
        'Goal': np.nan,
        'col2': filter_df['col2'].sum(),
        'col3': col3_agg(filter_df['col3']),
        'Name': filter_df['Name'].mode().iloc[0], # 取分组内出现次数最多的Name
        'Date': np.nan
    }
    summary_list.append(summary_row)

# 汇总行转DataFrame后和原表拼接
result_df = pd.concat([dff, pd.DataFrame(summary_list)], ignore_index=True)

说明 #

运行后输出result_df即可得到目标结果，如果你需要自定义其他列的聚合规则，直接修改summary_row中对应字段的计算逻辑即可。

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