CODEWITHSUNDEEP
pythonpandasdataframe
wen tse yang
wen tse yang

Reputation: 100

Is there a way to export a dataframe of multiple column index without the row index?

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: enter image description here

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~.

Upvotes: 2

Views: 2301

Answers (2)

Yehla
Yehla

Reputation: 191

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:

enter image description here

With openpyxl you can adjust the formatting as well, if this matters to you.

Upvotes: 1

Yehla
Yehla

Reputation: 191

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:

enter image description here

Upvotes: 0

Related Questions