Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Lost timezone information after groupby transform #27496

Closed
billy-doyle opened this issue Jul 21, 2019 · 2 comments · Fixed by #27510

Comments

@billy-doyle
Copy link

commented Jul 21, 2019

Code Sample, a copy-pastable example if possible

Similar issue as #24198 in pandas 0.25.0

import pandas as pd

df = pd.DataFrame({'time': [pd.Timestamp('2010-07-15 03:14:45'), 
                            pd.Timestamp('2010-11-19 18:47:06')],
                   'timezone': ['Etc/GMT+4', 'US/Eastern']})

df['time_tz'] = (df.groupby(['timezone'])['time']
                   .transform(lambda x: x.dt.tz_localize(x.name, ambiguous='NaT')))

df['time_tz_desired'] = (df.groupby(['timezone'])['time']
                   .apply(lambda x: x.dt.tz_localize(x.name, ambiguous='NaT')))
print(df)

Problem description

The timezone in the transform should be preserved but it is not.
For the apply the datetime returned is an object type

df.info() shows

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 4 columns):
time               2 non-null datetime64[ns]
timezone           2 non-null object
time_tz            2 non-null datetime64[ns]
time_tz_desired    2 non-null object
dtypes: datetime64[ns](2), object(2)
memory usage: 192.0+ bytes

Expected Output

Should preserve timezone

df.groupby(['timezone'])['time'].transform(lambda x: x.dt.tz_localize(x.name, ambiguous='NaT'))

And should return datetime

df.groupby(['timezone'])['time'].apply(lambda x: x.dt.tz_localize(x.name, ambiguous='NaT'))

Output of pd.show_versions()

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

commit : None
python : 3.7.3.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.25.0
numpy : 1.16.3
pytz : 2019.1
dateutil : 2.8.0
pip : 19.1.1
setuptools : 40.8.0
Cython : 0.29.10
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.3.4
html5lib : 1.0.1
pymysql : 0.9.3
psycopg2 : 2.8.2 (dt dec pq3 ext lo64)
jinja2 : 2.10.1
IPython : 7.5.0
pandas_datareader: None
bs4 : 4.7.1
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : 4.3.4
matplotlib : 3.0.3
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.3.0
sqlalchemy : 1.3.3
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

@billy-doyle billy-doyle changed the title Lost timezone information after groupby transfrom Lost timezone information after groupby transform Jul 21, 2019

@WillAyd

This comment has been minimized.

Copy link
Member

commented Jul 21, 2019

For the apply the datetime returned is an object type

That is in general what happens when you mix timezones inside of a container:

In [8]: pd.Series([pd.Timestamp('today').tz_localize('US/Eastern'), 
    pd.Timestamp('today').tz_localize('US/Central')])
Out[8]:
0    2019-07-20 18:00:52.167252-04:00
1    2019-07-20 18:00:52.167459-05:00
dtype: object

So I think the issue may just be that transform tries to infer back to datetime when it probably should just leave as object, though cc @mroeschke for thoughts

@billy-doyle

This comment has been minimized.

Copy link
Author

commented Jul 21, 2019

Hmm, the end result desired is to get everything into one format (utc) so doing:

df['time_tz'] = (df.groupby(['timezone'])['time']
                   .transform(lambda x: x.dt.tz_localize(x.name, ambiguous='NaT').dt.tz_convert('UTC')))

df['time_tz_desired'] = (df.groupby(['timezone'])['time']
                   .apply(lambda x: x.dt.tz_localize(x.name, ambiguous='NaT').dt.tz_convert('UTC')))

Returns the correct values for the apply but not the transform once again.

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 4 columns):
time               2 non-null datetime64[ns]
timezone           2 non-null object
time_tz            2 non-null datetime64[ns]
time_tz_desired    2 non-null datetime64[ns, UTC]
dtypes: datetime64[ns, UTC](1), datetime64[ns](2), object(1)
memory usage: 192.0+ bytes
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.