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
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
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!