[FIXED] pandas groupby on columns with aggregation

Issue

I have a dataframe like this:

userId   category  count
A        cat       24
B        dog       26
A        cat       32
C        bird      21
D        lion      6
D        cat       32
C        bird      22

I want the result to be this.

userId   cat   dog  bird lion 
A        56    0    0    0
B        0     26   0    0
C        0     0    43   0
D        32    0    0    6

It should groupBy userId and category should become columns. The category value should now display total count for each category.

How would I be able to do this using Pandas?

I've tried doing the following to group it by userId

groupData = df.groupby(['userId', 'category', 'count']).size()

But how do I to rotate and add up all the category counts?

Solution

Use a pivot_table:

df.pivot_table(
    index='userId',
    columns='category',
    values='count',
    aggfunc='sum',
    fill_value=0
)

# category  bird  cat  dog  lion
# userId                        
# A            0   56    0     0
# B            0    0   26     0
# C           43    0    0     0
# D            0   32    0     6

Answered By - Jondiedoop