to_excel leaves ExcelWriter Handle Open

[s-celles]

Hello,

try

import requests
from pandas.io.json import json_normalize
import json
import pandas as pd
response = requests.get("http://api.openweathermap.org/data/2.5/history/station?start=1356220800&end=1356307200&type=hour&id=5530")
df = json_normalize(json.loads(response.text)['list'])
df['dt'] = pd.to_datetime(df['dt'], unit='s')
#df = df.set_index('dt')
df = df[['dt', 'main.temp.ma']]
print(df)

engine = 'openpyxl'
filename = "out_%s.xlsx" % engine
writer = pd.ExcelWriter(filename, engine=engine)
#df.to_excel("out.xls")
df.to_excel(writer)

engine = 'xlsxwriter'
filename = "out_%s.xlsx" % engine
writer = pd.ExcelWriter(filename, engine=engine)
#df.to_excel("out.xls")
df.to_excel(writer)

engine = 'xlwt'
filename = "out_%s.xls" % engine
writer = pd.ExcelWriter(filename, engine=engine)
#df.to_excel("out.xls")
df.to_excel(writer)

Only out_xlsxwriter.xlsx is create
I was expecting out_openpyxl.xlsx and out_xlwt.xls to be created.

See also #9139

Kind regards

[jorisvandenbossche]

@scls19fr Thanks for the report! Do you see the correct behaviour if you do it without ExcelWriter? (using df.to_excel(.., engine=..)) Also, can you show the output of pd.show_versions()?
Further, do you see a warning when running the first example with openpyxl? I get "The installed version of lxml is too old to be used with openpyxl" which could maybe the reason that it does not work.

cc @neirbowj @jtratner

[jorisvandenbossche]

@scls19fr Some things:

• You have to close the ExcelWriter, if I do that, I get the expected output for all three engines. But apparantly, this is not needed for xlsxwriter, as for that it also works without closing (or using a context manager). The docs are also not very clear on that point.

Code I used to test:

df = pd.DataFrame(np.random.randn(3,3), columns=list('abc'))

engine = 'openpyxl' # or 'xlsxwriter', 'xlwt'

# standard
df.to_excel("out_{}_1.xlsx".format(engine), engine=engine)

# writer with context
with pd.ExcelWriter("out_{}_2.xlsx".format(engine), engine=engine) as writer:
    df.to_excel(writer)

# writer with close
writer = pd.ExcelWriter("out_{}_3.xlsx".format(engine), engine=engine)
df.to_excel(writer)
writer.close()

# writer without close
writer = pd.ExcelWriter("out_{}_4.xlsx".format(engine), engine=engine)
df.to_excel(writer)

For xlsxwriter I get all 4 files, but for openpyxl and xlwt only the first three

[s-celles]

Thanks for this tip.

pd.show_versions()

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.6.final.0
python-bits: 64
OS: Darwin
OS-release: 14.0.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: fr_FR.UTF-8

pandas: 0.15.2
nose: 1.3.4
Cython: 0.21.1
numpy: 1.9.1
scipy: 0.14.1rc1
statsmodels: 0.6.1
IPython: 2.3.1
sphinx: 1.2.3
patsy: 0.3.0
dateutil: 2.3
pytz: 2014.9
bottleneck: None
tables: 3.1.1
numexpr: 2.4
matplotlib: 1.4.2
openpyxl: 2.0.3
xlrd: 0.9.3
xlwt: 0.7.5
xlsxwriter: 0.6.4
lxml: 3.4.1
bs4: 4.3.2
html5lib: 0.999
httplib2: 0.8
apiclient: None
rpy2: None
sqlalchemy: 0.9.8
pymysql: 0.6.2.None
psycopg2: None

[jorisvandenbossche]

I suppose this depends on the behaviour of the used engine (if that already writes the file or not before closing the sheet explicitely).

The question is what the expected behaviour should be. If closing is needed, this should at least be documented better.

[jbrockmendel]

Is this fixed by #30096?

[mroeschke]

Guessing this was closed by #30096. Happy to reopen if this wasnt the case.