1

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
libertefudgy
  • 17
  • 3

1 Answers1

0

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.

acrobat
  • 812
  • 4
  • 7
  • is there a way i can create multiple dfs without having to manually create them. e.g. loop through the sheet names in the excel and create those sheet names as df? so rather than having to type case_df=pd.DataFrame(), switch_df=pd.DataFrame(), is there a way i can loop those sheet names and have the respective sheets append to the respective df? – libertefudgy Mar 26 '21 at 19:16
  • ```for sheet in sheets: exec('{} = pd.DataFrame'.format(sheet)) for file in files: for y in sheets: data=pd.read_excel(folder+file,sheet_name=y) sheet=sheet.append(data,ignore_index=True)``` but i got the error of: AttributeError: 'str' object has no attribute 'append' – libertefudgy Mar 26 '21 at 19:23
  • [Never call `DataFrame.append` or `pd.concat` inside a for-loop. It leads to quadratic copying.](https://stackoverflow.com/a/36489724/1422451). Appending to a list is an O(1) operation and does not require copying. – Parfait Mar 26 '21 at 19:30