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.

Please add some sample data in text format for us to to check — nitin3685, Nov 07 '19 at 01:31
Have you tried `.filter` method. I can't comment much without the sample data — nitin3685, Nov 07 '19 at 01:35
https://stackoverflow.com/questions/27488080/python-pandas-filter-rows-after-groupby , have you checked this link. It explains how to use `.apply` to do what you want. — nitin3685, Nov 07 '19 at 01:37

score 7 · Answer 1 · answered Nov 07 '19 at 01:50

7

Let us do something different

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

answered Nov 07 '19 at 01:50

BENY

317,841
20
164
234

1

me to! @Andy L. – ansev Nov 07 '19 at 02:06
2

@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 :) – Andy L. Nov 07 '19 at 02:17

score 6 · Accepted Answer · answered Nov 07 '19 at 01:36

6

A short way

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

A long way

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

answered Nov 07 '19 at 01:36

Andy L.

24,909
4
17
29

score 6 · Answer 3 · answered Nov 07 '19 at 02:29

6

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())

answered Nov 07 '19 at 02:29

ansev

30,322
5
17
31

score 1 · Answer 4 · answered Nov 07 '19 at 01:35

1

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

answered Nov 07 '19 at 01:35

moys

7,747
2
11
42

Pandas groupby and agg by condition

4 Answers4