Convert Pandas DataFrame to bytes-like object

Question

Hi I am trying to convert my df to binary and store it in a variable.

my_df:

 df = pd.DataFrame({'A':[1,2,3],'B':[4,5,6]})

my code:

 import io
 towrite = io.BytesIO()
 df.to_excel(towrite)  # write to BytesIO buffer
 towrite.seek(0)  # reset pointer
 

I am getting AttributeError: '_io.BytesIO' object has no attribute 'write_cells'

Full Traceback:

AttributeError                            Traceback (most recent call last)
<ipython-input-25-be6ee9d9ede6> in <module>()
      1 towrite = io.BytesIO()
----> 2 df.to_excel(towrite)  # write to BytesIO buffer
      3 towrite.seek(0)  # reset pointer
      4 encoded = base64.b64encode(towrite.read())  #

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py in to_excel(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose, freeze_panes)
   1422         formatter.write(excel_writer, sheet_name=sheet_name, startrow=startrow,
   1423                         startcol=startcol, freeze_panes=freeze_panes,
-> 1424                         engine=engine)
   1425 
   1426     def to_stata(self, fname, convert_dates=None, write_index=True,

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\formats\excel.py in write(self, writer, sheet_name, startrow, startcol, freeze_panes, engine)
    624 
    625         formatted_cells = self.get_formatted_cells()
--> 626         writer.write_cells(formatted_cells, sheet_name,
    627                            startrow=startrow, startcol=startcol,
    628                            freeze_panes=freeze_panes)

AttributeError: '_io.BytesIO' object has no attribute 'write_cells'

Answer 1

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

Answer 2

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.

Answer 3

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