已知最终库存值，如何用Pandas回溯计算SKU历史库存水平？

Got it, let's tackle this inventory back-calculation problem with Pandas. This mirrors the Excel "fill upward from a final value" logic you mentioned, and we'll use efficient vectorized operations instead of slow loops (critical for larger datasets).

Core Logic Breakdown #

First, let's align on the math:

• Forward calculation (normal inventory flow): Today's Stock = Yesterday's Stock + SUM_IN + SUM_OUT
• Reverse calculation (what we need): Yesterday's Stock = Today's Stock - SUM_IN - SUM_OUT

Since your DataFrame is sorted oldest to newest (earliest date first, final date at index 9), we'll reverse the DataFrame to compute cumulative changes, then flip it back to get the correct order.

Step-by-Step Implementation #

1. Set Up the Example DataFrame

First, let's replicate your input data:

import pandas as pd

# Your original input data
data = {
    'DATE': ['5/12/18', '5/13/18', '5/14/18', '5/15/18', '5/16/18', 
             '5/17/18', '5/18/18', '5/19/18', '5/20/18', '5/21/18'],
    'SUM_IN': [500, 0, 0, 0, 100, 0, 276, 0, 0, 0],
    'SUM_OUT': [0, -403, -58, -39, 0, -98, 0, -139, -59, -70]
}
df = pd.DataFrame(data)

# Known final stock value (last day, index 9)
final_stock = 10

2. Calculate Daily Inventory Changes

First, compute the net daily change in inventory:

df['delta'] = df['SUM_IN'] + df['SUM_OUT']

3. Reverse & Compute Stock Levels

We reverse the DataFrame to start from the final stock value, then calculate cumulative reverse changes:

# Reverse the DataFrame to start from the last day
reversed_df = df[::-1].copy()

# Calculate stock levels: start with final_stock, subtract cumulative delta (reverse of forward flow)
# shift(fill_value=0) ensures the first row (original last day) stays as final_stock
reversed_df['STOCK'] = final_stock - reversed_df['delta'].cumsum().shift(fill_value=0)

# Flip back to original order and assign the STOCK column
df['STOCK'] = reversed_df['STOCK'][::-1]

4. Add Optional Columns (Match Your Expected Output)

If you need the TRANSACTION_IN and TRANSACTION_OUT columns from your example, add them with simple logic:

# TRANSACTION_IN: 1 if there's incoming stock, else 0
df['TRANSACTION_IN'] = df['SUM_IN'].apply(lambda x: 1 if x > 0 else 0)

# TRANSACTION_OUT: Integer division of absolute SUM_OUT by 10 (matches your example)
df['TRANSACTION_OUT'] = df['SUM_OUT'].apply(lambda x: abs(x) // 10 if x < 0 else 0)

# Reorder columns to match your expected output
df = df[['DATE', 'TRANSACTION_IN', 'TRANSACTION_OUT', 'SUM_IN', 'SUM_OUT', 'STOCK']]

Verify the Output #

Running this code will give you exactly the expected result:

DATE  TRANSACTION_IN  TRANSACTION_OUT  SUM_IN  SUM_OUT  STOCK
0  5/12/18               1                0     500        0    500
1  5/13/18               0                9       0     -403     97
2  5/14/18               0                1       0      -58     39
3  5/15/18               0                1       0      -39      0
4  5/16/18               1                0     100        0    100
5  5/17/18               0                1       0      -98      2
6  5/18/18               1                0     276        0    278
7  5/19/18               0                1       0     -139    139
8  5/20/18               0                4       0      -59     80
9  5/21/18               0                7       0      -70     10

Key Notes #

• Sorting First: Make sure your DataFrame is sorted oldest to newest. If not, run df = df.sort_values('DATE') first.
• Efficiency: This vectorized approach is way faster than looping through rows, especially for large datasets (10k+ rows).
• Flexibility: If your SUM_OUT uses positive values for outbound stock (instead of negatives), just adjust the delta calculation to df['delta'] = df['SUM_IN'] - df['SUM_OUT'].

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