painfully slow runtime when handling data

[dadazhu]

Hi everyone,

I am new to Python, and just completed some codes as below that take forever to run. It seemed working if I only run the first 10 rows in the main DataFrame, I hope someone can assist me in improving the codes, so that it can run much faster. Many thanks

from datetime import timedelta import numpy as np import pandas as pd # Get data from the given excel file filepath = r'C:\Users\User\Desktop\Power BI\All.xlsx' df_CoC = pd.read_excel(filepath, usecols=['ID, 'Is there time loss?', 'MC Capacity Date From', 'MC Capacity Date To'], sheet_name='CoC') #18000 rows df_WRS = pd.read_excel(filepath, usecols=['ID', 'Date From', 'Date To'], sheet_name='WRS') #22000 rows df_Open = pd.read_excel(filepath, sheet_name='Open') #2500 rows Count_Open = df_Open['ID'].count() df_Open['CoC_MissingWRS'] = None df_Open['WRS_MissingCoC'] = None ####### Fill new 'CoC_MissingWRS' column in open query ######## for i in range(0, Count_Open): if i > 90: break imn = df_Open.loc[i, 'Injury Management: Injury Management Number'] #Fill WRS date array arrWRSDates = np.array([]) filtered_df = df_WRS[df_WRS['ID'] == imn] if not filtered_df.empty: Count_WRS = filtered_df['ID'].count()-1 for w in range(0, Count_WRS): #Date_From is always available Date_From = filtered_df.iloc[w]['Date From'].date() if filtered_df.iloc[w]['Date To'] is None: Date_To = Date_From else: Date_To = filtered_df.iloc[w]['Date To'].date() if Date_To < Date_From: Date_To = Date_From adate = Date_From while adate <= Date_To: if adate not in arrWRSDates: arrWRSDates = np.append(arrWRSDates, adate) adate += timedelta(days=1) # Fill Timeloss & all CoC date array arrTLCoCDates = np.array([]) arrAllCoCDates = np.array([]) filtered_df = df_CoC[df_CoC['ID'] == imn] if not filtered_df.empty: Count_CoC= filtered_df['ID'].count() - 1 for c in range(0, Count_CoC): Date_From = filtered_df.iloc[c]['MC Capacity Date From'].date() if filtered_df.iloc[c]['MC Capacity Date To'] is None: Date_To = Date_From else: Date_To = filtered_df.iloc[c]['MC Capacity Date To'].date() if Date_To < Date_From or Date_To is None: Date_To = Date_From adate = Date_From while adate <= Date_To: if df_CoC.iloc[c]['Is there time loss?'] == 'Yes': if adate not in arrTLCoCDates: arrTLCoCDates = np.append(arrTLCoCDates, adate) else: if adate not in arrAllCoCDates: arrAllCoCDatesCoCDates = np.append(arrAllCoCDates, adate) adate += timedelta(days=1) ##### Check if CoC missing WRS ##### bMissingWRS = 'No' if len(arrTLCoCDates) > 0: adate = min(arrTLCoCDates) while adate <= max(arrTLCoCDates) and bMissingWRS == 'No': if adate not in arrWRSDates and adate.weekday() < 5: bMissingWRS = 'Yes' df_Open.loc[i, 'CoC_MissingWRS'] = bMissingWRS ##### Check if WRS missing CoC ##### bMissingCoC = 'No' if len(arrWRSDates) > 0: adate = min(arrWRSDates) while adate <= max(arrWRSDates) and bMissingCoC == 'No': if adate not in arrAllCoCDates and adate.weekday() < 5: bMissingCoC = 'Yes' df_Open.loc[i, 'WRS_MissingCoC'] = bMissingCoC

[jefsummers]

Where is it slow?
Suggest you put sections into cells in a Jupyter notebook and execute the code segments individually. That should narrow it down. Then we can more likely help

[dadazhu]

Hi jefsummers,

Thank you for your advice, I tried to run the code in Power BI and PyCharm Community, I never got to see the result, unless I run it for the first 10 rows or something. I will look Jupyter notebook up, and will get back to you. Thank you again.

[snippsat]

The way you do it will be very slow,every time write a loop in Pandas it often the wrong approch.
The way Pands is build should try to use Vectorization it will be a lot faster,look at this blog post.