Pandas groupby each value from a column

Question

I have my dataset looking like this:

df = pd.DataFrame({"title":["movie1","movie2","movie3","movie4","movie5","movie6","movie7"],"genres":["Childrens Comedy","Comedy Drama","Western","Comedy Action","Action Childrens","Drama","Drama"],\
    "rating":[3,4,1,2,5,4,2],"user_id":[1,1,4,2,2,3,5], "gender":["F","F","F","M","M","M","M"]})

I would like to get the count of ratings given of each gender for each movie genre separately.

Expected output:

In the expected output we group by gender and want to count how many times each gender gave a rating in the specific movie genre (even if a movie has more movie genres).

Code until now but does not give the right output:

df.groupby(['genre','gender']).agg({"rating":"count"})

It doesn't give the right output as it groups only the genres that are fully the same. In that case only movie6 and movie7 will yell the correct results.

How do I group by each value in the genre column? I do not want to one hot encode them, as I already tried but the movie genres are so many in the real dataset that it simply doesn't work.

Thank you in advance!

user_2901002 · Answer

First use Series.str.split with reassing back to same column and DataFrame.explode, then get counts by GroupBy.size and add 0 for missing combinations by Series.unstack and DataFrame.stack trick:

df1 = (df.assign(genres = df['genres'].str.split())
         .explode('genres')
         .groupby(['genres','gender'])["rating"]
         .size()
         .unstack(fill_value=0)
         .stack()
         .sort_index(level=[1,0], ascending=[False, True])
         .reset_index(name='count')
         )
print (df1)
      genres gender  count
0     Action      M      2
1  Childrens      M      1
2     Comedy      M      1
3      Drama      M      2
4    Western      M      0
5     Action      F      0
6  Childrens      F      1
7     Comedy      F      2
8      Drama      F      1
9    Western      F      1

Pandas groupby each value from a column

1 Answers