基于groupby计算后生成目标DataFrame的技术实现问询

问题描述 #

现有如下结构的Pandas DataFrame：

ID  TradeDate    party    Deal   Asset  Start       Expire      Fixed   Quantity  MTM      Float
1   04/11/2024   party1   Sell   HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Buy    HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Buy    HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   WTI    01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   WTI    01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Buy    WTI    01/01/2024  02/01/2024  10.00   1000     2500.00   10.00

已按Start、Asset、Deal字段分组并计算，得到多个独立DataFrame组成的列表：

# 分组1：Sell-HO-01/01/2024
ID  TradeDate    party    Deal   Asset  Start       Expire      Fixed   Quantity  MTM      Float
1   04/11/2024   party1   Sell   HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
total                                                                   3000     7500.00   

# 分组2：Buy-HO-01/01/2024
ID  TradeDate    party    Deal   Asset  Start       Expire      Fixed   Quantity  MTM      Float
1   04/11/2024   party1   Buy    HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Buy    HO     01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
total                                                                   3000     5000.00   

# 分组3：Sell-WTI-01/01/2024
ID  TradeDate    party    Deal   Asset  Start       Expire      Fixed   Quantity  MTM      Float
1   04/11/2024   party1   Sell   WTI    01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
1   04/11/2024   party1   Sell   WTI    01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
total                                                                   3000     5000.00   

# 分组4：Buy-WTI-01/01/2024
ID  TradeDate    party    Deal   Asset  Start       Expire      Fixed   Quantity  MTM      Float
1   04/11/2024   party1   Buy    WTI    01/01/2024  02/01/2024  10.00   1000     2500.00   10.00
total                                                                   1000     2500.00   

需要转换为如下目标格式的DataFrame：

party   Deal  Asset Start        MTM       Float
party1  Sell  HO    01/01/2024   7500.00   10.00 
party1  Buy   HO    01/01/2024   5000.00   10.00
party1  Sell  WTI   01/01/2024   5000.00   10.00
party1  Buy   WTI   01/01/2024   2500.00   10.00

解决方案 #

不需要再次执行groupby操作，有两种高效实现路径：

路径一：直接从原始DataFrame生成结果（推荐） #

原始数据已包含所有必要维度，直接按目标字段分组聚合即可，跳过中间分组步骤，效率更高：

import pandas as pd

# 假设原始数据存储在df中
result = df.groupby(['party', 'Deal', 'Asset', 'Start']).agg(
    MTM=('MTM', 'sum'),          # 对MTM求和
    Float=('Float', 'first')     # 同组内Float值一致，取第一个即可
).reset_index()

print(result)

路径二：基于已有的分组DataFrame列表处理 #

如果必须使用已生成的分组列表，只需提取每个分组的关键信息即可：

# 假设分组后的DataFrame列表为group_dfs（可通过[g for _, g in groups]获取）
output_rows = []

for group_df in group_dfs:
    # 提取分组的基础属性（同组内值一致，取第一行）
    base_info = group_df[['party', 'Deal', 'Asset', 'Start', 'Float']].iloc[0]
    # 提取total行的MTM值
    total_mtm = group_df['MTM'].iloc[-1]
    
    # 组装成目标行
    output_rows.append({
        **base_info.to_dict(),
        'MTM': total_mtm
    })

result = pd.DataFrame(output_rows)
print(result)

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