在Pandas中基于时序买卖操作实现库存状态列的条件向前填充

Got it, let's walk through how to solve this problem. We need to maintain a running inventory state based on the sequential bought and sold operations, then populate the inventory column to show the current state before each day's operations (matching your expected output).

Step-by-Step Approach #

1. Track Inventory State: Use a list to keep track of inventory items (this preserves the order of purchase, which aligns with your example's output format).
2. Iterate Through Rows: For each day:
   • First, update the inventory column with the current state before any operations that day.
   • Then apply the bought operation (add the item to inventory if it's not already present, adjust if duplicates are allowed).
   • Then apply the sold operation (remove the item from inventory if it exists).

Full Code Implementation #

import pandas as pd

# Initialize the input DataFrame
df = pd.DataFrame([
    (1, None, None, None),
    (2, None, 'Apple', None),
    (3, None, 'Banana', None),
    (4, None, None, 'Apple'),
    (5, None, None, 'Banana'),
    (6, None, None, None)
], columns=['day', 'inventory', 'bought', 'sold'])

# Use a list to maintain inventory (preserves purchase order)
current_inventory = []

for idx, row in df.iterrows():
    # Update the inventory column with the current state BEFORE today's operations
    if current_inventory:
        df.at[idx, 'inventory'] = ', '.join(current_inventory)
    else:
        df.at[idx, 'inventory'] = None
    
    # Handle buy operation: add item if it's not already in inventory (adjust if duplicates are allowed)
    bought_item = row['bought']
    if pd.notna(bought_item):
        if bought_item not in current_inventory:
            current_inventory.append(bought_item)
    
    # Handle sell operation: remove item if it exists in inventory
    sold_item = row['sold']
    if pd.notna(sold_item):
        if sold_item in current_inventory:
            current_inventory.remove(sold_item)

# Print the final result
print(df.to_markdown(index=False))

Output #

Running this code will produce exactly the expected result:

| day | inventory   | bought | sold  |
|-----|-------------|--------|-------|
| 1   | None        | None   | None  |
| 2   | None        | Apple  | None  |
| 3   | Apple       | Banana | None  |
| 4   | Apple, Banana | None  | Apple |
| 5   | Banana      | None   | Banana|
| 6   | None        | None   | None  |

Customization Notes #

• Allow Duplicate Items: If your use case allows multiple instances of the same item (e.g., buying 2 Apples), replace the buy logic with a simple current_inventory.append(bought_item) (no check for existing items). For quantity tracking, use a dictionary to count items (e.g., {'Apple': 2, 'Banana': 1}) and format the inventory column to show quantities like Apple x2, Banana.
• Case Sensitivity: The code treats "apple" and "Apple" as different items. For case-insensitive handling, convert items to lowercase when adding/removing from inventory.

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