3

I have a pandas dataframe as below:

header = [np.array(['location','location','location','location2','location2','location2']), 
np.array(['S1','S2','S3','S4','S5','S6'])] 
df = pd.DataFrame(np.random.randn(5, 6), columns = header ) 
df

I want to export my dataframe to an excel sheet ignoring the index. Here is my code which exports my dataframe to excel spreadsheet but with index. when I am using the parameter, index = False, It gives me an error.

# output all the consolidated input to an excel sheet
out_file_name = os.path.join(folder_location, "templates", future_template)
writer = pd.ExcelWriter(out_file_name, engine='xlsxwriter')
# Write each dataframe to a different worksheet.
df.to_excel(writer, sheet_name='Ratings Inputs')
# Close the Pandas Excel writer and output the Excel file.
writer.save()
Shanoo
  • 1,185
  • 1
  • 11
  • 38
  • What error the index False gives you? – Mntfr Mar 04 '19 at 16:27
  • 1
    This is the error message I got with index = False "NotImplementedError: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented." – Shanoo Mar 04 '19 at 16:35
  • Oh then what happens is that you don't have an updated version of pandas this is a feature implemented in 0.17 if I recall correctly. Just update your pandas. – Mntfr Mar 04 '19 at 16:40

1 Answers1

0

DataFrame.to_excel(index=False) is still unsupported for MultiIndex (as of Pandas 1.3.4, Oct 2021). You will get the error:

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

You can try some workarounds instead:

  1. Write with index=True. Then using openpyxl, re-open the file, delete the undesired cols/rows, and re-save the file. This is a slow process, so it may not be practical for large dataframes.

  2. You can manually write the MultiIndex headers. This won't have merged cells though. See How to hide the rows index

wisbucky
  • 33,218
  • 10
  • 150
  • 101