Appending multiple sheets from multiple files to the same sheet names in the excel files

0
votes

I have multiple excel files and each file contains the same sheet names such as:

  1. "Keyset"
  2. "Case"
  3. "Switch"
  4. "Plate"
  5. "Layout"

The same sheets will have the same column headers.

How can i append excel 1 - 15 files into the same sheet?

For example - Keyset sheet:

Excel 1: (keyset sheet)

Year Bought Amount Paid
2020 250
2020 200

Excel 2: (keyset sheet)

Year Bought Amount Paid
2019 200
2019 200

Excel 3: (keyset sheet)

Year Bought Amount Paid
2019 242
2019 187

They have the same column headers, but i am not sure how to efficiently loop my code so that it will append to the same sheet for each excel file.

Desired output:

Year Bought Amount Paid
2020 250
2020 200
2019 200
2019 200
2019 242
2019 187
1
pythonexcelpandasdataframe

1 Answers

0
votes

You could do something like this:

filelist = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']

#initialise an empty dataframe
df = pd.DataFrame()

#loop your list of files, reading and appending each one
for fpath in filelist:
    df = df.append(pd.read_excel(fpath, sheet_name='Keyset'), ignore_index=True)

For multiple sheets, you could do multiple dfs (named appropriately), one for each sheet, then have all your complete ones at the end.