unable to write multi-index dataframe to excel

Question

I want to write a multi-index dataframe to excel:

col = [['info', '', 'key'], ['alert', 'date', 'price'], ['alert', 'date', 'amount']]
df = pd.DataFrame(columns = pd.MultiIndex.from_tuples(col))
df.loc[0, :] = np.random.random(3)
df.to_excel('data.xlsx', index = False)

However, an error occurs:

NotImplementedError: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.

I checked pandas version : pd.__version__ and the result is '0.25.3'.

How to solve the problem?

Thank you.

How important is for you to export directly as xlsx? If not very important, you can export as CSV first and then load into Excel. — smishra
– smishra, Commented Dec 10, 2019 at 3:16
Checkout this answer: stackoverflow.com/questions/17349574/… — smishra
– smishra, Commented Dec 10, 2019 at 3:19
It has been said that this problem has been solved in version 0.17.0 in stackoverflow.com/questions/33633858/…. But why do I have this problem? — Chan
– Chan, Commented Dec 10, 2019 at 3:31

Chan · Accepted Answer · 2019-12-10 07:27:33Z

2

After searching the web, I used pywin32 to solve the problem.

import win32com.client as win32
df.to_excel('data.xlsx', index = True)
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.DisplayAlerts = False
wb = excel.Workbooks.Open('data.xlsx')
excel.Visible = True
ws = wb.Worksheets('Sheet1')
ws.Columns(1).EntireColumn.Delete()
wb.SaveAs('data.xlsx')
excel.Application.Quit()

answered Dec 10, 2019 at 7:27

Chan

4,48110 gold badges34 silver badges66 bronze badges

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

Comments

PartonSwift · Accepted Answer · 2022-11-01 16:31:36Z

1

To remove the extra column, try resetting the index to 'info' column.

df.set_index(['info'])

answered Nov 1, 2022 at 16:31

PartonSwift

218 bronze badges

Comments

mike · Accepted Answer · 2019-12-10 03:18:11Z

0

Try

df.to_excel(r'data.xlsx', index = True)

ref: https://github.com/pandas-dev/pandas/issues/11292#issuecomment-447150410

answered Dec 10, 2019 at 3:18

mike

3733 silver badges14 bronze badges

4 Comments

Chan Over a year ago

if setting index to True, there will be an extra column in the excel file. How to remove that column?

Chan Over a year ago

Please refer to the revised question.

Chan Over a year ago

Can anyone help?

Nycbros Over a year ago

Adding comment to come back later. Also, I am also waiting on a answer to this question.

Collectives™ on Stack Overflow

unable to write multi-index dataframe to excel

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related