Using this code:
import numpy as np
import pandas as pd
df = pd.read_csv('wind.txt', header=0, delim_whitespace= True, index_col = True)
The Dataframe is something like this:
Date Vel Dir
2016-07-12 16:15:00 2.8 1.8
2016-07-12 16:16:00 3.9 21.8
2016-07-12 16:17:00 9.8 4.8
2016-07-12 16:18:00 16.9 5.8
2016-07-12 16:19:00 17.0 7.1
2016-07-12 16:20:00 NaN NaN
2016-07-12 16:21:00 2.8 1.8
2016-07-12 16:22:00 3.9 21.8
... ... ...
... ... ...
2017-01-01 00:00:00 21.2 19.7
Sometimes the dataframe has a lot of missing data like here:
Date Vel Dir
2016-07-12 17:56:00 2.8 1.8
2016-07-12 17:57:00 NaN NaN
2016-07-12 17:58:00 9.8 4.8
2016-07-12 17:59:00 NaN NaN
2016-07-12 18:00:00 NaN NaN
2016-07-12 18:01:00 NaN NaN
2016-07-12 18:02:00 2.8 1.8
2016-07-12 18:03:00 NaN NaN
... ... ...
... ... ...
2017-01-01 00:00:00 21.2 19.7
The first objective was to create a new dataframe, but instead time in 1 min use time in 3 hours. Using this code:
df2 = pd.DataFrame({'Date':pd.date_range(start='2016-07-12 18:00:00',end='2017-01-01 00:00:00',freq='3H')})
Till here everything was ok, this generate a dataframe without Vel and Dir as spected and it's like that:
Date
2016-07-12 18:00:00
2016-07-12 21:00:00
2016-07-13 00:00:00
2016-07-13 03:00:00
... ...
... ...
2017-01-01 00:00:00
The objective now is to fill df2 with Vel and Dir values of df1 based on Date, but some data is missing. Knowing this I've tried merge_asof in this code:
df3 = pd.merge_asof(df2,df1, on='Date', tolerance=pd.Timedelta("5 minutes")).fillna('NaN')
It worked, but it only fills missing data with the first row before. The objective is to use values in rows after and before to fill missing data. Something like that:
Date Vel Dir
2016-07-12 17:56:00 2.8 1.8
2016-07-12 17:57:00 NaN NaN
2016-07-12 17:58:00 9.8 4.8
2016-07-12 17:59:00 NaN NaN
2016-07-12 18:00:00 NaN NaN
2016-07-12 18:01:00 NaN NaN
2016-07-12 18:02:00 2.8 1.8
2016-07-12 18:03:00 NaN NaN
... ... ...
... ... ...
2017-01-01 00:00:00 21.2 19.7
Expected output:
2016-07-12 18:00:00 9.8 4.8
But if the dataframe is something like:
Date Vel Dir
2016-07-12 17:56:00 NaN NaN
2016-07-12 17:57:00 NaN NaN
2016-07-12 17:58:00 NaN NaN
2016-07-12 17:59:00 NaN NaN
2016-07-12 18:00:00 NaN NaN
2016-07-12 18:01:00 NaN NaN
2016-07-12 18:02:00 2.8 1.8
2016-07-12 18:03:00 NaN NaN
... ... ...
... ... ...
2017-01-01 00:00:00 21.2 19.7
Expected output:
2016-07-12 18:00:00 2.8 1.8
The objective is do this to all dataframe, if does not exist any value in 5 min before or after Vel and Dir must be NaN. Would be helpful if someone could help.