Count number of categories after GROUP BY in SQL or Pandas

user_4435573 0
Viewed 124

I have a dataframe df :

ORDERID    PRODUCTTYPE  PRODUCTID  PRODUCT
123         Fruits         2       Banana
123         Vegetables     3       Tomato
123         Vegetables     3       Onion
321         Fruits         2       Grapes
321         Fruits         2       Avocado

I need output as 

ORDERID  FRUITS  VEGETABLES
123       1          2
321       2          0

Is there any modification i need to perform to the groupby ? I'm doing 

df.groupby('ORDERID'['PRODUCTTYPE'].nunique().reset_index(name="count")

but thats just counting the number of categories

pandas pivot group-by oracle sql
4 Answers

In SQL, you can use conditional aggregation:

select
    orderid,
    sum(case when producttype = 'Fruits' then 1 else 0 end) fruits,
    sum(case when producttype = 'Vegetables' then 1 else 0 end) vegetables
from mytable
group by orderid

Or, if your database supports the modern filter clause to aggregate functions:

select
    orderid,
    count(*) filter(where producttype = 'Fruits') fruits,
    count(*) filter(where producttype = 'Vegetables') vegetables
from mytable
group by orderid
user_106767160

A combination of groupby().value_counts() and unstack():

(df.groupby('ORDERID')['PRODUCTTYPE']
   .value_counts()
   .unstack('PRODUCTTYPE', fill_value=0)
)

Or pivot_table:

df.pivot_table(index='ORDERID', 
               columns='PRODUCTTYPE', 
               values='PRODUCTID', 
               aggfunc='count',
               fill_value=0)

Output:

PRODUCTTYPE  Fruits  Vegetables
ORDERID                        
123               1           2
321               2           0
user_42384080 
select 
ORDERID,
count(Fruits) over (partition by ORDERID) as Fruits,
count(Vegetables) over (partition by ORDERID) as Vegetables
from 
table
group by ORDERID;
user_130421740

One function in pandas pd.crosstab

pd.crosstab(df.ORDERID,df.PRODUCTTYPE)
PRODUCTTYPE  Fruits  Vegetables
ORDERID                        
123               1           2
321               2           0
user_79645270
Related