# merge excel files and group soure files into separate folders 
# created by Ilyas

import os
import pandas as pd
import shutil
cwd = os.path.abspath('') 
files = os.listdir(cwd)  

df = pd.DataFrame()
x = len(files)
y = 1
z = 1
c = 1
folder =  ''
for file in files:
    if file.endswith('.xlsx'):
        print (file, x, y )
        folder = "merged_{}".format(z)
        create_folder = os.path.join(cwd, folder)
        try:
            os.mkdir(create_folder)
        except OSError:
            a = 'ok'
        df = df.append(pd.read_excel(file), ignore_index=True) 
        shutil.move(file, "{}/{}".format(create_folder, file))
        y += 1
        c += 1
        if c == 1001 or y == x:
            df.head() 
            df.to_excel(folder + '.xlsx')
            z += 1
            c = 1
            df = pd.DataFrame()