Blank Line Below Headers Created When Using Multiindex And To_excel In Python

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

Solution 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()

Solution 2:

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:

Solution 3:

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.

Solution 4:

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)