Groupby Count Only When A Certain Value Is Present In One Of The Column In Pandas

I have a dataframe similar to the below mentioned database: +------------+-----+--------+ | time | id | status | +------------+-----+--------+ | 1451606400 | id1 | Yes

Solution 1:

Use lambda function with apply and for count sum boolena True values proccesses like 1:

df1 = (df.groupby(['time','id','status'])
         .apply(lambda x: (x['status']== 'Yes').sum())
         .reset_index(name='count'))

Or create new column and aggregate sum:

df1 = (df.assign(A=df['status']=='Yes')
         .groupby(['time','id','status'])['A']
         .sum()
         .astype(int)
         .reset_index(name='count'))

Very similar solution with no new column, but worse readable a bit:

df1 = ((df['status']=='Yes')
        .groupby([df['time'],df['id'],df['status']])
        .sum()
        .astype(int)
        .reset_index(name='count'))

print (df)
         time   id status  count
0  1451606400  id1    Yes      2
1  1456790400  id2     No      0
2  1456790400  id2    Yes      1

Solution 2:

If you don't mind a slightly different output format, you can pd.crosstab:

df = pd.DataFrame({'time': [1451606400]*2 + [1456790400]*3,
                   'id': ['id1']*2 + ['id2']*3,
                   'status': ['Yes', 'Yes', 'No', 'Yes', 'No']})

res = pd.crosstab([df['time'], df['id']], df['status'])

print(res)

status          No  Yes
time       id          
1451606400 id1   0    2
1456790400 id2   2    1

The result is a more efficient way to store your data as you don't repeat your index in a separate row for every "Yes" / "No" category.