Home > Software engineering >  Replace NaN with the average of the last 5 values - Pandas
Replace NaN with the average of the last 5 values - Pandas

Time:11-04

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())

CodePudding user response:

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
  • Related