Are there single functions in pandas to perform the equivalents of SUMIF, which sums over a specific condition and COUNTIF, which counts values of specific conditions from Excel?
I know that there are many multiple step functions that can be used for
for example for sumif I can use (df.map(lambda x: condition), or df.size()) then use .sum()
and for countif I can use (groupby functions and look for my answer or use a filter and the .count())
Is there simple one step process to do these functions where you enter the condition and the data frame and you get the sum or counted results?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You can first make a conditional selection, and sum up the results of the selection using the sum function.
>> df = pd.DataFrame({'a': [1, 2, 3]})
>> df[df.a > 1].sum()
a 5
dtype: int64
Having more than one condition:
>> df[(df.a > 1) & (df.a < 3)].sum() a 2 dtype: int64
If you want to do COUNTIF, just replace sum() with count()
Method 2
You didn’t mention the fancy indexing capabilities of dataframes, e.g.:
>>> df = pd.DataFrame({"class":[1,1,1,2,2], "value":[1,2,3,4,5]})
>>> df[df["class"]==1].sum()
class 3
value 6
dtype: int64
>>> df[df["class"]==1].sum()["value"]
6
>>> df[df["class"]==1].count()["value"]
3
You could replace df["class"]==1by another condition.
Method 3
I usually use numpy sum over the logical condition column:
>>> import numpy as np
>>> import pandas as pd
>>> df = pd.DataFrame({'Age' : [20,24,18,5,78]})
>>> np.sum(df['Age'] > 20)
2
This seems to me slightly shorter than the solution presented above
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0