How do I group by a column, and count values in separate columns (Pandas)

Question

Here's an example data:

data = [['a1', 1, 'a'], ['b1', 2, 'b'], ['a1', 3, 'a'], ['c1', 4, 'c'], ['b1', 5, 'a'], ['a1', 6, 'b'], ['c1', 7, 'a'], ['a1', 8, 'a']] 

df = pd.DataFrame(data, columns = ['user', 'house', 'type']) 

user house type
a1     1    a
b1     2    b
a1     3    a
c1     4    c
b1     5    a
a1     6    b
c1     7    a
a1     8    a

The final output that I want is this (the types need to be their own columns):

user houses a b c    
a1      4   3 1 0
b1      2   1 1 0
c1      2   1 0 1

Currently, I'm able to get it by using the following code:

house = df.groupby(['user']).agg(houses=('house', 'count'))
a = df[df['type']=='a'].groupby(['user']).agg(a=('type', 'count'))
b = df[df['type']=='b'].groupby(['user']).agg(b=('type', 'count'))
c = df[df['type']=='c'].groupby(['user']).agg(c=('type', 'count'))

final = house.merge(a,on='user', how='left').merge(b,on='user', how='left').merge(c,on='user', how='left')

Is there a simpler, cleaner way to do this?

Answer 1

Here is one way using get_dummies() with groupby() and sum.

df['house']=1
df.drop('type',axis=1).assign(**pd.get_dummies(df['type'])).groupby('user').sum()

---

      house  a  b  c
user                
a1        4  3  1  0
b1        2  1  1  0
c1        2  1  0  1

Answer 2

I will do crosstab with margins=True

pd.crosstab(df.user,df.type,margins=True,margins_name='House').drop('House')
Out[51]: 
type  a  b  c  House
user                
a1    3  1  0      4
b1    1  1  0      2
c1    1  0  1      2