0

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

Kardu
  • 865
  • 3
  • 13
  • 24
  • Can you describe what does not work? Do you get an error? – johnjohn Sep 11 '21 at 12:10
  • What I did is no mistake. What I'd like was to have an automatic way that read more than 100 excel files every day and concatenate in the same dataframe. The difficulty here is that all the files are named differently but start with the same word - "answer" – Kardu Sep 11 '21 at 12:14
  • 1
    Does this help https://stackoverflow.com/questions/20908018/import-multiple-excel-files-into-python-pandas-and-concatenate-them-into-one-dat – johnjohn Sep 11 '21 at 12:17
  • 1
    it is perfect!! – Kardu Sep 11 '21 at 12:27

2 Answers2

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])
Umar.H
  • 22,559
  • 7
  • 39
  • 74
0

@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)
avats
  • 437
  • 2
  • 10