3
$\begingroup$

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:enter image description here

I have tried this:

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

but I get an output which looks wrong.

| improve this question | | | | |
$\endgroup$
  • 1
    $\begingroup$ On what basis is count generated? $\endgroup$ – Ankit Seth Apr 3 '18 at 10:01
4
$\begingroup$

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.

| improve this answer | | | | |
$\endgroup$
  • $\begingroup$ He wants some counts to be zero also.., otherwise awesome solution.. $\endgroup$ – Aditya Apr 3 '18 at 9:06
  • $\begingroup$ 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. $\endgroup$ – FirstSlack Apr 3 '18 at 19:03
  • $\begingroup$ Glad if it is of help! Good luck. $\endgroup$ – Kiritee Gak Apr 4 '18 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.