handling missing data in pandas python

Question

I have to impute missing values in the column named <Age> with the mean of the nearest available values above and below in the column <Age>.

If the Age column had values in order

NA,7,6,NA,7,8,NA,NA,NA,10,5,NA,NA,5,9,9,12,8,6,NA,NA

After imputation, column should look like 7,7,6,6.5,7,8,9,9,9,10,5,5,5,5,9,9,12,8,6,6,6

I know about filling value with mean, mode, or median forward filling, backward filling but not this one. I am new to this field and a student. Any help would be appreciated.

Thanks

Answer 1

IIUC you can simply use Pandas Series.interpolate() method:

Data:

In [8]: NA = np.nan

In [9]: s = pd.Series([NA,7,6,NA,7,8,NA,NA,NA,10,5,NA,NA,5,9,9,12,8,6,NA,NA])

In [10]: s
Out[10]:
0      NaN
1      7.0
2      6.0
3      NaN
4      7.0
5      8.0
6      NaN
7      NaN
8      NaN
9     10.0
10     5.0
11     NaN
12     NaN
13     5.0
14     9.0
15     9.0
16    12.0
17     8.0
18     6.0
19     NaN
20     NaN
dtype: float64

Solution:

In [11]: s.interpolate().bfill()
Out[11]:
0      7.0
1      7.0
2      6.0
3      6.5
4      7.0
5      8.0
6      8.5
7      9.0
8      9.5
9     10.0
10     5.0
11     5.0
12     5.0
13     5.0
14     9.0
15     9.0
16    12.0
17     8.0
18     6.0
19     6.0
20     6.0
dtype: float64

if you need rounded integers:

In [13]: s.interpolate().round().bfill().astype(int)
Out[13]:
0      7
1      7
2      6
3      6
4      7
5      8
6      8
7      9
8     10
9     10
10     5
11     5
12     5
13     5
14     9
15     9
16    12
17     8
18     6
19     6
20     6
dtype: int32