Pandas：基于供应商排名的容量分配列循环优化

I’ll walk through how to implement this rank-first capacity allocation logic, using your sample data to illustrate each step clearly.

Core Rules to Follow #

• First, group all shipments by their origin-destination (OD) pair—each pair is an independent allocation group (no cross-OD sharing of capacity).
• For each OD pair, prioritize allocating all possible volume to the rank 1 supplier first, up to their total capacity limit.
• If the total volume for the OD pair exceeds the rank 1 supplier’s capacity, allocate the remaining volume to the next highest rank supplier(s), and so on until all volume is assigned or all supplier capacities are exhausted.

Sample Data (Formatted for Clarity) #

Step-by-Step Allocation Examples #

NYC → AMS Pair

1. Calculate total volume per category for the OD pair:

   • Total Vol_A: 90 + 150 = 240
   • Total Vol_B: 1300 + 600 = 1900
   • Total Vol_C: 2500 + 1700 = 4200
   • Combined total: 240 + 1900 + 4200 = 6340

2. Allocate to rank 1 supplier (A, capacity 4000):

   • Assign all 240 Vol_A and 1900 Vol_B to A (uses 2140 capacity, leaving 1860 remaining)
   • Assign 1860 of the 4200 Vol_C to A (exhausts their capacity)
   • Remaining 2340 Vol_C goes to rank 2 supplier B

Final NYC→AMS Allocation:

• Provider A: Vol_A=240, Vol_B=1900, Vol_C=1860 (total used: 4000)
• Provider B: Vol_A=0, Vol_B=0, Vol_C=2340 (total used: 2340)

NYC → BRI Pair

1. Calculate total volume per category:

   • Total Vol_A: 105 + 300 = 405
   • Total Vol_B:700 +1300=2000
   • Total Vol_C:100+200=300
   • Combined total:405+2000+300=2705

2. Allocate to rank 1 supplier (A, capacity 2300):

   • Assign all 405 Vol_A to A (uses 405 capacity, leaving 1895 remaining)
   • Assign 1895 of the 2000 Vol_B to A (exhausts their capacity)
   • Remaining 105 Vol_B and all 300 Vol_C go to rank 2 supplier C

Final NYC→BRI Allocation:

• Provider A: Vol_A=405, Vol_B=1895, Vol_C=0 (total used:2300)
• Provider C: Vol_A=0, Vol_B=105, Vol_C=300 (total used:405)

Python Implementation Snippet #

Here’s a pandas-based solution that automates this logic:

import pandas as pd

# Load your sample data into a DataFrame
data = pd.DataFrame([
    ["NYC", "AMS", "A", 90, 1300, 2500, 4000, 1],
    ["NYC", "AMS", "B", 150, 600, 1700, 3000, 2],
    ["NYC", "BRI", "A", 105, 700, 100, 2300, 1],
    ["NYC", "BRI", "C", 300, 1300, 200, 2800, 2]
], columns=["Origin", "Dest", "Provider", "Vol_A", "Vol_B", "Vol_C", "Capacity", "rank"])

vol_columns = ["Vol_A", "Vol_B", "Vol_C"]
allocation_results = []

# Process each OD pair independently
for (origin, dest), od_group in data.groupby(["Origin", "Dest"]):
    # Sort suppliers by rank (highest priority first)
    sorted_suppliers = od_group.sort_values("rank").reset_index(drop=True)
    # Calculate total volume needed for each category
    total_volume = sorted_suppliers[vol_columns].sum().to_dict()
    remaining_volume = total_volume.copy()

    for _, supplier in sorted_suppliers.iterrows():
        alloc = {
            "Origin": origin,
            "Dest": dest,
            "Provider": supplier["Provider"],
            "rank": supplier["rank"]
        }
        remaining_cap = supplier["Capacity"]

        # Allocate each volume category until capacity is exhausted
        for col in vol_columns:
            if remaining_volume[col] <= 0 or remaining_cap <= 0:
                alloc[col] = 0
                continue
            # Assign the minimum of remaining volume or remaining capacity
            assign_amount = min(remaining_volume[col], remaining_cap)
            alloc[col] = assign_amount
            remaining_volume[col] -= assign_amount
            remaining_cap -= assign_amount

        allocation_results.append(alloc)

# Convert results to a readable DataFrame
allocation_df = pd.DataFrame(allocation_results)
print(allocation_df)

Running this code will output the exact priority-based allocations we walked through.

内容的提问来源于stack exchange，提问作者natnay