double slash when reading

Question

using this part of the code

for root, dirs, files in os.walk(data_location):
    for file in files:
        if file.endswith(".xlsx"):
            df = pd.read_excel(data_location + "\\" + file, usecols=col_list)
            df['File']=file
            df_total = pd.concat([df, df_total], ignore_index=True)

is giving this error:

FileNotFoundError: [Errno 2] No such file or directory: "C:\\Users\\bosuna\\

As you can see its doubling the "" in the path when it runs.

Answer 1

You're not passing the correct file path to pd.read_excel(). data_location is just the top folder you were searching and file is the filename. os.walk() goes down the folder structure, and passes each folder it's in via the variable you have as root. So the following line

df = pd.read_excel(data_location + "\\" + file, usecols=col_list)

should be:

df = pd.read_excel(root + "\\" + file, usecols=col_list)

or even better:

df = pd.read_excel(os.path.join(root, file), usecols=col_list)