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?

You only have a filename, but you need a fully qualified path to read the file. Otherwise Python is gonna look in the current directory where you run the script for that file. — Mushroomator, Aug 08 '23 at 11:15
To follow up on the above comment: insert a line `f = os.path.join(path, f)` above the `data = pd.read_excel(f)` line. — 9769953, Aug 08 '23 at 11:18
Does this answer your question? [Platform independent path concatenation using "/" , "\"?](https://stackoverflow.com/questions/10918682/platform-independent-path-concatenation-using) — Mushroomator, Aug 08 '23 at 11:18

bhargav3vedi · Accepted Answer · 2023-08-08T11:31:24.493

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)

score 0 · Answer 2 · answered Aug 08 '23 at 11:20

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().

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

2 Answers2