DataFrame.to_excel with xlsxwriter and constant_memory makes most of the cells empty

[mhooreman]

Problem description

Hello,

When we export data frames to excel using xlsxwriter with the option constant_memory set to True, most of the cells are empty.

Thanks a lot

Code Sample

import seaborn.apionly as sns
import pandas as pd
iris = sns.load_dataset('iris')
with pd.ExcelWriter('constant_memory_false.xlsx', engine='xlsxwriter', options=dict(constant_memory=False)) as xlw:
    iris.to_excel(xlw)  # Open it: this is OK
with pd.ExcelWriter('constant_memory_true.xlsx', engine='xlsxwriter', options=dict(constant_memory=True)) as xlw:
    iris.to_excel(xlw)  # Open it: most of the data are missing

Output of pd.show_versions()

commit: None python: 3.5.2.final.0 python-bits: 64 OS: Linux OS-release: 3.13.0-87-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: en_US.UTF-8 LOCALE: en_US.UTF-8

pandas: 0.19.2
nose: 1.3.7
pip: 9.0.1
setuptools: 34.1.1
Cython: 0.25.2
numpy: 1.12.0
scipy: 0.18.1
statsmodels: 0.6.1
xarray: None
IPython: 5.2.2
sphinx: 1.5.2
patsy: 0.4.1
dateutil: 2.6.0
pytz: 2016.10
blosc: None
bottleneck: 1.2.0
tables: 3.3.0
numexpr: 2.6.2
matplotlib: 2.0.0
openpyxl: None
xlrd: 1.0.0
xlwt: 1.2.0
xlsxwriter: 0.9.6
lxml: None
bs4: 4.5.3
html5lib: 0.9999999
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: 2.6.2 (dt dec pq3 ext lo64)
jinja2: 2.9.5
boto: None
pandas_datareader: None

[chris-b1]

xref #11355

This happens because pandas writes data column-by-column, where xlsxwriter needs the data row-by-row to use constant_memory. Fixing the above would fix this (a little complex, but PR welcome!) or we could also catch and raise a NotImplementedError in the meantime.

[mhooreman]

Thanks Chris

[bryanfree66]

import seaborn.apionly as sns
import pandas as pd
iris = sns.load_dataset('iris')
with pd.ExcelWriter('constant_memory_false.xlsx', engine='xlsxwriter',
options=dict(constant_memory=False)) as xlw:
iris.to_excel(xlw)
with pd.ExcelWriter('constant_memory_true.xlsx', engine='xlsxwriter',
options=dict(constant_memory=True)) as xlw:
iris.to_excel(xlw)
Traceback (most recent call last):
File "", line 1, in
File "/Users/bryan/code/pandas/pandas/io/excel.py", line 1765, in init
**engine_kwargs)
File "/Users/bryan/code/pandas/pandas/io/excel.py", line 924, in init
raise NotImplementedError('The option constant_memory=True is '
NotImplementedError: The option constant_memory=True is not supported.

[ohlr]

What is needed to solve this?
With pandas version v1 it does not throw an error but only reports the last row of the DF