qazwsx123
qazwsx123

Reputation: 247

How to count in dataframe groupby

I have a dataframe which contains data about buyers and sellers. I wanted to groupby each pair to see how many times a seller and buyer have a transaction, this also sums all their transaction to work out the total spent in their transactions:

df2 = df.groupby(['ID of seller','ID of buyer', 'currency'])["Total spent"].sum().reset_index(drop = False) 

this gives me:

df2 =

ID of seller    ID of buyer    Currency   Total spent
871             356            GBP        60
473             784            GBP        40
etc...     

I want to add another column which states how many times each pair has traded. So it would look like:

ID of seller    ID of buyer    Currency   Total spent  Num of transactions
871             356            GBP        60           2
etc...

from my understanding this would be done using .count() but i can't seem to work out where to fit it in.

Upvotes: 0

Views: 204

Answers (1)

bh00t
bh00t

Reputation: 71

This should work. You should provide a a reproducible example and the desired result for testing

df.groupby(['ID of seller','ID of buyer', 'currency']).agg(total_spent=('Total spent', 'sum'), num_txn=('Total spent', 'count'))

Upvotes: 2

Related Questions