The Problem

Figure out how to generate a new row when the sum of two values from another DF is larger than the specified max capacity of that row, and storing the new values in the new row.

Background

A truck plans to go to two customers to deliver some packages, forming two routes to drive to deliver these packages (see df_routes). The truck has a max capacity of how many packages it can take per route (column truck_max_capacity). On the date of delivery, we get an update on how many packages the customer actually wanted (see df_actuals), which is different from our initial plan.

In our scenario here, the new values from df_actuals > df_planned. Therefore a new row for the new route needs to be created so we can send a truck to pick up these packages. So the wanted output should in theory look like the below df_new_routes.

My idea for a solution:

A function that could take:

• The sum of the plan for customer A and B in DF_input_1: 11+12 = 23
• The new actual values from DF_input_2: 14 + 13 = 27
  • Look at the max_capacity column says the max should be = 24:
    1. We take 14 from customer A and 10 from customer B
    2. Generate a new row for Customer B for 27-24 = 3 (the rest value)

My challenges with this:

• I'm not sure how to complete the above steps so that a new row is generated with the rest value of 3
• Am I overcomplicating this? Is there a way to more easily check if something is over a new sum value --> then create a new row with the "rest" value of 3 here?

Code to reproduce dataframes:

df_routes = pd.DataFrame({
                    'date':['2021-12-24','2021-12-25'],
                    'customer_1_id':['A', 'A'],                    
                    'customer_2_id':['B', 'B'],                    
                    'customer_1_planned_packages':[11, 10],                    
                    'customer_2_planned_packages':[12, 10],                    
                    'total_pallets':[23, 20],
                    'truck_max_capacity':[24, 24],
                    'cost_route':[120, 120] 
                     })

df_actuals = pd.DataFrame({
                    'date':['2021-12-24','2021-12-24','2021-12-25','2021-12-25'],
                    'shipper_id':['A', 'B','A', 'B'],
                    'actual_packages':[14, 13, 14, 13]
                     })

df_new_routes = pd.DataFrame({
                    'date':['2021-12-24','2021-12-24', '2021-12-25', '2021-12-25'],
                    'customer_1_id':['A', 'B', 'A', 'B'],                    
                    'customer_2_id':['B', np.nan, 'B', np.nan],
                    'customer_1_actual_packages':[14, 3, 14, 3],                    
                    'customer_2_actual_packages':[10, np.nan, 10, np.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]
                     })