How to write multiIndex-columns excel with pandas

Question

I want to export a multiIndex-column.

I read an excel file (https://drive.google.com/open?id=1G6nE5wiNRf5sip22dQ8dfhuKgxzm4f8E) and exported it with the following code:

df = pd.read_excel('sample.xlsx')
df.to_excel('sample2.xlsx', index = False)

However, sample2.xlsx has different format as sample.xlsx.

For example, there are merged cells in sample.xlsx but not in sample2.xlsx and the blank cells in sample.xlsx become Unnamed:xx.

You can view sample2.xlsx here.

How to solve this problem?

Thank you.

can you update the sample data as well so others can understand the format differences — Pavan Kumar Polavarapu
– Pavan Kumar Polavarapu, Commented Oct 4, 2019 at 2:19

Divya Dass · Accepted Answer · 2019-10-04 09:54:46Z

1

Since you working with xlsx files, openpyxl package will do the job.

import openpyxl
wb_obj = openpyxl.load_workbook('sample.xlsx')
wb_obj.save('sample2.xlsx')

3 Comments

Thanks, Divya. How to convert a pandas dataframe to openpyxl object to save it?

sorry, but why convert to openpyxl object to save when we can directly save pandas dataframe?

It is because saving with pandas cannot keep the multi-indexed columns structure.