CODEWITHSUNDEEP
pythonexcelpandasdataframe
cowboykevin05
cowboykevin05

Reputation: 85

Python: Excel file (xlsx) export with variable as the file path, using pandas

I defined an .xlsx file path as the variable output:

print(output)

r'C:\Users\Kev\Documents\Python code.xlsx'

I want to export a pandas dataframe as an .xlxs file, but need to have the file path as the output variable.

I can get the code to work with the file path. I've tried about a dozen ways (copying and/or piecing code together from documentation, stack overflow, blogs, etc.) and getting a variety of errors. None worked. Here is one that worked with the file path:

df = pd.DataFrame(file_list)

df.to_excel(r'C:\Users\Kev\Documents\Python code.xlsx', index=False)

I would want something like: df.to_excel(output, index=False) In any form or package, as long as it produces the same xlsx file and won’t need to be edited to change the file path and name (that would be done where the variable output is defined.

I've attempted several iterations on the XlsxWriter site, the openpyxl site, the pandas site, etc. (with the appropriate python packages). Working in Jupyter Notebook, Python 3.8.

Any resources, packages, or code that will help me to use a variable in place of a file path for an xlsx export from a pandas dataframe?

Why I want it like this is a long story, but basically I'll have several places at the top of the code where myself and other (inexperienced) coders can quickly put file paths in and search for keywords (rather than hunt through code to find where to replace paths). The data itself is file paths that I'll iteratively search through (this is the beginning of a larger project).

Upvotes: 0

Views: 10441

Answers (4)

Gonçalo Peres
Gonçalo Peres

Reputation: 13582

Assuming that OP's dataframe is df, that OP is using Windows and wants to store the file in the Desktop, OP's username is cowboykevin05, and the filename that one wants is 0001.xlsx, one can use os.path as follows

from os import path

df.to_excel(path.join('C:\\Users\\cowboykevin05\\Desktop', '0001.xlsx'), index=False)

Upvotes: 0

Schulzjo
Schulzjo

Reputation: 317

You can use format strings with python3

import pandas as pd

df = pd.DataFrame({"a":"b"}, {"c": "d"})
file_name = "filename.xlsx"
df.to_excel(f"/your/path/to/file/{file_name}", index=False)

Upvotes: 1

Mahmud Alptekin
Mahmud Alptekin

Reputation: 175

os module would be the most useful here:

from os import path
output = path.abspath("your_excel_file.xlsx")
print(output)

this will return the current working directory path plus the file name you've put into the abspath function as a parameter. Also for those interested about why some people use backslash "\" and not forwardslash "/" while writing file paths here is a good stackoverflow answer for it So what IS the right direction of the path's slash (/ or \) under Windows?

Upvotes: 2

Talal Siddiqui
Talal Siddiqui

Reputation: 106

try to put the path this way

output = "C://Users//Kev//Documents//Python code.xlsx"
df.to_excel(output , index=False)

Always worked for me

or you can also do like

output = "C://Users//Kev//Documents//"
df.to_excel(output +"Python code.xlsx" , index=False)

Upvotes: 2

Related Questions