Pandas count number in one column based off a condition in a different column

user_5920226 0
Viewed 1372

I need to count the number of times id occurs given the condition that enroll > 0. This is what I have so far...Any thoughts on how to do this? Thanks!

raw_data = [['a', '0'], ['a', '0'], ['a', '1'], ['b', '0'], ['b', '0.5'], ['c', '0'], ['c', '0']]
df = pd.DataFrame(raw_data, columns = ['id', 'enroll'])
df

enter image description here

def countidsperenroll():
    for i in df['id']:
        if (enroll>0):
            return value.count()
        continue

The result should be a table with: the values:

3
2
0

because there were 3 'a' ids and there was an enroll> 0 with one of the 'a' ids. And because there were 2 'b' ids and there was an enroll > 0 with one of the 'b' ids. No 'enroll' for the 'c' id, So that gets a 0.

pandas if-statement for-loop python loops
3 Answers

We can do it two steps with value_counts

s=df.id.value_counts()
s.loc[~s.index.isin(df.loc[df.enroll>0,'id'].unique())]=0
s
a    3
c    0
b    2
Name: id, dtype: int64
user_79645270 
df.groupby("id").filter(lambda x: (x["enroll"]>0).any()).groupby("id").count()

First you groupby to filter out the groups that have at least one enroll greater than 0 than you groupy again to get the aggregate data.

user_8477730

You could use the fact that if enroll is greater than 0, then the sum per group will be greater than 0 :

(
    df.assign(temp=df.groupby("id").enroll.transform("sum").gt(0))
    .groupby("id")
    .temp.sum()
)

id
a    3.0
b    2.0
c    0.0
Name: temp, dtype: float64
user_71757130
Related