Skip to content

'remove_timezone' option not working for ExcelWriter with pandas 0.25.1 #28921

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.

Sign up for GitHub

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

Jump to bottom
Closed
willblenkhornjisc opened this issue Oct 11, 2019 · 5 comments
Closed

'remove_timezone' option not working for ExcelWriter with pandas 0.25.1 #28921

willblenkhornjisc opened this issue Oct 11, 2019 · 5 comments

Comments

@willblenkhornjisc
Copy link

Problem description

pd.ExcelWriter has a 'remove_timezone' option which removes the timezone information when printing out dataframes or series to excel using the pd.to_excel function. Thus allowing datetimes with timezone information to be written to excel files without their timezone information. Since upgrading pandas from 0.24.2 to 0.25.1, this functionality has stopped working and gives me the error below.

Code Sample, which recreates the error.

import pandas as pd
from pathlib import PosixPath,Path
import xlsxwriter

excel_filelocation = Path('/home/will/Documents/pandas_testing/test.xlsx')


writer = pd.ExcelWriter(excel_filelocation, 
                        engine = 'xlsxwriter',
                        options = {'remove_timezone': True})
    


date_with_timezone = pd.Series(['1997-07-16T19:20:30.45+01:00'])
date_with_timezone = pd.to_datetime(date_with_timezone)

date_with_timezone.to_excel(writer,
                            engine = 'xlsxwriter',
                            sheet_name = 'test',
                            startrow = 0,
                            startcol = 0, 
                            index = False,
                            header = False)

writer.save()

Package list

# Name                    Version                   Build  Channel
_libgcc_mutex             0.1                        main  
alabaster                 0.7.12                   py37_0  
asn1crypto                1.0.1                    py37_0  
astroid                   2.3.1                    py37_0  
attrs                     19.2.0                     py_0  
babel                     2.7.0                      py_0  
backcall                  0.1.0                    py37_0  
blas                      1.0                         mkl  
bleach                    3.1.0                    py37_0  
ca-certificates           2019.8.28                     0  
certifi                   2019.9.11                py37_0  
cffi                      1.12.3           py37h2e261b9_0  
chardet                   3.0.4                 py37_1003  
cloudpickle               1.2.2                      py_0  
cryptography              2.7              py37h1ba5d50_0  
dbus                      1.13.6               h746ee38_0  
decorator                 4.4.0                    py37_1  
defusedxml                0.6.0                      py_0  
docutils                  0.15.2                   py37_0  
entrypoints               0.3                      py37_0  
expat                     2.2.6                he6710b0_0  
fontconfig                2.13.0               h9420a91_0  
freetype                  2.9.1                h8a8886c_1  
glib                      2.56.2               hd408876_0  
gmp                       6.1.2                h6c8ec71_1  
gst-plugins-base          1.14.0               hbbd80ab_1  
gstreamer                 1.14.0               hb453b48_1  
icu                       58.2                 h9c2bf20_1  
idna                      2.8                      py37_0  
imagesize                 1.1.0                    py37_0  
intel-openmp              2019.4                      243  
ipykernel                 5.1.2            py37h39e3cac_0  
ipython                   7.8.0            py37h39e3cac_0  
ipython_genutils          0.2.0                    py37_0  
isort                     4.3.21                   py37_0  
jedi                      0.15.1                   py37_0  
jeepney                   0.4.1                      py_0  
jinja2                    2.10.3                     py_0  
jpeg                      9b                   h024ee3a_2  
jsonschema                3.0.2                    py37_0  
jupyter_client            5.3.3                    py37_1  
jupyter_core              4.5.0                      py_0  
keyring                   18.0.0                   py37_0  
lazy-object-proxy         1.4.2            py37h7b6447c_0  
libedit                   3.1.20181209         hc058e9b_0  
libffi                    3.2.1                hd88cf55_4  
libgcc-ng                 9.1.0                hdf63c60_0  
libgfortran-ng            7.3.0                hdf63c60_0  
libpng                    1.6.37               hbc83047_0  
libsodium                 1.0.16               h1bed415_0  
libstdcxx-ng              9.1.0                hdf63c60_0  
libuuid                   1.0.3                h1bed415_2  
libxcb                    1.13                 h1bed415_1  
libxml2                   2.9.9                hea5a465_1  
markupsafe                1.1.1            py37h7b6447c_0  
mccabe                    0.6.1                    py37_1  
mistune                   0.8.4            py37h7b6447c_0  
mkl                       2019.4                      243  
mkl-service               2.3.0            py37he904b0f_0  
mkl_fft                   1.0.14           py37ha843d7b_0  
mkl_random                1.1.0            py37hd6b4f25_0  
nbconvert                 5.6.0                    py37_1  
nbformat                  4.4.0                    py37_0  
ncurses                   6.1                  he6710b0_1  
numpy                     1.17.2           py37haad9e8e_0  
numpy-base                1.17.2           py37hde5b4d6_0  
numpydoc                  0.9.1                      py_0  
openssl                   1.1.1d               h7b6447c_2  
packaging                 19.2                       py_0  
pandas                    0.25.1           py37he6710b0_0  
pandoc                    2.2.3.2                       0  
pandocfilters             1.4.2                    py37_1  
parso                     0.5.1                      py_0  
pcre                      8.43                 he6710b0_0  
pexpect                   4.7.0                    py37_0  
pickleshare               0.7.5                    py37_0  
pip                       19.2.3                   py37_0  
prompt_toolkit            2.0.10                     py_0  
psutil                    5.6.3            py37h7b6447c_0  
ptyprocess                0.6.0                    py37_0  
pycodestyle               2.5.0                    py37_0  
pycparser                 2.19                     py37_0  
pyflakes                  2.1.1                    py37_0  
pygments                  2.4.2                      py_0  
pylint                    2.4.2                    py37_0  
pyopenssl                 19.0.0                   py37_0  
pyparsing                 2.4.2                      py_0  
pyqt                      5.9.2            py37h05f1152_2  
pyrsistent                0.15.4           py37h7b6447c_0  
pysocks                   1.7.1                    py37_0  
python                    3.7.4                h265db76_1  
python-dateutil           2.8.0                    py37_0  
pytz                      2019.3                     py_0  
pyzmq                     18.1.0           py37he6710b0_0  
qt                        5.9.7                h5867ecd_1  
qtawesome                 0.6.0                      py_0  
qtconsole                 4.5.5                      py_0  
qtpy                      1.9.0                      py_0  
readline                  7.0                  h7b6447c_5  
requests                  2.22.0                   py37_0  
rope                      0.14.0                     py_0  
secretstorage             3.1.1                    py37_0  
setuptools                41.4.0                   py37_0  
sip                       4.19.8           py37hf484d3e_0  
six                       1.12.0                   py37_0  
snowballstemmer           2.0.0                      py_0  
sphinx                    2.2.0                      py_0  
sphinxcontrib-applehelp   1.0.1                      py_0  
sphinxcontrib-devhelp     1.0.1                      py_0  
sphinxcontrib-htmlhelp    1.0.2                      py_0  
sphinxcontrib-jsmath      1.0.1                      py_0  
sphinxcontrib-qthelp      1.0.2                      py_0  
sphinxcontrib-serializinghtml 1.1.3                      py_0  
spyder                    3.3.6                    py37_0  
spyder-kernels            0.5.2                    py37_0  
sqlite                    3.30.0               h7b6447c_0  
testpath                  0.4.2                    py37_0  
tk                        8.6.8                hbc83047_0  
tornado                   6.0.3            py37h7b6447c_0  
traitlets                 4.3.3                    py37_0  
urllib3                   1.24.2                   py37_0  
wcwidth                   0.1.7                    py37_0  
webencodings              0.5.1                    py37_1  
wheel                     0.33.6                   py37_0  
wrapt                     1.11.2           py37h7b6447c_0  
wurlitzer                 1.0.3                    py37_0  
xlsxwriter                1.1.8                      py_0  
xz                        5.2.4                h14c3975_4  
zeromq                    4.3.1                he6710b0_3  
zlib                      1.2.11               h7b6447c_3

Error Message

Traceback (most recent call last):

  File "<ipython-input-4-762bf6da5b84>", line 1, in <module>
    runfile('/home/will/Documents/pandas_testing/timezone_error.py', wdir='/home/will/Documents/pandas_testing', post_mortem=True)

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/spyder_kernels/customize/spydercustomize.py", line 827, in runfile
    execfile(filename, namespace)

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/spyder_kernels/customize/spydercustomize.py", line 110, in execfile
    exec(compile(f.read(), filename, 'exec'), namespace)

  File "/home/will/Documents/pandas_testing/timezone_error.py", line 23, in <module>
    header = False)

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/pandas/core/generic.py", line 2256, in to_excel
    engine=engine,

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/pandas/io/formats/excel.py", line 739, in write
    freeze_panes=freeze_panes,

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/pandas/io/excel/_xlsxwriter.py", line 214, in write_cells
    for cell in cells:

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/pandas/io/formats/excel.py", line 687, in get_formatted_cells
    cell.val = self._format_value(cell.val)

  File "/home/will/miniconda3/envs/testing/lib/python3.7/site-packages/pandas/io/formats/excel.py", line 437, in _format_value
    "Excel does not support datetimes with "

ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.
@yogendrasoni
Copy link
Contributor

I think Remove_timezone is parameter for xlsxwriter engine and is getting passed as **enginekwargs. Nothing wrong in parameter passing. This issue is coming because
#27129 causing it to raise exception when timezone is present.
Not sure what can be a good solution. This is not backward compatible. we can raise only when engine is not capable/configured for handling timezones.

@willblenkhornjisc
Copy link
Author

willblenkhornjisc commented Oct 14, 2019 •
edited
Loading

I guess in the meantime manually removing the timezone information from the dataframe or series is my only option using pandas.Series.dt.tz_convert. I hope the problem gets fixed soon.

@yogendrasoni
Copy link
Contributor

@WillAyd @mroeschke Can you please suggest, what can be done here.

@WillAyd
Copy link
Member

WillAyd commented Oct 14, 2019

You can remove the timezone before trying to save to Excel. How to do that is case-specific as to what you actually want. As requested the intent is ambiguous, so I don't see this as something that will be supported

@mroeschke
Copy link
Member

Agreed with @WillAyd. Additionally, this behavior change was described generally in Other API changes in 0.25.0. https://pandas.pydata.org/pandas-docs/version/0.25.0/whatsnew/v0.25.0.html#other-api-changes

@jonmd jonmd mentioned this issue Dec 16, 2020
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@WillAyd @yogendrasoni @mroeschke @willblenkhornjisc