Pandas: Combining data items on multiple criteria-CodePudding

I am having a database of all customer transactions within company I work at.

ID	Payment	Amount	Month	Year
A	Inward	100	2	2005
A	Outward	200	2	2005
B	Inward	100	7	2017

I have hardships combining Sum/Count of Amount of those transactions per Customer ID per Month/Year.

Only item that I succeed at is combining Sum/Count of Amount of those transactions per customer ID.

Combined = data.groupby("ID")["Amount"].sum().rename("Sum").reset_index()

Can you please let me know what are the alternative solutions?

Thank you in advance!

CodePudding user response：

You can use a list of columns in groupby like:

>>> df.groupby(['ID', 'Year', 'Month', 'Payment'])['Amount'].agg(['sum', 'count'])

                       sum  count
ID Year Month Payment            
A  2005 2     Inward   100      1
              Outward  200      1
B  2017 7     Inward   100      1

For further:

>>> df.assign(Amount=np.where(df['Payment'].eq('Outward'),
                              -df['Amount'], df['Amount'])) \
      .groupby(['ID', 'Year', 'Month'])['Amount'].agg(['sum', 'count'])

               sum  count
ID Year Month            
A  2005 2     -100      2
B  2017 7      100      1