Using Pandas Data Frame how to apply count to multi level grouped columns?

Question

I have a data frame with multiple columns and I want to use count after group by such that it is applied to the combination of 2 or more columns. for example, let's say I have two columns:

user_id  product_name
1        Apple
1        Banana
1        Apple
2        Carrot
2        Tomato
2        Carrot
2        Tomato 
3        Milk
3        Cucumber

...

What I want to achieve is something like this:

user_id  product_name Product_Count_per_User
1        Apple        1 
1        Banana       2
2        Carrot       2
2        Tomato       2
3        Milk         1
3        Cucumber     1

I cannot get it. I tried this:

dcf6 = df3.groupby(['user_id','product_name'])['user_id', 'product_name'].count()

but it does not seem to get what I want and it is displaying 4 columns instead of 3. How to do to it? Thanks.

Answer 1

You are counting two columns at the same time, you can just use groupby.size:

(df.groupby(['user_id', 'Product_Name']).size()
   .rename('Product_Count_per_User').reset_index())

Or count only one column:

df.groupby(['user_id','Product_Name'])['user_id'].size()