pratik0701/Forecasting_SCM
0
1import streamlit as st2import pandas as pd3import altair as alt4import zipfile5 6# ----------------- PAGE CONFIG -----------------7st.set_page_config(page_title="Item Master Forecast App", layout="wide")8 9st.title("📦 Item Master Forecast App")10st.write("Upload your processed Excel file and explore it with search, forecast, and vendor intelligence.")11 12# ----------------- FILE UPLOAD -----------------13# ----------------- FILE UPLOAD -----------------14uploaded_file = st.file_uploader(15 "Upload Final Excel File (XLSX or ZIP containing XLSX)",16 type=["xlsx", "zip"]17)18 19if not uploaded_file:20 st.info("Please upload your Excel file (Final_Planning_With_Forecast_And_Vendor.xlsx).")21 st.stop()22 23# ----------------- LOAD DATA (HANDLE XLSX OR ZIP) -----------------24file_name = uploaded_file.name.lower()25 26if file_name.endswith(".xlsx"):27 # Direct Excel file28 df_raw = pd.read_excel(uploaded_file)29 30elif file_name.endswith(".zip"):31 # ZIP containing one or more Excel files32 try:33 with zipfile.ZipFile(uploaded_file) as z:34 # find first .xlsx inside zip35 xlsx_names = [n for n in z.namelist() if n.lower().endswith(".xlsx")]36 if not xlsx_names:37 st.error("No .xlsx file found inside the ZIP.")38 st.stop()39 # open the first Excel inside zip40 with z.open(xlsx_names[0]) as f:41 df_raw = pd.read_excel(f)42 except Exception as e:43 st.error(f"Error reading ZIP file: {e}")44 st.stop()45else:46 st.error("Unsupported file type. Please upload .xlsx or .zip.")47 st.stop()48 49df = df_raw.copy()50 51# ----------------- BASIC DATA CLEANING -----------------52# Fill vendor text fields53if "Rec_Vendor_Name" in df.columns:54 df["Rec_Vendor_Name"] = df["Rec_Vendor_Name"].fillna("No vendor data")55 56# Fill vendor numeric fields57for col in [58 "Rec_Vendor_Price_USD",59 "Rec_Vendor_LeadTime_Days",60 "Rec_Vendor_OnTime_Percent",61 "Rec_Vendor_Reliability_Score",62 "Rec_Vendor_Composite_Score",63]:64 if col in df.columns:65 df[col] = df[col].fillna(0)66 67# Fill inventory numeric fields68for col in ["safety_stock", "ROP", "On_Hand_Qty", "Coverage_Days",69 "forecast_3M", "forecast_6M", "forecast_12M"]:70 if col in df.columns:71 df[col] = df[col].fillna(0)72 73st.success(f"File uploaded successfully! Rows: {len(df):,}")74 75# Helper function for safe number formatting76def fmt(x):77 try:78 if pd.isna(x):79 return "-"80 return f"{float(x):.0f}"81 except Exception:82 return "-"83 84# ----------------- TABS -----------------85tab_dash, tab_search, tab_forecast, tab_vendor = st.tabs(86 ["📊 Dashboard", "🔎 Item Search", "📈 Forecast & Planning", "🤝 Vendor Recommendation"]87)88 89# ===========================================================90# TAB 1 – DASHBOARD91# ===========================================================92with tab_dash:93 st.subheader("📊 Overall Dashboard")94 95 total_rows = len(df)96 total_items = df["Item Name"].nunique() if "Item Name" in df.columns else total_rows97 zero_stock = df[df.get("On_Hand_Qty", 0) <= 0].shape[0] if "On_Hand_Qty" in df.columns else 098 below_safety = df[df.get("On_Hand_Qty", 0) < df.get("safety_stock", 0)].shape[0] \99 if ("On_Hand_Qty" in df.columns and "safety_stock" in df.columns) else 0100 101 col1, col2, col3, col4 = st.columns(4)102 col1.metric("Total Rows", f"{total_rows:,}")103 col2.metric("Unique Items", f"{total_items:,}")104 col3.metric("Items with Zero / Negative Stock", f"{zero_stock:,}")105 col4.metric("Items Below Safety Stock", f"{below_safety:,}")106 107 st.divider()108 109 st.write("### Top 10 Items by Coverage (Days)")110 if "Coverage_Days" in df.columns and "Item Name" in df.columns:111 top_cov = df.sort_values("Coverage_Days", ascending=False)[112 ["Item Name", "Coverage_Days"]113 ].head(10)114 chart = alt.Chart(top_cov).mark_bar().encode(115 x=alt.X("Coverage_Days:Q", title="Coverage Days"),116 y=alt.Y("Item Name:N", sort='-x', title="Item"),117 tooltip=["Item Name", "Coverage_Days"]118 )119 st.altair_chart(chart, use_container_width=True)120 else:121 st.info("Coverage_Days or Item Name column not found for dashboard chart.")122 123# ===========================================================124# TAB 2 – ITEM SEARCH125# ===========================================================126with tab_search:127 st.subheader("🔎 Search Items in Final Master")128 129 search_text = st.text_input("Search by Item Number / Name / Description:")130 131 filtered_df = df.copy()132 if search_text:133 filtered_df = df[134 df.apply(lambda row: row.astype(str).str.contains(search_text, case=False).any(), axis=1)135 ]136 137 st.write(f"Showing **{len(filtered_df):,}** records")138 st.dataframe(filtered_df, use_container_width=True)139 140 # Download filtered data141 csv_data = filtered_df.to_csv(index=False).encode("utf-8")142 st.download_button(143 label="⬇️ Download filtered records (CSV)",144 data=csv_data,145 file_name="filtered_items.csv",146 mime="text/csv",147 )148 149# ===========================================================150# TAB 3 – FORECAST & INVENTORY151# ===========================================================152with tab_forecast:153 st.subheader("📈 Forecast & Inventory Planning")154 155 required_columns = [156 "Item Name",157 "Item Description",158 "On_Hand_Qty",159 "safety_stock",160 "ROP",161 "forecast_3M",162 "forecast_6M",163 "forecast_12M",164 "Coverage_Days",165 ]166 167 missing_cols = [col for col in required_columns if col not in df.columns]168 if missing_cols:169 st.error(f"Missing columns in Excel: {missing_cols}")170 st.stop()171 172 # Item selection173 item_list = df["Item Name"].dropna().unique().tolist()174 item_selected = st.selectbox("Select Item (Item Name / Code)", item_list)175 176 item_data = df[df["Item Name"] == item_selected].iloc[0]177 178 st.write(f"### 🏷️ {item_data['Item Name']}")179 st.write(item_data["Item Description"])180 181 colA, colB, colC = st.columns(3)182 colD, colE, colF = st.columns(3)183 184 colA.metric("Forecast 3M", fmt(item_data["forecast_3M"]))185 colB.metric("Forecast 6M", fmt(item_data["forecast_6M"]))186 colC.metric("Forecast 12M", fmt(item_data["forecast_12M"]))187 188 colD.metric("Safety Stock", fmt(item_data["safety_stock"]))189 colE.metric("Reorder Point (ROP)", fmt(item_data["ROP"]))190 colF.metric("On-Hand Qty", fmt(item_data["On_Hand_Qty"]))191 192 st.metric("Coverage Days", fmt(item_data["Coverage_Days"]))193 194 st.success("Forecast and inventory values loaded successfully!")195 196 st.divider()197 198 # --- Forecast Chart (3/6/12M) ---199 st.write("### 📊 Forecast Trend (3M / 6M / 12M)")200 chart_df = pd.DataFrame({201 "Period": ["3M", "6M", "12M"],202 "ForecastQty": [203 float(item_data["forecast_3M"]),204 float(item_data["forecast_6M"]),205 float(item_data["forecast_12M"]),206 ],207 })208 chart = alt.Chart(chart_df).mark_line(point=True).encode(209 x=alt.X("Period:N", title="Period"),210 y=alt.Y("ForecastQty:Q", title="Forecast Quantity"),211 tooltip=["Period", "ForecastQty"]212 )213 st.altair_chart(chart, use_container_width=True)214 215# ===========================================================216# TAB 4 – VENDOR RECOMMENDATION217# ===========================================================218with tab_vendor:219 st.subheader("🤝 Vendor Recommendation Engine")220 221 vendor_cols = [222 "Item Name",223 "Item Description",224 "Rec_Vendor_Name",225 "Rec_Vendor_Price_USD",226 "Rec_Vendor_LeadTime_Days",227 "Rec_Vendor_OnTime_Percent",228 "Rec_Vendor_Reliability_Score",229 "Rec_Vendor_Composite_Score",230 ]231 232 missing = [c for c in vendor_cols if c not in df.columns]233 if missing:234 st.error(f"Missing vendor columns in Excel: {missing}")235 st.stop()236 237 # Item selection238 item_list_v = df["Item Name"].dropna().unique().tolist()239 item_selected_v = st.selectbox("Select Item for Vendor Comparison", item_list_v)240 241 item_rows = df[df["Item Name"] == item_selected_v]242 if item_rows.empty:243 st.warning("No vendor data for this item.")244 st.stop()245 246 item_data_v = item_rows.iloc[0]247 248 # Show selected item249 st.write(f"### 🏷️ {item_data_v['Item Name']}")250 st.write(item_data_v["Item Description"])251 252 # Recommended vendor summary253 st.subheader("⭐ Recommended Vendor")254 255 col1, col2, col3 = st.columns(3)256 col4, col5 = st.columns(2)257 258 col1.metric("Vendor", str(item_data_v["Rec_Vendor_Name"]))259 col2.metric("Price (USD)", fmt(item_data_v["Rec_Vendor_Price_USD"]))260 col3.metric("Lead Time (Days)", fmt(item_data_v["Rec_Vendor_LeadTime_Days"]))261 262 col4.metric("On-Time %", fmt(item_data_v["Rec_Vendor_OnTime_Percent"]))263 col5.metric("Reliability Score", fmt(item_data_v["Rec_Vendor_Reliability_Score"]))264 265 st.metric("Composite Score", fmt(item_data_v["Rec_Vendor_Composite_Score"]))266 267 st.success("Recommended vendor loaded successfully!")268 269 # --- Vendor metrics chart ---270 st.write("### 📊 Vendor Performance Profile (Recommended Vendor)")271 vc_df = pd.DataFrame({272 "Metric": ["Price (USD)", "Lead Time (Days)", "On-Time %", "Reliability", "Composite Score"],273 "Value": [274 float(item_data_v["Rec_Vendor_Price_USD"]),275 float(item_data_v["Rec_Vendor_LeadTime_Days"]),276 float(item_data_v["Rec_Vendor_OnTime_Percent"]),277 float(item_data_v["Rec_Vendor_Reliability_Score"]),278 float(item_data_v["Rec_Vendor_Composite_Score"]),279 ],280 })281 v_chart = alt.Chart(vc_df).mark_bar().encode(282 x=alt.X("Metric:N", sort=None),283 y=alt.Y("Value:Q"),284 tooltip=["Metric", "Value"]285 )286 st.altair_chart(v_chart, use_container_width=True)287 288 st.divider()289 290 # --- Full vendor table ---291 st.write("### 📋 Complete Vendor Details for this Item")292 st.dataframe(item_rows, use_container_width=True)293 294 # --- Download vendor data for this item ---295 vendor_csv = item_rows.to_csv(index=False).encode("utf-8")296 st.download_button(297 label="⬇️ Download vendor data for this item (CSV)",298 data=vendor_csv,299 file_name=f"vendor_data_{item_selected_v}.csv",300 mime="text/csv",301 )302 303 # --- Simple text report ---304 report_lines = [305 f"Item: {item_data_v['Item Name']}",306 f"Description: {item_data_v['Item Description']}",307 "",308 "=== Recommended Vendor ===",309 f"Name: {item_data_v['Rec_Vendor_Name']}",310 f"Price (USD): {fmt(item_data_v['Rec_Vendor_Price_USD'])}",311 f"Lead Time (Days): {fmt(item_data_v['Rec_Vendor_LeadTime_Days'])}",312 f"On-Time %: {fmt(item_data_v['Rec_Vendor_OnTime_Percent'])}",313 f"Reliability Score: {fmt(item_data_v['Rec_Vendor_Reliability_Score'])}",314 f"Composite Score: {fmt(item_data_v['Rec_Vendor_Composite_Score'])}",315 ]316 report_text = "\n".join(report_lines)317 318 st.download_button(319 label="⬇️ Download simple text report (open & Print to PDF)",320 data=report_text,321 file_name=f"ItemReport_{item_selected_v}.txt",322 mime="text/plain",323 )324