这是我的数据-
FROM TO DIRECTION AMOUNT
B A IN 100
A B OUT 200
A B IN 300
B A OUT 40
作为输出,我想显示谁总共向谁付款的摘要-
FROM TO AMOUNT
A B 300
B A 340
请澄清一下,如果A-> B是第2行和第1行(IN
表示从转移TO
到FROM
,OUT
意味着从转移FROM
到TO
)
我遇到了麻烦.groupby()
。我尝试过的-
df.groupby(['FROM', 'TO', 'DIRECTION'])
但是,当然这似乎并不能解决问题。任何帮助表示赞赏。
想法是FROM
和TO
条件的交换值:
mask = df['DIRECTION'] == 'IN'
df.loc[mask, ['TO', 'FROM']] = df.loc[mask, ['FROM', 'TO']].values
print (df)
FROM TO DIRECTION AMOUNT
0 A B IN 100
1 A B OUT 200
2 B A IN 300
3 B A OUT 40
然后聚合sum
:
df = df.groupby(['FROM', 'TO'], as_index=False)['AMOUNT'].sum()
print (df)
FROM TO AMOUNT
0 A B 300
1 B A 340
如果不想修改原件,则非常相似的解决方案DataFrame
:
mask = df['DIRECTION'] == 'IN'
df1 = df[['TO','FROM']].mask(mask, df[['FROM','TO']].values)
#output is same like above, only changed order of columns
print (df1)
TO FROM
0 B A
1 B A
2 A B
3 A B
df2 = df['AMOUNT'].groupby([df1['FROM'], df1['TO']]).sum().reset_index()
print (df2)
FROM TO AMOUNT
0 A B 300
1 B A 340
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句