Pandas: AmbigousTimeError on Timestamp.floor during dst change

[Safrone]

Code Sample, a copy-pastable example if possible

import pandas as pd
pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').floor('T')

Traceback (most recent call last):
  File "/venv/lib/python3.5/site-packages/IPython/core/interactiveshell.py", line 3265, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-2-42bf9ce66d1d>", line 1, in <module>
    pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').floor('T')
  File "pandas/_libs/tslibs/timestamps.pyx", line 696, in pandas._libs.tslibs.timestamps.Timestamp.floor
  File "pandas/_libs/tslibs/timestamps.pyx", line 667, in pandas._libs.tslibs.timestamps.Timestamp._round
  File "pandas/_libs/tslibs/timestamps.pyx", line 903, in pandas._libs.tslibs.timestamps.Timestamp.tz_localize
  File "pandas/_libs/tslibs/conversion.pyx", line 963, in pandas._libs.tslibs.conversion.tz_localize_to_utc
pytz.exceptions.AmbiguousTimeError: Cannot infer dst time from '2018-11-04 01:55:00', try using the 'ambiguous' argument



md5-0073f6d2c1d4a51232f56887dac28a48



pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').ceil('T')
pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').round('T')



md5-4e753d9f5077066bad4ccc1dc72e37b0



Timestamp('2018-11-04 01:55:00-0500', tz='America/New_York')

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.5.6.final.0
python-bits: 64
OS: Linux
OS-release: 4.15.0-38-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8
pandas: 0.23.4
pytest: None
pip: 10.0.1
setuptools: 39.1.0
Cython: None
numpy: 1.15.2
scipy: None
pyarrow: None
xarray: None
IPython: 7.0.1
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[Safrone]

A workaround I found:

import pandas as pd
import pytz
ts = pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York')
ts.astimezone(pytz.UTC).floor('T').astimezone(ts.tzinfo)

Timestamp('2018-11-04 01:55:00-0500', tz='America/New_York')

[gfyoung]

cc @mroeschke

I believe we might have fixed this in #22647?

@Safrone : Thanks for filing this! Can you installing the master branch an re-running your code?

[Safrone]

I cloned the latest version and am still having issues:

>>> import pandas as pd
>>> pd.__version__
'0.24.0.dev0+917.gf0877eccc'
>>> pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').floor('T')

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "pandas/_libs/tslibs/timestamps.pyx", line 824, in pandas._libs.tslibs.timestamps.Timestamp.floor
    return self._round(freq, RoundTo.MINUS_INFTY, ambiguous, nonexistent)
  File "pandas/_libs/tslibs/timestamps.pyx", line 751, in pandas._libs.tslibs.timestamps.Timestamp._round
    result = result.tz_localize(
  File "pandas/_libs/tslibs/timestamps.pyx", line 1096, in pandas._libs.tslibs.timestamps.Timestamp.tz_localize
    value = tz_localize_to_utc(np.array([self.value], dtype='i8'), tz,
  File "pandas/_libs/tslibs/conversion.pyx", line 1019, in pandas._libs.tslibs.conversion.tz_localize_to_utc
    raise pytz.AmbiguousTimeError(
pytz.exceptions.AmbiguousTimeError: Cannot infer dst time from %r, try using the 'ambiguous' argument

INSTALLED VERSIONS

commit: f0877eccc080cb38afa092436f09a79a65269d8c
python: 3.5.6.final.0
python-bits: 64
OS: Linux
OS-release: 4.15.0-38-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8
pandas: 0.24.0.dev0+917.gf0877eccc
pytest: None
pip: 10.0.1
setuptools: 39.1.0
Cython: 0.29
numpy: 1.15.4
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.5
pytz: 2018.7
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[mroeschke]

As the error notes, you need to specify the ambiguous argument in floor as these were added in #22647

In [1]: pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').f
   ...: loor('T', ambiguous=True)
Out[1]: Timestamp('2018-11-04 01:55:00-0400', tz='America/New_York')

In [2]: pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').f
   ...: loor('T', ambiguous=False)
Out[2]: Timestamp('2018-11-04 01:55:00-0500', tz='America/New_York')

In [3]: pd.Timestamp('2018-11-04 01:55:17.869342-0500', tz='America/New_York').f
   ...: loor('T', ambiguous='NaT')
Out[3]: NaT

[Safrone]

Ah yes you are correct, thank you.

[adamkpickering]

I think that the problems here are deeper, and cannot be solved by tacking the ambiguous keyword argument onto floor and others.

In [106]: a = pd.Timestamp('2018-11-04T7:31:33Z')
In [107]: a
Out[107]: Timestamp('2018-11-04 07:31:33+0000', tz='UTC')
In [108]: b = a.tz_convert('America/Edmonton')
In [109]: b
Out[109]: Timestamp('2018-11-04 01:31:33-0600', tz='America/Edmonton')
In [110]: b.floor('H')
---------------------------------------------------------------------------
AmbiguousTimeError                        Traceback (most recent call last)
<ipython-input-110-7a7b2ce5091f> in <module>()
----> 1 b.floor('H')
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/timestamps.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.timestamps.Timestamp.floor()
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/timestamps.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.timestamps.Timestamp._round()
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/timestamps.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.timestamps.Timestamp.tz_localize()
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/conversion.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.conversion.tz_localize_to_utc()
AmbiguousTimeError: Cannot infer dst time from '2018-11-04 01:00:00', try using the 'ambiguous' argument

In [127]: c = pd.Timestamp('2018-11-04T8:31:33Z')
In [128]: c
Out[128]: Timestamp('2018-11-04 08:31:33+0000', tz='UTC')
In [129]: d = c.tz_convert('America/Edmonton')
In [130]: d
Out[130]: Timestamp('2018-11-04 01:31:33-0700', tz='America/Edmonton')
In [131]: d.floor('H')
---------------------------------------------------------------------------
AmbiguousTimeError                        Traceback (most recent call last)
<ipython-input-131-ebab98f5821c> in <module>()
----> 1 d.floor('H')
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/timestamps.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.timestamps.Timestamp.floor()
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/timestamps.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.timestamps.Timestamp._round()
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/timestamps.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.timestamps.Timestamp.tz_localize()
/usr/local/lib/python3.5/dist-packages/pandas/_libs/tslibs/conversion.cpython-35m-x86_64-linux-gnu.so in pandas._libs.tslibs.conversion.tz_localize_to_utc()
AmbiguousTimeError: Cannot infer dst time from '2018-11-04 01:00:00', try using the 'ambiguous' argument

The thing is: 2018-11-04 01:31:33-0700 and 2018-11-04 01:31:33-0600 are not ambiguous times, so it makes no sense to require the programmer to use the ambiguous keyword to specify whether they are in standard or daylight savings time. If given a UTC offset and a location like America/Edmonton or America/New_York it is possible to infer whether that time is in standard or daylight savings time. If you look at the traceback from above:

AmbiguousTimeError: Cannot infer dst time from '2018-11-04 01:00:00', try using the 'ambiguous' argument

it seems that pandas strips the time offset while processing the time, which is very strange since the offset is crucial information.