pandas.floor('H') does not work with DST

[sdementen]

Using floor on a DatetimeIndex that has a timezone with DST fails when converting the result back to the tz as there may be ambiguous time (see example below).

# create 15' date range with Brussels tz
idx = pandas.date_range("2016-01-01","2017-01-01",freq="15T", tz="Europe/Brussels")
# transform the index to get rid of minutes
idx_h = idx.floor("H")

Expected Output

idx_h rounded to the hour

Actual Output

excepection raised when relocalizing the index after applying the rouding

Traceback (most recent call last):
  File "...", line 118, in <module>
    idx_h = idx.floor("H")
  File "...\lib\site-packages\pandas\tseries\base.py", line 98, in floor
    return self._round(freq, np.floor)
  File "...\lib\site-packages\pandas\tseries\base.py", line 89, in _round
    result = result.tz_localize(self.tz)#, ambiguous=[d.dst() for d in self])
  File "...\lib\site-packages\pandas\util\decorators.py", line 91, in wrapper
    return func(*args, **kwargs)
  File "...\lib\site-packages\pandas\tseries\index.py", line 1857, in tz_localize
    ambiguous=ambiguous)
  File "pandas\tslib.pyx", line 4087, in pandas.tslib.tz_localize_to_utc (pandas\tslib.c:69556)
pytz.exceptions.AmbiguousTimeError: Cannot infer dst time from Timestamp('2016-10-30 02:00:00'), try using the 'ambiguous' argument

Possible solution

The code to reconvert to local tz need to have 'ambiguous' defined and can use the original array of dst flags.
Lines 87-89 in ...\Lib\site-packages\pandas\tseries\base.py

        # reconvert to local tz
        if getattr(self, 'tz', None) is not None:
            result = result.tz_localize(self.tz)

to be replaced by
Lines 87-89 in ...\Lib\site-packages\pandas\tseries\base.py

        # reconvert to local tz
        if getattr(self, 'tz', None) is not None:
            result = result.tz_localize(self.tz, ambiguous=[d.dst() for d in self])

Would it be also useful to have a DatetimeIndex.dst function to return a boolean array reuseable in tz_localize ?

Output of pd.show_versions()

# Paste the output here ## INSTALLED VERSIONS

commit: None
python: 2.7.11.final.0
python-bits: 32
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 61 Stepping 4, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None

pandas: 0.18.0
nose: 1.3.7
pip: 8.1.1
setuptools: 20.3
Cython: 0.23.4
numpy: 1.10.4
scipy: 0.17.0
statsmodels: 0.6.1
xarray: None
IPython: 4.1.2
sphinx: 1.3.5
patsy: 0.4.0
dateutil: 2.5.1
pytz: 2016.2
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.5
matplotlib: 1.5.1
openpyxl: 2.3.2
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.8.4
lxml: 3.6.0
bs4: 4.4.1
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.0.12
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.39.0

[jreback]

So on the reconversion which should be passing a boolean dst array.

In [62]: idx.tz_localize(None).tz_localize(idx.tz)
AmbiguousTimeError: Cannot infer dst time from Timestamp('2016-10-30 02:00:00'), try using the 'ambiguous' argument

you can compute it like this

In [63]: dst = pd.to_timedelta([ x.tzinfo._dst for x in idx ])!=pd.Timedelta(0)

In [64]: dst
Out[64]: array([False, False, False, ..., False, False, False], dtype=bool)

In [65]: idx.tz_localize(None).tz_localize(idx.tz,ambiguous=dst)
Out[65]: 
DatetimeIndex(['2016-01-01 00:00:00+01:00', '2016-01-01 00:15:00+01:00', '2016-01-01 00:30:00+01:00', '2016-01-01 00:45:00+01:00', '2016-01-01 01:00:00+01:00', '2016-01-01 01:15:00+01:00',
               '2016-01-01 01:30:00+01:00', '2016-01-01 01:45:00+01:00', '2016-01-01 02:00:00+01:00', '2016-01-01 02:15:00+01:00',
               ...
               '2016-12-31 21:45:00+01:00', '2016-12-31 22:00:00+01:00', '2016-12-31 22:15:00+01:00', '2016-12-31 22:30:00+01:00', '2016-12-31 22:45:00+01:00', '2016-12-31 23:00:00+01:00',
               '2016-12-31 23:15:00+01:00', '2016-12-31 23:30:00+01:00', '2016-12-31 23:45:00+01:00', '2017-01-01 00:00:00+01:00'],
              dtype='datetime64[ns, Europe/Brussels]', length=35137, freq='15T')

So I would expose a .is_dst attribute to both Timestamp and DatetimeIndex; returning a boolean if the datetime is dst. (note that in python 3.6 this is called fold, maybe have this an an alt name.

Note that the above is not performant. This will have to be done in cython, like infer_dst is done (IOW you only need to look at certain ranges around transitions, rather than every value).

Pull-requests welcome

[jreback]

cc @rockg

[mroeschke]

Duplicate of #18946