如何用Pandas透视表实现双列的求和与均值双层聚合？

原始数据集 #

输入的原始数据如下：

Date_Time           LF      Name        Count   pwr     TS
0   2022-08-03 00:00:02 2885184100  OpenP1  1   0.302229    1
1   2022-08-03 00:00:02 2885184100  Net3    1   4.790000    3
2   2022-08-03 00:00:02 2885184100  OpenP1  1   0.000000    1
3   2022-08-03 00:00:02 2885184100  OpenP1  3   1.300000    4
4   2022-08-03 00:00:02 2885184100  Net3    1   0.033000    4
5   2022-08-03 00:00:05 2885184220  OpenP1  1   0.302229    1
6   2022-08-03 00:00:05 2885184220  Net3    1   4.790000    3
7   2022-08-03 00:00:05 2885184220  OpenP1  1   0.520000    1
8   2022-08-03 00:00:05 2885184220  OpenP1  2   0.000000    4
9   2022-08-03 00:00:05 2885184220  Net3    1   0.440000    4

聚合规则 #

• 针对每个LF，先对相同Name和TS的pwr字段求和；
• 再针对每个Name，对上述求和结果（最多4个TS对应值）取均值，最终均值关联LF；
• 同时按LF和Name对Count字段求和；

期望输出格式 #

Date_Time           LF          OpenP1_pwr  Net3_pwr    OpenP1_cnt  Net3_cnt
0   2022-08-03 00:00:02 2885184100  0.801115    1.205750    5   2
1   2022-08-03 00:00:05 288518220   0.205557    2.615000    4   2

遇到的问题 #

尝试用pivot_table实现，但无法完成正确聚合，代码如下：

tmp=theData.pivot_table(index='LF', columns=['Name','TS'], values=['pwr', 'Count'],
                      aggfunc={'pwr': '??','Count':'sum'},
                      fill_value=0)

解决方案 #

需求需要分阶段处理pwr的两次聚合，再合并Count的求和结果，具体实现步骤如下：

步骤1：处理pwr字段的两次聚合 #

先按LF+Name+TS分组求和pwr，再按LF+Name分组求均值，最后转换为宽格式匹配输出：

# 第一步：LF+Name+TS分组求和pwr
pwr_step1 = theData.groupby(['LF', 'Name', 'TS'])['pwr'].sum().reset_index()
# 第二步：LF+Name分组求均值
pwr_step2 = pwr_step1.groupby(['LF', 'Name'])['pwr'].mean().reset_index()
# 转换为宽格式，添加后缀区分
pwr_wide = pwr_step2.pivot(index='LF', columns='Name', values='pwr').add_suffix('_pwr').reset_index()

步骤2：处理Count字段的求和 #

按LF+Name分组求和Count，同样转换为宽格式：

count_agg = theData.groupby(['LF', 'Name'])['Count'].sum().reset_index()
count_wide = count_agg.pivot(index='LF', columns='Name', values='Count').add_suffix('_cnt').reset_index()

步骤3：合并结果并关联Date_Time #

将pwr和Count的宽表合并，再关联每个LF对应的唯一Date_Time：

# 合并pwr和count的结果
result = pwr_wide.merge(count_wide, on='LF')
# 获取每个LF对应的Date_Time并合并
date_map = theData[['LF', 'Date_Time']].drop_duplicates()
result = date_map.merge(result, on='LF').sort_values('Date_Time').reset_index(drop=True)

完整可运行代码 #

import pandas as pd

# 构造原始数据集
theData = pd.DataFrame([
    ["2022-08-03 00:00:02", 2885184100, "OpenP1", 1, 0.302229, 1],
    ["2022-08-03 00:00:02", 2885184100, "Net3", 1, 4.790000, 3],
    ["2022-08-03 00:00:02", 2885184100, "OpenP1", 1, 0.000000, 1],
    ["2022-08-03 00:00:02", 2885184100, "OpenP1", 3, 1.300000, 4],
    ["2022-08-03 00:00:02", 2885184100, "Net3", 1, 0.033000, 4],
    ["2022-08-03 00:00:05", 2885184220, "OpenP1", 1, 0.302229, 1],
    ["2022-08-03 00:00:05", 2885184220, "Net3", 1, 4.790000, 3],
    ["2022-08-03 00:00:05", 2885184220, "OpenP1", 1, 0.520000, 1],
    ["2022-08-03 00:00:05", 2885184220, "OpenP1", 2, 0.000000, 4],
    ["2022-08-03 00:00:05", 2885184220, "Net3", 1, 0.440000, 4],
], columns=["Date_Time", "LF", "Name", "Count", "pwr", "TS"])

# 处理pwr字段
pwr_step1 = theData.groupby(['LF', 'Name', 'TS'])['pwr'].sum().reset_index()
pwr_step2 = pwr_step1.groupby(['LF', 'Name'])['pwr'].mean().reset_index()
pwr_wide = pwr_step2.pivot(index='LF', columns='Name', values='pwr').add_suffix('_pwr').reset_index()

# 处理Count字段
count_agg = theData.groupby(['LF', 'Name'])['Count'].sum().reset_index()
count_wide = count_agg.pivot(index='LF', columns='Name', values='Count').add_suffix('_cnt').reset_index()

# 合并结果并关联Date_Time
result = pwr_wide.merge(count_wide, on='LF')
date_map = theData[['LF', 'Date_Time']].drop_duplicates()
result = date_map.merge(result, on='LF').sort_values('Date_Time').reset_index(drop=True)

print(result)

输出结果 #

运行后将得到与期望格式一致的结果：

Date_Time          LF  OpenP1_pwr  Net3_pwr  OpenP1_cnt  Net3_cnt
0  2022-08-03 00:00:02  2885184100    0.801115   1.20575           5         2
1  2022-08-03 00:00:05  2885184220    0.205557   2.61500           4         2

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