Sumifs in Pandas with two conditions

Question

I want the pandas equivalent of the Excel's sumifs for example

=SUMIFS($D4:D$107,$D$107,$G4:G$107)

I have three columns, the contract, the amount and transaction_type_tla. For each contract, I would like to sum the amount if the transaction type is CBP. The following formula is not working:

data['Var']=(data.groupby('contract',"transaction_type_tla=='CBP'")['amount'].cumsum())

Solution 1

Edit: I think @Wen's answer is more in line with what you're looking for, but in case you wanted the result as a series:

An easy way to do this is to first filter the list of transactions by the transaction_type_tla you're looking for and then apply the groupby and whatever aggregation method you want:

ans = data[data['transaction_type_tla'] == 'CBP']
ans.groupby('contract')['amount'].cumsum()

This will result in a series with your answer.

Solution 2

Borrow jp'data :-)

df['New']=df.groupby('contract').apply(lambda x : x['amount'][x['type']=='CBP'].cumsum()).reset_index(level=0,drop=True)
df
Out[258]: 
  contract  amount type    New
0        A     123  ABC    NaN
1        A     341  ABC    NaN
2        A     652  CBP  652.0
3        A     150  CBP  802.0
4        B     562  DEF    NaN
5        B     674  ABC    NaN
6        B     562  CBP  562.0
7        B     147  CBP  709.0

Comments

[ZaxR]

Also see: stackoverflow.com/questions/11012981/…

[Mark Stewart]

Thanks for your answer. Can you edit your answer to add some description on how that answers the question.