ValueError converting multi-index pandas dataframe to Excel

Question

I'm trying to export a multi index pandas dataframe to Excel with the row indexes and column labels intact. I also want the "Pool" index rows in the first column to be merged, which I believe pd.to_excel is supposed to do.

I've tried openpyxl as well, but cannot seem to get this to work without the ValueError. I also tried df=df.reset_index() just to see if I could get a flat file showing all the index and column labels, and that didn't work. Below is the code and results:

Python 3.6.0 (v3.6.0:41df79263a11, Dec 22 2016, 17:23:13) 
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> import numpy as np
>>> import math
>>> s1arrays = [np.array(['Pool1', 'Pool1', 'Pool2', 'Pool2']),
...             np.array(['Rate1', 'Rate2', 'Rate1', 'Rate2'])]
>>> tuples = list(zip(*s1arrays))
>>> index = pd.MultiIndex.from_tuples(tuples, names=['Pool', 'Rate'])
>>> df = pd.DataFrame(np.random.randn(4, 3), columns=[2019, 2020, 2021], index=index)
>>> print(df)
                 2019      2020      2021
Pool  Rate                               
Pool1 Rate1  0.564911 -0.883633 -0.333450
      Rate2 -1.043308  1.543050  1.342350
Pool2 Rate1 -0.838110  2.287242 -1.285863
      Rate2  0.076783 -1.074720  0.801417
>>> df.to_excel('Test Output.xlsx', sheet_name='Sheet1')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/core/generic.py", line 2127, in to_excel
    engine=engine)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/io/formats/excel.py", line 662, in write
    freeze_panes=freeze_panes)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/io/excel.py", line 1605, in write_cells
    xcell.value, fmt = self._value_with_fmt(cell.val)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/openpyxl/cell/cell.py", line 252, in value
    self._bind_value(value)
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/openpyxl/cell/cell.py", line 218, in _bind_value
    raise ValueError("Cannot convert {0!r} to Excel".format(value))
ValueError: Cannot convert 'Pool1' to Excel

What is the meaning of "Cannot convert {0!r} to Excel".format(value)) in this context?

What is your pandas version? For me in pandas 0.24.2, openpyxl: 2.4.10, xlrd: 1.1.0, xlwt: 1.3.0, xlsxwriter: 1.0.2 working nice. Test it by print (pd.show_versions()) — jezrael
– jezrael, Commented May 11, 2019 at 4:49
pandas: 0.24.2, openpyxl: 2.6.2, xlrd: 1.2.0, xlwt: None, xlsxwriter: None. Sure enough, that was the problem! I had to install xlwt and xlsxwriter individually. I would think these packages would install with pandas. Thank you! — dkingma
– dkingma, Commented May 11, 2019 at 5:14

jezrael · Accepted Answer · 2019-05-11 05:19:09Z

1

Problem is in old version of pandas, for me it working nice in last version:

pandas 0.24.2
openpyxl: 2.4.10
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2

So please upgrade your pandas version.

answered May 11, 2019 at 5:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Valdi_Bo · Accepted Answer · 2019-05-11 05:11:15Z

1

I tried your code and got the proper result (I was not able to replicte your error):

I have Python v. 3.7.0, Pandas v. 0.23.4 and Jupyter v. 1.0.0. Maybe you should upgrade your installation?

By the way: You can define s1arrays just as:

s1arrays = [['Pool1', 'Pool1', 'Pool2', 'Pool2'],
            ['Rate1', 'Rate2', 'Rate1', 'Rate2']]

Another remark is that spaces in file names are bad practice. Change the file name e.g. to Test_Output.xlsx.

answered May 11, 2019 at 5:11

Valdi_Bo

31.1k4 gold badges29 silver badges45 bronze badges

Collectives™ on Stack Overflow

ValueError converting multi-index pandas dataframe to Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related