How to group by, count if (or sum if) on new columns in pandas?

Question

How to group by, count if (or sum if) and have the results in individual new columns in pandas dataframe?

for example, I have the data in this format.

Date	animal
2021-01-01	dog
2021-01-01	dog
2021-01-02	cat
2021-01-02	dog
2021-01-03	mouse
2021-01-03	dog
2021-01-03	cat

in sql i would do something like this:

select date, sum(if(animal="dog",1,0) as dog, sum(if(animal="cat",1,0) as cat, sum(if(animal="mouse",1,0) as mouse

to get the desired result:

Date	dog	cat	mouse
2021-01-01	2	0	0
2021-01-02	1	1	0
2021-01-03	1	1	1

Nk03 · Answer 1 · 2021-05-28T16:19:52.797

You can do it in this way:

TRY pivot_table to get the required.
Use rename_axis to remove the axis name.
Finally reset the index.

df = (
    df.reset_index()
    .pivot_table(
        index='Date',
        columns='animal',
        values='index',
        aggfunc='count',
        fill_value=0)
    .rename_axis(columns=None)
    .reset_index()
)

OUTPUT:

         Date  cat  dog  mouse
0  2021-01-01    0    2      0
1  2021-01-02    1    1      0
2  2021-01-03    1    1      1

How to group by, count if (or sum if) on new columns in pandas?

1 Answers1