Python Pandas：如何用函数结合fillna/bfill补全库存数据缺失值

Let's walk through how to fill those missing stock_level values after creating a continuous date sequence for your inventory data. Here's a step-by-step solution using Pandas:

1. First, Set Up & Clean the Original Data #

First, let's load your raw inventory transaction data and make sure the date column is in a usable datetime format:

import pandas as pd

# Original inventory transaction data
raw_data = {
    "index": [0, 1, 2, 3, 4, 5],
    "itemid": [123456, 123456, 123456, 123457, 123457, 123457],
    "date": ["30.03.18", "04.04.18", "09.04.18", "01.04.18", "03.04.18", "11.04.18"],
    "sold": [-1, -1, 0, 0, -1, 0],
    "received": [0, 0, 1, 1, 0, 1],
    "balance": [-1, -1, 1, 1, -1, 1],
    "stock_level": [3, 2, 3, 3, 2, 3]
}

df = pd.DataFrame(raw_data)

# Convert date string to datetime (matches your dd.mm.yy format)
df["date"] = pd.to_datetime(df["date"], format="%d.%m.%y")

2. Generate Continuous Date Sequences per Item #

Next, we'll create a full daily date range for each unique item, spanning from their earliest to latest transaction date:

expanded_dfs = []

# Process each item individually
for item_id in df["itemid"].unique():
    # Get all transactions for the current item
    item_transactions = df[df["itemid"] == item_id]
    # Define the full date range for the item
    date_range = pd.date_range(
        start=item_transactions["date"].min(),
        end=item_transactions["date"].max(),
        freq="D"
    )
    # Create a dataframe with continuous dates for the item
    full_dates = pd.DataFrame({"itemid": item_id, "date": date_range})
    # Merge with original transactions to keep existing data and create NaNs for missing days
    merged_df = pd.merge(full_dates, item_transactions, on=["itemid", "date"], how="left")
    expanded_dfs.append(merged_df)

# Combine all expanded item dataframes
full_inventory = pd.concat(expanded_dfs).sort_values(["itemid", "date"]).reset_index(drop=True)

At this point, full_inventory will have NaN values in stock_level (and transaction columns) for dates with no inventory movement.

3. Fill Missing stock_level Values #

Since inventory levels stay constant between transactions, forward fill (ffill) is the perfect tool here. We'll group by itemid to ensure we only propagate values within the same item's data:

# Forward fill missing stock_level values per item
full_inventory["stock_level"] = full_inventory.groupby("itemid")["stock_level"].ffill()

# Optional: If you had leading NaNs (unlikely in your data), use backfill to cover those
# full_inventory["stock_level"] = full_inventory.groupby("itemid")["stock_level"].bfill()

Why This Works: #

• ffill takes the last valid stock_level value and uses it for all subsequent rows until a new transaction updates the level. This matches real-world inventory behavior—stock doesn't change unless there's a sale or receipt.
• Grouping by itemid prevents us from accidentally mixing stock levels between different products.

4. Verify the Result #

If you check full_inventory now, all missing stock_level values will be filled in. For example, item 123456 will have a stock_level of 3 from 30.03.18 to 03.04.18, then drop to 2 until 08.04.18, before jumping back to 3 on 09.04.18.

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