07 — Plotly dashboards

Duration: 15 min Prerequisites: chapter 05 (data loaded in SQLite), pip install plotly streamlit.

The four figures

The Dashboards tab shows four KPIs and four charts:

#	Figure	Question it answers
1	Bar — Spending by month	Where are my high-spend months?
2	Pie — Breakdown by category	What dominates my budget?
3	Horizontal bar — Top 10 merchants	Who do I pay the most?
4	Bar — Recurring merchants (≥ 3 active months)	Who do I pay every month?

csv-llm-shared/dashboards.py

import pandas as pd
import plotly.express as px
import plotly.graph_objects as go


def kpis(df: pd.DataFrame) -> dict:
    expenses = df[df["amount"] > 0]
    months = expenses["date"].str[:7].nunique() or 1
    return {
        "total":     expenses["amount"].sum(),
        "monthly":   expenses["amount"].sum() / months,
        "avg_tx":    expenses["amount"].mean(),
        "n_tx":      int((df["amount"] > 0).sum()),
    }


def fig_monthly(df: pd.DataFrame) -> go.Figure:
    expenses = df[df["amount"] > 0].copy()
    expenses["month"] = expenses["date"].str[:7]
    grouped = expenses.groupby("month", as_index=False)["amount"].sum()
    fig = px.bar(grouped, x="month", y="amount",
                 title="Spending by month",
                 labels={"month": "Month", "amount": "Total ($)"})
    fig.update_layout(bargap=0.2)
    return fig


def fig_categories(df: pd.DataFrame) -> go.Figure:
    expenses = df[df["amount"] > 0]
    grouped = expenses.groupby("category", as_index=False)["amount"].sum()
    return px.pie(grouped, names="category", values="amount",
                  title="Spending by category", hole=0.3)


def fig_top_merchants(df: pd.DataFrame, n: int = 10) -> go.Figure:
    expenses = df[df["amount"] > 0]
    top = (expenses.groupby("description", as_index=False)["amount"]
           .sum()
           .sort_values("amount", ascending=False)
           .head(n))
    return px.bar(top, x="amount", y="description", orientation="h",
                  title=f"Top {n} merchants",
                  labels={"amount": "Total ($)", "description": "Merchant"})


def fig_recurring(df: pd.DataFrame, min_months: int = 3) -> go.Figure:
    expenses = df[df["amount"] > 0].copy()
    expenses["month"] = expenses["date"].str[:7]
    by_merchant = (expenses.groupby("description")["month"]
                   .nunique()
                   .reset_index(name="active_months"))
    recurring = (by_merchant[by_merchant["active_months"] >= min_months]
                 .sort_values("active_months", ascending=False)
                 .head(20))
    return px.bar(recurring, x="active_months", y="description", orientation="h",
                  title=f"Recurring merchants (≥{min_months} active months)")

Wiring into Streamlit (csv-llm-ollama/app.py)

import streamlit as st
import pandas as pd
import sys
sys.path.insert(0, "../csv-llm-shared")
import dashboards

# df is already loaded from SQLite (see chapter 05)

k = dashboards.kpis(df)
c1, c2, c3, c4 = st.columns(4)
c1.metric("Total",            f"${k['total']:,.2f}")
c2.metric("Monthly average",  f"${k['monthly']:,.2f}")
c3.metric("Avg per tx",       f"${k['avg_tx']:,.2f}")
c4.metric("# expense tx",     k["n_tx"])

st.plotly_chart(dashboards.fig_monthly(df),       use_container_width=True)
st.plotly_chart(dashboards.fig_categories(df),    use_container_width=True)
st.plotly_chart(dashboards.fig_top_merchants(df), use_container_width=True)
st.plotly_chart(dashboards.fig_recurring(df),     use_container_width=True)

Filters above the charts

all_cats = sorted(df["category"].unique())
selected = st.multiselect("Filter by category:", all_cats, default=all_cats)

date_min, date_max = df["date"].min(), df["date"].max()
range_ = st.date_input("Date range:",
                       value=(pd.to_datetime(date_min), pd.to_datetime(date_max)))

filtered = df[df["category"].isin(selected)]
filtered = filtered[(filtered["date"] >= str(range_[0])) &
                    (filtered["date"] <= str(range_[1]))]

All figures use the filtered DataFrame.

Expected KPIs on the demo CSV

When you load data1-anonymized.csv without filters:

KPI	Value
Total	$118,667.69
Monthly average	$9,888.97 (12 months)
Avg per transaction	$175.80
# expense transactions	675

And the monthly bar chart shows a clear November peak ($16,230) from a few big Housing and Travel hits.

A note on Plotly choices

Choice	Why
px.pie(... hole=0.3)	Doughnut > pure pie. Same data, easier to read.
orientation="h" for merchants	Merchant names are long. Horizontal bars don’t truncate.
use_container_width=True	Streamlit handles responsive sizing for you.
temperature not used	These are pure pandas/plotly. No LLM in this chapter.

Takeaways

• 4 figures cover 90% of what you’d want from a budgeting app.
• The functions in dashboards.py take a DataFrame and return go.Figure — pure, easy to test.
• The Streamlit wiring is ~30 lines. KPIs, filters, charts, done.
• November shows a clear spending spike — would you have noticed it without the chart?

Next: LLM agent with SQL tool →