[Python] Python pd data frame processing very slow

Hi I'm looking for sugestions to make my python script more effient.

The problem exists in my function create_excel(), right now when i use a big input excel (150k lines) the program freezes and take 10 minutes to process. At the end of the year i expect the input excel to double, therefore i need to find a way to improve processing to reduce procesing time to at least 5 minutes.

This function takes a template (plantilla.df) with all the columns and format I want for the output excel.

Then it iterates over the rows in input file (combined_m1). If the columns are the same as the template columns it just copies the data from input file into the template file.

Then it creates temporary columns values using functions that maps different values.

This is an example of the python (it has 10 columns that needs functions I only have 3 in the example).

def get_bos_values(Bos):
Bos = str(Bos).strip()
mapping_row = bos_mapping_df.loc[bos_mapping_df['BOS'] == Bos]
if not mapping_row.empty:
account_value = str(mapping_row.iloc[0]['ACCOUNT']).strip()
account_name_value = str(mapping_row.iloc[0]['ACCOUNT NAME']).strip()
return account_value, account_name_value
return None, None

def get_type(CdG):
CdG=str(CdG).strip()
for i, source_value in enumerate(type_mapping_df['Source_value']):
source_value = str(source_value).strip()
if source_value in CdG:

return type_mapping_df.iloc['Target_value']
return None


def determine_inter_branch_flag(Type, Producto, group_code, interbusiness_mapping_df):
if not Type:
return None
elif Type not in ['Assets', 'Liabilities']:
return 'No - non Assets & Liability'
elif Type in ['Assets', 'Liabilities']:
if str(Producto).startswith('LIF'):
return 'No - LIF'
else:
if group_code in interbusiness_mapping_df['Source_value'].values:
return 'Yes'
else:
return 'No'
return None```


def create_excel():
try:
output_df = plantilla_df.copy()
output_rows = []

for index, row in combined_m1.iterrows():
temp_row = {}
for col_name in plantilla_df.columns:
if col_name in combined_m1fmis.columns:
temp_row[col_name] = row[col_name]

elif col_name == 'CdG':
temp_row['CdG'], temp_row['CdG desc'] = get_bos_values(row['Cuenta'])


elif col_name == 'Type':
temp_row['Type'] = get_type(temp_row['CdG'])


elif col_name == 'inter-branch flag':
temp_row['inter-branch flag'] = determine_inter_branch_flag(temp_row['Type'], row['Product Reference'], row['Group Code'], interbusiness_mapping_df)


output_rows.append(temp_row)



output_df = pd.DataFrame(output_rows, columns=plantilla_df.columns)

template_directory = os.path.dirname(template_file)

output_path_temp = os.path.join(template_directory, f'M1_{date_final}.xlsx')
output_df.to_excel(output_path_temp, index=False)

except Exception as e:
messagebox.showerror("Error", f"Failed to create excel:\n{str(e)}")```


Would it help to have all the mapping in the same df? Or to join all the mapping in one function... Do you have any idea how to improve the processing time?

Data looks like this

template
ID ; Cuenta ; CdG ; CdG desc ; Type ; inter-branch flag ; Group Code; Product Reference...
(Everything is empty; NaN)

combined_m1
ID ; Cuenta ; StartDate; EndDate; Group Code; Product Reference
AA1; 01 ; 250612 ; 250920 ; B1 ; 101
AA2; 02 ; 250101 ; 250910 ; B2 ; 102
...

bos_mapping_df
BOS ; ACCOUNT ; ACCOUNT NAME
01 ; PG1 ; Debts
02 ; PG2 ; Taxes

type_mapping_df
SOURCE_VALUE; TARGET_VALUE
PG1 ; Liability
PG2 ; Loss


Expected outcome
ID ; Cuenta ; CdG ; CdG desc ; Type ; inter-branch flag ; Group Code; Product Reference...
AA1; 01 ; PG1 ; Debts; Liability; Yes;B1; 101;...
AA2; 02 ; PG2 ; Taxes; Loss; No - non Assets & Liability; B2 ; 102;...


Any suggestions are welcome!!

Continue reading...