pandas groupby on columns with aggregation

Question

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?

Look into pivot_table, df.pivot_table(index = 'userId', columns = 'category', values = 'count', fill_value=0) — Vaishali, Feb 12 '19 at 20:52

Jondiedoop · Accepted Answer · 2019-02-12T20:54:01.927

0

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

edited Feb 12 '19 at 20:54

answered Feb 12 '19 at 20:52

Jondiedoop

3,174
8
23

2

need `aggfunc='sum'` – ALollz Feb 12 '19 at 20:53
1

Thanks for the correction :) edited – Jondiedoop Feb 12 '19 at 20:54
Thanks @Jondiedoop, this was exactly what I wanted! – user5844628 Feb 12 '19 at 21:23

pandas groupby on columns with aggregation

1 Answers1