Direct chart plotting Pandas DataFrame columns to Xlsxwriter in a loop

Question

I am looking for an efficient way to print multiple Pandas DataFrame plots directly to Excel using xlsxwriter without the need to save the plot to file each time.

I have my DataFrame generated and I am using a dictionary to outline the combinations of different plots I am looking to create. I have seen an example of the kind of method I am looking for at this previous answer in this link Writing pandas/matplotlib image directly into XLSX file , but as I am looping I think I am creating issues with the buffer from the previous loop.

Essentially I have a dictionary list which outlines (also a list) the plots I am looking to create. The ‘Plots’ field basically outlines the DataFrame column names combinations I am looking to create. My dict structure is along the lines of:

ITEM_DF={
        "First_Item":{
                    ID':'96',
                    'COLUMNS':[ 'Frame','Power','Size','Length'],
                    'Plots':[['Frame','Power'],['Frame','Size']]
                    },
        "Second_Item":{
                    ID':'435',
                    'COLUMNS':[ 'Frame','Height','Width','MidPoint'],
                    'Plots':[['Frame','Height'],['Frame','MidPoint']]
                    },  
        }

So using a similar idea to the earlier mentioned link I tried something along the lines of:

for item in Item_DF[key]['Plots']:
    imgdata= BytesIO()
    ploter = DF.plot(x=item[0], y=item[1])
    fig = ploter.get_figure()
    fig.savefig(imgdata, format="png")
    imgdata.seek(0)
    worksheet.insert_image(row_num, col_num, "",{'image_data': imgdata})
    row_num+=10

But I get the following error: IOError: [Errno 22] invalid mode ('rb') or filename: ''

Is this kind of idea the correct way of going about multiple plotting directly into Excel using Xlsxwriter, or should I look at alternatives?

Thanks for your time.

Answer 1

I get the following error: IOError: [Errno 22] invalid mode ('rb') or filename: ''

From where/what?

At a guess I would say it is because you didn't supply a filename. Try adding a default name like 'plot%d.png' % row_num for each plot.