cyacya123/KOALADX

1# stats_tab.py2# -*- coding: utf-8 -*-3 4import pandas as pd5import streamlit as st6import numpy as np7 8try:9    import altair as alt10except Exception:11    alt = None12 13 14def render_stats_tab(df_all_messages: pd.DataFrame, ss):15    st.subheader("Usage & Conversation Stats")16 17    df_all = (df_all_messages.copy() if df_all_messages is not None else pd.DataFrame())18    if df_all.empty:19        st.info("No messages available for stats. Import from Cloud Pull or CSV first.")20        return21 22    # Robust UTC→JST handling23    ts_utc = pd.to_datetime(df_all["ts"], errors="coerce", utc=True)24    ts_jst = ts_utc.dt.tz_convert("Asia/Tokyo")25    df_all["ts_jst"] = ts_jst26    df_all["day"] = ts_jst.dt.strftime("%Y-%m-%d")27    df_all["hour"] = ts_jst.dt.hour28    df_all["dow"] = ts_jst.dt.dayofweek  # 0=Mon..6=Sun29    df_all["dow_name"] = df_all["dow"].map({0: "Mon", 1: "Tue", 2: "Wed", 3: "Thu", 4: "Fri", 5: "Sat", 6: "Sun"})30 31    # Sender label (nickname > display_name > id)32    idx_map = ss.get("user_index", {}) if ss is not None else {}33 34    def _label(u: str) -> str:35        rec = (idx_map.get(u, {}) or {})36        nickname = str(rec.get("nickname", "")).strip()37        display = str(rec.get("display_name", "")).strip()38        base = nickname or display or u39        suffix = u[-6:] if isinstance(u, str) and len(u) >= 6 else u40        return f"{base} ({suffix})"41 42    df_all["sender"] = df_all["user_id"].astype(str).map(_label)43 44    # Controls45    st.markdown("**Time Range & Metric**")46    colr1, colr2, colr3 = st.columns([1.2, 1, 1.2])47    with colr1:48        range_choice = st.selectbox("Range", ["Past day", "Past week", "Past month", "Past year", "All"], index=1)49    with colr2:50        metric_type = st.radio("Metric", ["Message time", "First-seen (follow) time"], index=0)51    with colr3:52        gran_override = st.selectbox(53            "Granularity",54            ["Auto", "Hourly", "Daily", "Weekly"],55            index=0,56            help="Auto picks Hourly for ≤2 days, else Daily.",57        )58 59    now_jst = pd.Timestamp.now(tz="Asia/Tokyo")60    if range_choice == "Past day":61        start_jst = now_jst - pd.Timedelta(days=1)62    elif range_choice == "Past week":63        start_jst = now_jst - pd.Timedelta(weeks=1)64    elif range_choice == "Past month":65        start_jst = now_jst - pd.Timedelta(days=30)66    elif range_choice == "Past year":67        start_jst = now_jst - pd.Timedelta(days=365)68    else:69        start_jst = df_all["ts_jst"].min() or (now_jst - pd.Timedelta(days=365))70    end_jst = now_jst71 72    dff = df_all[(df_all["ts_jst"] >= start_jst) & (df_all["ts_jst"] <= end_jst)].copy()73    if dff.empty:74        st.info("No messages in the selected window.")75        return76 77    st.markdown("### Overview")78 79    # Frequency80    if gran_override == "Hourly":81        freq = "H"82    elif gran_override == "Daily":83        freq = "D"84    elif gran_override == "Weekly":85        freq = "W"86    else:87        freq = "H" if (end_jst - start_jst) <= pd.Timedelta(days=2) else "D"88 89    # Main time series90    if metric_type == "Message time":91        series = dff.set_index("ts_jst").resample(freq).size()92        title_main = "Messages over time"93    else:94        first_seen = df_all.groupby("user_id")["ts_jst"].min().dropna()95        fs_win = first_seen[(first_seen >= start_jst) & (first_seen <= end_jst)]96        series = fs_win.to_frame("ts_jst").set_index("ts_jst").resample(freq).size()97        title_main = "New users over time (first seen)"98 99    series_df = series.rename_axis("time").reset_index(name="count")100    if not series_df.empty:101        if alt:102            st.altair_chart(103                alt.Chart(series_df).mark_line(point=True).encode(104                    x=alt.X("time:T", title="Time (JST)"),105                    y=alt.Y("count:Q", title="Count"),106                ).properties(height=240, title=title_main),107                use_container_width=True,108            )109        else:110            st.line_chart(series_df.set_index("time")["count"], height=240)111 112    # Hour-of-day113    by_hour = dff.groupby("hour").size().reset_index(name="count")114    if alt:115        st.altair_chart(116            alt.Chart(by_hour).mark_bar().encode(117                x=alt.X("hour:O", title="Hour (JST)"),118                y=alt.Y("count:Q", title="Messages"),119            ).properties(height=180, title="Messages by hour"),120            use_container_width=True,121        )122    else:123        st.bar_chart(by_hour.set_index("hour")["count"], height=180)124 125    # Weekday126    order_dow = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]127    by_dow = dff.groupby("dow_name").size().reindex(order_dow).fillna(0).reset_index()128    by_dow.columns = ["weekday", "count"]129    if alt:130        st.altair_chart(131            alt.Chart(by_dow).mark_bar().encode(132                x=alt.X("weekday:N", sort=order_dow, title="Weekday"),133                y=alt.Y("count:Q", title="Messages"),134            ).properties(height=180, title="Messages by weekday"),135            use_container_width=True,136        )137    else:138        st.bar_chart(by_dow.set_index("weekday")["count"], height=180)139 140    # Role breakdown141    with st.expander("Role breakdown"):142        role_counts = dff.groupby("role").size().reset_index(name="count").sort_values("count", ascending=False)143        if alt:144            st.altair_chart(145                alt.Chart(role_counts).mark_bar().encode(146                    x=alt.X("role:N", title="Role"),147                    y=alt.Y("count:Q", title="Messages"),148                ).properties(height=160, title="Messages by role"),149                use_container_width=True,150            )151        else:152            st.bar_chart(role_counts.set_index("role")["count"], height=160)153 154    st.markdown("---")155 156    # Top 10 senders per-day table157    st.markdown("### Top 10 Senders (with per-day counts)")158    pivot = (159        dff.assign(day=dff["ts_jst"].dt.strftime("%Y-%m-%d"))160        .pivot_table(index="sender", columns="day", values="text", aggfunc="count", fill_value=0)161    )162 163    top10 = pd.DataFrame()164    if pivot.empty:165        st.info("No senders in this window.")166    else:167        pivot["__Total"] = pivot.sum(axis=1)168        top10 = pivot.sort_values("__Total", ascending=False).head(10)169        cols = ["__Total"] + [c for c in top10.columns if c != "__Total"]170        st.dataframe(top10[cols], use_container_width=True, height=260)171 172    st.markdown("---")173 174    # Per-user breakdown175    st.markdown("### Per-user Breakdown")176    users_list = sorted(dff["sender"].unique())177    if not users_list:178        st.info("No users to analyze in this window.")179        return180 181    pick_sender = st.selectbox("Select a sender", options=users_list, index=0, key="stats_pick_sender")182    uid_sel = dff.loc[dff["sender"] == pick_sender, "user_id"].iloc[0]183    dfu = dff[dff["user_id"] == uid_sel].copy()184 185    total_msgs = dfu.shape[0]186    active_days = dfu["day"].nunique()187    lengths = dfu["text"].astype(str).map(len)188    words = dfu["text"].astype(str).map(lambda s: len(s.split()))189    median_gap = 0.0190    if total_msgs > 1:191        gaps = dfu.sort_values("ts_jst")["ts_jst"].diff().dropna().dt.total_seconds() / 60.0192        if not gaps.empty:193            median_gap = float(gaps.median())194 195    c1, c2, c3, c4, c5 = st.columns(5)196    c1.metric("Messages", f"{total_msgs}")197    c2.metric("Active days", f"{active_days}")198    c3.metric("Avg length (chars)", f"{float(lengths.mean()):.1f}" if total_msgs else "0.0")199    c4.metric("Avg words", f"{float(words.mean()):.1f}" if total_msgs else "0.0")200    c5.metric("Median gap (min)", f"{median_gap:.1f}")201 202    # Timeline203    freq_u = "H" if (end_jst - start_jst) <= pd.Timedelta(days=2) else "D"204    ser_u = dfu.set_index("ts_jst").resample(freq_u).size()205    ser_u_df = ser_u.rename_axis("ts_jst").reset_index(name="count")206    if not ser_u_df.empty:207        if alt:208            st.altair_chart(209                alt.Chart(ser_u_df).mark_line(point=True).encode(210                    x=alt.X("ts_jst:T", title="Time (JST)"),211                    y=alt.Y("count:Q", title="Messages"),212                ).properties(height=220, title=f"Messages over time — {pick_sender}"),213                use_container_width=True,214            )215        else:216            st.line_chart(ser_u_df.set_index("ts_jst")["count"], height=220)217 218    # Length histogram219    if not dfu.empty:220        if alt:221            hist = pd.DataFrame({"length": lengths})222            st.altair_chart(223                alt.Chart(hist).mark_bar().encode(224                    x=alt.X("length:Q", bin=alt.Bin(maxbins=30), title="Message length (chars)"),225                    y=alt.Y("count():Q", title="Messages"),226                ).properties(height=180, title="Message length distribution"),227                use_container_width=True,228            )229        else:230            st.bar_chart(lengths.value_counts().sort_index(), height=180)231 232    # Heatmap (weekday × hour)233    if alt and not dfu.empty:234        dfu_heat = dfu.groupby(["dow_name", "hour"]).size().reset_index(name="count")235        st.altair_chart(236            alt.Chart(dfu_heat).mark_rect().encode(237                x=alt.X("hour:O", title="Hour (JST)"),238                y=alt.Y("dow_name:O", sort=["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"], title="Weekday"),239                color=alt.Color("count:Q", title="Msgs", scale=alt.Scale(scheme="bluegreen")),240            ).properties(height=180, title="Activity heatmap"),241            use_container_width=True,242        )243 244    st.markdown("---")245 246    # Extra insights247    st.markdown("### Extra Insights")248    peak_hour = int(dff["hour"].mode().iloc[0]) if not dff["hour"].isna().all() else 0249    peak_dow = dff["dow_name"].mode().iloc[0] if not dff["dow_name"].isna().all() else "N/A"250    peak_hour_u = int(dfu["hour"].mode().iloc[0]) if not dfu["hour"].isna().all() else 0251    peak_dow_u = dfu["dow_name"].mode().iloc[0] if not dfu["dow_name"].isna().all() else "N/A"252 253    e1, e2, e3, e4 = st.columns(4)254    e1.metric("Global peak hour", f"{peak_hour}:00")255    e2.metric("Global peak weekday", peak_dow)256    e3.metric("User peak hour", f"{peak_hour_u}:00")257    e4.metric("User peak weekday", peak_dow_u)258 259    # Rolling 7-day sum260    ser_daily = dff.set_index("ts_jst").resample("D").size()261    ser_daily_df = ser_daily.rename_axis("ts_jst").reset_index(name="count")262    if not ser_daily_df.empty:263        ser_daily_df["rolling_7d"] = ser_daily_df["count"].rolling(7, min_periods=1).sum()264        if alt:265            bars = alt.Chart(ser_daily_df).mark_bar().encode(266                x=alt.X("ts_jst:T", title="Date (JST)"),267                y=alt.Y("count:Q", title="Daily messages"),268                tooltip=["ts_jst:T", "count:Q", "rolling_7d:Q"],269            ).properties(height=200, title="Daily messages & rolling 7-day sum")270            line = alt.Chart(ser_daily_df).mark_line(strokeDash=[4, 2]).encode(271                x="ts_jst:T",272                y=alt.Y("rolling_7d:Q", title="Rolling 7-day sum"),273            )274            st.altair_chart(bars + line, use_container_width=True)275        else:276            st.line_chart(ser_daily_df.set_index("ts_jst")[["count", "rolling_7d"]], height=200)277 278    # Exports279    st.markdown("#### Export")280    if isinstance(top10, pd.DataFrame) and not top10.empty:281        csv_sum = top10.reset_index().rename(columns={"sender": "User"})282        st.download_button(283            "⬇️ Download Top10 table (CSV)",284            data=csv_sum.to_csv(index=False),285            file_name="top10_senders.csv",286            mime="text/csv",287        )288    st.download_button(289        "⬇️ Download filtered messages (CSV)",290        data=dff.to_csv(index=False),291        file_name="messages_filtered.csv",292        mime="text/csv",293    )294