Python get value counts from multiple columns and average from another column

user_11918314 0
Viewed 499

I have a dataframe with the following columns

Movie    Rating  Genre_0     Genre_1    Genre_2
MovieA   8.9     Action      Comedy     Family
MovieB   9.1     Horror      NaN        NaN
MovieC   4.4     Comedy      Family     Adventure
MovieD   7.7     Action      Adventure  NaN
MovieE   9.5     Adventure   Comedy     NaN
MovieF   7.5     Horror      NaN        NaN
MovieG   8.6     Horror      NaN        NaN

I'd like get a dataframe which has value counts for each genre and the average rating for each time the genre appears

Genre     value_count   Average_Rating
Action    2             8.3  
Comedy    3             7.6
Horror    3             8.4
Family    2             6.7
Adventure 3             7.2

I have tried the following code and am able to get the value counts. However, am unable to get the average rating of each genre based on the number of times each genre appears. Any form of help is much appreciated, thank you.

#create a list for the genre columns
genre_col = [col for col in df if col.startswith('Genre_')]

#get value counts of genres
genre_counts = df[genre_col].apply(pd.Series.value_counts).sum(1).to_frame(name='Count')
genre_counts.index.name = 'Genre'

genre_counts = genre_counts.reset_index()
pandas-groupby pandas series dataframe python
2 Answers

You can .melt the dataframe then group then melted frame on genre and aggregate using a dictionary that specifies the columns and their corresponding aggregation functions:

# filter and melt the dataframe
m = df.filter(regex=r'Rating|Genre').melt('Rating', value_name='Genre')

# group and aggregate
dct = {'Value_Count': ('Genre', 'count'), 'Average_Rating': ('Rating', 'mean')}
df_out = m.groupby('Genre', as_index=False).agg(**dct)

>>> df_out

       Genre  Value_Count  Average_Rating
0     Action            2            8.30
1  Adventure            3            7.20
2     Comedy            3            7.60
3     Family            2            6.65
4     Horror            3            8.40
user_128331660

The process of encoding the genre to their value counts is frequency encoding it can be done with this code

df_frequency_map = df.Genre_0.value_counts().to_dict()
df['Genre0_frequency_map'] = df.Genre_0.map(df_frequency_map)

The add the average as a feature in your dataset I think you can just perform the same thing but calculate the average before performing to the to_dict() function.

df_frequency_map = df.df.Genre_0.value_counts().mean().to_dict()
df['Genre0_mean_frequency_map'] = df.Genre_0.map(df_frequency_map)
user_124170320
Related