基于Pandas实现多工作表库存余额时间线统计及短缺预警

Hey there, let's tackle your inventory alert table request head-on. This is totally feasible, and while there are a few details to work through, it's well within reach using either Python (with pandas) or Excel's built-in Power Query. Let's break down the feasibility, difficulty, and a concrete example:

Feasibility: 100% Doable #

You have two solid paths to implement this:

• Python + pandas: Ideal if you need automated, repeatable processing (great for large datasets or regular updates)
• Excel Power Query: Perfect if you prefer a no-code, Excel-native workflow (no need to leave your spreadsheet environment)

Both tools can handle the table joins, data aggregation, and calculation logic you need.

Difficulty: Moderate #

The main challenges are focused on three key steps—none are showstoppers, but they require careful setup:

1. Product name standardization: Your examples show mismatched labels like 5.25 (instock) and 5 1/4 (ordered/promised) for the same product. You'll need to define a rule to unify these.
2. Date alignment: You'll need to create a complete grid of all products + all relevant dates from the three tables to ensure no time points are missed.
3. Calculation logic alignment: You'll need to confirm the exact formula for balance (based on your sample output, it looks like balance = onhand + qtyordered - commited, but we can adjust this if needed).

Here's a working code snippet that maps directly to your requirements:

import pandas as pd

# 1. Load your Excel sheets
df_instock = pd.read_excel("your_inventory_file.xlsx", sheet_name="instock")
df_ordered = pd.read_excel("your_inventory_file.xlsx", sheet_name="ordered")
df_promised = pd.read_excel("your_inventory_file.xlsx", sheet_name="promised")

# 2. Standardize product names (fix mismatches like 5.25 vs 5 1/4)
def standardize_product(product):
    if str(product).strip() == "5 1/4":
        return "5.25"
    return str(product).strip()

for df in [df_instock, df_ordered, df_promised]:
    df["product"] = df["product"].apply(standardize_product)

# 3. Aggregate data by product + date
# Sum total onhand inventory per product (adjust if instock has date-specific snapshots)
onhand_totals = df_instock.groupby("product")["qty"].sum().reset_index()
onhand_totals.rename(columns={"qty": "onhand"}, inplace=True)

# Sum ordered quantities per product + date
ordered_agg = df_ordered.groupby(["product", "date"])["qty"].sum().reset_index()
ordered_agg.rename(columns={"qty": "qtyordered"}, inplace=True)

# Sum promised quantities per product + date
promised_agg = df_promised.groupby(["product", "date"])["qty"].sum().reset_index()
promised_agg.rename(columns={"qty": "commited"}, inplace=True)

# 4. Create a complete product-date grid to avoid missing rows
all_products = pd.concat([onhand_totals["product"], ordered_agg["product"], promised_agg["product"]]).unique()
all_dates = pd.concat([ordered_agg["date"], promised_agg["date"]]).unique()
product_date_grid = pd.MultiIndex.from_product([all_products, all_dates], names=["product", "date"]).to_frame(index=False)

# 5. Merge all data together
merged_data = product_date_grid.merge(onhand_totals, on="product", how="left")
merged_data = merged_data.merge(ordered_agg, on=["product", "date"], how="left")
merged_data = merged_data.merge(promised_agg, on=["product", "date"], how="left")

# 6. Clean up missing values and calculate metrics
merged_data[["onhand", "qtyordered", "commited"]] = merged_data[["onhand", "qtyordered", "commited"]].fillna(0)
merged_data["balance"] = merged_data["onhand"] + merged_data["qtyordered"] - merged_data["commited"]
merged_data["shortage"] = merged_data["balance"].apply(lambda x: "y" if x < 0 else "n")

# 7. Sort and format the final output
final_output = merged_data.sort_values(["product", "date"])[
    ["product", "date", "onhand", "qtyordered", "commited", "balance", "shortage"]
]

# Export to Excel
final_output.to_excel("inventory_alert_report.xlsx", index=False)

• If your instock table has date-specific inventory snapshots (not just a total), adjust the aggregation to group by product + date instead of just product.
• Double-check the date formatting across all sheets—pandas can auto-convert most formats, but you may need to add parse_dates=["date"] to the read_excel calls if dates load as text.
• Expand the standardize_product function if you have other mismatched product labels (e.g., 6 vs 6.0).

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