Import multiple excel files start with same name into pandas and concatenate them into one dataframe

Question

I have everyday multiple excel files with different names, but all these files start with the same name, for instance, "Answer1.xlsx", "AnswerAVD.xlsx","Answer2312.xlsx", etc.

Is it possible to read and concatenate all these files in a pandas dataframe?

I Know how to do one by one, but is not a solution

import pandas as pd

dfs1 = pd.read_excel('C:/Answer1.xlsx')
dfs2 = pd.read_excel('C:/AnswerAVD.xlsx')
dfs3 = pd.read_excel('C:/Answer2312.xlsx')

Final=pd.concat([dfs1 , dfs2 ,dfs3 ])

Many thanks for your help

Answer 1

use a glob method with pathlib and then concat using pandas and a list comprehension.

from pathlib import Path
import pandas as pd

src_files = Path('C:\\').glob('*Answer*.xlsx')

df = pd.concat([pd.read_excel(f, index_col=None, header=0) for f in src_files])

Answer 2

@Kardu This will help you do this in a concise manner and there are many useful comments also for other alternatives.

Also, inspired by the same post, this should help.

import pandas as pd
import glob

path = r'C:\' # use your path
all_files = glob.glob(path + "/Answer*.xlsx")

li = []

for filename in all_files:
    df = pd.read_excel(filename, index_col=None, header=0)
    li.append(df)

frame = pd.concat(li, axis=0, ignore_index=True)