Pandas/Datetime rounding error (Rounding to 10 seconds)

Question

I have a problem using the round() funktion on a pandas.Series / DataFrame filled with Timestamps.

Code example to round to the nearest 10-second mark:

import pandas as pd

date = pd.DataFrame([pd.to_datetime('2022-03-02 06:46:05'), pd.to_datetime('2022-03-02 06:46:15'), pd.to_datetime('2022-03-02 06:46:25'), pd.to_datetime('2022-03-02 06:46:35'), pd.to_datetime('2022-03-02 06:46:45'), pd.to_datetime('2022-03-02 06:46:55'), pd.to_datetime('2022-03-02 06:47:05'), pd.to_datetime('2022-03-02 06:47:15'), pd.to_datetime('2022-03-02 06:47:25')])

date[1] = date[0].round('10s')

date

OUT:
    0                     1
0   2022-03-02 06:46:05   2022-03-02 06:46:00
1   2022-03-02 06:46:15   2022-03-02 06:46:20
2   2022-03-02 06:46:25   2022-03-02 06:46:20
3   2022-03-02 06:46:35   2022-03-02 06:46:40
4   2022-03-02 06:46:45   2022-03-02 06:46:40
5   2022-03-02 06:46:55   2022-03-02 06:47:00
6   2022-03-02 06:47:05   2022-03-02 06:47:00
7   2022-03-02 06:47:15   2022-03-02 06:47:20
8   2022-03-02 06:47:25   2022-03-02 06:47:20
dtype: datetime64[ns]

Whenever a Timestamp has a seconds value in [5, 25, 45] the rounded value is set to [0, 20, 40], although it should be set to [10, 30, 50]. Any idea on how to fix this?

Thanks in advance!

curies, that `pandas.Timestamp.round` does not work correctly in this case — RomanPerekhrest, Feb 16 '23 at 14:27

score 3 · Answer 1 · answered Feb 16 '23 at 14:25

3

Try to use .dt.ceil():

date[1] = date[0].dt.ceil('10s')
print(date)

Prints:

                    0                   1
0 2022-03-02 06:46:04 2022-03-02 06:46:10
1 2022-03-02 06:46:05 2022-03-02 06:46:10
2 2022-03-02 06:46:06 2022-03-02 06:46:10
3 2022-03-02 06:46:24 2022-03-02 06:46:30
4 2022-03-02 06:46:25 2022-03-02 06:46:30
5 2022-03-02 06:46:26 2022-03-02 06:46:30
6 2022-03-02 06:46:44 2022-03-02 06:46:50
7 2022-03-02 06:46:45 2022-03-02 06:46:50
8 2022-03-02 06:46:46 2022-03-02 06:46:50

answered Feb 16 '23 at 14:25

Andrej Kesely

168,389
15
48
91

1

Thank you. I already tried that. I unfortunately need a working "real" rounding function. Especially because i have sampled data with an intervall of 10s (usually) that happens to land on a 5s-mark. Thus i have samples at 5s, 15s, 25s, 35s, ... I would like to have unique timestamps in this case =) – JaboSammy Feb 16 '23 at 14:27

score 3 · Accepted Answer · answered Feb 16 '23 at 14:30

Use trick - add some small timedelta, because python should round 5 not like expected:

date[1] = date[0].add(pd.Timedelta('1us')).round('10s')
print (date)
                    0                   1
0 2022-03-02 06:46:05 2022-03-02 06:46:10
1 2022-03-02 06:46:15 2022-03-02 06:46:20
2 2022-03-02 06:46:25 2022-03-02 06:46:30
3 2022-03-02 06:46:35 2022-03-02 06:46:40
4 2022-03-02 06:46:45 2022-03-02 06:46:50
5 2022-03-02 06:46:55 2022-03-02 06:47:00
6 2022-03-02 06:47:05 2022-03-02 06:47:10
7 2022-03-02 06:47:15 2022-03-02 06:47:20
8 2022-03-02 06:47:25 2022-03-02 06:47:30

Pandas/Datetime rounding error (Rounding to 10 seconds)

2 Answers2