I'm writing a function to get the HOR_df output below by feeding in 2 dataframes, planned_routes and actual_shippers.
The function should look at the actual_pallets (from actual_shippers df) and then look at planned_routes to see if the columns actual pallets > max_truck_capacity. If it does, it should generate a new row, like in the picture below.
Visualisation of the inputs and wanted output:
Note
In the above case: S1 had planned 10 pallets, but with the new actual_pallets was increased so that the max_truck_capacity is too small to handle the new actual_pallets. Therefore a new row is generated with the S2 ID, and the 3 needed extra pallets.
HOR_df has in this case made sure that on the 1st of December 2021, the actual_pallets for shipper S2 were split up onto routes of 10 and 3 pallets separately, instead of the 10 pallets that were in the initial planned_routes.
Potential idea how it should be done
I'm not sure what's the most efficient way to do this, for instance if I should build something that iteratively goes through and "fill the routes up" with the new "actual_pallet" data?
# x = planned_routes
# y = actual_shippers
# z = cost of adhocs and cancellations
# w = truck eligibility
def optimal_trips(x, y, z, w):
# Step 1: Take in actual shippers package and pallet data
# Step 2: Take the actual data and feed it into the planned routes and add routes based on demand.
# Step 3: return a df with the new optimal routes.
Code for the dfs (to replicate)
Input 1:
planned_routes = pd.DataFrame({
'date':['2021-12-01','2021-12-02'],
'planned_route_id':['R1', 'R2'],
'S1_id':['S1', 'S1'],
'S2_id':['S2', 'S2'],
'S3_id':['NaN', 'NaN'],
'S4_id':['NaN', 'NaN'],
'S1_planned_packages':[110, 100],
'S2_planned_packages':[120, 100],
'S3_planned_packages':['NaN', 'NaN'],
'S4_planned_packages':['NaN', 'NaN'],
'total_planned_packages':[230, 200],
'S1_planned_pallets':[11, 10],
'S2_planned_pallets':[12, 10],
'S3_planned_pallets':['NaN', 'NaN'],
'S4_planned_pallets':['NaN', 'NaN'],
'total_pallets':[23, 20],
'truck_max_capacity':[24, 24],
'cost_route':[120, 120]
})
Input 2:
actual_shippers = pd.DataFrame({
'date':['2021-12-01','2021-12-01','2021-12-02','2021-12-02'],
'shipper_id':['S1', 'S2','S1', 'S2'],
'actual_packages':[140, 130, 140, 130],
'shipper_spp':[10, 10, 10, 10],
'actual_pallets':[14, 13, 14, 13],
'shipper_max_eligibility':[24, 24, 24, 24],
'truck_max_capacity':[24, 24, 24, 24]
})
Wanted output:
HOR_df = pd.DataFrame({
'date':['2021-12-01','2021-12-01', '2021-12-02', '2021-12-02'],
'planned_route_id':['R1', 'R3','R2', 'R4'],
'S1_id':['S1', 'S2', 'S1', 'S2'],
'S2_id':['S2', 'NaN', 'S2', 'NaN'],
'S3_id':['NaN', 'NaN','NaN', 'NaN'],
'S4_id':['NaN', 'NaN', 'NaN', 'NaN'],
'S1_actual_packages':[140, 0, 140, 0],
'S2_actual_packages':[100, 30, 100, 30],
'S3_actual_packages':['NaN', 'NaN', 'NaN', 'NaN'],
'S4_actual_packages':['NaN', 'NaN', 'NaN', 'NaN'],
'total_planned_packages':[240, 30, 240, 30], # sum(S1_actual_packages, S2_actual packages, S3... etc)
'S1_actual_pallets':[14, 3, 14, 3],
'S2_actual_pallets':[10, 'NaN', 10, 'NaN'],
'S3_actual_pallets':['NaN', 'NaN', 'NaN', 'NaN'],
'S4_actual_pallets':['NaN', 'NaN', 'NaN', 'NaN'],
'total_pallets':[24, 3, 24, 3], #sum(S1_actual_pallets, S2 ... etc)
'truck_max_capacity':[24, 24, 24, 24],
'cost_route':[120, 130, 120, 130]
})