Pandas: How can I fill in the n/a with the mean of previous none-empty value and next none-empty value

user_6166551 0
Viewed 690

I have some N/A value in my dataframe

df = pd.DataFrame({'A':[1,1,1,3],
              'B':[1,1,1,3],
              'C':[1,np.nan,3,5],
              'D':[2,np.nan, np.nan, 6]})
print(df)

    A   B   C   D
0   1   1   1.0 2.0
1   1   1   NaN NaN
2   1   1   3.0 NaN
3   3   3   5.0 6.0

How can I fill in the n/a value with the mean of its previous non-empty value and next non-empty value in its column? For example, the second value in column C should be filled in with (1+3)/2= 2

Desired Output:

    A   B   C   D
0   1   1   1.0 2.0
1   1   1   2.0 4.0
2   1   1   3.0 4.0
3   3   3   5.0 6.0

Thanks!

data-science pandas python
1 Answers

Use ffill and bfill for replace NaNs by forward and back filling, then concat and groupby by index with aggregate mean:

df1 = pd.concat([df.ffill(), df.bfill()]).groupby(level=0).mean()
print (df1)
   A  B    C    D
0  1  1  1.0  2.0
1  1  1  2.0  4.0
2  1  1  3.0  4.0
3  3  3  5.0  6.0

Detail:

print (df.ffill())
   A  B    C    D
0  1  1  1.0  2.0
1  1  1  1.0  2.0
2  1  1  3.0  2.0
3  3  3  5.0  6.0

print (df.bfill())
   A  B    C    D
0  1  1  1.0  2.0
1  1  1  3.0  6.0
2  1  1  3.0  6.0
3  3  3  5.0  6.0
user_29010020
Related