close

How to do a conditional count after groupby on a Pandas Dataframe?

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to do a conditional count after groupby on a Pandas Dataframe in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

Table of Contents

How to do a conditional count after groupby on a Pandas Dataframe?

  1. How to do a conditional count after groupby on a Pandas Dataframe?

    You can count the occurence of 'one' for the groupby dataframe, in the column 'key2' like this: df.groupby('key1')['key2'].apply(lambda x: x[x == 'one'].count())

  2. conditional count after groupby on a Pandas Dataframe

    You can count the occurence of 'one' for the groupby dataframe, in the column 'key2' like this: df.groupby('key1')['key2'].apply(lambda x: x[x == 'one'].count())

Method 1

I think you need add condition first:

#if need also category c with no values of 'one'
df11=df.groupby('key1')['key2'].apply(lambda x: (x=='one').sum()).reset_index(name='count')
print (df11)
  key1  count
0    a      2
1    b      1
2    c      0

Or use categorical with key1, then missing value is added by size:

df['key1'] = df['key1'].astype('category')
df1 = df[df['key2'] == 'one'].groupby(['key1']).size().reset_index(name='count') 
print (df1)
  key1  count
0    a      2
1    b      1
2    c      0

If need all combinations:

df2 = df.groupby(['key1', 'key2']).size().reset_index(name='count') 
print (df2)
  key1 key2  count
0    a  one      2
1    a  two      1
2    b  one      1
3    b  two      1
4    c  two      1

df3 = df.groupby(['key1', 'key2']).size().unstack(fill_value=0)
print (df3)
key2  one  two
key1          
a       2    1
b       1    1
c       0    1

Method 2

You can count the occurence of ‘one’ for the groupby dataframe, in the column ‘key2’ like this: df.groupby('key1')['key2'].apply(lambda x: x[x == 'one'].count())

yield

key1
a    2
b    1
c    0
Name: key2, dtype: int64

Summery

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read