pythonpandasdatabasenumpymean

Replace NaN with the average of the last 5 values - Pandas

I want to know how can I replace the NaN in my dataset with the last average of 5 last values.

Column A Column B
1 2
2 5
3 5
4 2
5 2
NaN 2
NaN 2
1 2
1 2
1 2
1 NaN
1 2
1 2

For example, in this case the first NaN will be the average of (1,2,3,4,5) and second NaN will be the average of (2,3,4,5, The value of the other NaN).

I have tried

df.fillna(df.mean())
Solution

  • As mentioned, it has been answered here, but the updated version for the latest pandas version is as follow:

    
data = {
    'col1': [1, 2, 3, 4, 5, np.nan, np.nan, 1, 1, 1, 1, 1, 1],
    'col2': [2, 5, 5, 2, 2, 2, 2, 2, 2, 2, np.nan, 2, 2]
}
df = pd.DataFrame(data)

window_size = 5
df = df.fillna(df.rolling(window_size + 1, min_periods = 1).mean())

    outputs:

        col1  col2
0    1.0   2.0
1    2.0   5.0
2    3.0   5.0
3    4.0   2.0
4    5.0   2.0
5    3.0   2.0
6    3.5   2.0
7    1.0   2.0
8    1.0   2.0
9    1.0   2.0
10   1.0   2.0
11   1.0   2.0
12   1.0   2.0