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

Openpyxl Timedelta Behavior Inconsistent in Excel Roundtrip #19900

Closed
WillAyd opened this issue Feb 26, 2018 · 0 comments · Fixed by #19921
Closed

Openpyxl Timedelta Behavior Inconsistent in Excel Roundtrip #19900

WillAyd opened this issue Feb 26, 2018 · 0 comments · Fixed by #19921
Labels
IO Excel read_excel, to_excel Timedelta Timedelta data type
Milestone

Comments

@WillAyd
Copy link
Member

WillAyd commented Feb 26, 2018

When round-tripping excel files in tandem with Timedelta data, the values returned by openpyxl differ from those returned by xlwt and xlsxwriter. To illustrate:

In []: df = pd.DataFrame([pd.to_timedelta(1, 'D')])

In []: df.to_excel('some_file.xls') # xlwt
    .: pd.read_excel('some_file.xls')
Out[]: 
   0
0  1

In []: pd.set_option('io.excel.xlsx.writer', 'xlsxwriter')
In []: df.to_excel('some_file.xlsx')
    .: pd.read_excel('some_file.xlsx')
Out[]: 
   0
0  1

In []: pd.set_option('io.excel.xlsx.writer', 'openpyxl')
In []: df.to_excel('some_file.xlsx')
    .: pd.read_excel('some_file.xlsx')
Out[]: 
           0
0 1900-01-01

Only the xlwt option was being tested previously, but I uncovered this nuance while refactoring tests in #19829.

While the openpyxl result maintains date-like metadata I question if it's worth being different from the other engines, especially since that metadata misrepresents the object as a datetime and not necessarily a Timedelta (assuming the latter doesn't exist in Excel). Curious to hear others' thoughts

INSTALLED VERSIONS

commit: 92dbc78
python: 3.6.4.final.0
python-bits: 64
OS: Darwin
OS-release: 17.4.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.0.dev0+383.g92dbc78af
pytest: 3.4.1
pip: 9.0.1
setuptools: 38.5.1
Cython: 0.27.3
numpy: 1.14.1
scipy: 1.0.0
pyarrow: 0.8.0
xarray: 0.10.0
IPython: 6.2.1
sphinx: 1.7.0
patsy: 0.5.0
dateutil: 2.6.1
pytz: 2018.3
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.4
feather: 0.4.0
matplotlib: 2.1.2
openpyxl: 2.5.0
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml: 4.1.1
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: 1.2.1
pymysql: 0.8.0
psycopg2: None
jinja2: 2.10
s3fs: 0.1.3
fastparquet: 0.1.4
pandas_gbq: None
pandas_datareader: None

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
IO Excel read_excel, to_excel Timedelta Timedelta data type
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants