如何将Pandas DataFrame透视为买入/卖出/总计三个独立表格

初始DataFrame如下：

ID   Counterparty   Date         Commodity   Deal    Price   Total
--   ------------   -----        --------    -----   -----  ------
1     party1        04/03/2024     Oil       Sell    10.00   100.00
2     party1        04/03/2024     Oil       Sell    10.00   100.00
3     party1        04/03/2024     Oil       Sell    10.00   100.00
4     party1        04/03/2024     Oil       Buy     10.00   100.00
5     party1        04/03/2024     Oil       Buy     10.00   100.00
6     party1        04/03/2024     Oil       Buy     10.00   100.00
7     party2        04/03/2024     Oil       Sell    5.00    50.00
8     party2        04/03/2024     Oil       Sell    5.00    50.00
9     party2        04/03/2024     Oil       Sell    5.00    50.00
10    party2        04/03/2024     Oil       Buy     5.00    50.00
11    party2        04/03/2024     Oil       Buy     5.00    50.00
12    party2        04/03/2024     Oil       Buy     5.00    50.00

已完成分组步骤，得到如下结果：

Counterparty      Commodity     Deal      Total
party1             Oil          Sell      300
party1             Oil          Buy       300
party2             Oil          Sell      150
party2             Oil          Buy       150

实现代码：

df_grouped = df.groupby(['Counterparty', 'Commodity', 'Deal'])['Total'].sum().reset_index()

后续需要生成三个独立的DataFrame：卖出表、买入表和总计表，格式如下：

Sell
          Oil    
party1    300.00 
party2    150.00 

Buy
          Oil    
party1    300.00 
party2    150.00 

Total 
          Oil    
party1    600.00 
party2    300.00 

尝试以下代码时出现重复索引错误：

df_pivot = df_grouped.pivot(index='Counterparty', columns='Commodity', values='MTMValue').fillna(0).rename_axis(None, axis=0)

请问pivot是正确的解决方法吗？或者有更优的实现方式？

1. 错误原因分析 #

你之前的代码出错主要有两个原因：

• 列名错误：代码里用了MTMValue，但你的DataFrame中对应的列是Total
• 未按Deal拆分直接透视，会导致Counterparty+Commodity组合出现重复行（同一个对手方同一种商品既有Buy又有Sell），触发重复索引错误

2. 正确实现方式 #

Pivot是合适的方法，但需要针对不同的Deal类型分别处理，同时单独计算总计：

生成卖出表和买入表

先筛选对应Deal的数据，再进行透视：

# 卖出表
sell_df = df_grouped[df_grouped['Deal'] == 'Sell'].pivot(
    index='Counterparty', 
    columns='Commodity', 
    values='Total'
).fillna(0).rename_axis(None, axis=1).rename_axis(None, axis=0)
sell_df.index.name = 'Sell'

# 买入表
buy_df = df_grouped[df_grouped['Deal'] == 'Buy'].pivot(
    index='Counterparty', 
    columns='Commodity', 
    values='Total'
).fillna(0).rename_axis(None, axis=1).rename_axis(None, axis=0)
buy_df.index.name = 'Buy'

生成总计表

先按Counterparty和Commodity求和，再透视：

total_grouped = df_grouped.groupby(['Counterparty', 'Commodity'])['Total'].sum().reset_index()
total_df = total_grouped.pivot(
    index='Counterparty', 
    columns='Commodity', 
    values='Total'
).fillna(0).rename_axis(None, axis=1).rename_axis(None, axis=0)
total_df.index.name = 'Total'

简化写法（用unstack替代pivot）

也可以用unstack实现，代码更简洁：

# 卖出表
sell_df = df_grouped[df_grouped['Deal'] == 'Sell'].set_index(['Counterparty', 'Commodity'])['Total'].unstack().fillna(0)
sell_df.index.name = 'Sell'
sell_df.columns.name = None

# 买入表
buy_df = df_grouped[df_grouped['Deal'] == 'Buy'].set_index(['Counterparty', 'Commodity'])['Total'].unstack().fillna(0)
buy_df.index.name = 'Buy'
buy_df.columns.name = None

# 总计表
total_df = df_grouped.groupby(['Counterparty', 'Commodity'])['Total'].sum().unstack().fillna(0)
total_df.index.name = 'Total'
total_df.columns.name = None

3. 最终效果 #

运行后得到的三个DataFrame分别为：

• 卖出表：

Sell     Oil
party1  300.0
party2  150.0

• 买入表：

Buy      Oil
party1  300.0
party2  150.0

• 总计表：

Total    Oil
party1  600.0
party2  300.0

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