How to fill missing values with similar rows median

user_10361602 0
Viewed 510

How can I fill missing values with similar rows median for titanic dataset?

I have a Age column where I have 80 missing values and I want to fill those missing values by taking the median of similar rows of there other columns. ['SibSp', 'Parch', 'Pclass']

Assume this eg:

    Age SibSp Parch Pclass
0   28   2      1     2
1  NaN   2      1     2
2   30   3      3     1
3   40   4      3     3
4   30   2      1     2
5   40   2      1     2

Now I want to fill the NaN in Age column by considering similar value in other three columns. In this case I have three rows with sample values as row 1.

   Age SibSp Parch Pclass
0   28   2      1     2
4   30   2      1     2
5   40   2      1     2

The median here for Age col is 30. So fill the NaN with 30.

Required output.

    Age SibSp Parch Pclass
0   28   2      1     2
1   30   2      1     2  # Fill with median of those above three rows.
2   30   3      3     1
3   40   4      3     3
4   30   2      1     2
5   40   2      1     2

This is something I wrote but it's not working.

# Filling missing values Of Age.
for i in combined.Age[combined.Age.isna()].index.tolist():
    age_med = combined.Age.median()
    mask1 = combined.SibSp.eq(combined.iloc[i].SibSp)
    mask2 = combined.Parch.eq(combined.iloc[i].Parch)
    mask3 = combined.Pclass.eq(combined.iloc[i].Pclass)
    age_pred = combined.Age[mask1 & mask2 & mask3].median()
    combined.iloc[i]['Age'] = age_pred

I'm sure there are better ways to do it using groupby or something but I couldn't figure it out. Can anyone help?

Thank you.

pandas python-3.x python
1 Answers

Use GroupBy.transform for return median with same size Series like original DataFrame, so is possible use fillna:

med = df.groupby(['SibSp', 'Parch', 'Pclass'])['Age'].transform('median')
df['Age'] = df['Age'].fillna(med)

Or similar solution with lambda function:

df['Age'] = (df.groupby(['SibSp', 'Parch', 'Pclass'])['Age']
               .transform(lambda x: x.fillna(x.median())))

print (df)
    Age  SibSp  Parch  Pclass
0  28.0      2      1       2
1  30.0      2      1       2
2  30.0      3      3       1
3  40.0      4      3       3
4  30.0      2      1       2
5  40.0      2      1       2
user_29010020
Related