How Can I Use Pandas To Round Inconsistent Timestamps To Five Minute Intervals And Fill Gaps?

Input - one year of weather data at irregular intervals (~ 5 minutes) Desired Output - one year of weather data at regular 5 min intervals I'm trying to clean up a year of weather

Solution 1:

Starting with:

ABdatetime2015-02-02 08:03:00  43.5NaN2015-02-02 08:08:00  43.402015-02-02 08:13:00  43.302015-02-02 08:18:00  43.272015-02-02 08:28:00  43.192015-02-02 08:33:00  43.0112015-02-02 08:38:00  43.092015-02-02 08:43:00  43.0112015-02-02 09:00:00  43.19DatetimeIndex:8entries,2015-02-02 08:03:00 to2015-02-02 08:43:00Datacolumns(total2columns):A8non-nullfloat64B7non-nullfloat64dtypes:float64(2)

You can .resample() the DateTimeIndex:

df.resample('5Min')ABdatetime2015-02-02 08:00:00  43.5NaN2015-02-02 08:05:00  43.402015-02-02 08:10:00  43.302015-02-02 08:15:00  43.272015-02-02 08:20:00   NaNNaN2015-02-02 08:25:00  43.192015-02-02 08:30:00  43.0112015-02-02 08:35:00  43.092015-02-02 08:40:00  43.0112015-02-02 08:45:00   NaNNaN2015-02-02 08:50:00   NaNNaN2015-02-02 08:55:00   NaNNaN2015-02-02 09:00:00  43.19

In case your datetime is actually of typestring, you can first:

df['datetime'] = pd.to_datetime(df.datetime)
df.set_index('datetime', inplace=True)