How to count grouped occurrences?

My dataset has 2 columns:

1) Gender( that has 1 or 2)

2) Rating1(That has 3 ratings (1,2,3))

rating1: 1,2,3,3,2

gender: 2,1,2,2,1


I want output as:

I have tried this:

nutri.groupby(['Rating1','Gender']).nunique()


but I get an output which looks wrong.

• On what basis is count generated? Apr 3 '18 at 10:01

You can do that by

>>> import pandas as pd
>>> m = pd.DataFrame({'gender': [1, 2, 2, 1, 1, 2, 1], 'rating': [3, 4, 2, 1, 3, 1, 5]})
>>> m.groupby(['rating','gender']).size().to_frame('count').reset_index()
rating  gender  count
0       1       1      1
1       1       2      1
2       2       2      1
3       3       1      2
4       4       2      1
5       5       1      1


Hope this is what you want to pull.

Edit: As mentioned, I did not account for zero values.

You need to do one more step additional to get what you want. Finding the combinations that are missing and then joining it. The one liner solution would be

>>> from itertools import product
>>> m.groupby(['rating', 'gender']).size().to_frame('count').reset_index().merge(
pd.DataFrame(list(set([i for i in product(*[m.gender, m.rating])])), columns=['gender', 'rating']),
on=['rating', 'gender'],
how='right').fillna(value=0)
rating  gender  count
0       1       1    1.0
1       1       2    1.0
2       2       2    1.0
3       3       1    2.0
4       4       2    1.0
5       5       1    1.0
6       2       1    0.0
7       4       1    0.0
8       3       2    0.0
9       5       2    0.0


Explanation

Get the original grouped counts as mentioned before the first edit but this time you need to join with the combinations that are missing to get the zero counts. Use itertools.product to get all combinations of gender and rating and right join it with original grouped frame on rating and gender to get merged DataFrame which has numpy.na values if no count is present and then use fillna method to fill it with zero. The only loophole is if a rating say 4 is not there in the original data, there is no combination that follows it.

Hope this helps.

• He wants some counts to be zero also.., otherwise awesome solution.. Apr 3 '18 at 9:06
• I was actually working on a Big Dataset and I don't really need a count for 0 If anything I will use fillna(0.0). I was interested in getting the format. like the example in above question: If the rating is 2.0, how many are men(1) and how many are women (2). In R, there is this 'dplyr ' package which works so smooth, I wanted to learn Python for job interviews. Apr 3 '18 at 19:03
• Glad if it is of help! Good luck. Apr 4 '18 at 2:49