<jupyter_start><jupyter_text>class Excel_comparison:        def __init__(self,Golden_Excel_file,Extracted_Excel_file, result_path):        self.Golden_Excel_file    = Golden_Excel_file        self.Extracted_Excel_file = Extracted_Excel_file        self.result_path          = result_path        self.excel_1 = pd.ExcelFile(self.Golden_Excel_file)     creaed variables so they can run every time we create object        self.excel_2 = pd.ExcelFile(self.Extracted_Excel_file)        self.name    = self.Golden_Excel_file.split('\\')[-1]            def excel_comparision(self):         Create a Pandas Excel writer using XlsxWriter as the engine.        writer = pd.ExcelWriter(self.result_path+'\\excel_comparison_{}'.format(self.name), engine='xlsxwriter')        make the result data frame with shape is equel to df_1.shape        for sheet in self.excel_1.sheet_names: for loop on number of sheets present innexcel file                if sheet in self.excel_2.sheet_names:                    df_1 = pd.read_excel(self.excel_1, sheet_name= sheet)                    df_2 = pd.read_excel(self.excel_2, sheet_name= sheet)                    result_df = pd.DataFrame(data = np.NaN, index = df_1.index,columns=df_1.columns)                    for i in df_1.index:                        for j in df_2.index:                             if statement checks the key('Name'+ DOB) of df_1 row with every row in df_2                             if (df_1.iloc[i,:2].apply(lambda x:str(x).strip()) ==df_2.iloc[j,:2].apply(lambda x:str(x).strip())).all():                                appending the value TRUE, FALSE by checking series of i'th row of df_1 to series of j'th row of df_2                                result_df.iloc[i,:] = (df_1.iloc[i,:].apply(lambda x:str(x).strip()) == df_2.iloc[j,:].apply(lambda x:str(x).strip())).values                                break                            else:                                result_df.iloc[i,:] = "key not found"                    result_df.to_excel(writer, sheet_name = sheet)                else:                    result_df = pd.DataFrame(data = {'message':'Sheet not found'}, index=[0])                    result_df.to_excel(writer, sheet_name = sheet)           writer.save()    def key_occurance(self):         Create a Pandas Excel writer using XlsxWriter as the engine.        writer = pd.ExcelWriter(self.result_path+'\\key_occurance_{}'.format(self.name), engine='xlsxwriter')        for sheet in self.excel_1.sheet_names: for loop on number of sheets present innexcel file            if sheet in self.excel_2.sheet_names:                df_1 = pd.read_excel(self.excel_1, sheet_name= sheet)                df_2 = pd.read_excel(self.excel_2, sheet_name= sheet)                result2_df = df_1.iloc[:,:2]                result2_df['key_occurance_in_exrt'] = np.zeros(df_1.shape[0])                for i in df_1.index:                    for j in df_2.index:                        if (df_1.iloc[i,:2] == df_2.iloc[j,:2]).all(): this will check key whether present or not                            result2_df.iloc[i,2] = result2_df.iloc[i,2]+1 if present it will add to our count                result2_df.to_excel(writer, sheet_name = sheet)            else:                result2_df = pd.DataFrame(data = {'message':'Sheet not found'}, index=[0])                result2_df.to_excel(writer, sheet_name = sheet)          writer.save()        def extra_keys(self):         Create a Pandas Excel writer using XlsxWriter as the engine.        writer = pd.ExcelWriter(self.result_path+'\\extra_Keys_{}'.format(self.name), engine='xlsxwriter')        for sheet in self.excel_1.sheet_names: for loop on number of sheets present innexcel file            if sheet in self.excel_2.sheet_names:                df_1 = pd.read_excel(self.excel_1, sheet_name= sheet)                df_2 = pd.read_excel(self.excel_2, sheet_name= sheet)                 not_found_keys are the keys from df_2(extracted) which are not found in df_1(golden data)                not_found_key = [] create empty list                for i in df_2.index:                    for j in df_1.index:                        if (df_2.iloc[i,:2] == df_1.iloc[j,:2]).all():                            break                        else:                            pass                    else:                        not_found_key.append(df_2.iloc[i,:2]) append the not founded key to list                result3_df= pd.DataFrame(data=not_found_key) convert list data frame , so we can convert it to excel                result3_df.to_excel(writer, sheet_name = sheet) convert data frame to excel, sheetwise            else:                result2_df = pd.DataFrame(data = {'message':'Sheet not found'}, index=[0])                result2_df.to_excel(writer, sheet_name = sheet)        writer.save()<jupyter_code># here we can put here directory path of golden, extracted and result(where you want to save your results)
golden_data_folder  = r'C:\Users\Shrirang\Desktop\Data Science Practice\My Projects\Mahesh Jupyter notebook\Golden Data'
extracted_data_folder=   r'C:\Users\Shrirang\Desktop\Data Science Practice\My Projects\Mahesh Jupyter notebook\Extracted Data'
result_path         =  r"C:\Users\Shrirang\Desktop\Data Science Practice\My Projects\Mahesh Jupyter notebook\Results"
key = ['Name','DOB']

# get all the excel files in a list
golden_excels = []
extracted_excels = []

for dirname, _, filenames in os.walk(golden_data_folder):
    for filename in filenames:
        golden_excels.append(str(os.path.join(dirname, filename)))
        
for dirname, _, filenames in os.walk(extracted_data_folder):
    for filename in filenames:
        extracted_excels.append(str(os.path.join(dirname, filename)))
for g_path in golden_excels:
    for e_path in extracted_excels:
        if g_path.split('\\')[-1] == e_path.split('\\')[-1]:
            a = Excel_comparison(g_path,e_path, result_path)
            Excel_comparison.result_path =result_path
            a.key_occurance()
            a.excel_comparision()
            a.extra_keys()
            break

key = ['Name', 'DOB']
class Excel_comparison:

    
    def __init__(self,Golden_Excel_file,Extracted_Excel_file, result_path, key):
        self.Golden_Excel_file    = Golden_Excel_file
        self.Extracted_Excel_file = Extracted_Excel_file
        self.result_path          = result_path
        self.excel_1 = pd.ExcelFile(self.Golden_Excel_file)    # creaed variables so they can run every time we create object
        self.excel_2 = pd.ExcelFile(self.Extracted_Excel_file)
        self.name    = self.Golden_Excel_file.split('\\')[-1]
        self.key     = self.key
    
    
    def excel_comparision(self):

        # Create a Pandas Excel writer using XlsxWriter as the engine.
        writer = pd.ExcelWriter(self.result_path+'\\excel_comparison_{}'.format(self.name), engine='xlsxwriter')

        #make the result data frame with shape is equel to df_1.shape
        for sheet in self.excel_1.sheet_names: #for loop on number of sheets present innexcel file
                if sheet in self.excel_2.sheet_names:
                    df_1 = pd.read_excel(self.excel_1, sheet_name= sheet)
                    df_2 = pd.read_excel(self.excel_2, sheet_name= sheet)

                    result_df = pd.DataFrame(data = np.NaN, index = df_1.index,columns=df_1.columns)
                    for i in df_1.index:
                        for j in df_2.index:
                            # if statement checks the key('Name'+ DOB) of df_1 row with every row in df_2 
                            if (df_1[key].iloc[i].apply(lambda x:str(x).strip()) ==df_2[key].iloc[j].apply(lambda x:str(x).strip())).all():
                                #appending the value TRUE, FALSE by checking series of i'th row of df_1 to series of j'th row of df_2
                                result_df.iloc[i,:] = (df_1.iloc[i,:].apply(lambda x:str(x).strip()) == df_2.iloc[j,:].apply(lambda x:str(x).strip())).values
                                break
                            else:
                                result_df.iloc[i,:] = "key not found"
                    result_df.to_excel(writer, sheet_name = sheet)
                else:
                    result_df = pd.DataFrame(data = {'message':'Sheet not found'}, index=[0])
                    result_df.to_excel(writer, sheet_name = sheet)   
        writer.save()

    def key_occurance(self):

        # Create a Pandas Excel writer using XlsxWriter as the engine.
        writer = pd.ExcelWriter(self.result_path+'\\key_occurance_{}'.format(self.name), engine='xlsxwriter')

        for sheet in self.excel_1.sheet_names: #for loop on number of sheets present innexcel file
            if sheet in self.excel_2.sheet_names:
                df_1 = pd.read_excel(self.excel_1, sheet_name= sheet)
                df_2 = pd.read_excel(self.excel_2, sheet_name= sheet)


                result2_df = df_1[key].iloc[:]
                result2_df['key_occurance_in_exrt'] = np.zeros(df_1.shape[0])
                for i in df_1.index:
                    for j in df_2.index:
                        if (df_1[key].iloc[i] == df_2[key].iloc[j]).all(): #this will check key whether present or not
                            result2_df['key_occurance_in_exrt'].iloc[i] = result2_df['key_occurance_in_exrt'].iloc[i]+1 #if present it will add to our count

                result2_df.to_excel(writer, sheet_name = sheet)
            else:
                result2_df = pd.DataFrame(data = {'message':'Sheet not found'}, index=[0])
                result2_df.to_excel(writer, sheet_name = sheet)  
        writer.save()


    
    def extra_keys(self):

        # Create a Pandas Excel writer using XlsxWriter as the engine.
        writer = pd.ExcelWriter(self.result_path+'\\extra_Keys_{}'.format(self.name), engine='xlsxwriter')

        for sheet in self.excel_1.sheet_names: #for loop on number of sheets present innexcel file
            if sheet in self.excel_2.sheet_names:
                df_1 = pd.read_excel(self.excel_1, sheet_name= sheet)
                df_2 = pd.read_excel(self.excel_2, sheet_name= sheet)


                # not_found_keys are the keys from df_2(extracted) which are not found in df_1(golden data)
                extra_found_key = [] #create empty list
                for i in df_2.index:
                    for j in df_1.index:
                        if (df_2[key].iloc[i] == df_1[key].iloc[j]).all():
                            break
                        else:
                            pass
                    else:
                        extra_found_key.append(df_2[key].iloc[i]) #append the not founded key to list

                result3_df= pd.DataFrame(data=extra_found_key) #convert list data frame , so we can convert it to excel

                result3_df.to_excel(writer, sheet_name = sheet) #convert data frame to excel, sheetwise
            else:
                result2_df = pd.DataFrame(data = {'message':'Sheet not found'}, index=[0])
                result2_df.to_excel(writer, sheet_name = sheet)
        writer.save()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09886068000358841, 'fraction_numerical': 0.012380012559433032, 'mean_word_length': 2.065163596370635, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '217104', 'license': 'MIT', 'n_tokens_mistral': 3508, 'n_tokens_neox': 3347, 'n_words': 813, 'path': '.ipynb_checkpoints/Shuffle-checkpoint.ipynb', 'repo_name': 'shrirangdixit/excel_key_pair_checking', 'size': '11147'}