SUMIFS style calculation of pandas GroupBy-CodePudding

I am trying to divide the result of two columns multiplied with each other, by the aggregate total I get for a groupby, which is based on a SUMIFS style conditions matching.

I groupby as normal

df.groupby(["Month","Category","Currency"])["Amount"].sum()

How do I honor the values that come out of this groupby and use each respective number as the denomenator of the division of the main dataframe calculation (roughly written below).

Month            Category      Currency                    Amount
2000-01-31      Deposit-CA           FC               -8994788.78
                                    GBP             -791962507.93                                 USD               -31808347.06
2001-02-28     Deposit-SA            FC              -10496460.50
                                    KWD            -1064463148.00
2002-08-31      Corporate           KWD                 343753.63

For example I want to calculate

df["Profit_Rate"]*df["Amount"]/groupby amount sum for each case conditions matching

With a SUMIF in excel you are able to provide ranges of data and the conidtion which you would like to text to get a total number that can be used in the calculation.

My question is how to access these totals from a groupby and utilise them as a number in another equation. For example in my case trying to calculate percentage yield based on multiple factors.

CodePudding user response：

You're probably looking to transform the sum:

totals = df.groupby(["Month","Category","Currency"])["Amount"].transform('sum')

Then you can do

df["Profit_Rate"] * df["Amount"] / totals