title: generate lowest number of rows in pandas based on planned vs actual data
body: <h1>Description</h1>
  <p>We have a <code>planned_routes</code> df that shows us what our plan was, and a <code>actual_shippers</code> df that shows us the actual demand of shippers (customers). Looking at planned routes and then the actual demand, we can see if there are any routes that can be added (adhoc) or cancelled. The output I'm looking for is to generate a new DF with what the actual optimal routes would look like (see <code>HOR_df</code> below as example).</p>
  <p>Visualisation of the inputs and wanted output:
  <a href="https://i.stack.imgur.com/CMiGt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CMiGt.png" alt="dfs" /></a></p>
  <h1>The code problem:</h1>
  <p>We want to create a new df (see <code>HOR_df</code>) that gives us:</p>
  <ul>
  <li>The new optimal routes given the actuals df</li>
  <li>Create new routes where demand is too high for our current capacity</li>
  <li>while <strong>minimize the total number of extra adhoc (added) routes</strong></li>
  <li>while getting all pallets to the shippers</li>
  <li>has the shipper information about how many pallets are shipped</li>
  </ul>
  <h3>Two situations to consider:</h3>
  <ol>
  <li><p>Actual pallets/packages = 0. Then it should be cancelled and return a row with a value from the <code>cost_for_route</code> column (in this case = £0).</p>
  </li>
  <li><p>Actual pallets/packages &gt; planned pallets/packages. The max pallet threshold for a truck is in the column <code>truck_max_capacity</code>. If <code>actual_pallet</code> &gt; <code>shipper_max_eligibility</code> column in the <code>actual_shipper</code> df = then it should generate a new row (a new adhoc route) with the number of pallets we cannot fill on the first truck, with the associated cost from the <code>cost_adhoc_cancellations</code> df for that capacity. See <code>HOR_df</code> below for an example how the output should look after feeding in the input dfs.</p>
  </li>
  </ol>
  <h2>1st attempt of solution (potential idea how it should be done)</h2>
  <p>Write a function that takes in the dataframes and does the below steps:</p>
  <pre><code># 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, or cancel if demand = 0
          
      # Step 3: return a df with the new optimal routes with the associated costs and shipper information
  
  </code></pre>
  <p>I get stuck on the fact that it would be easier if every route just had 1 shipper, but as you can see there are scenarios where we would have two shippers on the same route which we need to account for the actuals.</p>
  <h3>Input DFs info:</h3>
  <ol>
  <li><p><code>planned_routes</code> = the planned routes of 1-4 shippers (customers) where a planned truck is delivering 140 packages, divided by the column SPP (shipments per pallet). This gives us how many pallets that should be delivered to customer S1 and S2. See a visualisation of the data and example DF code at the bottom.</p>
  </li>
  <li><p><code>actual_shippers</code> = the actual number of packages and pallets the shippers actually wanted. This is the demand we need to assess if we need to cancel or add any new routes (adhocs).</p>
  </li>
  <li><p><code>truck_eligability</code> = what type of truck (capacity) a shipper_id can get access to.</p>
  </li>
  <li><p><code>cost_adhoc_cancellations</code> = Cost for regular pickup vs adhoc / cancellation. If a shipper with a truck eligibility of 24 pallets, regular = 120, adhoc = £120*1.5, cancellation = 0.</p>
  </li>
  </ol>
  <h3>DF code (to replicate)</h3>
  <p><strong>Input 1:</strong></p>
  <pre><code>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]
                       })
  </code></pre>
  <p><strong>Input 2:</strong></p>
  <pre><code>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]
                       })
  </code></pre>
  <p><strong>Wanted output:</strong></p>
  <pre><code>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]
                       })
  </code></pre>