waves_dashboard_app.py

import pandas as pd
import numpy as np
import streamlit as st
from pathlib import Path
import requests, certifi
from io import StringIO
from PIL import Image
import plotly.express as px
import plotly.graph_objects as go

# ── Constants ────────────────────────────────────────────────────────────────
GSHEET_CSV_URL = (
    "https://docs.google.com/spreadsheets/d/"
    "1dnBvmjXwUbf7ImcCnKyw1i4Z8V6EuqEdKytytRqZEjc"
    "/export?format=csv&gid=1779300350"
)
DATA_PATH = Path(__file__).parent / "WavesDashboard.xlsx"
LOGO_PATH = Path(__file__).parent / "DHF_logo_blue.png"

DHF_BLUE  = "#0285D0"
DHF_NAVY  = "#052452"
DHF_SLATE = "#B4C6CC"
DHF_GREEN = "#00A86B"
DHF_MID   = "#5BA3C9"
QUARTER_ORDER = ["Q1", "Q2", "Q3", "Q4"]

# ── Page config (must be first Streamlit call) ────────────────────────────────
st.set_page_config(
    page_title="Waves Dashboard | DHF",
    page_icon="🌊",
    layout="wide",
    initial_sidebar_state="expanded",
)

# ── Global CSS ────────────────────────────────────────────────────────────────
st.markdown("""
<style>
/* Larger metric values */
[data-testid="stMetricValue"] { font-size: 2rem !important; font-weight: 800; }

/* Impact hero cards */
.impact-card {
    background: linear-gradient(135deg, #0285D0, #052452);
    border-radius: 14px;
    padding: 28px 20px;
    text-align: center;
    color: white;
    height: 100%;
}
.impact-number {
    font-size: 3rem;
    font-weight: 900;
    line-height: 1.1;
    letter-spacing: -1px;
}
.impact-label {
    font-size: 0.95rem;
    opacity: 0.88;
    margin-top: 6px;
    line-height: 1.4;
}
</style>
""", unsafe_allow_html=True)


# ── Helper: consistent Plotly styling ────────────────────────────────────────
def dhf_fig(fig, title: str, xlab: str = "", ylab: str = ""):
    fig.update_layout(
        title=dict(text=title, font=dict(color=DHF_NAVY, size=16, family="sans-serif")),
        paper_bgcolor="rgba(0,0,0,0)",
        plot_bgcolor="rgba(0,0,0,0)",
        font=dict(color=DHF_NAVY, family="sans-serif"),
        xaxis=dict(title=xlab, gridcolor=DHF_SLATE, linecolor=DHF_SLATE, zeroline=False),
        yaxis=dict(title=ylab, gridcolor=DHF_SLATE, linecolor=DHF_SLATE, zeroline=False),
        hoverlabel=dict(bgcolor=DHF_NAVY, font_color="white", bordercolor=DHF_NAVY),
        margin=dict(l=40, r=20, t=55, b=40),
        showlegend=False,
    )
    return fig


# ── Helper: flexible column name resolver ────────────────────────────────────
def pick_col(df_in, *candidates):
    cols = list(df_in.columns)
    norm = {c.lower().strip(): c for c in cols}
    for cand in candidates:
        key = cand.lower().strip()
        if key in norm:
            return norm[key]
    cand_keys = [c.lower().strip() for c in candidates]
    for c in cols:
        lc = c.lower().strip()
        if any(k in lc for k in cand_keys):
            return c
    return None


# ── Data loader ───────────────────────────────────────────────────────────────
@st.cache_data(ttl=300)
def load_data(csv_url: str, xlsx_path: Path):
    if csv_url:
        try:
            resp = requests.get(
                csv_url, timeout=15,
                verify=certifi.where(),
                headers={"User-Agent": "waves-dashboard/2.0"},
            )
            resp.raise_for_status()
            df = pd.read_csv(StringIO(resp.text))
            df.columns = [c.strip() for c in df.columns]
            for c in df.columns:
                if c != "Property Name":
                    df[c] = pd.to_numeric(df[c], errors="coerce")
            return df, "Google Sheets (live)"
        except Exception as e:
            st.warning(f"Couldn't load Google Sheet ({e}). Falling back to local Excel…")

    df = pd.read_excel(xlsx_path, sheet_name="Quarterly Performance Data")
    df.columns = [c.strip() for c in df.columns]
    for c in df.columns:
        if c != "Property Name":
            df[c] = pd.to_numeric(df[c], errors="coerce")
    return df, "Local Excel file"


# ── Extract tidy quarterly metric ─────────────────────────────────────────────
def extract_metric(df_in, label, contains):
    matched = [c for c in df_in.columns if all(w.lower() in c.lower() for w in contains)]
    if not matched:
        return pd.DataFrame(columns=["Property Name", label, "Quarter"])
    tidy = (
        df_in[["Property Name"] + matched]
        .melt(id_vars=["Property Name"], var_name="col", value_name=label)
    )
    tidy["Quarter"] = tidy["col"].str.extract(r"(Q[1-4])", expand=False)
    return tidy.drop(columns=["col"]).dropna(subset=[label])


def filter_quarters(tidy, q_filter):
    return tidy[tidy["Quarter"].isin(q_filter)] if not tidy.empty else tidy


# ── Load data ─────────────────────────────────────────────────────────────────
df, source_label = load_data(GSHEET_CSV_URL, DATA_PATH)

# Resolve key columns once
total_units_col = pick_col(df,
    "Total Households Units", "Total Household Units", "Total Units", "Total Housing Units")
connected_col = pick_col(df,
    "Total Household Units Connected to Date", "Households Units Connected to Date",
    "Units Connected to Date", "Connected to Date", "Connected Units")
pre_grant_col = pick_col(df,
    "Pre-Grant Units Connected", "Pre Grant Units Connected", "Connected Pre-Grant")


# ── Sidebar ───────────────────────────────────────────────────────────────────
with st.sidebar:
    if LOGO_PATH.exists():
        st.image(str(LOGO_PATH), width=130)
    st.markdown("---")

    properties = ["All"] + sorted(df["Property Name"].dropna().unique().tolist())
    selected_property = st.selectbox("Property", properties, index=0)

    quarters = QUARTER_ORDER
    q_filter = st.multiselect("Quarters", quarters, default=quarters)

    show_table = st.checkbox("Show data table", value=False)

    st.markdown("---")
    if st.button("🔄 Refresh data"):
        st.cache_data.clear()
        st.rerun()

    st.caption(f"Source: **{source_label}**")
    st.caption("Refreshes every 5 min")


# ── Apply property filter ──────────────────────────────────────────────────────
df_f = df.copy()
if selected_property != "All":
    df_f = df_f[df_f["Property Name"] == selected_property]

# Extract quarterly metrics
uptime      = filter_quarters(extract_metric(df_f, "Network Uptime (%)",           ["% Network Uptime"]),           q_filter)
connections = filter_quarters(extract_metric(df_f, "Households Connected",         ["Households Units Connected"]), q_filter)
fiber       = filter_quarters(extract_metric(df_f, "Fiber Installed",              ["Fiber Installed"]),            q_filter)
events      = filter_quarters(extract_metric(df_f, "Community Events Held",        ["Community Event Held"]),       q_filter)
wifi        = filter_quarters(extract_metric(df_f, "Community WiFi Access Points", ["Community Wifi Access"]),      q_filter)

# Compute aggregate KPIs
total_units_sel = int(df_f[total_units_col].sum()) if total_units_col else 0
connected_sel   = int(df_f[connected_col].sum())   if connected_col   else 0
pre_grant_sel   = int(df_f[pre_grant_col].sum())   if pre_grant_col   else 0
pct_sel         = (connected_sel / total_units_sel * 100.0) if total_units_sel else 0.0
communities_count = df_f["Property Name"].nunique()
avg_uptime      = uptime["Network Uptime (%)"].mean() if not uptime.empty else None


# ── Hero header ───────────────────────────────────────────────────────────────
col_logo, col_title, _ = st.columns([1, 6, 1])
if LOGO_PATH.exists():
    col_logo.image(str(LOGO_PATH), width=130)
col_title.title("🌊 Waves Internet Service")
col_title.markdown("*Connecting affordable housing communities to the digital world*")

st.markdown("---")


# ── Impact hero section ───────────────────────────────────────────────────────
if selected_property == "All":
    loc_label = f"across {communities_count} Baltimore communities"
else:
    loc_label = f"in {selected_property}"

uptime_display = f"{avg_uptime:.1f}%" if avg_uptime is not None else "—"

card1, card2, card3 = st.columns(3)
with card1:
    st.markdown(f"""
    <div class="impact-card">
        <div class="impact-number">{connected_sel:,}</div>
        <div class="impact-label">households connected to<br>high-speed internet {loc_label}</div>
    </div>
    """, unsafe_allow_html=True)

with card2:
    if selected_property == "All":
        card2_num = str(communities_count)
        card2_lab = "affordable housing<br>communities served"
    else:
        card2_num = f"{pct_sel:.0f}%"
        card2_lab = "of units connected<br>in this property"
    st.markdown(f"""
    <div class="impact-card">
        <div class="impact-number">{card2_num}</div>
        <div class="impact-label">{card2_lab}</div>
    </div>
    """, unsafe_allow_html=True)

with card3:
    st.markdown(f"""
    <div class="impact-card">
        <div class="impact-number">{uptime_display}</div>
        <div class="impact-label">average network uptime<br>across all quarters</div>
    </div>
    """, unsafe_allow_html=True)

st.markdown("<br>", unsafe_allow_html=True)


# ── KPI metrics row ───────────────────────────────────────────────────────────
k1, k2, k3, k4 = st.columns(4)
k1.metric("Total Units", f"{total_units_sel:,}")
k2.metric("Connected Pre-Grant", f"{pre_grant_sel:,}")
k3.metric("Connected To-Date", f"{connected_sel:,}")
k4.metric("% Units Connected", f"{pct_sel:.1f}%")

st.progress(min(1.0, pct_sel / 100.0))
st.caption(f"**{connected_sel:,}** of **{total_units_sel:,}** units connected")

st.markdown("---")


# ── Property detail card ──────────────────────────────────────────────────────
if selected_property != "All":
    total_events = int(events["Community Events Held"].sum()) if not events.empty else 0
    total_wifi   = int(wifi["Community WiFi Access Points"].sum()) if not wifi.empty else 0

    with st.container(border=True):
        st.markdown(f"### 📍 {selected_property}")
        c1, c2, c3, c4 = st.columns(4)
        c1.metric("Total Units", f"{total_units_sel:,}")
        c2.metric("Pre-Grant Connected", f"{pre_grant_sel:,}")
        c3.metric("Connected To-Date", f"{connected_sel:,}")
        c4.metric("Connection Rate", f"{pct_sel:.1f}%")

        c5, c6, c7 = st.columns(3)
        c5.metric("Community Events (YTD)", str(total_events))
        c6.metric("WiFi Access Points", str(total_wifi))
        if avg_uptime is not None:
            c7.metric("Avg Network Uptime", f"{avg_uptime:.1f}%")

    if pct_sel >= 90:
        st.success(f"{selected_property} has exceeded 90% connectivity — a major milestone!")

    st.markdown("---")


# ── Comparison chart: all properties ─────────────────────────────────────────
if selected_property == "All" and total_units_col and connected_col:
    st.subheader("Connectivity Rate by Property")
    cmp = df.copy()
    cmp["% Connected"] = (cmp[connected_col] / cmp[total_units_col] * 100).round(1)
    cmp = cmp[["Property Name", "% Connected"]].dropna().sort_values("% Connected")

    fig = px.bar(
        cmp, x="% Connected", y="Property Name", orientation="h",
        text="% Connected", color_discrete_sequence=[DHF_BLUE],
    )
    fig.update_traces(
        texttemplate="%{text:.1f}%",
        textposition="outside",
        hovertemplate="<b>%{y}</b><br>Connected: %{x:.1f}%<extra></extra>",
    )
    fig.add_vline(x=100, line_dash="dot", line_color=DHF_NAVY, opacity=0.35)
    fig.update_xaxes(range=[0, 115])
    dhf_fig(fig, "", "% Units Connected", "")
    fig.update_layout(height=600, margin=dict(l=10, r=80, t=20, b=40))
    st.plotly_chart(fig, use_container_width=True)
    st.markdown("---")


# ── Quarterly charts ──────────────────────────────────────────────────────────
st.subheader("Quarterly Performance")

c1, c2 = st.columns(2)

with c1:
    if uptime.empty:
        st.info("No uptime data for selected filters.")
    else:
        pivot = (
            uptime.groupby("Quarter")["Network Uptime (%)"]
            .mean()
            .reindex(QUARTER_ORDER)
            .reset_index()
        )
        fig = px.line(
            pivot, x="Quarter", y="Network Uptime (%)",
            markers=True, color_discrete_sequence=[DHF_BLUE],
        )
        fig.update_traces(
            line=dict(width=3),
            marker=dict(size=10, color=DHF_NAVY, line=dict(width=2, color="white")),
            hovertemplate="<b>%{x}</b><br>Uptime: %{y:.1f}%<extra></extra>",
        )
        fig.update_yaxes(ticksuffix="%")
        dhf_fig(fig, "Network Uptime by Quarter", "Quarter", "Avg Uptime (%)")
        st.plotly_chart(fig, use_container_width=True)

with c2:
    if connections.empty:
        st.info("No connection data for selected filters.")
    else:
        pivot = (
            connections.groupby("Quarter")["Households Connected"]
            .sum()
            .reindex(QUARTER_ORDER)
            .reset_index()
        )
        fig = px.bar(
            pivot, x="Quarter", y="Households Connected",
            text="Households Connected", color_discrete_sequence=[DHF_BLUE],
        )
        fig.update_traces(
            texttemplate="%{text:,}",
            textposition="outside",
            hovertemplate="<b>%{x}</b><br>Households: %{y:,}<extra></extra>",
        )
        dhf_fig(fig, "Households Connected by Quarter", "Quarter", "Households")
        st.plotly_chart(fig, use_container_width=True)

c3, c4, c5 = st.columns(3)

with c3:
    if fiber.empty:
        st.info("No fiber data for selected filters.")
    else:
        pivot = (
            fiber.groupby("Quarter")["Fiber Installed"]
            .sum()
            .reindex(QUARTER_ORDER)
            .reset_index()
        )
        fig = px.bar(
            pivot, x="Quarter", y="Fiber Installed",
            text="Fiber Installed", color_discrete_sequence=[DHF_NAVY],
        )
        fig.update_traces(
            texttemplate="%{text:,.0f} ft",
            textposition="outside",
            hovertemplate="<b>%{x}</b><br>Fiber: %{y:,.0f} ft<extra></extra>",
        )
        dhf_fig(fig, "Fiber Installed by Quarter", "Quarter", "Linear Feet")
        st.plotly_chart(fig, use_container_width=True)

with c4:
    if events.empty:
        st.info("No event data for selected filters.")
    else:
        pivot = (
            events.groupby("Quarter")["Community Events Held"]
            .sum()
            .reindex(QUARTER_ORDER)
            .reset_index()
        )
        fig = px.bar(
            pivot, x="Quarter", y="Community Events Held",
            text="Community Events Held", color_discrete_sequence=[DHF_GREEN],
        )
        fig.update_traces(
            texttemplate="%{text}",
            textposition="outside",
            hovertemplate="<b>%{x}</b><br>Events: %{y}<extra></extra>",
        )
        dhf_fig(fig, "Community Events by Quarter", "Quarter", "Events")
        st.plotly_chart(fig, use_container_width=True)

with c5:
    if wifi.empty:
        st.info("No WiFi access point data for selected filters.")
    else:
        pivot = (
            wifi.groupby("Quarter")["Community WiFi Access Points"]
            .sum()
            .reindex(QUARTER_ORDER)
            .reset_index()
        )
        fig = px.bar(
            pivot, x="Quarter", y="Community WiFi Access Points",
            text="Community WiFi Access Points", color_discrete_sequence=[DHF_MID],
        )
        fig.update_traces(
            texttemplate="%{text}",
            textposition="outside",
            hovertemplate="<b>%{x}</b><br>Access Points: %{y}<extra></extra>",
        )
        dhf_fig(fig, "WiFi Access Points by Quarter", "Quarter", "Access Points")
        st.plotly_chart(fig, use_container_width=True)


# ── Data table + export ───────────────────────────────────────────────────────
if show_table:
    st.markdown("---")
    st.subheader("Detail Data")

    fname = f"waves_{selected_property.replace(' ', '_')}.csv"
    st.download_button(
        label="⬇ Download CSV",
        data=df_f.to_csv(index=False).encode("utf-8"),
        file_name=fname,
        mime="text/csv",
    )
    st.dataframe(df_f.reset_index(drop=True), use_container_width=True, hide_index=True)


# ── Footer ────────────────────────────────────────────────────────────────────
st.markdown("---")
st.caption("Digital Housing Fellowship · Waves Internet Service · Data refreshes every 5 minutes")