Add a new record for each missing second in a DataFrame with TimeStamp

Question

Be the next Pandas DataFrame:

|      date                           |     counter      |
|-------------------------------------|------------------|
|          2022-01-01 10:00:01        |        1         |
|          2022-01-01 10:00:04        |        1         |
|          2022-01-01 10:00:06        |        1         |

I want to create a function that, given the previous DataFrame, returns another similar DataFrame, adding a new row for each missing time instant and counter 0 in that time interval.

|      date                           |     counter      |
|-------------------------------------|------------------|
|          2022-01-01 10:00:01        |        1         |
|          2022-01-01 10:00:02        |        0         |
|          2022-01-01 10:00:03        |        0         |
|          2022-01-01 10:00:04        |        1         |
|          2022-01-01 10:00:05        |        0         |
|          2022-01-01 10:00:06        |        1         |

In case the initial DataFrame contained more than one day, you should do the same, filling in with each missing second interval for all days included.

Thank you for your help.

Answer 1

Use DataFrame.asfreq working with DatetimeIndex:

df = df.set_index('date').asfreq('1S', fill_value=0).reset_index()
print (df)
                 date  counter
0 2022-01-01 10:00:01        1
1 2022-01-01 10:00:02        0
2 2022-01-01 10:00:03        0
3 2022-01-01 10:00:04        1
4 2022-01-01 10:00:05        0
5 2022-01-01 10:00:06        1

Answer 2

You can also use df.resample:

In [314]: df = df.set_index('date').resample('1S').sum().fillna(0).reset_index()

In [315]: df
Out[315]: 
                 date  counter
0 2022-01-01 10:00:01        1
1 2022-01-01 10:00:02        0
2 2022-01-01 10:00:03        0
3 2022-01-01 10:00:04        1
4 2022-01-01 10:00:05        0
5 2022-01-01 10:00:06        1