pandas: write dataframe to excel file object (not file)?

Question

I have a dataframe that I want to convert to excel file, and return it using HTTP. Dataframe's to_excel method accepts either a path, or an ExcelWriter, which, in turn, refers to a path.

Is there any way to convert the dataframe to a file object, without writing it to disk?

the problem is, I want to convert it to excel format first, not just convert the dataframe to bytes — blue note
– blue note, Commented Jul 10, 2019 at 17:46

Alex · Accepted Answer · 2019-07-10 18:35:11Z

10

This can be done using the BytesIO Object in the standard library:

import pandas
from io import BytesIO

# Create Random Data for example
cols = ["col1", "col2"]
df = pandas.DataFrame.from_records([{k: 0.0 for k in cols} for _ in range(25)])

# Create an in memory binary file object, and write the dataframe to it.
in_memory_fp = BytesIO()
df.to_excel(in_memory_fp)

# Write the file out to disk to demonstrate that it worked.
in_memory_fp.seek(0,0)
with open("my_file.xlsx", 'wb') as f:
    f.write(in_memory_fp.read())

In the above example, I wrote the object out to a file so you can verify that it works. If you want to just return the raw binary data in memory all you need is:

in_memory_fp.seek(0,0)
binary_xl = in_memory_fp.read()

answered Jul 10, 2019 at 18:35

Alex

1,27014 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas: write dataframe to excel file object (not file)?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related