DOC: DatetimeTZDtype astype behavior with Series.astype

[Loupiol]

Code Sample

import pandas as pd

typ = pd.DatetimeTZDtype(tz = 'EST')
s = pd.Series([pd.Timestamp('2020-01-01 15:00')])
print(s.astype(typ))

0   2020-01-01 10:00:00-05:00
dtype: datetime64[ns, EST] 

Problem description

Hello,

I noticed a strange behavior using DatetimeTZDtype , when specifying a timezone as argument. When the type is applied to a tz-naive datetime column using astype, it will first localize the timestamps to UTC and then convert the timezone, whereas I was expecting it would just localize my tz-naive timestamps.

My situation is I am getting data from a database and need to convert the columns to the desired types. Using DatetimeTZDtype and astype is a nice way to convert and localize in just one line, but I encountered this unexpected behavior (though it is easy to work around that).

I tracked the issue to these lines https://github.com/pandas-dev/pandas/blob/master/pandas/core/internals/blocks.py#L2090

Thanks

Expected Output

0   2020-01-01 15:00:00-05:00
dtype: datetime64[ns, EST]

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.6.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 158 Stepping 13, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None
pandas : 1.0.3
numpy : 1.18.2
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 45.2.0
Cython : 0.29.16
pytest : 5.4.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : 0.9.3
psycopg2 : None
jinja2 : 2.11.1
IPython : 7.13.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.2.1
numexpr : 2.7.1
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 0.16.0
pytables : None
pytest : 5.4.1
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.15
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : 0.48.0

[paste the output of pd.show_versions() here leaving a blank line after the details tag]

[TomAugspurger]

Thanks for the bug report. Your expected output matches the result for DatetimeIndex.astype() with the same values

In [10]: pd.Index([pd.Timestamp('2020-01-01 15:00')]).astype('datetime64[ns, EST]')
Out[10]: DatetimeIndex(['2020-01-01 15:00:00-05:00'], dtype='datetime64[ns, EST]', freq=None)

cc @mroeschke and @jbrockmendel if you have thoughts on which of these is correct.

[jbrockmendel]

So first off, as a matter of heading off future headaches, you should be using "US/Eastern" instead of "EST".

I would expect the data stored in the database to be UTC times (or unix timestamps). is that correct? if not, how are they stored?

[mroeschke]

This behavior is documented in https://pandas.pydata.org/docs/user_guide/timeseries.html#time-zone-series-operations

In [458]: s_naive = pd.Series(pd.date_range('20130101', periods=3))

In [459]: s_naive
Out[459]: 
0   2013-01-01
1   2013-01-02
2   2013-01-03
dtype: datetime64[ns]

# localize and convert a naive time zone
In [463]: s_naive.astype('datetime64[ns, US/Eastern]')
Out[463]: 
0   2012-12-31 19:00:00-05:00
1   2013-01-01 19:00:00-05:00
2   2013-01-02 19:00:00-05:00
dtype: datetime64[ns, US/Eastern]

I think the comment can be made more explicit as # localize to UTC and convert to US/Eastern. Think that small doc update would be worthwhile

[mroeschke]

And the astype documentation could be updated to reflect this behavior too. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.astype.html

[Loupiol]

If that's the expected behavior, I'll make it work with that then. A quick example in astype showing this would indeed avoid any future confusion (that's the first doc I checked).

However what's the rationale behind having a different behavior for Series and DatetimeIndex as @TomAugspurger showed?

[TomAugspurger]

I don’t think that’s deliberate.

…

On Apr 8, 2020, at 11:54, Loupiol ***@***.***> wrote:  If that's the expected behavior, I'll make it work with that then. A quick example in astype showing this would indeed avoid any future confusion (that's the first doc I checked). However what's the rationale behind having a different behavior for Series and DatetimeIndex as @TomAugspurger showed? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[jbrockmendel]

However what's the rationale behind having a different behavior for Series and DatetimeIndex

That definitely seems undesirable

[mroeschke]

Created #33401 for that discrepancy

[venkateshdatta1993]

Hey @mroeschke can I pick this up?

If I understand correctly, changes need to be made in two pages. 1. The astype page and 2. The timezone series operations page.

[mroeschke]

Sure @venkateshdatta1993. Just this page needs an updated example with using a timezone data type with astype https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.astype.html

[venkateshdatta1993]

take

[venkateshdatta1993]

@mroeschke Awesome, I’ll work on it. Thanks for the clarification.