I would like to fill missing values in a pandas dataframe with the average of the cells directly before and after the missing value. So if it was [1, NaN, 3], the NaN value would be 2 because (1 + 3)/2. I could not find any way to do this with Pandas or Scikit-learn. Is there any way to do this?
4 Answers
df = pd.DataFrame({'val': [1,np.nan, 4, 5, np.nan, 10]})
print(df)
gives
val
0 1.0
1 NaN
2 4.0
3 5.0
4 NaN
5 10.0
Then you can do
df = pd.concat([ df.fillna(method='ffill'), df.fillna(method='bfill')], axis=1).mean(axis=1)
which gives your desired results:
0 1.0
1 2.5
2 4.0
3 5.0
4 7.5
5 10.0
dtype: float64
This method will work even when there are multiple nan's in a row, and also if the nan's are at the beginning or end of the data. Works on one column at a time
Related