Groupby and count of other column with condition

Question

I have a dataframe:

id    value
A       1 
A       0
A       1 
B       1
B       1
C       0

I'm trying to group by id and count occurances in value column such that I count no. of ones and no. of 0's in each group:

id   No. of 1's   No of 0's
A    2             1
B    1             0
C    0             1

I know of a way to groupby and use aggregate function

df.groupby('id').agg({'value': xxx})

But i think there should be a much better way to do this.

Use `df = pd.crosstab(df['id'],df['value']).rename(columns=lambda x: f"counts_of_{x}'s")` — jezrael, Sep 30 '20 at 13:09

score 0 · Answer 1 · answered Sep 30 '20 at 13:07

you need unstack and add_prefix/suffix

df.groupby(["id", "value"])["value"].count().unstack(1).fillna(0).add_prefix(
    "counts_of_"
).add_suffix("'s")


value  counts_of_0's  counts_of_1's
id                                 
A                1.0            2.0
B                0.0            2.0
C                1.0            0.0

Groupby and count of other column with condition

1 Answers1