UnknownTimeZoneError: 'tzutc()' when writing a dataframe using from_dict() then reading it using read_parquet()

[joeax]

Code Sample

import datetime
import fastparquet
import pandas as pd

from dateutil.tz import tzutc

def setup():
    # note: mock or real API call results in the same error
    data = mock_list_s3_bucket()
    df = pd.DataFrame.from_dict(data)
    #print(data)
    df.to_parquet(path, engine='auto', compression='snappy')

def main():
    # error happens here - pytz.exceptions.UnknownTimeZoneError: 'tzutc()'
    df = pd.read_parquet(path, engine='auto')
    print(df)

def mock_list_s3_bucket():
    # mock a call to S3 API
    return [
        {'Key': 'fun/file1.txt', 'LastModified': datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc()), 'ETag': '"8c768c05b4faea563a8520acb983fb79"', 'Size': 4445, 'StorageClass': 'GLACIER'},
        {'Key': 'fun/file2.txt', 'LastModified': datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc()), 'ETag': '"8c768c05b4faea563a8520acb983fb79"', 'Size': 4445, 'StorageClass': 'GLACIER'},
        {'Key': 'fun/file3.txt', 'LastModified': datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc()), 'ETag': '"8c768c05b4faea563a8520acb983fb79"', 'Size': 4445, 'StorageClass': 'GLACIER'}
    ]

if __name__ == "__main__":
    setup()
    main()

Problem description

  File "pandas\_libs\tslibs\timezones.pyx", line 84, in pandas._libs.tslibs.timezones.maybe_get_tz
  File "pandas\_libs\tslibs\timezones.pyx", line 99, in pandas._libs.tslibs.timezones.maybe_get_tz
  File "C:\Users\userx\AppData\Local\Programs\Python\Python37\lib\site-packages\pytz\__init__.py", line 178, in timezone
    raise UnknownTimeZoneError(zone)
pytz.exceptions.UnknownTimeZoneError: 'tzutc()'

I have an app that reads bucket list data on a periodic basis from the S3 API. Up until recently, everything worked fine. When we upgraded to pandas 0.24 the problem with the parquet files being generated started to surface.

Note: I created a clean VM, installed pandas 0.24 and all dependencies, and was able to reproduce the issue.

Here is more info on the column metadata generated by fastparquet.

pandas 0.23 metadata

{"columns": [{"metadata": null, "name": "ETag", "numpy_type": "object", "pandas_type": "unicode"}, {"metadata": null, "name": "Key", "numpy_type": "object", "pandas_type": "unicode"}, {"metadata": {"timezone": "UTC"}, "name": "LastModified", "numpy_type": "datetime64[ns, UTC]", "pandas_type": "datetimetz"}, {"metadata": null, "name": "Size", "numpy_type": "int64", "pandas_type": "int64"}, {"metadata": null, "name": "StorageClass", "numpy_type": "object", "pandas_type": "unicode"}], "index_columns": [], "pandas_version": "0.23.4"}

pandas 0.24 metadata

{"columns": [{"metadata": null, "name": "ETag", "numpy_type": "object", "pandas_type": "unicode"}, {"metadata": null, "name": "Key", "numpy_type": "object", "pandas_type": "unicode"}, {"metadata": {"timezone": "tzutc()"}, "name": "LastModified", "numpy_type": "datetime64[ns, tzutc()]", "pandas_type": "datetimetz"}, {"metadata": null, "name": "Size", "numpy_type": "int64", "pandas_type": "int64"}, {"metadata": null, "name": "StorageClass", "numpy_type": "object", "pandas_type": "unicode"}], "index_columns": [], "pandas_version": "0.24.1"}

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.7.0.final.0 python-bits: 64 OS: Windows OS-release: 10 machine: AMD64 processor: Intel64 Family 6 Model 142 Stepping 9, GenuineIntel byteorder: little LC_ALL: None LANG: en_US.UTF-8 LOCALE: None.None

pandas: 0.24.1
pytest: None
pip: 19.0.3
setuptools: 39.0.1
Cython: None
numpy: 1.16.1
scipy: None
pyarrow: None
xarray: None
IPython: 7.2.0
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2018.9
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: 4.2.5
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: 0.2.1
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[jreback]

pls remove anything extraneous here - just show the minimal construction and call which errros

[joeax]

I cleaned up the comment and showed where the error happens.

[jorisvandenbossche]

Update on this, running the example with both latest fastparquet and pyarrow (with pandas master):

Writing with pyarrow fails:

In [7]: df.to_parquet('__test_tzutc3.parquet', engine='pyarrow', compression='snappy')
...
~/miniconda3/envs/dev37/lib/python3.7/site-packages/pyarrow/types.pxi in pyarrow.lib.tzinfo_to_string()
ValueError: Unable to convert timezone `tzutc()` to string

Writing with fastparquet succeeds, but reading with pandas then fails (the same as what @joeax reported initially):

In [9]: df.to_parquet('__test_tzutc_fp.parquet', engine='fastparquet', compression='GZIP')

In [10]: pd.read_parquet('__test_tzutc_fp.parquet', engine='fastparquet')
...
UnknownTimeZoneError: 'tzutc()'

In case of writing / reading the file with fastparquet, the error boils down to fastparquet doing:

pd.Series(d).dt.tz_localize('tzutc()')

which then fails in pandas (so fastparquet seems to have written a literal string representation of the timezone in the parquet file).

When reading that file with pyarrow, the same happens (but inside the arrow code).

[jorisvandenbossche]

This is related to a change in pandas. Previously, we would parse input with dateutil.tz.tz.tzutc() timezone to pytz.UTC timezone:

In [33]: pd.__version__  
Out[33]: '0.23.4'

In [34]: pd.Series([datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc())])
Out[34]: 
0   2018-06-07 02:59:59+00:00
dtype: datetime64[ns, UTC]

In [35]: pd.Series([datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc())]).dtype.tz 
Out[35]: <UTC>

In [36]: type(pd.Series([datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc())]).dtype.tz)
Out[36]: pytz.UTC

but from pandas 0.24, we started to preserve the dateutil object:

In [3]: pd.__version__
Out[3]: '0.24.0'

In [4]: pd.Series([datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc())])
Out[4]: 
0   2018-06-07 02:59:59+00:00
dtype: datetime64[ns, tzutc()]

In [5]: pd.Series([datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc())]).dtype.tz
Out[5]: tzutc()

In [6]: type(pd.Series([datetime.datetime(2018, 6, 7, 2, 59, 59, tzinfo=tzutc())]).dtype.tz)
Out[6]: dateutil.tz.tz.tzutc

And it is this object that fastparquet and pyarrow cannot handle.
(I suppose they might not be able to handle dateutil timezones in general)

@joeax in any case: workaround that you can do yourself: convert the timezone to a pytz one (eg with dt.tz_convert('UTC'))

[jorisvandenbossche]

@joeax I opened https://issues.apache.org/jira/browse/ARROW-5248 for supporting this on the pyarrow side, and dask/fastparquet#424 on the fastparquet side.

So we can follow up on both projects, and therefore closing this issue here.

[shadiakiki1986]

Just came here to re-iterate the workaround by @jorisvandenbossche a bit more completely

import pytz
df['Timestamp'] = df.Timestamp.dt.tz_convert(pytz.utc)

import pyarrow as pa
context = pa.default_serialization_context()
pybytes = context.serialize(df).to_buffer().to_pybytes()  # <<<<< This wouldn't work without the tz_convert call above

Docs: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.tz_convert.html