I have a dataframe as follows:

A      B
AA     BB
-----------
2      1
3      4
5      8
9      7

This dataframe have multilevel column. To save in excel I am doing the following

col1= ["A","B"]

col2= ["AA","BB"]

r1 = [2,1]

r2 =[3,4]

df = pd.DataFrame(columns = [col1,col2])

df.loc[(len(df))] = r1

df.to_excel("name.xlsx")

But in excel I can see one empty row automatically added between column name and data. How can I save the dataframe in xlax format so that empty row will not populate?

CodePudding user response：

It is nothing but a bug of "to_excel" while processing multiindex.

Just use to_csv:

import pandas as pd
col1= ["A","B"]

col2= ["AA","BB"]

r1 = [2,1]

r2 =[3,4]

df = pd.DataFrame(columns = [col1,col2])

df.loc[(len(df))] = r1
df.loc[(len(df))] = r2

df.to_csv("name.csv")

CodePudding user response：

Here is a workaround - reset the column index:

df.T.reset_index(level=1).T.to_excel("name.xlsx")

          A   B
level_1  AA  BB
0         2   1

To set custom names for the column levels (do this, and then export to excel):

• only one of the levels:

df.columns.set_names('name_lev_2', level=1, inplace=True)

             A   B
name_lev_2  AA  BB
0            2   1

• both levels:

df.rename_axis(columns=['name_lev_1', 'name_lev_2'])  # not in place

name_lev_1   A   B
name_lev_2  AA  BB
0            2   1