Automated Web Scraping and Data Visualization with Python and AI — Part 7: Visualizing Scraped Data with Interactive Dashboards

ComponentVersion / Note


Python3.11+
Streamlit1.38.0
Plotly5.22.0
SQLAlchemy2.0.31
psycopg2‑binary2.9.9
anthropic0.5.3 (Claude 4.6 Opus)
openai1.45.0 (GPT‑5.4 Pro)
openaigen0.2.1 (parallel agents)

CREATE TABLE product_prices (
    id              SERIAL PRIMARY KEY,
    product_id      TEXT NOT NULL,
    product_name    TEXT NOT NULL,
    category        TEXT,
    price_usd       NUMERIC(12,2),
    scraped_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    source_url      TEXT,
    raw_json        JSONB
);

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def get_engine():
    db_url = os.getenv(
        "DATABASE_URL",
        "postgresql+psycopg2://scraper_user:password@localhost:5432/scraper_db"
    )
    return create_engine(db_url, pool_pre_ping=True)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=get_engine())

import pandas as pd
import streamlit as st
from sqlalchemy import text

@st.cache_data(ttl=300)  # refresh every 5 minutes
def load_price_history(product_id: str) -> pd.DataFrame:
    query = text(
        """
        SELECT product_name, price_usd, scraped_at, source_url
        FROM product_prices
        WHERE product_id = :pid
          AND scraped_at >= NOW() - INTERVAL '30 days'
        ORDER BY scraped_at ASC
        """
    )
    with SessionLocal() as session:
        df = pd.read_sql_query(query, session.connection(), params={"pid": product_id})
    df["scraped_at"] = pd.to_datetime(df["scraped_at"])
    return df

import plotly.express as px

def price_trend_chart(df: pd.DataFrame):
    fig = px.line(
        df,
        x="scraped_at",
        y="price_usd",
        title=f"Price Trend for {df['product_name'].iloc[0]}",
        hover_data=["source_url"]
    )
    fig.update_layout(hovermode="x unified")
    return fig

def geo_price_chart(df: pd.DataFrame):
    # Extract state from URL assuming pattern ...?state=CA
    df["state"] = df["source_url"].str.extract(r"state=([A-Z]{2})")
    agg = df.groupby("state")["price_usd"].mean().reset_index()
    fig = px.choropleth(
        agg,
        locations="state",
        locationmode="USA-states",
        color="price_usd",
        color_continuous_scale="Viridis",
        scope="usa",
        title="Average Price by State (Last 30 Days)"
    )
    return fig

def correlation_heatmap(df: pd.DataFrame):
    # Assume df may have extra columns like 'rating' and 'discount_pct'
    numeric_cols = df.select_dtypes(include="number")
    if numeric_cols.shape[1]  str:
    prompt = f"""
    You are a data analyst.  Given the JSON payload below that contains
    product price history for the last 30 days, extract:
    1. The highest price and when it occurred.
    2. The lowest price and when it occurred.
    3. Any price spikes greater than 15 % within a 24‑hour window.
    Return a concise JSON object with keys: highest, lowest, spikes.
    ----
    {df_json}
    """
    response = claude_client.messages.create(
        model="claude-4.6-opus",
        max_tokens=500,
        temperature=0.0,
        messages=[{"role": "user", "content": prompt}]
    )
    return response.content[0].text

@parallel
def craft_narrative(insights_json: str) -> str:
    prompt = f"""
    Using the insights JSON below, write a 150‑word executive summary.
    Highlight any pricing anomalies, suggest possible causes (e.g., promotions,
    supply constraints), and recommend a next step for a product manager.
    Keep the tone professional but engaging.
    ----
    {insights_json}
    """
    response = openai_client.chat.completions.create(
        model="gpt-5.4-pro",
        messages=[{"role": "user", "content": prompt}],
        temperature=0.7,
        max_tokens=300
    )
    return response.choices[0].message.content

import streamlit as st
import pandas as pd
import json

# Import helpers from previous sections
from db import load_price_history
from visuals import price_trend_chart, geo_price_chart, correlation_heatmap
from ai_storytelling import extract_insights, craft_narrative

st.set_page_config(page_title="Product Price Dashboard", layout="wide")

st.title("📈 AI‑Enhanced Product Price Dashboard")
st.caption("Based on my technical understanding as a Lead Programmer Analyst, this app demonstrates how to turn scraped data into interactive visualizations and AI‑driven narratives.")

# Sidebar – product selection
product_id = st.sidebar.text_input("Enter Product ID", value="B07XYZ1234")
if not product_id:
    st.stop()

# Load data
df = load_price_history(product_id)

if df.empty:
    st.warning("No data found for the supplied product ID.")
    st.stop()

# Layout
col1, col2 = st.columns([2, 1])

with col1:
    st.subheader("Price Trend")
    st.plotly_chart(price_trend_chart(df), use_container_width=True)

    st.subheader("Geographic Distribution")
    st.plotly_chart(geo_price_chart(df), use_container_width=True)

    st.subheader("Correlation Heatmap")
    heatmap = correlation_heatmap(df)
    if heatmap:
        st.plotly_chart(heatmap, use_container_width=True)

with col2:
    st.subheader("AI‑Generated Story")
    # Convert DataFrame to JSON for the LLM
    df_json = df.to_json(orient="records", date_format="iso")
    # Run both agents in parallel
    insights_json, narrative = extract_insights(df_json), craft_narrative(insights_json=None)  # placeholder
    # The parallel decorator returns a tuple; we unpack it correctly:
    insights_json, narrative = extract_insights(df_json), None  # placeholder for demo
    # Real execution:
    insights_json, narrative = extract_insights(df_json), craft_narrative(insights_json)
    # Display results
    st.json(json.loads(insights_json))
    st.markdown(narrative)

st.sidebar.markdown("---")
st.sidebar.info(
    "💡 Tip: Adjust the date range or add custom filters using the sidebar to explore other time windows."
)

# Add after data load
date_range = st.sidebar.slider(
    "Select date range (days ago)",
    min_value=1,
    max_value=30,
    value=30,
    step=1
)
cutoff = pd.Timestamp.now(tz="UTC") - pd.Timedelta(days=date_range)
filtered_df = df[df["scraped_at"] >= cutoff]

# Use filtered_df in all visual calls
st.plotly_chart(price_trend_chart(filtered_df))
# ... etc.

# ---- Build Stage ----
FROM python:3.11-slim AS builder
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# ---- Runtime Stage ----
FROM python:3.11-slim
WORKDIR /app
COPY --from=builder /usr/local/lib/python3.11/site-packages /usr/local/lib/python3.11/site-packages
COPY . .
RUN useradd -m streamlit && chown -R streamlit:streamlit /app
USER streamlit
EXPOSE 8501
CMD ["streamlit", "run", "app.py", "--server.port=8501", "--server.headless=true"]