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?

You should add the error you're getting. Does the directory C:\Users\Bala\output\ exist? — user357269
– user357269, Commented Aug 10, 2016 at 21:55
Yes. That is local directory of my box and it works for to_csv. — balakishore nadella
– balakishore nadella, Commented Aug 10, 2016 at 21:58

Kartik · Accepted Answer · 2016-08-11 22:59:43Z

21

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.

edited Aug 11, 2016 at 22:59

answered Aug 10, 2016 at 22:03

Kartik

8,73345 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

John Y Over a year ago

os.path.abspath is a nice function, but you have completely misrepresented what it does. It can't magically interpret literal backslashes in strings, and it is not at all what is needed here. Honestly, the best solution is to use forward slashes instead of backslashes (even in Windows). If you insist on using backslashes, you should be using raw strings.

Kartik Over a year ago

You are right. Answer edited. I used to use it for situations, when I had both Windows and Ubuntu on my machine, and required the same code to run on files in my CWD. I found it quite handy, but it is not the solution to this question.

John Y Over a year ago

Good edit, now I feel comfortable upvoting this answer.

Rachel Over a year ago

Still doesn't work for me somehow. I tried to wrap the entire thing in a function:

def excel_to_path(frame, path):     writer = pd.ExcelWriter(path , engine='xlsxwriter')     frame.to_excel(writer, sheet_name='Sheet1')     writer.save()

and to pass dfand "../foo/bar/myfile.xlsx" any ideas?

Kartik Over a year ago

@Rachel try replacing the relative path with absolute path. Also please use context manager. The doc shows pandas implementation. It'll save your butt when something goes wrong.

user357269 · Accepted Answer · 2016-08-10 22:01:41Z

4

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!

answered Aug 10, 2016 at 22:01

user357269

1,91317 silver badges45 bronze badges

Collectives™ on Stack Overflow

write dataframe to excel file at given path

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related