I have two dataframes with multiple columns like below
head(df
SCHEDULING_DC_NBR COMMODITY_CODE Unload_Start_Time DOW Dlry
0 6042.0 SCGR 15:15 SUN 5
1 6042.0 SCGR 15:30 SUN 6
2 6042.0 SCGR 15:45 SUN 7
3 6042.0 SCGR 16:15 SUN 8
4 6042.0 SCGR 18:30 SUN 9
head(config_df)
Node Window APPLICABLE_DAYS COMMODITY_CODE Window_start_time
7023.0 03:15 AM to 03:16 AM MON SCPR 03:15
7023.0 03:15 AM to 03:16 AM THUR SCPR 03:15
7023.0 03:15 AM to 03:16 AM FRI SCPR 03:15
6042.0 06:00 PM to 06:05 PM SUN SCPR 18:00
6042.0 03:00 PM to 03:05 PM SUN SCGR 15:00
I want to apply row-wise operation on dataframe df to find the appropriate Window from config_df using some logic like below using apply function
def window_mapping(hist_df):
window_times = []
row = hist_df.copy()
window_times = pd.to_datetime(config_df.loc[(config_df['Node'].values == row['SCHEDULING_DC_NBR'].values)
& (config_df['COMMODITY_CODE'].values == row['COMMODITY_CODE'].values)
& (config_df['APPLICABLE_DAYS'].str.contains(row['DOW'].values,case=False))
,"Window_start_time"].values)
if( len(window_times) > 0 ):
if pd.to_datetime(row['Unload_Start_Time']) <= min(window_times):
return config_df.loc[(config_df['Node'] == row['SCHEDULING_DC_NBR']) & (config_df['COMMODITY_CODE'] == row['COMMODITY_CODE']) & ( config_df['Window_start_time'] == min(window_times).strftime('%H:%M')),"Window"].values[0], row['DOW']
elif pd.to_datetime(row['Unload_Start_Time']) >= max(window_times):
return config_df.loc[(config_df['Node'] == row['SCHEDULING_DC_NBR']) & (config_df['COMMODITY_CODE'] == row['COMMODITY_CODE']) & ( config_df['Window_start_time'] == max(window_times).strftime('%H:%M')),"Window"].values[0], row['DOW']
else:
# Find the difference of row['Unload_Start_Time'] with all window_times and get the smallest +ve difference
differences = {}
for times in window_times:
differences[times] = (pd.to_datetime(row['Unload_Start_Time']) - times).seconds/60
return config_df.loc[(config_df['Node'] == row['SCHEDULING_DC_NBR']) & (config_df['COMMODITY_CODE'] == row['COMMODITY_CODE']) & ( config_df['Window_start_time'] == min(differences, key=differences.get).strftime('%H:%M')),"Window"].values[0], row['DOW']
else:
return '',''
Below is the apply function used to apply the above function
a['Processed_window'],a['Processed_DOW'] = zip(*a.apply(window_mapping,axis=1))
The final output after applying the window_mapping function looks like below
SCHEDULING_DC_NBR COMMODITY_CODE Unload_Start_Time DOW Processed_window Processed_DOW
6042.0 SCGR 15:15 SUN 03:00 PM to 03:05 PM SUN
6042.0 SCGR 15:30 SUN 03:00 PM to 03:05 PM SUN
6042.0 SCGR 15:45 SUN 03:00 PM to 03:05 PM SUN
6042.0 SCGR 16:15 SUN 03:00 PM to 03:05 PM SUN
6042.0 SCGR 18:30 SUN 06:00 PM to 06:05 PM SUN
Using the apply function it took below time just for 1000 records. My original dataframe contains more than 120K records.
**14.9 s ± 160 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)**
Is there a better way to do this operation. I know np.where and np.select could be used for conditional checking. however, I am doing more than just conditional checking by calculating a list and a dictionary for my checking which are used for computation. A detailed solution or approach would really help.