Count If with Condition Pandas DataFrame

Question

I have a data frame:

   A    B    C   
0  xx   No   1
1  xx   No   2
2  xx   Yes  3
3  xx   Yes  4
4  xx   No   1
5  xx   No   2
6  xx   No   3
7  xx   Yes  4
8  xx   No   1
9  xx   No   2

If want to create a column that counts the number of times a number appear in column C, when column B is equal to Yes

so the output would be

   A    B    C   countifs   
0  xx   No   1      
1  xx   No   2
2  xx   Yes  3      1
3  xx   Yes  4      2
4  xx   No   1
5  xx   No   2
6  xx   No   3
7  xx   Yes  4      2
8  xx   No   1
9  xx   No   2

Is this possible?

jezrael · Accepted Answer · 2020-03-11 11:25:02Z

4

For count only Trues values use GroupBy.transform with sum only for filtered rows by mask:

m = df['B'].eq('Yes')
df['countifs'] = m.groupby(df.loc[m, 'C']).transform('sum').fillna('')
print (df)
    A    B  C countifs
0  xx   No  1         
1  xx   No  2         
2  xx  Yes  3        1
3  xx  Yes  4        2
4  xx   No  1         
5  xx   No  2         
6  xx   No  3         
7  xx  Yes  4        2
8  xx   No  1         
9  xx   No  2

edited Mar 11, 2020 at 11:25

answered Mar 11, 2020 at 11:18

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fred.schwartz Over a year ago

thanks alot. Is it possible to group this with another column as well? so C and A?

jezrael Over a year ago

@fred.schwartz - Yes, aonly assign column m like df['countifs'] = df.assign(m=m).groupby(['C', 'B'])['m'].transform('sum').fillna('')

yatu · Accepted Answer · 2020-03-11 11:20:23Z

3

Here's one approach:

y = df.B.eq('Yes')
df.loc[y, 'countifs'] = df[y].groupby('C').B.transform('count').values

print(df.fillna(''))

   A    B   C countifs
0  xx   No  1         
1  xx   No  2         
2  xx  Yes  3        1
3  xx  Yes  4        2
4  xx   No  1         
5  xx   No  2         
6  xx   No  3         
7  xx  Yes  4        2
8  xx   No  1         
9  xx   No  2

answered Mar 11, 2020 at 11:20

yatu

88.6k12 gold badges93 silver badges148 bronze badges

Comments

Jaroslav Bezděk · Accepted Answer · 2020-03-11 11:30:47Z

1

Here is another approach, using numpy.where, .value_counts(), and .map():

>>> col_C_cnts = df[df.B == 'Yes']['C'].value_counts()
>>> df['countifs'] = np.where(df.B == 'Yes', df['C'].map(col_C_cnts), pd.np.nan)
>>> print(df.fillna(''))
    A    B  C countifs
0  xx   No  1         
1  xx   No  2         
2  xx  Yes  3        1
3  xx  Yes  4        2
4  xx   No  1         
5  xx   No  2         
6  xx   No  3         
7  xx  Yes  4        2
8  xx   No  1         
9  xx   No  2

answered Mar 11, 2020 at 11:30

Jaroslav Bezděk

7,7156 gold badges34 silver badges59 bronze badges

Collectives™ on Stack Overflow

Count If with Condition Pandas DataFrame

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related