CODEWITHSUNDEEP
pythonexcelpandas
Kardu
Kardu

Reputation: 885

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

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

Upvotes: 0

Views: 1396

Answers (2)

Umar.H
Umar.H

Reputation: 23099

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])

Upvotes: 1

avats
avats

Reputation: 445

@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)

Upvotes: 0

Related Questions