I have data on movies and all movies have IMDB score, however some do not have a meta critic score

Eg:

I want to fill in the null values in the meta critic score with the mean of the values of movies that have the same IMDB score so the null value in this table should be replaced by the mean of movies B,C,E

How would I achieve this with Numpy / Pandas?

I looked up online and the closest solution I could find was averaging all the metacritic scores and replacing the null values with that Average.

CodePudding user response：

groupby fillna

df.groupby('IMDB Score')['Meta Score'].apply(lambda x: x.fillna(x.mean()))

output:

0    86.0
1    90.0
2    89.0
3    91.0
4    66.0
5    44.0
Name: Meta Score, dtype: float64

make result to Meta Score column

CodePudding user response：

You can sort the columns with missing values then do a forward fill:

df['Meta Score'] = df.groupby(['Name','IMDB Score'])['Meta Score'].ffill()

CodePudding user response：

df.groupby('IMDB Score')['Meta Score'].transform(lambda value: value.fillna(value.mean()))