Writing pandas/matplotlib image directly into XLSX file
Asked Answered
G

1

11

I am generating plots in pandas/matplotlib and wish to write them to an XLSX file. I am not looking to create native Excel charts; I am merely writing the plots as non-interactive images. I am using the XlsxWriter library/engine.

The closest solution I have found is the answer to this SO question, which suggests using the XlsxWriter.write_image() method. However, this method appears to take a filename as its input. I am trying to programmatically pass the direct output from a pandas/matplotlib plot() call, e.g. something like this:

h = results.resid.hist()
worksheet.insert_image(row, 0, h) # doesn't work

or this:

s = df.plot(kind="scatter", x="some_x_variable", y="resid")
worksheet.insert_image(row, 0, s) # doesn't work

Is there any way to accomplish this, short of the workaround of writing the image to a disk file first?

Update

Answer below got me on the right track and am accepting. I needed to make a few changes, mainly (I think) because I am using Python 3 and perhaps some API changes. Here is the solution:

from io import BytesIO
import matplotlib.pyplot as plt

imgdata = BytesIO()
fig, ax = plt.subplots()
results.resid.hist(ax=ax)
fig.savefig(imgdata, format="png")
imgdata.seek(0)

worksheet.insert_image(
    row, 0, "",
    {'image_data': imgdata}
)

The "" in the insert_image() code is to trick Excel, which is still expecting a filename/URL/etc.

Gilley answered 8/6, 2015 at 18:54 Comment(5)
Quote: The "" in the insert_image() code is to trick the API, which is still expecting a filename/URL/etc. Strictly speaking, it is Excel that is expecting a filename, and for consistency it would be best to supply one.Halftone
Also, out of curiosity, why are you using matplotlib in this case and not adding a chart to the worksheet directly?Halftone
Thanks, I'll fix that. Re 2nd comment, the reason is that I am not exporting the data that would build the charts, just some different data and the plots themselves: basically regression result summary and a whole lot of diagnostic plots like normal histogram of resids, QQ, scatters vs. resids, etc. This is all for diagnostic purposes, not final business report.Gilley
This inserts a static image, not a real Plot - kind of a crummy version of a real Excel sheet. Any way to insert a "real" graph (with editable axes etc)?Mohun
Yup that's why my question says " I am not looking to create native Excel charts; I am merely writing the plots as non-interactive images."Gilley
H
11

You can save the image to memory as a file object (not to disk) and then use that when inserting to Excel file:

import matplotlib.pyplot as plt
from cStringIO import StringIO
imgdata = StringIO()

fig, ax = plt.subplots()

# Make your plot here referencing ax created before
results.resid.hist(ax=ax)

fig.savefig(imgdata)

worksheet.insert_image(row, 0, imgdata)
Holmann answered 8/6, 2015 at 19:35 Comment(2)
StringIO is gone from Python 3. Any suggestions on how to make this work in Python 3?Meddlesome
In python 3 StringIO and BytesIO are in the builtin io module. Please have a look at the updated OP's question with example.Holmann

© 2022 - 2024 — McMap. All rights reserved.