How to make a slice of DataFrame and "fillna" in specific slice using Python Pandas?

Question

The problem: let us take Titanic dataset from Kaggle. I have dataframe with columns "Pclass", "Sex" and "Age". I need to fill NaN in column "Age" with a median for certain group. If it is a woman from 1st class, I would like to fill her age with the median for 1st class women, not with the median for whole Age column.

The question is how to make this change in a certain slice?

I tried:

data['Age'][(data['Sex'] == 'female')&(data['Pclass'] == 1)&(data['Age'].isnull())].fillna(median)

where the "median" is my value, but nothing changes "inplace=True" didn't help.

Thanks alot!

Answer 1

I believe you need filter by masks and assign back:

data = pd.DataFrame({'a':list('aaaddd'),
                     'Sex':['female','female','male','female','female','male'],
                     'Pclass':[1,2,1,2,1,1],
                     'Age':[40,20,30,20,np.nan,np.nan]})

print (data)
    Age  Pclass     Sex  a
0  40.0       1  female  a
1  20.0       2  female  a
2  30.0       1    male  a
3  20.0       2  female  d
4   NaN       1  female  d
5   NaN       1    male  d

#boolean mask
mask1 = (data['Sex'] == 'female')&(data['Pclass'] == 1)

#get median by mask without NaNs
med = data.loc[mask1, 'Age'].median()
print (med)
40.0

#repalce NaNs
data.loc[mask1, 'Age'] = data.loc[mask1, 'Age'].fillna(med)
print (data)
    Age  Pclass     Sex  a
0  40.0       1  female  a
1  20.0       2  female  a
2  30.0       1    male  a
3  20.0       2  female  d
4  40.0       1  female  d
5   NaN       1    male  d

What is same as:

mask2 = mask1 &(data['Age'].isnull())

data.loc[mask2, 'Age'] = med
print (data)
    Age  Pclass     Sex  a
0  40.0       1  female  a
1  20.0       2  female  a
2  30.0       1    male  a
3  20.0       2  female  d
4  40.0       1  female  d
5   NaN       1    male  d

EDIT:

If need replace all groups NaNs by median:

data['Age'] = data.groupby(["Sex","Pclass"])["Age"].apply(lambda x: x.fillna(x.median()))
print (data)

    Age  Pclass     Sex  a
0  40.0       1  female  a
1  20.0       2  female  a
2  30.0       1    male  a
3  20.0       2  female  d
4  40.0       1  female  d
5  30.0       1    male  d