Is there a way to combine multiple excel-sheets from multiple excel-files with the same format?

0
votes

I have a folder with 100 excel-files which each have 10 different sheets. The format and the name of the sheets are exactly the same for all 100 excel-files.

Is there a way to make a DataFrame for each sheet containing data from all 100 Excel-files? So for example, there is a sheet called "Population", and I want to make a dataframe including the population-data from all the 100 excel-files combined.

Is this possible? I am not a Python-pro, but I have tried many different things. Most guides help you to combine multiple excel-files or multiple sheets within one excel-file.

1
pythonexceldataframe

1 Answers

0
votes

It can be achieved with pandas.

Read all files and concat them along axis 0. Here I 'm assuming that all the excel files are in a single folder.

Docs Ref:https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html

folder = os.path.abspath("myfolder")

pd.concat(map(lambda x:pandas.read_excel(x), os.listdir(folder)), ignore_index=True)