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

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

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?

Solution

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)