Trying to fill null values with sub-grouped mean value using pandas fillna() and groupby().transform() is doing nothing with the null values

Question

I'm doing EDA on the US police shooting dataset where I'm stuck with filling the 482 null values for the age column. Since no strategy was mentioned on how to fill them, I want to fill the null values with the mean age of the respective race in a gender to which the victim belongs.

There are a total of 7729 rows. Each victims/person belongs to one of two genders- male and female, as well as to one of the six races given. The following groupby() result gives a nice summary of the races, sub-grouped by gender and lastly, the mean age of that gender in that race:

mean_age_by_race_gender = df.groupby(['race','gender'])['age'].mean().round(2)

race  gender
A     F         43.67
      M         36.04
B     F         34.03
      M         32.80
H     F         32.47
      M         33.65
N     F         31.20
      M         32.62
O     F         25.00
      M         33.14
W     F         39.81
      M         40.08
Name: age, dtype: float64

That means, if a White male's age is NaN it should be replaced with the mean white male age, i.e, 40.08.

After some searching, I found the most mentioned way of performing the null replacement is by using this piece of code, which contains fillna() and groupby().transform():

df["age"] = df.groupby(['race','gender'])['age'].transform(lambda x: x.fillna(x.mean()))

But this code didn't impute any null age with the mean of the gender in its race, as I wanted! I did the following change to the above code:

df["new_age"] = df.groupby(['race','gender'])['age'].transform(lambda x:x.fillna(x.mean()))

Here, it's creating a new column new_age; if I compare age and new_age side by side, they both show the same number of null values! Without imputing them!

I tried changing the code to the following, but it's still doing the same thing as before:

df["age"] = df['age'].fillna(df.groupby(['race','gender'])['age'].transform('mean'))

I am clueless. How can I replace/impute only the null ages with the mean age of the gender in a race to which the victim belongs!

Answer 1

The groupby + transform will not work in the case, we want to impute null values since transform aims to transform all the values of the column.

Its difficult to determine what's wrong with your current piece of code because its multilevel groupby. But you could rather loop through all the races, query based on gender, calculate nanmean age and fillna with the respective race + gender mean age

for r in races:
    f_avg = df[ (df['race']==r')&(df['gender']=='F') ]['age'].mean() #df.mean() by default skips NaNs
    m_avg = df[ (df['race']==r')&(df['gender']=='M') ]['age'].mean()
    # Impute NaNs manually
    df[ (df['race']==r')&(df['gender']=='F') ]['age'].fillna(f_avg, inplace=True)
    df[ (df['race']==r')&(df['gender']=='M') ]['age'].fillna(m_avg, inplace=True)