Input missed values with mean of nearest neighbors in column
I use where
method and specify to replace any 0
with np.nan
. Once we have specified 0
to be NaN
we can use fillna
method. By using ffill
and bfill
we fill all NaN
with the corresponding previous and proceeding values, add them, and divide by 2.
df.where(df.replace(to_replace=0, value=np.nan),
other=(df.fillna(method='ffill') + df.fillna(method='bfill'))/2)
Number
Date
2012-01-31 00:00:00 676.0
2012-02-29 00:00:00 673.0
2012-03-31 00:00:00 670.0
2012-04-30 00:00:00 669.0
2012-05-31 00:00:00 668.0
#use apply to fill the Number with average from surrounding rows.
df['Number'] = df.reset_index().apply(lambda x: df.reset_index()\
.iloc[[x.name-1,x.name+1]]['Number'].mean() \
if (x.name>0) & (x.Number==0) else x.Number,axis=1).values
df
Out[1440]:
Number
Date
2012-01-31 00:00:00 676.0
2012-02-29 00:00:00 673.0
2012-03-31 00:00:00 670.0
2012-04-30 00:00:00 669.0
2012-05-31 00:00:00 668.0