CODEWITHSUNDEEP
pythonpandasgroup-by
emax
emax

Reputation: 7245

Python: how to groupy and sum payments between people in Pandas?

I have a dataframe df that contains the information of the payments from Name1 to Name2 contains the information of some users.

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

I would like to groupby and for each name have the total amount spent and the total amount 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

Upvotes: 2

Views: 111

Answers (1)

jezrael
jezrael

Reputation: 863166

Use melt with aggregate sum and reshape by 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

Another solution with double groupby and 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

Upvotes: 2

Related Questions