Group by one column and take count of multiple categories in Pandas

user_5942100 0
Viewed 49

I have a dataset, df, where I would like to group by one column and then take the count of each category within a second column

name    location    sku
svc1    ny          hey1
svc2    ny          hey1
svc3    ny          hey1
svc4    ny          hey1
lo1     ny          ok1
lo2     ny          ok1
fab1    ny          hi
fab2    ny          hi
fab3    ny          hi
hello   ca          no
hello   ca          no

desired

location    sku     count
ny          hey1    4
ny          ok1     2
ny          hi      3
ca          no      2

doing

df2 = pd.DataFrame()
df2['sku'] = df.groupby('location')['sku'].nth(0)
df2['count'] = df.groupby('sku').count()

However, I am getting NAN for count, and I am not getting all of the data listed under sku.

Any suggestion is appreciated.

pandas numpy python
1 Answers

You are looking to group by two columns:

df.groupby(['location','sku']).size().reset_index(name='count')

Or groupby one column and value_counts the other:

# this should be slightly faster
(df.groupby('location')['sku'].value_counts()
      .reset_index(name='count'))

Output:

  location   sku  count
0       ca    no      2
1       ny  hey1      4
2       ny    hi      3
3       ny   ok1      2
user_42384080
Related