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

Question

I have the following dataframe:

   key1  key2 0    a   one 1    a   two 2    b   one 3    b   two 4    a   one 5    c   two 

Now, I want to group the dataframe by the key1 and count the column key2 with the value "one" to get this result:

   key1   0    a   2 1    b   1 2    c   0 

I just get the usual count with:

df.groupby(['key1']).size() 

But I don't know how to insert the condition.

I tried things like this:

df.groupby(['key1']).apply(df[df['key2'] == 'one']) 

But I can't get any further. How can I do this?

Answer 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