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

  • 1
    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
up vote 4 down vote accepted

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.