Merge Excel files with pandas in python

Question

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')

user13315601 · Accepted Answer · 2020-07-22 19:34:12Z

2

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')

answered Jul 22, 2020 at 19:34

user13315601

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

Comments

Peter · Accepted Answer · 2020-07-22 18:56:20Z

1

How about actually changing the current working directory with

os.chdir(cwd)

Just printing the path doesn't help.

answered Jul 22, 2020 at 18:56

Peter

12.7k2 gold badges37 silver badges52 bronze badges

Comments

Trenton McKinney · Accepted Answer · 2020-07-22 19:55:31Z

1

Use pathlib
- Path.glob() to find all the files
- Use Path.rglob() if you want to include subdirectories
Use pandas.concat to combine the dataframes created with the pd.read_excel in the list comprehension

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')

edited Jul 22, 2020 at 19:55

answered Jul 22, 2020 at 19:47

Trenton McKinney

63.2k41 gold badges170 silver badges213 bronze badges

Collectives™ on Stack Overflow

Merge Excel files with pandas in python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related