Convert Pandas DataFrame to bytes-like object

Pickle

Pickle is a reproducible format for a Pandas dataframe, but it's only for internal use among trusted users. It's not for sharing with untrusted users due to security reasons.

import pickle

# Export:
my_bytes = pickle.dumps(df, protocol=4)

# Import:
df_restored = pickle.loads(my_bytes)

This was tested with Pandas 1.1.2. Unfortunately this failed for a very large dataframe, but then what worked is pickling and parallel-compressing each column individually, followed by pickling this list. Alternatively you can pickle chunks of the large dataframe.

CSV

If you must use a CSV representation:

df.to_csv(index=False).encode()

Note that various datatypes are lost when using CSV.

Parquet

See this answer. Note that various datatypes are converted when using parquet.

Excel

Avoid its use for the most part because it limits the max number of rows and columns.


I required to upload the file object to S3 via boto3 which didn't accept the pandas bytes object. So building on the answer from Asclepius I cast the object to a BytesIO, eg:

from io import BytesIO  
data = BytesIO(df.to_csv(index=False).encode('utf-8'))

I solved the issue by upgrading pandas to newer version.

 import io
 towrite = io.BytesIO()
 df.to_excel(towrite)  # write to BytesIO buffer
 towrite.seek(0) 
 print(towrite)
 b''
 print(type(towrite))
 _io.BytesIO

if you want to see the bytes-like object use getvalue,

print(towrite.getvalue())
b'PK\x03\x04\x14\x00\x00\x00\x08\x00\x00\x00!\x00<\xb