1
$\begingroup$

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

$\endgroup$
4
  • 1
    $\begingroup$ 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 ? $\endgroup$ Jan 26, 2017 at 19:05
  • $\begingroup$ 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? $\endgroup$
    – Stereo
    Jan 27, 2017 at 10:10
  • $\begingroup$ 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) $\endgroup$ Jan 27, 2017 at 10:42
  • $\begingroup$ 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 $\endgroup$ Jan 27, 2017 at 10:50

1 Answer 1

5
$\begingroup$

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
$\endgroup$
3
  • $\begingroup$ thnx Maxu its works $\endgroup$ Jan 27, 2017 at 11:01
  • $\begingroup$ sure, and thnx a lot man $\endgroup$ Jan 27, 2017 at 11:08
  • $\begingroup$ @AbhishekPathak, you are very welcome :) $\endgroup$ Jan 27, 2017 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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