I had a movie dataset including 'budget' and 'genres' attributes.

I'd like to fill in the missing value of budget with the mean budget of each genre.

I first create two dataframes with or without budget.

BudgetNull = data[data['budget'].isnull()]
BudgetNotNull = data[data['budget'].notnull()]

Then, calculate the mean budget of each genre based on the BudgetNotNull dataset.

budget_of_genre = BudgetNotNull.groupby('genres')['budget'].mean()

enter image description here

Finally, I'd like to fill in the budget of BudgetNull based on its genre.

enter image description here

For instance, 'Marine Boy' is a action movie, therefore, fill in budget_of_genre['Action'].

How do I do this via for loop? Or there's other ways?


Using a transform as follows could work:

df["budget"] = df.groupby("genres")["budget"].transform(lambda x: x.fillna(x.mean()))

The mean calculation uses only the non-null values in its calculation. So the mean of each group's non-null values are imputed to that same group's null values.

See also this question on Stack Overflow.

  • $\begingroup$ Thank you Wes for your help. Does this method take the instances with missing budget into account as well? Can I remove them when calculating the mean value? $\endgroup$ – C. Ho Feb 11 at 14:35
  • $\begingroup$ It will fill only the null values using the mean of the non-null values. What do you mean by 'missing'? Deliberately missed or just null values? $\endgroup$ – Danny Feb 11 at 14:38
  • $\begingroup$ I see. Sorry I meant null value. Thanks !!! $\endgroup$ – C. Ho Feb 11 at 14:40

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.