Pandas groupby and agg by condition

Question

df.groupby(['Month']).agg({'Status' : ['count']})

The line above groups the dataframe by Month and counts the number of Status for each month. Is there a way to only get a count where Status=X? Something like the incorrect code below:

df.groupby(['Month']).agg({'Status' == 'X' : ['count']})

Essentially, I want a count of how many Status are X for each month.

Consumable

Please add some sample data in text format for us to to check – Keenankeene

Have you tried .filter method. I can't comment much without the sample data – Keenankeene

#27488580 , have you checked this link. It explains how to use .apply to do what you want. – Keenankeene

Answer 1

A short way

(df.Status == 'X').groupby(df.Month).sum()

A long way

df.where(df.Status == 'X').groupby('Month').Status.count()

Hematuria

Answer 2

Let us do something different

pd.crosstab(df.Month,df.Status)['X']

Reinstate

me to! @Andy L. – Undine

@ansev: I like Wen's different things. He usually comes up with surprisingly refreshed solution which I never think of. I still remember his solution using sum to concatenate series of lists which I never thought of before :) – Hematuria

Answer 3

Also can use lambda function

df.groupby('Month').agg(lambda x: (x=='X').sum())

or

df.groupby('Month').Status.agg(lambda x: (x=='X').sum())

Undine

Answer 4

You an do df.loc[df.Status=='X'].groupby(['Month']).agg({'Status' : ['count']})

Jankell