BUG/API: master issue for resample on DST days

[dflatow]

• Incorrect resampling due to DST #5694

df = pd.DataFrame([0], index=[datetime(2012, 11, 4, 23, 0, 0)])
df = df.tz_localize('America/New_York')
df.resample(rule='D', how='sum')

raises a AmbiguousTimeError even though datetime(2012, 11, 4, 23, 0, 0) is not ambiguous.

• Exception during resample w/ tz #8601

idx = date_range('2014-10-08 00:00','2014-10-09 00:00', freq='D', tz='Europe/Berlin')
pd.Series(5, idx).resample('MS')

• AmbiguousTimeError exception when resampling near DST change date #8744

index = pd.to_datetime(pd.Series([
'2014-10-26 07:35:49',
'2014-10-26 07:45:08',
'2014-10-26 08:04:58'
]))

df = pd.DataFrame(np.arange(len(index)), index=index)
df = df.tz_localize('Asia/Krasnoyarsk', ambiguous='NaT')
df.resample('D')

AmbiguousTimeError: Cannot infer dst time from Timestamp('2014-10-26 01:00:00'), try using the 'ambiguous' argument

• Exception when resampling ends on a DST change date #8653 (might be separate issue)

import datetime
import pytz as tz
import pandas as pd

rome = tz.timezone('Europe/Rome')

dr = []
for i in range(2):
    dp = datetime.datetime(2014, 10, 25) + datetime.timedelta(days=i)
    dr.append(rome.localize(dp))

series = {}
for i, ddr in enumerate(dr):
    series[ddr] = i * 10

s1 = pd.Series(series)
s1 = s1.resample('D', how='mean')

• BUG: Resampling a TZ-aware Series fails if rule='W': "Values falls before first bin" #9119
• BUG: "ValueError: Values falls after last bin" on resample('1D') of df with DST index, new in 0.15.2 #9173
• BUG: Series.resample across daylight saving boundary causes segfault. #9468

[jreback]

cc @rockg

can you take a look?

[rockg]

A little background...what is done behind the scenes is to make a DatetimeIndex that has the passed frequency that forms the basis for the resample. In this case, it's a daily curve. Unfortunately, the AmbiguousTime s is related to what we've seen with other things (e.g., #5175). Basically your date is normalized (_adjust_dates_anchored in tseries.resample), but it returns 2012-11-04 00:00:00-05:00 when it should return 2012-11-04 00:00:00-04:00. The dates are normalized using datetime.replace() by setting components to zero, but the tzoffset does not change. I believe the fix is to localize. Let me do some digging on possible solutions. @dflatow, in the meantime a simple fix would be to use UTC or not localize at all.

[dflatow]

My workaround is to use a groupby instead of resample (the timezones matter for the analysis I'm doing - so UTC won't work). Any word on a fix though?

[rockg]

Good news is that I have all of these figured out. One issue I'm having is related to #8601. Basically when you take a time in non-DST and apply the +MS rule, it goes to the last hour of the month because of DST (see #5175) for more explanation. In that commit the logic was applied to DateOffset, but all the other offsets need to remove the timezone, apply the offset, and then put it back. This seems pretty ugly and I'm looking for suggestions to handle this in a uniform way rather than duplicating the logic for every single offset.

import pandas as pd
ts = pd.Timestamp('11/2/2012', tz='US/Eastern')
ts + pd.offsets.MonthBegin()
Out[4]: Timestamp('2012-11-30 23:00:00-0500', tz='US/Eastern') #Should be 2012-12-01 00

The below basically needs to be added to apply for each offset.

        tzinfo = getattr(other, 'tzinfo', None)
        if tzinfo is not None:
            other = other.replace(tzinfo=None)

        # Apply offset, e.g., other = other + relativedelta(...)
        if tzinfo is not None:
            other = tslib._localize_pydatetime(other, tzinfo)

The other issue was caused by _adjust_dates_anchored and the assumption made about the hours in a day. This assumption fails when you are on a DST day, but it was a pretty simple fix (basically move everything to UTC, calculate the anchor, and then relocalize).

Another problem is that tslib.normalize_date doesn't handle DST properly. I'm inclined to change this so that when you normalize an hour on the DST day and after the offset change, it returns the right offset.

Wrong:

import pandas as pd
ts = pd.Timestamp('11/4/2012 23:00', tz='US/Eastern')
ts
Out[4]: Timestamp('2012-11-04 23:00:00-0500', tz='US/Eastern')
pd.tseries.tools.normalize_date(ts)
Out[5]: Timestamp('2012-11-04 00:00:00-0500', tz='US/Eastern') #Should be -0400

Right:

import pandas as pd
ts = pd.Timestamp('11/4/2012 23:00', tz='US/Eastern')
ts
Out[4]: Timestamp('2012-11-04 23:00:00-0500', tz='US/Eastern')
pd.tseries.tools.normalize_date(ts)
Out[5]: Timestamp('2012-11-04 00:00:00-0400', tz='US/Eastern')

[rockg]

I guess the removal of tzinfofrom the offset should really happen in apply_wraps which there is some logic for but the tzinfo is not removed from other.

[jreback]

closed by #9623