Add missing dates in pandas df, but date range has (valid) duplicates

user_14267930 0
Viewed 182

I have a dataset that has multiple values received per second - up to 100 DFS (no more, but not consistently 100). The challenge is that the date field did not capture time more granularly than second, so multiple rows have the same hh:mm:ss timestamp. These are fine, but I also have several seconds missing across the set, i.e., not showing at all.

Therefore my 2 initial columns might look like this, where I am missing the 54 sec step:

2020-08-24 03:36:53, 5
2020-08-24 03:36:53, 8
2020-08-24 03:36:53, 6
2020-08-24 03:36:55, 8

Because of the legit date "duplicates" and the information I need from this, I don't want to aggregate but I do need to create the missing seconds, insert them and fill (NaN, etc) so I can then manage them appropriately for aligning with other datasets.

The only way I can seem to do this is with a nested if loop which looks at the previous timestamp and if it is the same as the current cell (pt == ct) then no action, if it is 1 less (pt = (ct-1)) then no action but it if is more than the current cell by 2 or more, insert the missing (pt <= (ct-2)). This feels a bit cumbersome (though workable). Am I missing an easier way to do this?

I have checked a lot of "fill missing dates" threads on here as well as in various functions on pandas.pydata.org but reindexing and the most common date fills all seem to rely on dates not having duplicates. Any advice would be fantastic.

pandas datetime python
1 Answers

This can be solved by creating a pandas series containing all timepoints you want to consider and then merge this with the original dataframe.

For example:

start, end = df['date'].min(), df['date'].max()
all_timepoints = pd.date_range(start, end, freq='s').to_series(name='date')

df.merge(all_timepoints , on='date', how='outer', sort=True).fillna(0)

Will give:

          date          value
0   2020-08-24 03:36:53   5.0
1   2020-08-24 03:36:53   8.0
2   2020-08-24 03:36:53   6.0
3   2020-08-24 03:36:54   0.0
4   2020-08-24 03:36:55   8.0
user_75795470
Related