Spaces:

bigroll
/

adm

Runtime error

adm / app.py

prev perfect

1ff6d5a verified 9 months ago

12.7 kB

import streamlit as st
import pandas as pd
import plotly.express as px
import duckdb
import os

# --- Set page layout to wide ---
st.set_page_config(layout="wide")

# --- Language toggle ---
flag_style = """
    <style>
    .flag-container {
        display: flex;
        justify-content: center;
        gap: 10px;
    }
    .flag {
        cursor: pointer;
        width: 40px;
        height: 30px;
    }
    </style>
"""
st.markdown(flag_style, unsafe_allow_html=True)

flag_selection = st.radio(
    "",
    ["🇬🇧", "🇧🇬"],
    horizontal=True,
    label_visibility="collapsed"
)
LANG = "English" if flag_selection == "🇬🇧" else "Български"

T = {
    "English": {
        "title": "📊 Sentiment Analysis Dashboard",
        "select_entities": "Select Entities",
        "select_domains": "Select Domains (News Sites)",
        "score_type": "Select Score Type",
        "group_by_domain": "Group by Domain",
        "all_scores": "Show All Score Types",
        "no_data": "No data matches the selected filters.",
        "avg_over_time": "Average {} Over Time",
        "scores_over_time": "Sentiment Scores Over Time",
        "select_entities_prompt": "Please select at least one entity to view the graph."
    },
    "Български": {
        "title": "📊 Табло за анализ на настроенията",
        "select_entities": "Изберете обекти",
        "select_domains": "Изберете източници (сайтове)",
        "score_type": "Изберете тип оценка",
        "group_by_domain": "Групирай по сайт",
        "all_scores": "Покажи всички типове оценки",
        "no_data": "Няма данни за избраните филтри.",
        "avg_over_time": "Средна стойност на {} във времето",
        "scores_over_time": "Оценки на настроенията във времето",
        "select_entities_prompt": "Моля, изберете поне един обект, за да видите графиката."
    }
}

# --- Secrets ---
if "MOTHERDUCK_TOKEN" not in st.secrets:
    st.error("MOTHERDUCK_TOKEN not found in secrets.")
    st.stop()

MOTHERDUCK_TOKEN = st.secrets["MOTHERDUCK_TOKEN"]

# --- Data Fetching (MotherDuck) ---
@st.cache_data(ttl=300)
def get_data():
    try:
        # Connect to MotherDuck using the token
        con = duckdb.connect(f'md:?token={MOTHERDUCK_TOKEN}')
        
        # Query based on your schema
        query = """
            SELECT entity, entity_score, domain, title_score, overall_score, created_at
            FROM sentiment_analysis
        """
        
        df = con.sql(query).df()
        
        # 1. Convert to Datetime (handle numeric or string inputs)
        if pd.api.types.is_numeric_dtype(df["created_at"]):
             df["created_at"] = pd.to_datetime(df["created_at"], unit="s")
        else:
             df["created_at"] = pd.to_datetime(df["created_at"])
             
        # 2. FIX: Remove Timezone Information (Make it Naive)
        if pd.api.types.is_datetime64_any_dtype(df["created_at"]):
            if df["created_at"].dt.tz is not None:
                df["created_at"] = df["created_at"].dt.tz_localize(None)

        return df
    except Exception as e:
        st.error(f"Failed to fetch data: {e}")
        return pd.DataFrame()

df = get_data()
if df.empty:
    st.warning("No data available.")
    st.stop()

# --- UI ---
st.title(T[LANG]["title"])

# --- Sidebar for filters ---
with st.sidebar:
    # Entity dropdown with counts
    entity_counts = df["entity"].value_counts()
    entity_labels = [f"{ent} ({count})" for ent, count in entity_counts.items()]
    entity_lookup = {f"{ent} ({count})": ent for ent, count in entity_counts.items()}
    selected_labels = st.multiselect(T[LANG]["select_entities"], entity_labels)
    entities = [entity_lookup[label] for label in selected_labels]

    # Domain dropdown with counts
    domain_counts = df["domain"].value_counts()
    domain_labels = [f"{dom} ({count})" for dom, count in domain_counts.items()]
    domain_lookup = {f"{dom} ({count})": dom for dom, count in domain_counts.items()}
    selected_domain_labels = st.multiselect(T[LANG]["select_domains"], domain_labels)
    domains = [domain_lookup[label] for label in selected_domain_labels]

    # Score type and grouping options
    score_type = st.selectbox(T[LANG]["score_type"], ["entity_score", "title_score", "overall_score"])
    group_by_domain = st.checkbox(T[LANG]["group_by_domain"])
    group_by_score_type = st.checkbox(T[LANG]["all_scores"])

    # Timeframe selection
    timeframes = ["All", "Last 7 Days", "Last 30 Days", "Last Year", "Custom"]
    selected_timeframe = st.selectbox("Select Timeframe", timeframes)

    # Custom date range selection
    start_date, end_date = None, None
    if selected_timeframe == "Custom":
        col1, col2 = st.columns(2)
        with col1:
            start_date = st.date_input("Start Date")
        with col2:
            end_date = st.date_input("End Date")
        if start_date > end_date:
            st.error("Start date must be before or equal to the end date.")
            st.stop()

# --- Filters ---
filtered_df = df.copy()
if entities:
    filtered_df = filtered_df[filtered_df["entity"].isin(entities)]
if domains:
    filtered_df = filtered_df[filtered_df["domain"].isin(domains)]

if selected_timeframe != "All":
    now = pd.Timestamp.now()
    if selected_timeframe == "Last 7 Days":
        start_date = now - pd.Timedelta(days=7)
        end_date = now
    elif selected_timeframe == "Last 30 Days":
        start_date = now - pd.Timedelta(days=30)
        end_date = now
    elif selected_timeframe == "Last Year":
        start_date = now - pd.Timedelta(days=365)
        end_date = now
    
    if start_date and end_date:
        filtered_df = filtered_df[
            (filtered_df["created_at"] >= pd.Timestamp(start_date)) &
            (filtered_df["created_at"] <= pd.Timestamp(end_date))
        ]

# --- Stop early if no entity selected ---
if not entities:
    st.info(T[LANG]["select_entities_prompt"])
    st.stop()

# --- Continue only if data exists ---
if not filtered_df.empty:
    
    # --- LEGEND SORTING LOGIC ---
    # We calculate the average "entity_score" for each entity/domain combo.
    # This ranking is used to sort the legend, regardless of what score is currently plotted.
    rank_group_cols = ["entity"]
    if group_by_domain:
        rank_group_cols.append("domain")
    
    # Calculate average entity_score for ranking
    rank_df = filtered_df.groupby(rank_group_cols)["entity_score"].mean().reset_index()
    # Create a dictionary for mapping: key -> score
    rank_lookup = {
        tuple(row[col] for col in rank_group_cols): row["entity_score"]
        for _, row in rank_df.iterrows()
    }

    # --- SMART DATE BINNING (Max ~20 Dots) ---
    min_date = filtered_df["created_at"].min()
    max_date = filtered_df["created_at"].max()
    days_span = (max_date - min_date).days + 1
    
    if days_span <= 20:
        freq = "D"
    else:
        step = int(days_span / 20)
        step = max(1, step)
        freq = f"{step}D"
        
    filtered_df["date"] = filtered_df["created_at"].dt.floor(freq)
    # --- END BINNING ---

    if group_by_score_type:
        score_cols = ["entity_score", "title_score", "overall_score"]
        melted = filtered_df.melt(
            id_vars=["date", "entity", "domain"],
            value_vars=score_cols,
            var_name="score_type",
            value_name="score"
        )

        group_cols = ["date", "entity"]
        if group_by_domain:
            group_cols.append("domain")
        group_cols.append("score_type")

        grouped = (
            melted.groupby(group_cols)["score"]
            .mean()
            .reset_index()
        )
        
        # FIX: Round scores to 2 decimals for the hover tooltip
        grouped["score"] = grouped["score"].round(2)

        stats_group = ["entity", "score_type"]
        if group_by_domain:
            stats_group.append("domain")

        stats = (
            melted.groupby(stats_group)["score"]
            .agg(["mean", "count"])
            .round(2)
            .reset_index()
        )

        stat_lookup = {
            tuple(row[col] for col in stats_group): f"{row['mean']:.2f}, n={int(row['count'])}"
            for _, row in stats.iterrows()
        }

        def build_label(row):
            key = tuple(row[col] for col in stats_group)
            label = f"{row['entity']}"
            if group_by_domain:
                label += f" | {row['domain']}"
            label += f" | {row['score_type'].replace('_', ' ').title()}"
            label += f" (avg: {stat_lookup.get(key, '')})"
            return label

        grouped["label"] = grouped.apply(build_label, axis=1)

        # Apply Sorting: Get rank score based on entity/domain
        def get_rank_score(row):
            key_cols = [row["entity"]]
            if group_by_domain:
                key_cols.append(row["domain"])
            return rank_lookup.get(tuple(key_cols), -999) # Default to low if missing

        grouped["rank_score"] = grouped.apply(get_rank_score, axis=1)
        
        # Sort by Rank (Entity Score), then Score Type, then Date
        grouped = grouped.sort_values(by=["rank_score", "label", "date"], ascending=[False, True, True])

        fig = px.line(
            grouped,
            x="date",
            y="score",
            color="label",
            labels={"date": "Date", "score": "Score", "label": "Legend"},
            title=T[LANG]["scores_over_time"]
        )
        fig.update_traces(mode="lines+markers")

    else:
        group_cols = ["date", "entity"]
        if group_by_domain:
            group_cols.append("domain")

        grouped = (
            filtered_df.groupby(group_cols)[score_type]
            .mean()
            .reset_index()
        )

        # FIX: Round scores to 2 decimals for the hover tooltip
        grouped[score_type] = grouped[score_type].round(2)

        stats_group = ["entity"]
        if group_by_domain:
            stats_group.append("domain")
        stats = (
            filtered_df.groupby(stats_group)[score_type]
            .agg(["mean", "count"])
            .round(2)
            .reset_index()
        )

        stat_lookup = {
            tuple(row[col] for col in stats_group): f"{row['mean']:.2f}, n={int(row['count'])}"
            for _, row in stats.iterrows()
        }

        def build_label(row):
            key = tuple(row[col] for col in stats_group)
            label = f"{row['entity']}"
            if group_by_domain:
                label += f" | {row['domain']}"
            label += f" (avg: {stat_lookup.get(key, '')})"
            return label

        grouped["label"] = grouped.apply(build_label, axis=1)
        
        # Apply Sorting: Get rank score based on entity/domain
        def get_rank_score(row):
            key_cols = [row["entity"]]
            if group_by_domain:
                key_cols.append(row["domain"])
            return rank_lookup.get(tuple(key_cols), -999)

        grouped["rank_score"] = grouped.apply(get_rank_score, axis=1)
        
        # Sort by Rank (Entity Score), then Date
        grouped = grouped.sort_values(by=["rank_score", "label", "date"], ascending=[False, True, True])

        fig = px.line(
            grouped,
            x="date",
            y=score_type,
            color="label",
            labels={"date": "Date", score_type: "Score", "label": "Legend"},
            title=T[LANG]["avg_over_time"].format(score_type.replace("_", " ").title())
        )
        fig.update_traces(mode="lines+markers")

    # Grid lines at every integer, thicker at -5 and 5
    shapes = []
    for y in range(-10, 11):
        shapes.append({
            "type": "line",
            "xref": "paper",
            "x0": 0,
            "x1": 1,
            "yref": "y",
            "y0": y,
            "y1": y,
            "line": {
                "color": "Black",
                "width": 1 if y in [-5, 5] else 0.2,
                "dash": "solid"
            }
        })

    fig.update_layout(
        xaxis=dict(type="date"),
        yaxis=dict(range=[-10, 10]),
        plot_bgcolor="white",
        shapes=shapes,
        legend=dict(
            orientation="h",
            yanchor="top",
            y=-0.3,
            xanchor="center",
            x=0.5,
            title="",
            traceorder="normal"
        )
    )

    st.plotly_chart(fig, use_container_width=True)
else:
    st.warning(T[LANG]["no_data"])

	import streamlit as st
	import pandas as pd
	import plotly.express as px
	import duckdb
	import os

	# --- Set page layout to wide ---
	st.set_page_config(layout="wide")

	# --- Language toggle ---
	flag_style = """
	<style>
	.flag-container {
	display: flex;
	justify-content: center;
	gap: 10px;
	}
	.flag {
	cursor: pointer;
	width: 40px;
	height: 30px;
	}
	</style>
	"""
	st.markdown(flag_style, unsafe_allow_html=True)

	flag_selection = st.radio(
	"",
	["🇬🇧", "🇧🇬"],
	horizontal=True,
	label_visibility="collapsed"
	)
	LANG = "English" if flag_selection == "🇬🇧" else "Български"

	T = {
	"English": {
	"title": "📊 Sentiment Analysis Dashboard",
	"select_entities": "Select Entities",
	"select_domains": "Select Domains (News Sites)",
	"score_type": "Select Score Type",
	"group_by_domain": "Group by Domain",
	"all_scores": "Show All Score Types",
	"no_data": "No data matches the selected filters.",
	"avg_over_time": "Average {} Over Time",
	"scores_over_time": "Sentiment Scores Over Time",
	"select_entities_prompt": "Please select at least one entity to view the graph."
	},
	"Български": {
	"title": "📊 Табло за анализ на настроенията",
	"select_entities": "Изберете обекти",
	"select_domains": "Изберете източници (сайтове)",
	"score_type": "Изберете тип оценка",
	"group_by_domain": "Групирай по сайт",
	"all_scores": "Покажи всички типове оценки",
	"no_data": "Няма данни за избраните филтри.",
	"avg_over_time": "Средна стойност на {} във времето",
	"scores_over_time": "Оценки на настроенията във времето",
	"select_entities_prompt": "Моля, изберете поне един обект, за да видите графиката."
	}
	}

	# --- Secrets ---
	if "MOTHERDUCK_TOKEN" not in st.secrets:
	st.error("MOTHERDUCK_TOKEN not found in secrets.")
	st.stop()

	MOTHERDUCK_TOKEN = st.secrets["MOTHERDUCK_TOKEN"]

	# --- Data Fetching (MotherDuck) ---
	@st.cache_data(ttl=300)
	def get_data():
	try:
	# Connect to MotherDuck using the token
	con = duckdb.connect(f'md:?token={MOTHERDUCK_TOKEN}')

	# Query based on your schema
	query = """
	SELECT entity, entity_score, domain, title_score, overall_score, created_at
	FROM sentiment_analysis
	"""

	df = con.sql(query).df()

	# 1. Convert to Datetime (handle numeric or string inputs)
	if pd.api.types.is_numeric_dtype(df["created_at"]):
	df["created_at"] = pd.to_datetime(df["created_at"], unit="s")
	else:
	df["created_at"] = pd.to_datetime(df["created_at"])

	# 2. FIX: Remove Timezone Information (Make it Naive)
	if pd.api.types.is_datetime64_any_dtype(df["created_at"]):
	if df["created_at"].dt.tz is not None:
	df["created_at"] = df["created_at"].dt.tz_localize(None)

	return df
	except Exception as e:
	st.error(f"Failed to fetch data: {e}")
	return pd.DataFrame()

	df = get_data()
	if df.empty:
	st.warning("No data available.")
	st.stop()

	# --- UI ---
	st.title(T[LANG]["title"])

	# --- Sidebar for filters ---
	with st.sidebar:
	# Entity dropdown with counts
	entity_counts = df["entity"].value_counts()
	entity_labels = [f"{ent} ({count})" for ent, count in entity_counts.items()]
	entity_lookup = {f"{ent} ({count})": ent for ent, count in entity_counts.items()}
	selected_labels = st.multiselect(T[LANG]["select_entities"], entity_labels)
	entities = [entity_lookup[label] for label in selected_labels]

	# Domain dropdown with counts
	domain_counts = df["domain"].value_counts()
	domain_labels = [f"{dom} ({count})" for dom, count in domain_counts.items()]
	domain_lookup = {f"{dom} ({count})": dom for dom, count in domain_counts.items()}
	selected_domain_labels = st.multiselect(T[LANG]["select_domains"], domain_labels)
	domains = [domain_lookup[label] for label in selected_domain_labels]

	# Score type and grouping options
	score_type = st.selectbox(T[LANG]["score_type"], ["entity_score", "title_score", "overall_score"])
	group_by_domain = st.checkbox(T[LANG]["group_by_domain"])
	group_by_score_type = st.checkbox(T[LANG]["all_scores"])

	# Timeframe selection
	timeframes = ["All", "Last 7 Days", "Last 30 Days", "Last Year", "Custom"]
	selected_timeframe = st.selectbox("Select Timeframe", timeframes)

	# Custom date range selection
	start_date, end_date = None, None
	if selected_timeframe == "Custom":
	col1, col2 = st.columns(2)
	with col1:
	start_date = st.date_input("Start Date")
	with col2:
	end_date = st.date_input("End Date")
	if start_date > end_date:
	st.error("Start date must be before or equal to the end date.")
	st.stop()

	# --- Filters ---
	filtered_df = df.copy()
	if entities:
	filtered_df = filtered_df[filtered_df["entity"].isin(entities)]
	if domains:
	filtered_df = filtered_df[filtered_df["domain"].isin(domains)]

	if selected_timeframe != "All":
	now = pd.Timestamp.now()
	if selected_timeframe == "Last 7 Days":
	start_date = now - pd.Timedelta(days=7)
	end_date = now
	elif selected_timeframe == "Last 30 Days":
	start_date = now - pd.Timedelta(days=30)
	end_date = now
	elif selected_timeframe == "Last Year":
	start_date = now - pd.Timedelta(days=365)
	end_date = now

	if start_date and end_date:
	filtered_df = filtered_df[
	(filtered_df["created_at"] >= pd.Timestamp(start_date)) &
	(filtered_df["created_at"] <= pd.Timestamp(end_date))
	]

	# --- Stop early if no entity selected ---
	if not entities:
	st.info(T[LANG]["select_entities_prompt"])
	st.stop()

	# --- Continue only if data exists ---
	if not filtered_df.empty:

	# --- LEGEND SORTING LOGIC ---
	# We calculate the average "entity_score" for each entity/domain combo.
	# This ranking is used to sort the legend, regardless of what score is currently plotted.
	rank_group_cols = ["entity"]
	if group_by_domain:
	rank_group_cols.append("domain")

	# Calculate average entity_score for ranking
	rank_df = filtered_df.groupby(rank_group_cols)["entity_score"].mean().reset_index()
	# Create a dictionary for mapping: key -> score
	rank_lookup = {
	tuple(row[col] for col in rank_group_cols): row["entity_score"]
	for _, row in rank_df.iterrows()
	}

	# --- SMART DATE BINNING (Max ~20 Dots) ---
	min_date = filtered_df["created_at"].min()
	max_date = filtered_df["created_at"].max()
	days_span = (max_date - min_date).days + 1

	if days_span <= 20:
	freq = "D"
	else:
	step = int(days_span / 20)
	step = max(1, step)
	freq = f"{step}D"

	filtered_df["date"] = filtered_df["created_at"].dt.floor(freq)
	# --- END BINNING ---

	if group_by_score_type:
	score_cols = ["entity_score", "title_score", "overall_score"]
	melted = filtered_df.melt(
	id_vars=["date", "entity", "domain"],
	value_vars=score_cols,
	var_name="score_type",
	value_name="score"
	)

	group_cols = ["date", "entity"]
	if group_by_domain:
	group_cols.append("domain")
	group_cols.append("score_type")

	grouped = (
	melted.groupby(group_cols)["score"]
	.mean()
	.reset_index()
	)

	# FIX: Round scores to 2 decimals for the hover tooltip
	grouped["score"] = grouped["score"].round(2)

	stats_group = ["entity", "score_type"]
	if group_by_domain:
	stats_group.append("domain")

	stats = (
	melted.groupby(stats_group)["score"]
	.agg(["mean", "count"])
	.round(2)
	.reset_index()
	)

	stat_lookup = {
	tuple(row[col] for col in stats_group): f"{row['mean']:.2f}, n={int(row['count'])}"
	for _, row in stats.iterrows()
	}

	def build_label(row):
	key = tuple(row[col] for col in stats_group)
	label = f"{row['entity']}"
	if group_by_domain:
	label += f" \| {row['domain']}"
	label += f" \| {row['score_type'].replace('_', ' ').title()}"
	label += f" (avg: {stat_lookup.get(key, '')})"
	return label

	grouped["label"] = grouped.apply(build_label, axis=1)

	# Apply Sorting: Get rank score based on entity/domain
	def get_rank_score(row):
	key_cols = [row["entity"]]
	if group_by_domain:
	key_cols.append(row["domain"])
	return rank_lookup.get(tuple(key_cols), -999) # Default to low if missing

	grouped["rank_score"] = grouped.apply(get_rank_score, axis=1)

	# Sort by Rank (Entity Score), then Score Type, then Date
	grouped = grouped.sort_values(by=["rank_score", "label", "date"], ascending=[False, True, True])

	fig = px.line(
	grouped,
	x="date",
	y="score",
	color="label",
	labels={"date": "Date", "score": "Score", "label": "Legend"},
	title=T[LANG]["scores_over_time"]
	)
	fig.update_traces(mode="lines+markers")

	else:
	group_cols = ["date", "entity"]
	if group_by_domain:
	group_cols.append("domain")

	grouped = (
	filtered_df.groupby(group_cols)[score_type]
	.mean()
	.reset_index()
	)

	# FIX: Round scores to 2 decimals for the hover tooltip
	grouped[score_type] = grouped[score_type].round(2)

	stats_group = ["entity"]
	if group_by_domain:
	stats_group.append("domain")
	stats = (
	filtered_df.groupby(stats_group)[score_type]
	.agg(["mean", "count"])
	.round(2)
	.reset_index()
	)

	stat_lookup = {
	tuple(row[col] for col in stats_group): f"{row['mean']:.2f}, n={int(row['count'])}"
	for _, row in stats.iterrows()
	}

	def build_label(row):
	key = tuple(row[col] for col in stats_group)
	label = f"{row['entity']}"
	if group_by_domain:
	label += f" \| {row['domain']}"
	label += f" (avg: {stat_lookup.get(key, '')})"
	return label

	grouped["label"] = grouped.apply(build_label, axis=1)

	# Apply Sorting: Get rank score based on entity/domain
	def get_rank_score(row):
	key_cols = [row["entity"]]
	if group_by_domain:
	key_cols.append(row["domain"])
	return rank_lookup.get(tuple(key_cols), -999)

	grouped["rank_score"] = grouped.apply(get_rank_score, axis=1)

	# Sort by Rank (Entity Score), then Date
	grouped = grouped.sort_values(by=["rank_score", "label", "date"], ascending=[False, True, True])

	fig = px.line(
	grouped,
	x="date",
	y=score_type,
	color="label",
	labels={"date": "Date", score_type: "Score", "label": "Legend"},
	title=T[LANG]["avg_over_time"].format(score_type.replace("_", " ").title())
	)
	fig.update_traces(mode="lines+markers")

	# Grid lines at every integer, thicker at -5 and 5
	shapes = []
	for y in range(-10, 11):
	shapes.append({
	"type": "line",
	"xref": "paper",
	"x0": 0,
	"x1": 1,
	"yref": "y",
	"y0": y,
	"y1": y,
	"line": {
	"color": "Black",
	"width": 1 if y in [-5, 5] else 0.2,
	"dash": "solid"
	}
	})

	fig.update_layout(
	xaxis=dict(type="date"),
	yaxis=dict(range=[-10, 10]),
	plot_bgcolor="white",
	shapes=shapes,
	legend=dict(
	orientation="h",
	yanchor="top",
	y=-0.3,
	xanchor="center",
	x=0.5,
	title="",
	traceorder="normal"
	)
	)

	st.plotly_chart(fig, use_container_width=True)
	else:
	st.warning(T[LANG]["no_data"])