CODEWITHSUNDEEP
pythonexcelpandas
Roggan1337
Roggan1337

Reputation: 112

Merge Excel files with pandas in python

I'm almost done with merging excel files with pandas in python but when I give the path it wont work. I get the error ''No such file or directory: 'file1.xlsx'''. When I leave the path empty it work but I want to decide from what folder it should take files from. AND I saved the file the folder 'excel'

cwd = os.path.abspath('/Users/Viktor/downloads/excel') #If i leave it empty and have files in /Viktor it works but I have the desired excel files in /excel 

print(cwd)
files = os.listdir(cwd)  
df = pd.DataFrame()
for file in files:
   if file.endswith('.xlsx'):
       df = df.append(pd.read_excel(file), ignore_index=True) 
df.head() 
df.to_excel(r'/Users/Viktor/Downloads/excel/resultat/merged.xlsx')

Upvotes: 1

Views: 4268

Answers (3)

Trenton McKinney
Trenton McKinney

Reputation: 62403

from pathlib import Path
import pandas as pd

# path to files
p = Path('/Users/Viktor/downloads/excel')

# find the xlsx files
files = p.glob('*.xlsx')

# create the dataframe 
df = pd.concat([pd.read_excel(file, ignore_index=True) for file in files])

# save the file
df.to_excel(r'/Users/Viktor/Downloads/excel/resultat/merged.xlsx')

Upvotes: 1

user13315601
user13315601

Reputation:

pd.read_excel(file) looks for the file relative to the path where the script is executed. If you execute in '/Users/Viktor/' try with:

import os
import pandas as pd

cwd = os.path.abspath('/Users/Viktor/downloads/excel') #If i leave it empty and have files in /Viktor it works but I have the desired excel files in /excel 

#print(cwd)
files = os.listdir(cwd)  


df = pd.DataFrame()
for file in files:
   if file.endswith('.xlsx'):
       df = df.append(pd.read_excel('downloads/excel/' + file), ignore_index=True) 
df.head() 
df.to_excel(r'/Users/Viktor/downloads/excel/resultat/merged.xlsx')

Upvotes: 2

Peter
Peter

Reputation: 12315

How about actually changing the current working directory with

os.chdir(cwd)

Just printing the path doesn't help.

Upvotes: 1

Related Questions