How to fill subsequent null values in pandas dataframe using previous rolling mean values?

user_11623103 0
Viewed 69

I have a dataframe like below.

df=pd.DataFrame({ 'month' : [1,2,3,4,5,6],
                  'temp'  : [50,60,40,np.nan,np.nan,np.nan]})

df

Output:

  month temp
0   1   50.0
1   2   60.0
2   3   40.0
3   4   NaN
4   5   NaN
5   6   NaN

I want to fill missing value using rolling average of previous two values. Now, in case of index 4(or month 5), I want to use calculated rolling mean at index 3 and index 2 value. So,

  • temp at index 3: 50 (~ avg(60,40))
  • temp at index 4: 45 (~ avg(40,50))

Expected output:

  month temp
0   1   50.0
1   2   60.0
2   3   40.0
3   4   50.0
4   5   45.0
5   6   47.5

I checked the documentation but there does not seem a way to do that. Any work around? Thank you.

missing-data rolling-computation pandas dataframe python
1 Answers

As mentioned by @NoobVB, a similar question was already solved in this post. You just have to adjust the code according to your problem. An alternative way to solve this for an extended version of your example data frame is given below:

import pandas as pd
import numpy as np

df=pd.DataFrame({'month': [1,2,3,4,5,6,7,8],
                'temp': [50,60,40,np.nan,np.nan,np.nan,20,np.nan]})

series = df["temp"]
for i in range(series.isna().sum()):
  shifted_series = series.rolling(2).mean().shift(periods=1)
  series = series.combine_first(shifted_series)

df['new_temp'] = series

df

--------------------------------------------
    month   temp    new_temp
0   1       50.0    50.00
1   2       60.0    60.00
2   3       40.0    40.00
3   4       NaN     50.00
4   5       NaN     45.00
5   6       NaN     47.50
6   7       20.0    20.00
7   8       NaN     33.75
--------------------------------------------
user_139213990
Related