# handling missing data in pandas python

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

• So you know what to do and what is your output. What seems to be the problem here exactly? Are u asking for something other than mean of nearest values type imputation ? – Kiritee Gak Jan 26 '17 at 19:05
• Welcome to Datascience SE. It is not clear what your question is. Could you describe to us what you are looking for so people can help you better? – Stereo Jan 27 '17 at 10:10
• i have a data set with multiple missing value . i want to replace that values with the mean of a previous (not a NaN ) value for example i have 4,Nan,5 than i want to replace Nan with 4.5 that is the mean of 4 and 5 ((4+5)/2) – Abhishek Pathak Jan 27 '17 at 10:42
• and in the case i have more than one Nan value consecutive i want to replace all of them with the mean of previous not NaN value for eaxample in 4,5,NaN,NaN,NaN,9,6 the NaN will be replaced by a 7 because ((5+9)/2=7) as it is the mean of previous Not NaN values and new data becomes 4,5,7,7,7,9,6 – Abhishek Pathak Jan 27 '17 at 10:50

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

• thnx Maxu its works – Abhishek Pathak Jan 27 '17 at 11:01
• sure, and thnx a lot man – Abhishek Pathak Jan 27 '17 at 11:08
• @AbhishekPathak, you are very welcome :) – MaxU Jan 27 '17 at 11:10