DF.stack() on single column datetime with timezone causes loss of timezone

[dworvos]

Code Sample, a copy-pastable example if possible

import pandas as pd

ts1 = list(pd.date_range(freq="D", start="20180101", end="20180103"))
df1 = pd.DataFrame({"A": ts1}, index=["a", "b", "c"])
print("Works as expected; no tz")
print(df1.stack())
#a  A   2018-01-01
#b  A   2018-01-02
#c  A   2018-01-03
#dtype: datetime64[ns]

ts2 = list(pd.date_range(freq="D", start="20180101", end="20180103", tz="America/New_York"))
ts3 = list(pd.date_range(freq="D", start="20180104", end="20180106", tz="America/New_York"))

df2 = pd.DataFrame({"A": ts2, "B": ts3}, index=["a", "b", "c"])
print("Works as expected; has tz")
print(df2.stack())
#a  A   2018-01-01 00:00:00-05:00
#   B   2018-01-04 00:00:00-05:00
#b  A   2018-01-02 00:00:00-05:00
#   B   2018-01-05 00:00:00-05:00
#c  A   2018-01-03 00:00:00-05:00
#   B   2018-01-06 00:00:00-05:00
#dtype: datetime64[ns, America/New_York]

df3 = pd.DataFrame({"A": ts2}, index=["a", "b", "c"])
print("Unexpected loss of tz, but converted to UTC")
print(df3.stack())
#a  A   2018-01-01 05:00:00
#b  A   2018-01-02 05:00:00
#c  A   2018-01-03 05:00:00
#dtype: datetime64[ns]

Problem description

Stacking a DF with multiple datetime with timezone columns seems fine but stacking a single datetime with timezone column appears to convert the time to a naive datetime.

This can be worked around using the .dt.tz and .dt.tz_localize/.dt.tz_convert functionality

Expected Output

a A 2018-01-01 00:00:00-05:00
b A 2018-01-02 00:00:00-05:00
c A 2018-01-03 00:00:00-05:00
dtype: datetime64[ns, America/New_York]

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.3.final.0
python-bits: 64
OS: Linux
OS-release: 3.10.0-514.21.1.el7.x86_64
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: en_US.utf-8
LANG: en_US.utf-8
LOCALE: en_US.UTF-8

pandas: 0.22.0
pytest: None
pip: 9.0.1
setuptools: 28.8.0
Cython: None
numpy: 1.14.0
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 6.2.1
sphinx: None
patsy: 0.4.1
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.1.0
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: 4.6.0
html5lib: 1.0b10
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[TomAugspurger]

Your expected output looks correct here.

Do you have any interest in writing a PR to fix it?

[dworvos]

Sure, I will give writing a PR a try!

[dworvos]

Hi, please let me know if this is the wrong place to ask these questions, but in trying to write fix, I think I'm at a point where you guys might be able to give me some pointers in continuing.

I've written two test cases for this issue (I'll need your guidance on where to put these test cases later) - one for the multicolumn case and one for the single column case. What's interesting to me is that the DF dtypes in my test cases are slightly different:

def test_stack_datetime_with_tz_multi_column():
    # GH19420 Stacking with single column causes loss of tz info
    dt_tz = [
        pd.Timestamp("20180101", tz="America/New_York"),
        pd.Timestamp("20180102", tz="America/New_York"),
        pd.Timestamp("20180103", tz="America/New_York"),
    ]

    df = DataFrame({"A": dt_tz, "B": dt_tz}, index=["a", "b", "c"])
    assert df["A"].dtype == 'datetime64[ns, America/New_York]'
    assert df["B"].dtype == 'datetime64[ns, America/New_York]'
    assert df.values.dtype == np.object_

    result = df.stack()
    expected = Series(sorted(dt_tz * 2),
                      MultiIndex.from_product([["a", "b", "c"], ["A", "B"]]))

    assert_series_equal(result, expected)

This test case passes.

def test_stack_datetime_with_tz_single_column():

    # GH19420 Stacking with single column causes loss of tz info
    dt_tz = [
        pd.Timestamp("20180101", tz="America/New_York"),
        pd.Timestamp("20180102", tz="America/New_York"),
        pd.Timestamp("20180103", tz="America/New_York"),
    ]

    df = DataFrame({"A": dt_tz}, index=["a", "b", "c"])
    assert df["A"].dtype == 'datetime64[ns, America/New_York]'
    assert df.values.dtype == np.object_

    result = df.stack()
    expected = Series(dt_tz, MultiIndex.from_product([["a", "b", "c"], ["A"]]))

    assert_series_equal(result, expected)

Unfortunately I hit the trigger the assert df.values.dtype == np.object_. But say we were to ignore that assert and continue the assert_series_equal would trigger.

The cause of this appears to be when we call the function stack in core/reshape.py. Inside that function we eventually call frame.values.ravel(). In the multi column case this appears to call ndarray.ravel() and converts the resulting times to an array of pd.Timestamps and the timezone is preserved. In the single column case this appears to call DatetimeIndex.ravel() which returns a numpy array of naive datetimes and when passed to frame._constructor_sliced results in the incorrect Series.

Grepping for "class Timestamp" shows results in _libs/tslibs/timestamps.pyx which I'm hoping you guys might be able to help me out with - my hypothesis is if I can convert the DatetimeIndex to pd.Timestamps the frame._constructor_sliced may work but I'm not sure the right way to go about that.

[TomAugspurger]

This may be worth holding on until some of the extension array stuff settles down: #19696. Once DatetimeTZ is an ExtensionArray, things like ravel will be handled more consistently.

[TomAugspurger]

This is fixed on master.

In [5]: df3
Out[5]:
                          A
a 2018-01-01 00:00:00-05:00
b 2018-01-02 00:00:00-05:00
c 2018-01-03 00:00:00-05:00

In [6]: df3.stack()
Out[6]:
a  A   2018-01-01 00:00:00-05:00
b  A   2018-01-02 00:00:00-05:00
c  A   2018-01-03 00:00:00-05:00
dtype: datetime64[ns, America/New_York]

May want a test to validate this.