bendans/cocoa-defect-detector

1import pandas as pd2import numpy as np3import streamlit as st4import plotly.express as px5from datetime import datetime6 7st.set_page_config(page_title="Smart Meter Support Governance Dashboard", layout="wide")8 9# ----------------------------10# Data loading11# ----------------------------12@st.cache_data13def load_data(path: str) -> pd.DataFrame:14    df = pd.read_csv(path)15 16    # Expected columns (edit these to match your data)17    # ticket_id, created_at, first_response_at, resolved_at, status, priority,18    # region, vendor, meter_model, firmware, category, root_cause, resolution_code,19    # field_visit, reopened, customers_affected20 21    # Parse timestamps22    for c in ["created_at", "first_response_at", "resolved_at"]:23        if c in df.columns:24            df[c] = pd.to_datetime(df[c], errors="coerce")25 26    # Normalize booleans27    for c in ["field_visit", "reopened"]:28        if c in df.columns:29            df[c] = df[c].astype(str).str.lower().isin(["true", "1", "yes", "y"])30 31    # Compute durations32    if {"created_at", "first_response_at"}.issubset(df.columns):33        df["ttr_hours"] = (df["first_response_at"] - df["created_at"]).dt.total_seconds() / 360034 35    if {"created_at", "resolved_at"}.issubset(df.columns):36        df["ttrslv_hours"] = (df["resolved_at"] - df["created_at"]).dt.total_seconds() / 360037 38    # SLA target hours by priority (edit to your SLA)39    sla_map = {"P1": 4, "P2": 24, "P3": 72, "P4": 168}40    if "priority" in df.columns and "ttrslv_hours" in df.columns:41        df["sla_target_hours"] = df["priority"].map(sla_map)42        df["sla_met"] = np.where(43            df["sla_target_hours"].notna() & df["ttrslv_hours"].notna(),44            df["ttrslv_hours"] <= df["sla_target_hours"],45            np.nan46        )47        df["sla_breach_hours"] = np.where(48            df["sla_target_hours"].notna() & df["ttrslv_hours"].notna() & (df["ttrslv_hours"] > df["sla_target_hours"]),49            df["ttrslv_hours"] - df["sla_target_hours"],50            051        )52 53    # Customer impact score (simple governance-friendly weighting)54    # Edit weights to match your organization55    pr_weight = {"P1": 10, "P2": 5, "P3": 2, "P4": 1}56    if "priority" in df.columns:57        df["impact_score"] = df["priority"].map(pr_weight).fillna(1)58 59    if "customers_affected" in df.columns:60        df["customers_affected"] = pd.to_numeric(df["customers_affected"], errors="coerce").fillna(0)61        df["impact_score"] = df["impact_score"] + (df["customers_affected"] * 0.1)62 63    return df64 65 66# ----------------------------67# Sidebar controls68# ----------------------------69st.sidebar.title("Filters")70 71data_path = st.sidebar.text_input("CSV path", value="tickets.csv")72try:73    df = load_data(data_path)74except Exception as e:75    st.error(f"Could not load data: {e}")76    st.stop()77 78# Date range filter (uses created_at)79if "created_at" in df.columns:80    min_date = df["created_at"].min()81    max_date = df["created_at"].max()82    date_range = st.sidebar.date_input(83        "Created date range",84        value=(min_date.date() if pd.notna(min_date) else datetime.today().date(),85               max_date.date() if pd.notna(max_date) else datetime.today().date())86    )87    if isinstance(date_range, tuple) and len(date_range) == 2:88        start_date, end_date = pd.to_datetime(date_range[0]), pd.to_datetime(date_range[1]) + pd.Timedelta(days=1) - pd.Timedelta(seconds=1)89        df = df[(df["created_at"] >= start_date) & (df["created_at"] <= end_date)]90 91def multi_filter(col_name: str):92    if col_name in df.columns:93        options = sorted([x for x in df[col_name].dropna().astype(str).unique()])94        selected = st.sidebar.multiselect(col_name.replace("_", " ").title(), options)95        if selected:96            return df[df[col_name].astype(str).isin(selected)]97    return df98 99for col in ["region", "priority", "status", "vendor", "meter_model", "firmware", "category", "root_cause", "resolution_code"]:100    df = multi_filter(col)101 102# ----------------------------103# Header and KPI tiles104# ----------------------------105st.title("Smart Electricity Meter Support Governance Dashboard")106 107colA, colB, colC, colD, colE, colF = st.columns(6)108 109open_tickets = int((df["status"].astype(str).str.lower() != "resolved").sum()) if "status" in df.columns else len(df)110closed_tickets = int((df["status"].astype(str).str.lower() == "resolved").sum()) if "status" in df.columns else 0111 112sla_compliance = None113sla_breaches = None114if "sla_met" in df.columns:115    valid = df["sla_met"].dropna()116    sla_compliance = float(valid.mean() * 100) if len(valid) else None117    sla_breaches = int((df["sla_met"] == False).sum())  # noqa: E712118 119mttr = float(df["ttrslv_hours"].median()) if "ttrslv_hours" in df.columns and df["ttrslv_hours"].notna().any() else None120tfr = float(df["ttr_hours"].median()) if "ttr_hours" in df.columns and df["ttr_hours"].notna().any() else None121 122repeat_rate = float(df["reopened"].mean() * 100) if "reopened" in df.columns and len(df) else None123field_rate = float(df["field_visit"].mean() * 100) if "field_visit" in df.columns and len(df) else None124impact_total = float(df["impact_score"].sum()) if "impact_score" in df.columns else None125 126colA.metric("Open tickets", f"{open_tickets:,}")127colB.metric("Resolved tickets", f"{closed_tickets:,}")128colC.metric("SLA compliance", f"{sla_compliance:.1f}%" if sla_compliance is not None else "N/A")129colD.metric("SLA breaches", f"{sla_breaches:,}" if sla_breaches is not None else "N/A")130colE.metric("Median TTR (hrs)", f"{tfr:.1f}" if tfr is not None else "N/A")131colF.metric("Median MTTR (hrs)", f"{mttr:.1f}" if mttr is not None else "N/A")132 133st.caption(134    f"Repeat incident rate: {repeat_rate:.1f}% | Field visit rate: {field_rate:.1f}% | Total impact score: {impact_total:.1f}"135    if repeat_rate is not None and field_rate is not None and impact_total is not None136    else ""137)138 139# ----------------------------140# Layout: Overview charts141# ----------------------------142left, right = st.columns([1.2, 1])143 144with left:145    st.subheader("Tickets opened vs resolved (trend)")146    if "created_at" in df.columns and "status" in df.columns:147        dft = df.copy()148        dft["created_day"] = dft["created_at"].dt.date149 150        opened = dft.groupby("created_day")["ticket_id"].count().reset_index(name="opened")151        resolved = dft[dft["status"].astype(str).str.lower() == "resolved"].copy()152        if "resolved_at" in resolved.columns:153            resolved["resolved_day"] = resolved["resolved_at"].dt.date154            resolved = resolved.groupby("resolved_day")["ticket_id"].count().reset_index(name="resolved")155            trend = opened.merge(resolved, left_on="created_day", right_on="resolved_day", how="left").drop(columns=["resolved_day"])156        else:157            trend = opened.copy()158            trend["resolved"] = np.nan159 160        fig = px.line(trend, x="created_day", y=["opened", "resolved"], markers=True)161        st.plotly_chart(fig, use_container_width=True)162    else:163        st.info("Add created_at, status, and optionally resolved_at to see the trend chart.")164 165with right:166    st.subheader("Hotspots: Region x Category")167    if {"region", "category"}.issubset(df.columns):168        pivot = df.pivot_table(index="region", columns="category", values="ticket_id", aggfunc="count", fill_value=0)169        pivot = pivot.reset_index().melt(id_vars="region", var_name="category", value_name="count")170        fig = px.density_heatmap(pivot, x="category", y="region", z="count")171        st.plotly_chart(fig, use_container_width=True)172    else:173        st.info("Add region and category columns to enable the hotspot heatmap.")174 175# ----------------------------176# Operational section177# ----------------------------178st.subheader("Backlog aging and SLA performance")179c1, c2 = st.columns(2)180 181with c1:182    st.markdown("**Aging buckets (by created date)**")183    if "created_at" in df.columns:184        age_days = (pd.Timestamp.utcnow().tz_localize(None) - df["created_at"]).dt.total_seconds() / 86400185        buckets = pd.cut(age_days, bins=[-np.inf, 2, 7, 14, 30, np.inf], labels=["0–2", "3–7", "8–14", "15–30", "30+"])186        aging = buckets.value_counts().sort_index().reset_index()187        aging.columns = ["age_bucket", "count"]188        fig = px.bar(aging, x="age_bucket", y="count")189        st.plotly_chart(fig, use_container_width=True)190    else:191        st.info("Add created_at to compute aging.")192 193with c2:194    st.markdown("**SLA met vs missed (by priority)**")195    if {"priority", "sla_met"}.issubset(df.columns):196        sla = df.dropna(subset=["sla_met"]).groupby(["priority", "sla_met"])["ticket_id"].count().reset_index(name="count")197        sla["sla_met"] = sla["sla_met"].map({True: "Met", False: "Missed"})198        fig = px.bar(sla, x="priority", y="count", color="sla_met", barmode="group")199        st.plotly_chart(fig, use_container_width=True)200    else:201        st.info("Add priority and compute sla_met to see SLA performance by priority.")202 203# ----------------------------204# Technical section205# ----------------------------206st.subheader("Technical patterns")207t1, t2, t3 = st.columns([1, 1, 1])208 209with t1:210    st.markdown("**Top categories by volume**")211    if "category" in df.columns:212        top = df["category"].astype(str).value_counts().head(10).reset_index()213        top.columns = ["category", "count"]214        fig = px.bar(top, x="count", y="category", orientation="h")215        st.plotly_chart(fig, use_container_width=True)216    else:217        st.info("Add category to see top issue types.")218 219with t2:220    st.markdown("**Top root causes**")221    if "root_cause" in df.columns:222        top = df["root_cause"].astype(str).value_counts().head(10).reset_index()223        top.columns = ["root_cause", "count"]224        fig = px.bar(top, x="count", y="root_cause", orientation="h")225        st.plotly_chart(fig, use_container_width=True)226    else:227        st.info("Add root_cause to see root cause distribution.")228 229with t3:230    st.markdown("**Resolution type split**")231    if "resolution_code" in df.columns:232        res = df["resolution_code"].astype(str).value_counts().reset_index()233        res.columns = ["resolution_code", "count"]234        fig = px.pie(res, names="resolution_code", values="count")235        st.plotly_chart(fig, use_container_width=True)236    else:237        st.info("Add resolution_code to see fix type split.")238 239# ----------------------------240# Detail table241# ----------------------------242st.subheader("Ticket details")243show_cols = [c for c in [244    "ticket_id", "created_at", "first_response_at", "resolved_at", "status", "priority", "region", "vendor",245    "meter_model", "firmware", "category", "root_cause", "resolution_code", "field_visit", "reopened",246    "ttr_hours", "ttrslv_hours", "sla_target_hours", "sla_met", "sla_breach_hours", "customers_affected", "impact_score"247] if c in df.columns]248 249st.dataframe(df[show_cols].sort_values(by="created_at", ascending=False) if "created_at" in df.columns else df[show_cols])250 251