How to incrementally fill missing timestamps in a pandas dataframe?

user_4139143 0
Viewed 653

I have a pandas dataframe with some missing timestamps that are spaced regularly apart at minute intervals as below

df
time                  value
2020-03-04 17:00:00   -0.923
2020-03-04 17:01:00   -0.829
....

The issue is that, in some places the time column is missing (the value column has no missing values).

2020-03-04 22:08:00  0.023
NaN                  0.039
NaN                  0.104
2020 03-04 22:11:00  0.192

How could I forward fill the NaN's in the time column so that they would increment the time correctly at each row? The desired output using the example above would look like

2020-03-04 22:08:00  0.023
2020-03-04 22:09:00  0.039
2020-03-04 22:10:00  0.104
2020 03-04 22:11:00  0.192
pandas timestamp python
2 Answers

Use Series.interpolate with convert to native format - unix times:

df['date'] = (pd.to_datetime(df['date'].dropna()
                                       .astype(np.int64)
                                       .reindex(df.index).interpolate()))
print (df)
                 date    col
0 2020-03-04 22:08:00  0.023
1 2020-03-04 22:09:00  0.039
2 2020-03-04 22:10:00  0.104
3 2020-03-04 22:11:00  0.192
user_29010020

One possible way is to convert the data into float, interpolate and convert back:

# convert to datetime if not already is
df['time'] = pd.to_datetime(df['time'])

old_time = pd.to_datetime('1970-01-01')
interpolated = df['time'].sub(old_time).dt.total_seconds().interpolate()

df['time'] = pd.to_timedelta(interpolated, unit='s') + old_time

Output:

                 time  value
0 2020-03-04 22:08:00  0.023
1 2020-03-04 22:09:00  0.039
2 2020-03-04 22:10:00  0.104
3 2020-03-04 22:11:00  0.192
user_42384080
Related