Fill missing value in different columns of dataframe using mean or median of last n values

user_1103956 0
Viewed 60

I have a dataframe which contains timeseries data. What i want to do is efficiently fill all the missing values in different columns by substituting with a median value using timedelta of say "N" mins. E.g if for a column say i have data for 10:20, 10:21,10:22,10:23,10:24,.... and data in 10:22 is missing then with timedelta of say 2 mins i would want it to be filled by median value of 10:20,10:21,10:23 and 10:24.

One way i can do is :

for all column in dataframe:
      Find index which has nan value
      for all index which has nan value:
          extract all values using between_time with index-timedelta and index_+deltatime
          find the media of extracted value
          set value in the index with that extracted median value.

This looks like 2 for loops running and not a very efficient one. Is there a efficient way to do it.

Thanks

pandas time-series dataframe python-3.x
1 Answers

IIUC you can resample your time column, then fillna with rolling window set to center:

# dummy data setup
np.random.seed(500)

n = 2

df = pd.DataFrame({"time":pd.to_timedelta([f"10:{i}:00" for i in range(15)]),
                   "value":np.random.randint(2, 10, 15)})

df = df.drop(df.index[[5,10]]).reset_index(drop=True)

print (df)

       time  value
0  10:00:00      4
1  10:01:00      9
2  10:02:00      3
3  10:03:00      3
4  10:04:00      8
5  10:06:00      9
6  10:07:00      2
7  10:08:00      9
8  10:09:00      9
9  10:11:00      7
10 10:12:00      3
11 10:13:00      3
12 10:14:00      7

s = df.set_index("time").resample("60S").asfreq()

print (s.fillna(s.rolling(n*2+1, min_periods=1, center=True).mean()))

          value
time           
10:00:00    4.0
10:01:00    9.0
10:02:00    3.0
10:03:00    3.0
10:04:00    8.0
10:05:00    5.5
10:06:00    9.0
10:07:00    2.0
10:08:00    9.0
10:09:00    9.0
10:10:00    7.0
10:11:00    7.0
10:12:00    3.0
10:13:00    3.0
10:14:00    7.0
user_92844230
Related