python - Looping through a folder to merge several excel sheets into one column -

i have several workbooks, each 3 sheets. want loop through each workbook , merge data sheet_1 new workbook_1 file, sheet_2 workbook_2 file & sheet_3 workbook_3.

as far can tell script below need, except rather appending data, overwrites data previous iteration.

for sake of parsimony i've shortened, cleaned & simplified script, i'm happy share full script if needed.

import pandas pd import glob  search_dir= ('/users/path/*.xlsx')  sheet_names = ['sheet_1','sheet_2','sheet_2']  def a_joiner(sheet):     loop_x in glob.glob(search_dir):        try:          if sheet == 'sheet_1':             id_file= pd.excelfile(loop_x)                                         df_1 = id_file.parse(sheet, header= none)                          writer= pd.excelwriter('/users/path/%s.xlsx' %(sheet), engine= 'xlsxwriter')                                      df_1.to_excel(writer)                                 writer.save()          elif sheet == 'sheet_2':            #do same above          else:            #and same above again      except exception e:        print('error:',e)  sheet in sheet_names:     a_joiner(sheet) 

you can easilly append data like:

df = [] f in ['c:\\file1.xls', 'c:\\ file2.xls']:     data = pd.read_excel(f, 'sheet1').iloc[:-2]     data.index = [os.path.basename(f)] * len(data)     df.append(data)  df = pd.concat(df) 

from: using pandas combining/merging 2 different excel files/sheets