import os

import pandas as pd

FILE_PATH = r'C:\Users\tskar\Desktop\Passportization.xls'
BASE_DIR = r'C:\Users\tskar\Desktop\base.xlsx'
DIRECTORY = 'clinics'


def to_excel(file, out_name):
    file.to_excel(out_name)


def refine_files(file):
    refined = file[file['Unnamed: 1'].notna()][['Unnamed: 1', 'Unnamed: 2']]
    return refined


def main():
    base = pd.read_excel(BASE_DIR)

    # fn - daabrunebs failis saxelebs am direeqtoriashi
    # path.join sachiroa ro mtliani failis misamarti aawyo directorias + failis saxeli, os.join gaaaketebs magas
    for fn in os.listdir(DIRECTORY):
        file = os.path.join(DIRECTORY, fn)
        df = refine_files(pd.read_excel(file))
        # base-s daemateba da daemateba 
        # tab-ebis da egetbs gasworreba ari ctrl+shift+l rorame
        base = base.merge(df, left_on='Unnamed: 1', right_on='Unnamed: 1')

    to_excel(base, 'res.xlsx')


if __name__ == "__main__":
    main()