balakishore nadella balakishore nadella - 3 months ago 28
Python Question

write dataframe to excel file at given path

I have a dataframe
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

This is working :
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

but when I try
out_path = "C:\Users\Bala\output\temp-excel.xlsx"
writer = pd.ExcelWriter(out_path , engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()


I'm getting error : IOError: [Errno 22] invalid mode ('wb') or filename: 'C:\Users\Bala Nadella\output\temp-excel.xlsx'.

How to create file in given path.

Answer

String literals

Study the table in that link. You need to escape your '\' with '\\'. Partly, DOS is responsible for this mess in the world.

out_path = "C:\\Users\\Bala\\output\\temp-excel.xlsx"

or

out_path = "C:/Users/Bala/output/temp-excel.xlsx"

will work.

But best alternative is this:

import os
out_path = os.path.abspath("C:\Users\Bala\output\temp-excel.xlsx")

os.path.abspath will properly format the path native to the OS. I recommend giving the os package a read as well.