BUG: grouping with tz-aware: Values falls after last bin

[ahcub]

Code Sample, a copy-pastable example if possible

import pandas as pd

index = pd.DatetimeIndex([1478064900001000000, 1480037118776792000], tz='UTC').tz_convert('America/Chicago')

print(index)

df = pd.DataFrame([1, 2], index=index)

for d, data in df.groupby(pd.Grouper(freq='1d')):
    pass

Problem description

grouping is not handling non-UTC index properly due to daylight saving time change
and the problem occurs in file https://github.com/pandas-dev/pandas/blob/master/pandas/core/resample.py because of timestamp normalization line: 1638

last = last.normalize()

the normalization causes an error, and I would expect pandas to simply add an extra bin, instead of raising an exception.
it was introduced in the latest version of pandas 0.24.0 and it does not reproduce on 0.23.4
[this should explain why the current behaviour is a problem and why the expected output is a better solution.]

Note: We receive a lot of issues on our GitHub tracker, so it is very possible that your issue has been posted before. Please check first before submitting so that we do not have to handle and close duplicates!

Note: Many problems can be resolved by simply upgrading pandas to the latest version. Before submitting, please check if that solution works for you. If possible, you may want to check if master addresses this issue, but that is not necessary.

For documentation-related issues, you can check the latest versions of the docs on master here:

https://pandas-docs.github.io/pandas-docs-travis/

If the issue has not been resolved there, go ahead and file it in the issue tracker.

Expected Output

I expect the grouping to be successful regardless of the time range selected

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]

INSTALLED VERSIONS

commit: None
python: 3.7.2.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 78 Stepping 3, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.24.0
pytest: 4.1.1
pip: 18.1
setuptools: 40.6.3
Cython: None
numpy: 1.16.0
scipy: 1.2.0
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.5
pytz: 2018.9
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.2
openpyxl: 2.5.14
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: 0.9.3
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[mroeschke]

Note that the operation is equivalent to df.resample('D')...:

This patch fixes it for me. We have some internal inconsistency since 'D' mean "calendar day" for the user but can internally operate as 24H:

--- a/pandas/core/resample.py
+++ b/pandas/core/resample.py
@@ -1613,20 +1613,20 @@ def _get_timestamp_range_edges(first, last, offset, closed='left', base=0):
     A tuple of length 2, containing the adjusted pd.Timestamp objects.
     """
     if isinstance(offset, Tick):
-        is_day = isinstance(offset, Day)
-        day_nanos = delta_to_nanoseconds(timedelta(1))
-
-        # #1165 and #24127
-        if (is_day and not offset.nanos % day_nanos) or not is_day:
-            first, last = _adjust_dates_anchored(first, last, offset,
-                                                 closed=closed, base=base)
-            if is_day and first.tz is not None:
-                # _adjust_dates_anchored assumes 'D' means 24H, but first/last
-                # might contain a DST transition (23H, 24H, or 25H).
-                # Ensure first/last snap to midnight.
-                first = first.normalize()
-                last = last.normalize()
-            return first, last
+        if isinstance(offset, Day):
+            # _adjust_dates_anchored assumes 'D' means 24H, but first/last
+            # might contain a DST transition (23H, 24H, or 25H).
+            # So "pretend" the dates are naive when adjusting the endpoints
+            tz = first.tz
+            first = first.tz_localize(None)
+            last = last.tz_localize(None)
+
+        first, last = _adjust_dates_anchored(first, last, offset,
+                                             closed=closed, base=base)
+        if isinstance(offset, Day):
+            first = first.tz_localize(tz)
+            last = last.tz_localize(tz)
+        return first, last

     else:
         first = first.normalize()

In [3]: df.resample('D').sum()
Out[3]:
                           0
2016-11-02 00:00:00-05:00  1
2016-11-03 00:00:00-05:00  0
2016-11-04 00:00:00-05:00  0
2016-11-05 00:00:00-05:00  0
2016-11-06 00:00:00-05:00  0
2016-11-07 00:00:00-06:00  0
2016-11-08 00:00:00-06:00  0
2016-11-09 00:00:00-06:00  0
2016-11-10 00:00:00-06:00  0
2016-11-11 00:00:00-06:00  0
2016-11-12 00:00:00-06:00  0
2016-11-13 00:00:00-06:00  0
2016-11-14 00:00:00-06:00  0
2016-11-15 00:00:00-06:00  0
2016-11-16 00:00:00-06:00  0
2016-11-17 00:00:00-06:00  0
2016-11-18 00:00:00-06:00  0
2016-11-19 00:00:00-06:00  0
2016-11-20 00:00:00-06:00  0
2016-11-21 00:00:00-06:00  0
2016-11-22 00:00:00-06:00  0
2016-11-23 00:00:00-06:00  0
2016-11-24 00:00:00-06:00  2

[ahcub]

I agree that your patch looks better, let's give it a try.
I need to say though that when I try it on latest release of pandas it produced an extra bin on grouping for me, maybe something changed since the release so it doesn't for you

                             0
2016-11-02 00:00:00-05:00  1.0
2016-11-03 00:00:00-05:00  NaN
2016-11-04 00:00:00-05:00  NaN
2016-11-05 00:00:00-05:00  NaN
2016-11-06 00:00:00-05:00  NaN
2016-11-07 00:00:00-06:00  NaN
2016-11-08 00:00:00-06:00  NaN
2016-11-09 00:00:00-06:00  NaN
2016-11-10 00:00:00-06:00  NaN
2016-11-11 00:00:00-06:00  NaN
2016-11-12 00:00:00-06:00  NaN
2016-11-13 00:00:00-06:00  NaN
2016-11-14 00:00:00-06:00  NaN
2016-11-15 00:00:00-06:00  NaN
2016-11-16 00:00:00-06:00  NaN
2016-11-17 00:00:00-06:00  NaN
2016-11-18 00:00:00-06:00  NaN
2016-11-19 00:00:00-06:00  NaN
2016-11-20 00:00:00-06:00  NaN
2016-11-21 00:00:00-06:00  NaN
2016-11-22 00:00:00-06:00  NaN
2016-11-23 00:00:00-06:00  NaN
2016-11-24 00:00:00-06:00  2.0
2016-11-25 00:00:00-06:00  NaN
```