Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

When saving a dataframe to csv or excel, pandas will automatically add a first column as row index. I know there's a index=False argument to avoid this. However, if my dataframe have multiple column index, the error shows:

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

Is there another way to skip this first column while keeping the multi-level column name for the header rows inside the excel file?

An example code to generate the dataframe:

import pandas as pd
import numpy as np
col = pd.MultiIndex.from_arrays([['one', 'one', 'one', 'two', 'two', 'two'],
                                ['a', 'b', 'c', 'a', 'b', 'c']])
data = pd.DataFrame(np.random.randn(4, 6), columns=col)
data.to_excel('test.xlsx')
And open the excel file you'll see:
I would like to keep B1:G2 as my column name structure and drop the A:A (and also A3:G3). Thank you for any help~.
                You mean why not make the multi-index to just 1 level? Because I need to keep the format of multiple column index to take less effort on manual operation after export to excel.
– wen tse yang
                Jun 16, 2022 at 8:15
                I didn't try it but it should probably work like @MYousefi mentioned, if you don't set inplace=True in the reset index you can still keep your multi-index df. Something like this: df.reset_index().to_excel(index=False)
– Yehla
                Jun 16, 2022 at 9:41
                @Yehla I just tried this and still see the error. The reason I need this df to be 3-level multi-index is that I want the excel file also see the first 3 rows as my 3-level column names.
– wen tse yang
                Jun 16, 2022 at 10:28
                @wen tse yang Sorry I'm a bit confused. Do you want to see the index in the excel or not? I just tried  df.reset_index(drop=True).to_excel("test.xlsx", index=False) and for me this worked. If you want to see the index as the first columns remove drop=True . The initial df will keep its multi-index.
– Yehla
                Jun 16, 2022 at 11:52
I think currently this is not possible with pandas. You could however solve it with openpyxl. Something like this might do the trick:
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
# opening an excel workbook and worksheet
wb = Workbook()
ws = wb.active
# writing dataframe to excel
for r in dataframe_to_rows(data, index=False, header=True):
    ws.append(r)
# merging header cells
for merge in range(int(data.shape[1]/3)):
    ws.merge_cells(start_row=1, end_row=1, start_column=merge*3+1, end_column=merge*3+3)
# saving to excel
wb.save("test.xlsx")
There is for sure a nicer way to solve the merging of the header cells. But this should suffice to give you some idea.
The output file looks like that:
With openpyxl you can adjust the formatting as well, if this matters to you.
This might solve your problem:
data.T.reset_index(level=1, drop=True).T.to_excel("test.xlsx", index=False)
The first part data.T.reset_index(level=1, drop=True) transposes the dataframe and gets rid of the level 1 index (which in your case is the a,b,c-index).
.T.to_excel("test.xlsx", index=False) then transposes the dataframe to its original form and drops the row index while writing it to the excel.
The output looks like this:
                Thank you but what I need is to keep the column name format as shown in my figure. Is it possible to keep the first row merged format ("one" takes 3 columns and "two" takes 3 columns) and the second row of ('a', 'b', 'c', 'a', 'b', 'c')?
– wen tse yang
                Jun 21, 2022 at 6:02
                Aaah I really misunderstood you. I think there is no way to do this with pandas yet. I added another answer to do it with openpyxl. Hope this helps.
– Yehla
                Jun 22, 2022 at 12:48
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.