How to replace NaN values where the other columns meet a certain criteria?

user_10120211 0
Viewed 5326

I am working on the titanic datset from Kaggle and am trying to replace the NaN values in one column based on information from the other columns.

In my specific example I am trying to replace the unknown age of male, 1st class passengers with the average age of male, 1st class passengers.

How do I do this?

I have been able to segment the data and replace the null values of that new dataframe, but it doesn't carry over to the original dataframe and I am a bit unclear on how to make it do so.

Here is my code:

missingage_1stclass_male = pd.DataFrame(
    titanic[
        (titanic['Age'].isnull()) &
        (titanic['Pclass'] == 1) &
        (titanic['Sex'] == 'male')
    ]
)
missingage_1stclass_male.Age.fillna(40.5, inplace=True)

My original dataframe with all the values is named titanic.

kaggle pandas python
4 Answers

I am trying to replace the unknown age of male, 1st class passengers with the average age of male, 1st class passengers.

You can split the problem into 2 steps. First calculate the average age of male, 1st class passengers:

mask = (df['Pclass'] == 1) & (df['Sex'] == 'male')
avg_filler = df.loc[mask, 'Age'].mean()

Then update values satisfying your criteria:

df.loc[df['Age'].isnull() & mask, 'Age'] = avg_filler
user_92095460

You can group the data by required columns and fillna, something like

df['age'] = df.groupby(['pclass', 'sex']).age.apply(lambda x: x.fillna(x.mean()))

Edit: for filling null values of only specific rows

df.loc[((df.pclass == 1) & (df.sex == 'male') & (df.age.isnull())) , 'age'] = df.loc[((df.pclass == 1) & (df.sex == 'male') ) , 'age'].mean()
user_62873080

I think the .fillna() will help you with this

here is an example on how to use:

>>> df = pd.DataFrame([[np.nan, 2, np.nan, 0],
...                    [3, 4, np.nan, 1],
...                    [np.nan, np.nan, np.nan, 5],
...                    [np.nan, 3, np.nan, 4]],
...                    columns=list('ABCD'))
>>> df
     A    B   C  D
0  NaN  2.0 NaN  0
1  3.0  4.0 NaN  1
2  NaN  NaN NaN  5
3  NaN  3.0 NaN  4

>>> df.fillna(0)
A   B   C   D
0   0.0 2.0 0.0 0
1   3.0 4.0 0.0 1
2   0.0 0.0 0.0 5
3   0.0 3.0 0.0 4
user_52639670

You can simply select the rows whose columns meet certain criteria and then replace it as your need.

df[df['Pclass'] == 1 & df['Sex'] == 'male'].fillna(df['age'].mean())
user_85817810
Related