Count of how many cast members have worked with other different actors/cast

user_3092377 0
Viewed 53

I have a dataframe:

title     |      cast 
------------------------------
movie1    |  cast1,cast2,cast3
movie2    |  cast4,cast1,cast6,cast7
movie3    |  cast4,cast3,cast5

pd.DataFrame({'movie': ['movie1','movie2','movie3'], 'cast': ['cast1,cast2,cast3','cast4,cast1,cast6,cast7','cast4,cast3,cast5']})

So, I want to get result something as :

cast   |      count
------------------------------
cast1  |  5 
cast2  |  2
cast3  |  4
cast4  |  5
cast5  |  2
cast6  |  3
cast7  |  3

To do that, 

df_cast = df.join(df.cast
              .str.strip(',')
              .str.split(',',expand=True)
              .stack()
              .reset_index(level=1,drop=True)
              .rename('cast_member')).reset_index(drop=True)

This would add a new column cast_member with each cell having just one cast member name in it. I tried using groupby('cast_member') but, I am not sure how to proceed after that.

enter image description here

I am new to pandas so I would really appreciate an answer even though it could be a simple one.

pandas dataframe python
1 Answers

Use GroupBy.transform for new column with count per movie first:

df_cast['cast_count'] = df_cast.groupby('movie')['movie'].transform('size')
print (df_cast)
    movie                     cast cast_member   cast_count
0  movie1        cast1,cast2,cast3       cast1            3
1  movie1        cast1,cast2,cast3       cast2            3
2  movie1        cast1,cast2,cast3       cast3            3
3  movie2  cast4,cast1,cast6,cast7       cast4            4
4  movie2  cast4,cast1,cast6,cast7       cast1            4
5  movie2  cast4,cast1,cast6,cast7       cast6            4
6  movie2  cast4,cast1,cast6,cast7       cast7            4
7  movie3        cast4,cast3,cast5       cast4            3
8  movie3        cast4,cast3,cast5       cast3            3
9  movie3        cast4,cast3,cast5       cast5            3

Then aggregate size with sum per cast_count and subtract for final count:

df = df_cast.groupby('cast_member')['cast_count'].agg(['size','sum'])
df1 = df['sum'].sub(df['size']).rename('count').reset_index()
print (df1)
  cast_member  count
0       cast1      5
1       cast2      2
2       cast3      4
3       cast4      5
4       cast5      2
5       cast6      3
6       cast7      3
user_29010020
Related