write dataframe to excel file at given path

Question

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 can I create a file at a given path?

Answer 1

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 = r"C:\Users\Bala\output\temp-excel.xlsx" # the `r` prefix means raw string

But best alternative is this:

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

It will work on any platform.

---

Edited to remove the solution with os.path.abspath. After the comment below this answer, I read the documentation myself and realized that it has a different purpose. Although I have used it in the past to make my code dual OS friendly, because it neatly appends CWD to the path, and changes / to \\ when moving from Debian to Windows and vice versa.

Answer 2

In a string the backslash is an escape character. It means that you're giving a special command, not a regular character. Eg. "Hello\nWorld" means, put a newline between "Hello" and "World".

If you actually want to use a backslash as a character, type it as "\\".

Or better yet, just use forward slashes!