CODEWITHSUNDEEP
pythonexcelpandasnumpydataframe
Shashi Mistry
Shashi Mistry

Reputation: 27

Sumifs excel formula in Pandas

I have seen a lot of SUMIFS question being answered here but is very different from the one I need.

1st Trade data frame contains transaction id and C_ID

transaction   C_ID
1             101
2             103
3             104
4             101
5             102
6             104

2nd Customer data frame contains C_ID, On/Off, Amount

C_ID   On/Off  Amount
102     On      320
101     On      400
101     On      200
103     On       60
104     Off      80
104     On      100

So i want to calculate the Amount based on the C_ID with a condition on column 'On/Off' in Customer data frame. The resulting trade data frame should be

transaction      C_ID    Amount
1                 101     600
2                 103      60
3                 104     100
4                 101     600
5                 102     320
6                 104     100

So here’s the formula in EXCEL on how Amount are calculated

=SUMIFS(Customer.Amount, Customer.C_ID = Trade.C_ID, Customer.On/Off = On)

So i want to replicate this particular formula in Python using Pandas

Upvotes: 0

Views: 109

Answers (2)

BENY
BENY

Reputation: 323316

We do filter groupby + reindex assign 

df1['Amount']=df2.loc[df2['On/Off']=='On'].groupby(['C_ID']).Amount.sum().reindex(df1.C_ID).tolist()
df1
Out[340]: 
   transaction  C_ID  Amount
0            1   101     600
1            2   103      60
2            3   104     100
3            4   101     600
4            5   102     320
5            6   104     100

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150785

You can use groupby() on filtered data to compute the sum and map to assign new column to transaction data.

s = df2[df2['On/Off']=='On'].groupby('C_ID')['Amount'].sum()
df1['Amount'] = df1['C_ID'].map(s)

Upvotes: 1

Related Questions