merge_workbooks

#!/usr/local/bin/python3
import pandas as pd
import glob
import os
import sys
input_path = sys.argv[1]
output_file = sys.argv[2]
all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))
df_names = []
for i in all_workbooks:
	df_names.append(os.path.basename(i)[:-5])
data_frames = []
for workbook in all_workbooks:
    all_worksheets = pd.read_excel(workbook, sheetname=0, index_col=None)
    data_frames.append(all_worksheets)
all_data_concatenated = pd.concat(data_frames, keys=df_names)
all_data_concatenated.reset_index(level=0, inplace=True)
all_data_concatenated.rename(columns={'level_0': 'Shadow'}, inplace=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='all_data_all_workbooks', index=False)
writer.save()