Reports

I changed my code to merging and sql as the comments sugested, it made it faster but it still takes up to two minutes to save the excel.
Thank you for all the suggestions!
Changed the function to:

plantilla_df = pd.read_excel(template_file, sheet_name='Template')
branch_mapping_df = pd.read_excel(template_file, sheet_name='BranchMapping', dtype={'Source_value': str})  
type_mapping_df = pd.read_excel(template_file, sheet_name='TypeMapping', dtype={'Source_value': str}) 
interbusiness_mapping_df = pd.read_excel(template_file, sheet_name='InterbusinessMapping', dtype={'Source_value': str})
bos_mapping_df = pd.read_excel(bos_mapping_file, dtype=str).fillna("NA")
customer_mapping_df = pd.read_excel(customer_mapping_file, dtype=str)
murex_mapping_df = pd.read_excel(murex_mapping_file, dtype=str)
bb_mapping_df = pd.read_excel(bb_mapping_file, dtype=str)
bb_mapping_df['Corporate Product Name - Hierarchy (English)'] = bb_mapping_df['Corporate Product Name - Hierarchy (English)'].str.replace('.', '')



# Merging the DataFrames
merged_df = combined_m1 \
    .merge(branch_mapping_df, how='left', left_on='Codigo', right_on='Source_value') \
    .merge(bos_mapping_df, how='left', left_on='Cuenta', right_on='BOS') \
    .merge(type_mapping_df, how='left', left_on='ACCOUNT', right_on='Source_value', suffixes=('', '_type')) \
    .merge(bb_mapping_df, how='left', left_on='ACCOUNT', right_on='Corporate Product Code - Hierarchy', suffixes=('', '_bb')) \
    .merge(customer_mapping_df, how='left', left_on='Cliente', right_on='Customer Code') \
    .merge(murex_mapping_df, how='left', left_on='Folder', right_on='SOURCE_VALUE')



# Adding the calculated columns
merged_df['inter-branch flag'] = np.select(
    [
        merged_df['Type'].isnull(),
        ~merged_df['Type'].isin(['Assets', 'Liabilities']),
        merged_df['Type'].isin(['Assets', 'Liabilities']) & (merged_df['Reference'].str.startswith('LIF')),
        merged_df['Type'].isin(['Assets', 'Liabilities']) & (merged_df['Group Code'].isin(interbusiness_mapping_df['Source_value']))
    ],
    ['', 'No - non Assets & Liability', 'No - LIF', 'Yes'],
    default='No'
)



output_df = merged_df[[
    "Codigo",
    "Target_value",    # "Branch"
    "Fecha de Envio",
    "Numero",
    "Cuenta",
    "ACCOUNT",         # "CdG"
    "ACCOUNT NAME",    # "CdG desc"
    "Target_value_type", # "Type"
    "Level in BB",     # "BB code"
    "Corporate Product Name - Hierarchy (English)",  # "BB Desc" 
    "Starting Date", 
    "Group Code",
    "inter-branch flag",
    "Reference",
    "local account",
    "Cliente",
    "Customer Name"

]].rename(columns={
    "Target_value": "Branch",
    "ACCOUNT": "CdG",
    "ACCOUNT NAME": "CdG desc",
    "Target_value_type": "Type",
    "Level in BB": "BB code",
    "Corporate Product Name - Hierarchy (English)": "BB Desc"
})

# Output the final DataFrame
print(output_df)

template_directory = os.path.dirname(template_file)
output_path_temp = os.path.join(template_directory, 'Depurar.xlsx')
output_df.to_excel(output_path_temp, index=False)
79668902
