import pandas as pd from openpyxl import load_workbook from openpyxl.styles import PatternFill from openpyxl.utils import range_boundaries # Load MomentsPay_Bank_new.xlsx moments_pay_bank_df = pd.read_excel('MomentsPayAnderson_Bank_new1.xlsx') # Load Transaction details as on 14.12.2023 transaction_details_df = pd.read_excel('AHISintegratedPhonepe10-06.xlsx') # Convert relevant columns to a common data type (string) transaction_details_df['TransactionId'] = transaction_details_df['TransactionId'].astype(str).replace(r'\.0$', '', regex=True).replace(r' ','', regex=True) transaction_details_df['Amount'] = transaction_details_df['Amount'].astype(str).replace(r'\.0$', '', regex=True) moments_pay_bank_df['transaction_id'] = moments_pay_bank_df['transaction_id'].astype(str).replace(r'\.0$', '', regex=True) moments_pay_bank_df['Amount'] = moments_pay_bank_df['Amount'].astype(str).replace(r'\.0$', '', regex=True) moments_pay_bank_df['txn_identifier'] = moments_pay_bank_df['txn_identifier'].astype(str).replace(r'\.0$', '', regex=True) # Specify the mapping between fields excluding 'processing_id' and 'transaction_id' field_mapping = {'TransactionId': 'transaction_id','Amount':'Amount'} # Remove time component from 'Date' and convert to datetime format #transaction_details_df['Doc Date'] = transaction_details_df['Doc Date'].str.split().str[0] #transaction_details_df['Doc Date'] = pd.to_datetime(transaction_details_df['Doc Date'], format='%d/%m/%Y') # Apply the location renaming and categorization unit_mapping = { 'ADYAR ANDERSON':'ADYAR ANDERSON', 'ANDERSON GUDUVANCHERI':'ANDERSON GUDUVANCHERI', 'ANDERSON HYDERABAD':'ANDERSON HYDERABAD', 'ANDERSON KANCHIPURAM':'ANDERSON KANCHIPURAM', 'ANDERSON VADAPALANI':'ANDERSON VADAPALANI', 'ANDERSON VELLORE':'ANDERSON VELLORE', 'ANDERSON VIJAYAWADA':'ANDERSON VIJAYAWADA', 'ANDHRA B2B CENTRE':'ANDHRA B2B CENTRE', 'ANGAI CLINIC ADYAR':'ANGAI CLINIC ADYAR', 'ANNANAGAR CC':'ANNANAGAR CC', 'CHENNAI SPECIALITY CLINIC - CHOOLAIMEDU':'CHENNAI SPECIALITY CLINIC - CHOOLAIMEDU', 'CHILD CARE HOSPITAL':'CHILD CARE HOSPITAL', 'CHILDRENS MEDICAL CENTRE':'CHILDRENS MEDICAL CENTRE', 'CHROMEPET MAIN CENTRE':'CHROMEPET MAIN CENTRE', 'CLRI':'CLRI', 'DATA CENTER':'DATA CENTER', 'DEEN ORTHO CENTER':'DEEN ORTHO CENTER', 'DR P MUTHUKUMAR NEUROLOGY CLINIC VALASARAVAKKAM':'DR P MUTHUKUMAR NEUROLOGY CLINIC VALASARAVAKKAM', 'GENETICS KILPAUK':'GENETICS KILPAUK', 'GREAMS ROAD':'GREAMS ROAD', 'HUBLI ANDERSON':'HUBLI ANDERSON', 'I CARE CANCER CLINIC':'I CARE CANCER CLINIC', 'ICF ANDERSON':'ICF ANDERSON', 'JAYA EYE CARE CENTRE':'JAYA EYE CARE CENTRE', 'KOVAI PETCT':'KOVAI PETCT', 'HOME COLLECTION CENTRE':'HOME COLLECTION CENTRE', 'NAGERCOIL CENTRE':'NAGERCOIL CENTRE', 'NANGANALLUR CENTRE':'NANGANALLUR CENTRE', 'NELLORE':'NELLORE', 'NIRAM HOSPITAL':'NIRAM HOSPITAL', 'NUNGAMBAKKAM':'NUNGAMBAKKAM', 'PDR ORTHOPAEDIC HOSPITAL':'PDR ORTHOPAEDIC HOSPITAL', 'PERAMBUR COLLECTION POINT':'PERAMBUR COLLECTION POINT', 'PONDICHERRY COLLECTION POINT':'PONDICHERRY COLLECTION POINT', 'RGGH(MMC)':'RGGH(MMC)', 'SRIRANGAM ANDERSON':'SRIRANGAM ANDERSON', 'SRN NURO CLINIC':'SRN NURO CLINIC', 'TAMBARAM CC':'TAMBARAM CC', 'THANJAVUR PROCESSING CENTRE':'THANJAVUR PROCESSING CENTRE', 'THIRUMULLAIVOYIL':'THIRUMULLAIVOYIL', 'THIRUNELVELI PALAYAMKOTTAI':'THIRUNELVELI PALAYAMKOTTAI', 'TIRUPATHI CC':'TIRUPATHI CC', 'VELLORE NALAM HOSPITAL':'VELLORE NALAM HOSPITAL', 'VILLUPURAM ANDERSON':'VILLUPURAM ANDERSON', 'WEST MAMBALAM ANDERSON':'WEST MAMBALAM ANDERSON', 'ANDERSON MADURAI':'ANDERSON MADURAI', 'ANDERSON COIMBATORE':'ANDERSON COIMBATORE', 'ANDERSON DELHI NCR':'ANDERSON DELHI NCR', 'BANGALORE MAIN LAB - ANDERSON':'BANGALORE MAIN LAB - ANDERSON', 'CAMP 4 ONLINE':'CAMP 4 ONLINE', 'CHENNAI B2B CENTRE':'CHENNAI B2B CENTRE', 'ENRICH LIFESTYLE CLINIC':'ENRICH LIFESTYLE CLINIC', 'KARNATAKA B2B':'KARNATAKA B2B', 'KODAMBAKKAM ANDERSON':'KODAMBAKKAM ANDERSON', 'MUMBAI B2B CENTRE':'MUMBAI B2B CENTRE', 'TRICHY ANDERSON':'TRICHY ANDERSON', 'SALEM PETCT':'SALEM PETCT', 'SUPERLIFE PHYSIO CLINIC - VEPERY':'SUPERLIFE PHYSIO CLINIC - VEPERY', } def normalize_whitespace(value): if isinstance(value, str): return ' '.join(value.split()).upper() # Remove extra spaces and convert to uppercase return value # Normalize TenentName in transaction_details_df transaction_details_df['TenentName'] = transaction_details_df['TenentName'].apply(normalize_whitespace) # Normalize keys in unit_mapping unit_mapping = {normalize_whitespace(k): normalize_whitespace(v) for k, v in unit_mapping.items()} # Apply normalization to mapped tenant names transaction_details_df['TenentName'] = transaction_details_df['TenentName'].replace(unit_mapping) # Remove duplicates from moments_pay_bank_df based on 'transaction_id' moments_pay_bank_df_dedup = moments_pay_bank_df.drop_duplicates(subset=['transaction_id'], keep='first') # Merge dataframes on the specified fields merged_df = pd.merge(transaction_details_df, moments_pay_bank_df_dedup[list(field_mapping.values())], left_on=list(field_mapping.keys()), right_on=list(field_mapping.values()), how='left') unmerged_df = pd.merge(transaction_details_df, moments_pay_bank_df_dedup[list(field_mapping.values())], left_on=list(field_mapping.keys()), right_on=list(field_mapping.values()), how='outer', indicator=True) equal_records = merged_df[~merged_df['transaction_id'].isnull()] equal_records.insert(0, 'S.No.', range(1, len(equal_records) + 1)) equal_records['momentpay matched'] = 'YES' #equal_records['txn_identifier'] = equal_records.apply(lambda row: moments_pay_bank_df_dedup[ # (moments_pay_bank_df_dedup['transaction_id'] == row['TransactionId']) & # (moments_pay_bank_df_dedup['Amount'] == row['Amount']) ] # .reset_index(drop=True)['txn_identifier'].values[0], axis=1) def get_txn_identifier(row): try: # Filter the dataframe for matching rows and select the first 'txn_identifier' value matched_row = moments_pay_bank_df_dedup[ (moments_pay_bank_df_dedup['transaction_id'] == row['TransactionId']) & (moments_pay_bank_df_dedup['Amount'] == row['Amount']) ] if not matched_row.empty: return matched_row.iloc[0]['txn_identifier'] else: return None # No match found except Exception as e: print(f"Error processing row: {row}, Error: {e}") return None # Apply the function to populate 'txn_identifier' equal_records['txn_identifier'] = equal_records.apply(get_txn_identifier, axis=1) unequal_records = unmerged_df[unmerged_df['_merge'] == 'left_only'] unequal_records.insert(0, 'S.No.', range(1, len(unequal_records) + 1)) unequal_records['momentpay matched'] = 'NO' # Function to create or overwrite an Excel sheet def create_or_overwrite_sheet(sheet_name, data): with pd.ExcelWriter('Anderson-HISBANK10-06.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer: data.to_excel(writer, sheet_name=sheet_name, index=False) # Generate sheet names sheet_name_matched = 'HISPhonepeBank-Matched' sheet_name_unmatched = 'HISPhonepeBank-Unmatched' create_or_overwrite_sheet(sheet_name_matched, equal_records) create_or_overwrite_sheet(sheet_name_unmatched, unequal_records) wb = load_workbook('Anderson-HISBANK10-06.xlsx') Summary_Sheet = wb['Summary'] File_amount = moments_pay_bank_df['Amount'].astype(float).sum() print(File_amount) transaction_date = moments_pay_bank_df['TransactionDate'].mode()[0] print(transaction_date) settled_date = moments_pay_bank_df['SettlementDate'].mode()[0] Summary_Sheet['A102'] = settled_date # Update summary sheet with total transaction details num_rows = len(transaction_details_df) Summary_Sheet['E102'] = num_rows total_amount = transaction_details_df['Amount'].astype(float).sum() Summary_Sheet['F102'] = f"₹{total_amount:,.2f}" # Update summary sheet with matched transaction details HIS_Matched_df = pd.read_excel('Anderson-HISBANK10-06.xlsx', sheet_name='HISPhonepeBank-Matched') num_rows = len(HIS_Matched_df) Summary_Sheet['G102'] = num_rows total_amount = HIS_Matched_df['Amount'].astype(float).sum() Summary_Sheet['H102'] = f"₹{total_amount:,.2f}" # Update summary sheet with unmatched transaction details HIS_UnMatched_df = pd.read_excel('Anderson-HISBANK10-06.xlsx', sheet_name='HISPhonepeBank-Unmatched') num_rows = len(HIS_UnMatched_df) Summary_Sheet['I102'] = num_rows total_amount = HIS_UnMatched_df['Amount'].astype(float).sum() Summary_Sheet['J102'] = f"₹{total_amount:,.2f}" # Define the color for the header header_fill = PatternFill(fgColor='1274bd', fill_type='solid') for sheet_name in ['HISPhonepeBank-Matched', 'HISPhonepeBank-Unmatched']: worksheet = wb[sheet_name] # Apply style to the header row for row in worksheet.iter_rows(min_row=1, max_row=1): for cell in row: cell.fill = header_fill # Save the workbook wb.save('Anderson-HISBANK10-06.xlsx') # Combine the matched and unmatched DataFrames to calculate total counts and amounts combined_df = pd.concat([HIS_Matched_df, HIS_UnMatched_df], ignore_index=True) # Define the column name for unit/location and total amount unit_location_col = 'TenentName' total_amount_col = 'Amount' # Calculate total count and total amount for each unique location total_grouped = combined_df.groupby(unit_location_col).agg( total_count=pd.NamedAgg(column=unit_location_col, aggfunc='count'), total_amount=pd.NamedAgg(column=total_amount_col, aggfunc='sum') ).reset_index() # Calculate matched count and matched total amount for each unique location matched_grouped = HIS_Matched_df.groupby(unit_location_col).agg( matched_count=pd.NamedAgg(column=unit_location_col, aggfunc='count'), matched_amount=pd.NamedAgg(column=total_amount_col, aggfunc='sum') ).reset_index() # Calculate unmatched count and unmatched total amount for each unique location unmatched_grouped = HIS_UnMatched_df.groupby(unit_location_col).agg( unmatched_count=pd.NamedAgg(column=unit_location_col, aggfunc='count'), unmatched_amount=pd.NamedAgg(column=total_amount_col, aggfunc='sum') ).reset_index() # Create a dataframe for all the locations from unit_mapping with default 0 values location_df = pd.DataFrame(list(unit_mapping.items()), columns=[unit_location_col, 'dummy']) location_df['total_count'] = 0 location_df['total_amount'] = 0.0 location_df['matched_count'] = 0 location_df['matched_amount'] = 0.0 location_df['unmatched_count'] = 0 location_df['unmatched_amount'] = 0.0 # Merge the grouped data with the location_df to ensure all locations from unit_mapping are included summary_df = total_grouped.merge(matched_grouped, on=unit_location_col, how='left')\ .merge(unmatched_grouped, on=unit_location_col, how='left') # Merge with location_df to ensure all unit_mapping locations are included, even with 0 values summary_df = summary_df.merge(location_df, on=unit_location_col, how='right', suffixes=('_data', '_mapped')) # Fill NaN values with 0 (in case there are locations that are only in unit_mapping and not in the data) summary_df['total_count'] = summary_df['total_count_data'].fillna(0).astype(int) summary_df['total_amount'] = summary_df['total_amount_data'].fillna(0.0) summary_df['matched_count'] = summary_df['matched_count_data'].fillna(0).astype(int) summary_df['matched_amount'] = summary_df['matched_amount_data'].fillna(0.0) summary_df['unmatched_count'] = summary_df['unmatched_count_data'].fillna(0).astype(int) summary_df['unmatched_amount'] = summary_df['unmatched_amount_data'].fillna(0.0) # Drop unnecessary columns summary_df.drop(columns=[col for col in summary_df.columns if col.endswith('_data')], inplace=True) # Reorder the columns summary_df = summary_df[['TenentName', 'total_count', 'total_amount', 'matched_count', 'matched_amount', 'unmatched_count', 'unmatched_amount']] # Load the summary sheet where you want to update the values summary_sheet_path = 'Anderson-HISBANK10-06.xlsx' book = load_workbook(summary_sheet_path) summary_sheet = book['Summary'] # Define the starting row and column for updating the Summary sheet start_row = 103 start_col = 4 # Function to find the top-left cell of a merged cell range def find_top_left_cell(merged_ranges, row, col): for merged_range in merged_ranges: min_col, min_row, max_col, max_row = range_boundaries(str(merged_range)) if min_row <= row <= max_row and min_col <= col <= max_col: return min_row, min_col return row, col # Update the Summary sheet with the new summary data merged_ranges = summary_sheet.merged_cells.ranges for index, row in summary_df.iterrows(): for col_num, value in enumerate(row): # Find the top-left cell if the cell is part of a merged range r, c = find_top_left_cell(merged_ranges, start_row + index, start_col + col_num) # Write the value to the correct cell summary_sheet.cell(row=r, column=c, value=value) # Save the workbook without deleting existing sheets book.save(summary_sheet_path) print(summary_df)