Not able to read file even though it is on the dictionary

Question

I'm trying to append multiple Excel files. Tried to append them as below, but I am not able to.

path = r"C:\Users\u07\Downloads\IEX Prices"
files = os.listdir(path)
files


files_xls = [f for f in files if f[-4:] == 'xlsx']   # Pick out files whose last 4 characters are 'xlsx' 
files_xls

These are the files which I want to append. And the files are in files_xls

['PriceMinute (1).xlsx',
 'PriceMinute (10).xlsx',]

I am using this code to append the files

df = pd.DataFrame()
for f in files_xls:
    data = pd.read_excel(f)
    df = df.append(data)
     

But I'm getting this error

FileNotFoundError: [Errno 2] No such file or directory: 'PriceMinute (1).xlsx'

Can someone please help?

Answer 1

When you use os.listdir it will return only list of filenames. If you want to read any of the file, you must append actual file path.

import os
path = r"C:\Users\u07\Downloads\IEX Prices"
files = os.listdir(path)

df = pd.DataFrame()
for f in files_xls:
    data = pd.read_excel(os.path.join(path, f))
    df = df.append(data)

append method is deprecated better to use concat.

import os
import pandas as pd

path = r"C:\Users\u07\Downloads\IEX Prices"
files = os.listdir(path)

lst = []

for f in files_xls:
    data = pd.read_excel(os.path.join(path, f))
    lst.append(data)

df = pd.concat(lst)

Answer 2

This can happen when you are not really in the same working directory when you launch python. This can be solved by placing those files in the same folder as the python script or just calling the files with the whole root direction.

Since you have your path already captured in a variable, you can add it this way

df = pd.DataFrame()
for f in files_xls:
    file_path = path + f
    data = pd.read_excel(file_path)
    df = pd.concat([df, data], axis=0, ignore_index=True)

This will load the file from the absolute path. To check where you python script is you can always use pwd().