技术需求：基于Pandas寻找可容纳待存储物品的最近Rack所在Aisle

Problem Statement #

We need to identify the nearest (smallest numbered) Rack within an Aisle that has enough combined shelf space to hold all items to be stored. Key rules to follow:

• Items must be placed on shelves with remaining space, and can't be split across different Aisles.
• We prioritize Racks starting from the smallest number (closest to the aisle entrance).
• After selecting the correct Rack, we need to track which shelves hold which items, along with remaining shelf space.

Background Structure #

• Aisle: Groups multiple Racks
• Rack: Contains multiple Shelves
• Shelf: Has a SpaceInLiters value representing available storage capacity

Example Data #

First, let's define our sample inventory and items to store using pandas DataFrames:

import pandas as pd

# Sample inventory data
ExampleOfTheStock = pd.DataFrame({
    'Aisle': [1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2],
    'Rack': [1, 1, 1, 2, 2, 2, 1, 1, 2, 2, 3, 3],
    'Shelves': ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'A', 'B', 'A', 'B'],
    'SpaceInLiters': [1, 2, 1, 9, 3, 4, 0, 0, 1, 1, 9, 9]
})

# Items needing storage
ItemsToBeStored = pd.DataFrame({
    'Items': [1, 2],
    'VolumeInLiters': [2, 8]
})

# Expected result
DesiredResult = pd.DataFrame({
    'WinnerAisle': [1, 1, 1],
    'WinnerRack': [2, 2, 2],
    'WinnerShelves': ['A', 'B', None],  # Can also use 'C' if simpler
    'WinnerSpaceInLiters': [1, 2, 9],  # Calculation: 9-8=1, 4-2=2, 9-0=9 (for unused shelf)
    'StoredItems': [1, 2, None]
})

Step-by-Step Solution Code #

Here's a working implementation that meets the requirements:

def find_closest_rack(stock_df, items_df):
    # Calculate total volume needed to store all items
    total_required_volume = items_df['VolumeInLiters'].sum()
    
    # Group inventory by Aisle + Rack to get total available space per rack
    rack_total_space = stock_df.groupby(['Aisle', 'Rack'])['SpaceInLiters'].sum().reset_index()
    rack_total_space = rack_total_space.rename(columns={'SpaceInLiters': 'TotalAvailableSpace'})
    
    # Filter racks that can hold the total volume, then sort to get the closest first
    eligible_racks = rack_total_space[rack_total_space['TotalAvailableSpace'] >= total_required_volume] \
        .sort_values(by=['Aisle', 'Rack'], ascending=True)
    
    # Grab the winning Aisle and Rack (first entry in sorted eligible list)
    winner_aisle, winner_rack = eligible_racks.iloc[0][['Aisle', 'Rack']]
    
    # Get all shelves for the winning rack, sorted by shelf name
    winning_shelves = stock_df[(stock_df['Aisle'] == winner_aisle) & (stock_df['Rack'] == winner_rack)] \
        .sort_values(by='Shelves').reset_index(drop=True)
    
    # Sort items by volume (largest first to optimize space usage; adjust if you need original order)
    sorted_items = items_df.sort_values(by='VolumeInLiters', ascending=False).reset_index(drop=True)
    
    # Assign items to shelves and track results
    result_rows = []
    remaining_items = sorted_items.copy()
    
    for _, shelf in winning_shelves.iterrows():
        shelf_name = shelf['Shelves']
        available_space = shelf['SpaceInLiters']
        
        # Check if we can place an item on this shelf
        if not remaining_items.empty and remaining_items['VolumeInLiters'].iloc[0] <= available_space:
            selected_item = remaining_items.iloc[0]
            result_rows.append({
                'WinnerAisle': winner_aisle,
                'WinnerRack': winner_rack,
                'WinnerShelves': shelf_name,
                'WinnerSpaceInLiters': available_space - selected_item['VolumeInLiters'],
                'StoredItems': selected_item['Items']
            })
            # Remove the assigned item from remaining list
            remaining_items = remaining_items.drop(remaining_items.index[0])
        else:
            # No item left to place, or item is too big (we already verified total rack space)
            result_rows.append({
                'WinnerAisle': winner_aisle,
                'WinnerRack': winner_rack,
                'WinnerShelves': shelf_name,
                'WinnerSpaceInLiters': available_space,
                'StoredItems': None
            })
    
    # Convert results to DataFrame and return
    return pd.DataFrame(result_rows)

# Run the function with sample data
final_result = find_closest_rack(ExampleOfTheStock, ItemsToBeStored)
print(final_result)

Explanation of the Logic #

1. Total Volume Check: We first calculate the sum of all item volumes to ensure the selected Rack has enough total space.
2. Rack Eligibility: We group the inventory by Aisle and Rack to find which racks can accommodate the total volume, then sort them to prioritize the closest (smallest number) rack.
3. Shelf Assignment: For the winning rack, we iterate through its shelves, placing items (starting with the largest volume to maximize space usage) on shelves with enough room. We track remaining shelf space and which item is stored.
4. Result Formatting: We convert the shelf assignments into the required DataFrame structure, matching the desired output.

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