CODEWITHSUNDEEP
pandas
Chan
Chan

Reputation: 4301

unable to write multi-index dataframe to excel

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.

enter image description here

Upvotes: 1

Views: 1698

Answers (3)

PartonSwift
PartonSwift

Reputation: 21

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

df.set_index(['info'])

Upvotes: 1

Chan
Chan

Reputation: 4301

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()

Upvotes: 2

mike
mike

Reputation: 373

Try

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

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

Upvotes: 0

Related Questions