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

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.

jezrael · Accepted Answer · 2022-05-13 10:51:15Z

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

answered May 13, 2022 at 10:51

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mayank Porwal · Accepted Answer · 2022-05-13 11:06:04Z

0

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

answered May 13, 2022 at 11:06

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related