Python:如何在熊猫中对人与人之间的付款进行分组和求和?

Question

我有一个数据帧df，其中包含从Name1到Name2的付款信息，其中包含一些用户的信息。

df
Name1    Name2    amount
0     Tom       Jack     554
1     Eva       Laura    334
2     Eva       Tom      45
3     Jack      Sara     120
4     Jack      Tom      30

我想groupby，每个名字都有spent的总额和总金额received

df
Name      Spent    Received
0     Tom      554        75
1     Jack     150        554
2     Sara      0         120
3     Laura     0         334
4     Eva      379         0

Answer 1

将melt与聚合sum一起使用，并通过unstack重塑：

df = (df.melt('amount', value_name='Name')
.groupby(['Name', 'variable'])['amount']
.sum()
.unstack(fill_value=0)
.rename(columns={'Name1':'Spent','Name2':'Received'})
.rename_axis(None, 1)
.reset_index())
print (df)
Name  Spent  Received
0    Eva    379         0
1   Jack    150       554
2  Laura      0       334
3   Sara      0       120
4    Tom    554        75

另一种双groupby和双concat解决方案：

df = (pd.concat([df.groupby('Name1')['amount'].sum(),
df.groupby('Name2')['amount'].sum()], 
axis=1, 
keys=('Spent','Received'))
.fillna(0)
.rename_axis('Name')
.reset_index())
print (df)
Name  Spent  Received
0    Eva  379.0       0.0
1   Jack  150.0     554.0
2  Laura    0.0     334.0
3   Sara    0.0     120.0
4    Tom  554.0      75.0