Closed
Description
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.
Metadata
Metadata
Assignees
Labels
No labels