Pandas: Excel sumifs equivalent

user_10981411 0
Viewed 165

For the below table, how do I implement a Python equivalent of the following SUMIFS statement?

SUMIFS(A3:A7,B3:B7,"<="&C1,C3:C7,">0")

enter image description here

Code for the DataFrame:

dfp = pd.DataFrame({'Value': [1223, 34, 34, 345],
                    'Date': ['04/03/2020', '06/04/2020','08/06/2020','12/12/2020'],
                    'Tval' : [0.01,0.015,-0.023,-0.0005]})

The expected output is 1257.

pandas python
1 Answers

This will do the trick:

from datetime import datetime as dt

# Define the reference date as a datetime object.
date = dt(2020, 12, 5)
# Convert dates to a datatime object for compare.
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')

# Apply filters and sum.
df.loc[(df['Tval'] > 0) & (df['Date'] <= date), 'Value'].sum()

Output:

>>> 1257

Note: The above assumes international date format (D/M/Y).

user_63404960
Related