Pandas Timestamp rounds 30 seconds inconsistently

Question

I'm trying to round a pandas DatetimeIndex (or Timestamp) to the nearest minute, but I'm having a problem with Timestamps of 30 seconds - some rounding up, some rounding down (this seems to alternate).

Any suggestions to fix this so that 30s always rounds up?

>>> pd.Timestamp(2019,6,1,6,57,30).round('1T')
Timestamp('2019-06-01 06:58:00')

>>> pd.Timestamp(2019,6,1,6,58,30).round('1T')
Timestamp('2019-06-01 06:58:00')

The top result looks fine, with 57m 30s rounding up to 58m, but I'd expect the bottom result to round up to 59m - not down to 58m.

BENY · Answer 1 · 2019-06-17T15:34:04.380

6

This is ceil round

pd.Timestamp(2019,6,1,6,57,30).ceil('1T')
Out[344]: Timestamp('2019-06-01 06:58:00')
pd.Timestamp(2019,6,1,6,58,30).ceil('1T')
Out[345]: Timestamp('2019-06-01 06:59:00')

Update , this is decimal problem

from decimal import Decimal, ROUND_HALF_UP
s=Decimal((pd.Timestamp(2019,6,1,6,58,30).value//60)/1e9).quantize(0, ROUND_HALF_UP)
pd.to_datetime(int(s)*60*1e9)
Out[28]: Timestamp('2019-06-01 06:59:00')
s=Decimal((pd.Timestamp(2019,6,1,6,57,30).value//60)/1e9).quantize(0, ROUND_HALF_UP)
pd.to_datetime(int(s)*60*1e9)
Out[30]: Timestamp('2019-06-01 06:58:00')

edited Jun 17 '19 at 15:34

answered Jun 17 '19 at 14:31

BENY

317,841
20
164
234

@ALollz, you're right - that's exactly what I want here. Is this achievable with a different function or a workaround? – Peter Jun 17 '19 at 14:51
1

@ALollz yelp , just get time fix it – BENY Jun 17 '19 at 15:34
@WeNYoBen, any idea how I might get this to work on a DatetimeIndex? – Peter Jun 17 '19 at 15:49
@Peter it is work for float , and you just need convert it back – BENY Jun 17 '19 at 16:04

ALollz · Accepted Answer · 2019-06-17T16:04:11.403

3

The rounding is consistent; the choice followed is, "when halfway between two integers the even integer is chosen." You want half-up rounding, which you will need to implement yourself.

import numpy as np
import pandas as pd

def half_up_minute(x):
    m = (x - x.dt.floor('1T')).dt.total_seconds() < 30   # Round True Down, False Up
    return x.where(m).dt.floor('1T').fillna(x.dt.ceil('1T'))

# For indices:
def half_up_minute_idx(idx):
    m = (idx - idx.floor('1T')).total_seconds() < 30   # Round True Down, False Up
    return pd.Index(np.select([m], [idx.floor('1T')], default=idx.ceil('1T')))

# Sample Data
df = pd.DataFrame({'date': pd.date_range('2019-01-01', freq='15S', periods=10)})
df['rounded'] = half_up_minute(df.date)

Output:

                 date             rounded
0 2019-01-01 00:00:00 2019-01-01 00:00:00
1 2019-01-01 00:00:15 2019-01-01 00:00:00
2 2019-01-01 00:00:30 2019-01-01 00:01:00
3 2019-01-01 00:00:45 2019-01-01 00:01:00
4 2019-01-01 00:01:00 2019-01-01 00:01:00
5 2019-01-01 00:01:15 2019-01-01 00:01:00
6 2019-01-01 00:01:30 2019-01-01 00:02:00
7 2019-01-01 00:01:45 2019-01-01 00:02:00
8 2019-01-01 00:02:00 2019-01-01 00:02:00
9 2019-01-01 00:02:15 2019-01-01 00:02:00

edited Jun 17 '19 at 16:04

answered Jun 17 '19 at 14:52

ALollz

57,915
7
66
89

This looks good, thanks! I wasn't able to implement it on a DatetimeIndex, but I think I can get around this by creating a temporary column in my DataFrame to run the function on, then use set_index with this. – Peter Jun 17 '19 at 15:34
Still struggling with the DatetimeIndex actually. I'm coming up against "AttributeError: 'DatetimeIndex' object has no attribute 'dt'". Any thoughts? – Peter Jun 17 '19 at 15:48
Looks like it's getting close. I think it's just the fillna part that's not working - I'm getting "TypeError: 'value' must be a scalar, passed: DatetimeIndex". – Peter Jun 17 '19 at 16:08
1

Works perfectly now. Thanks very much! – Peter Jun 17 '19 at 16:14

Pandas Timestamp rounds 30 seconds inconsistently

2 Answers2

Output:

Linked