Blank line below headers created when using MultiIndex and to_excel in Python

Question

I am trying to save a Pandas dataframe to an excel file using the to_excel function with XlsxWriter.

When I print the dataframe to the terminal then it reads as it should, but when I save it to excel and open the file, there is an extra blank line below the headers which shouldn't be there. This only happens when using MultiIndex for the headers, but I need the layered headers that it offers and I can't find a solution.

Below is code from an online MultiIndex example which produces the same result as the project I'm working on. Any solutions would be greatly appreciated.

import numpy as np
import pandas as pd
import xlsxwriter

tuples = [('bar', 'one'), ('bar', 'two'), ('baz', 'one'), ('baz', 'two'), ('foo', 'one'), ('foo', 'two'), ('qux', 'one'), ('qux', 'two')]

index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])

iterables = [['bar', 'baz', 'foo', 'qux'], ['one', 'two']]

pd.MultiIndex.from_product(iterables, names=['first', 'second'])

df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index)

print(df)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='test1')

The excel output created:

Answer 1

Really appreciate both the question and @Teoretic's workaround.

However in my case the merged cells for the Multiindex columns are very useful, and those are lost with @Teoretic's. I've done an alternative workaround hiding the entire row before writing, it works, thus I include it here in case is useful for anybody.

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='test1')
writer.sheets['test1'].set_row(2, None, None, {'hidden': True})
writer.save()

Answer 2

I have one more solution. It based on idea to save in two calls of the to_excel function - the first save only header, the second save table without header. To realise I prepared such function:

def save_double_column_df(df, xl_writer, startrow = 0, **kwargs):
    '''Function to save doublecolumn DataFrame, to xlwriter'''
    # inputs:
    # df - pandas dataframe to save
    # xl_writer - book for saving
    # startrow - row from wich data frame will begins
    # **kwargs - arguments of `to_excel` function of DataFrame`
    df.drop(df.index).to_excel(xl_writer, startrow = startrow, **kwargs)
    df.to_excel(xl_writer, startrow = startrow + 1, header = False, **kwargs)

The example of using:

y = [('K1','l'),("K1",'m'),("K2",'l'),('K2','m'),("K3",'l'),('K3','m')]
col_list = pd.MultiIndex.from_tuples(y)
A = pd.DataFrame(np.random.randint(2,5,(4,6)), columns = col_list)

xl_writer = pd.ExcelWriter("test_result/multiindex_saving.xlsx",engine='xlsxwriter')
save_double_column_df(A, xl_writer, sheet_name = 'hello')
xl_writer.close()

The result looks like:

Answer 3

This is most likely a bug in pandas.

See this question for suggested solution:

No easy way out of this but to delete that row by reading the xlsx in again.

Also there is a link into the GitHub issue, addressing this topic.

So I made this workaround, it might be helpful for you:

df = pd.read_excel('/home/teoretic/test.xlsx', index_col=0)
df = df.drop(np.nan)  # <== dropping an empty row

rename_dct = dict.fromkeys(df.loc[:,df.columns.str.contains('^Unnamed')], '')
df = df.rename(columns=rename_dct)  # <== renaming 'Unnamed' columns to blank space 

writer = pd.ExcelWriter('/home/teoretic/test_new.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='test1')

writer.close()

This is an output file:

Answer 4

I have found the following workaround using the package openpyxl by reading the file with the empty line, deleting the line, and re-writing the file. The advantage I've found with this method is that it preserves the pretty merged cells in the MultiIndex columns and actually deletes the empty row from the Excel file. By actually deleting the empty row, if the rest of your data is numeric, this will allow you to use Excel's filtering capabilities in your spreadsheet out of the box without having to manually delete the empty row in the file.

# Assuming df is your data frame with MultiIndex columns that you have already written to Excel

# Load the file with the empty line and select the sheet you want to edit
wb = openpyxl.load_workbook(filename = 'file_with_empty_line.xlsx')
ws = wb['sheet_name_to_edit']

# The row number to delete is 1-indexed in Excel
row_to_delete = df.columns.nlevels
ws.delete_rows(row_to_delete)

# If you want to write the name of the index into the empty cell above the index col
# after deleting the row (assuming you are writing to the first column):
ws['A'+str(row_to_delete)]=df.index.name

# Save the modified workbook to file
wb.save(filename = 'file_without_emtpy_line.xlsx')

While its not ideal to have to import an entire package to handle this, my usage case required me to not simply hide the missing row. @lrnzcig's solution is much better if you can get away with only having to hide the empty row.

Answer 5

I used ExcelWriter to remove that blank line. I was adding the df to an existing sheet.

with pd.ExcelWriter(“PATH_TO_EXCEL”,mode=“a”,engine=“openpyxl”) as writer:
    writer.book=load_workbook(“PATH_TO_EXCEL”)
    df.to_excel(writer,sheet_name=“sample”,startrow=0,startcol=0)
    writer.sheets[‘sample’].delete_rows(3)