Add incrementing seconds to DateTime column Pandas

April 4, 2023

I have the following type of df:

    Date_time         Col1
0 2023-03-04 10:30:00  10
1 2023-03-04 10:30:00  11
2 2023-03-04 10:30:00  21
3 2023-03-04 10:30:00  54
4 2023-03-04 10:30:00  12 
5 2023-03-04 10:30:00  13
6 2023-03-04 10:30:00  21
...
58 2023-03-04 10:30:00  22
59 2023-03-04 10:30:00  21
60 2023-03-04 10:31:00  25
61 2023-03-04 10:31:00  21
...

For some reason, the seconds show 0 throughout the df, however in fact they must be incremented by 1 second.

I would like to add 1 second increment to Date_time column and keep incrementing up to 59 seconds, then reset it as the minute changes. Please see below the desired outcome.

    Date_time         Col1
0 2023-03-04 10:30:00  10
1 2023-03-04 10:30:01  11
2 2023-03-04 10:30:02  21
3 2023-03-04 10:30:03  54
4 2023-03-04 10:30:04  12 
5 2023-03-04 10:30:05  13
6 2023-03-04 10:30:06  21
....
58 2023-03-04 10:30:58  22
59 2023-03-04 10:30:59  21
60 2023-03-04 10:31:00  25
61 2023-03-04 10:31:01  21
...

>Solution :

Use a groupby.cumcount and TimedeltaIndex:

df['Date_time'] = pd.to_datetime(df['Date_time'])

df['Date_time'] += pd.TimedeltaIndex(df.groupby('Date_time').cumcount(), unit='s')

Output:

             Date_time  Col1
0  2023-03-04 10:30:00    10
1  2023-03-04 10:30:01    11
2  2023-03-04 10:30:02    21
3  2023-03-04 10:30:03    54
4  2023-03-04 10:30:04    12
5  2023-03-04 10:30:05    13
6  2023-03-04 10:30:06    21
...
58 2023-03-04 10:30:58    22
59 2023-03-04 10:30:59    21
60 2023-03-04 10:31:00    25
61 2023-03-04 10:31:01    21