import os from pathlib import Path import sqlite3 import pandas as pd import streamlit as st import io # ---------- Config & helpers ---------- PARQUET_BASE = Path(os.getenv("EDA_PARQUET_DIR", "data/interim/interim_copy")) # initial default (overridable below) DB_PATH = os.getenv("EDA_DB_PATH", os.getenv("DB_PATH", "/data/olist.sqlite")) def _fallback_date_bounds(): # Olist public dataset range return pd.Timestamp("2016-01-01").date(), pd.Timestamp("2018-12-31").date() def df_to_csv_bytes(df: pd.DataFrame) -> bytes: buf = io.StringIO() df.to_csv(buf, index=False) return buf.getvalue().encode("utf-8") def _data_source_badge(used_parquet: bool, db_path: str): src = "parquet" if used_parquet else (db_path if db_path else "unknown") st.caption(f"Data source: **{src}**") DB_PATH = os.getenv("EDA_DB_PATH") or os.getenv("DB_PATH") or DB_PATH def has_table(conn, name: str) -> bool: try: q = "SELECT name FROM sqlite_master WHERE type='table' AND lower(name)=lower(?) LIMIT 1" return pd.read_sql_query(q, conn, params=[name]).shape[0] > 0 except Exception: return False def sql_df(conn, query: str, params=None) -> pd.DataFrame: try: return pd.read_sql_query(query, conn, params=params or []) except Exception: return pd.DataFrame() # ---------- Builders ---------- def build_summaries_from_db(db_path: str) -> dict: conn = sqlite3.connect(db_path, timeout=30) conn.row_factory = sqlite3.Row T_ORDERS = "orders" T_ITEMS = "order_items" T_REVIEWS = "order_reviews" T_PAY = "order_payments" T_PRODUCTS = "products" T_CUSTOMERS = "customers" T_CAT_TRANS = "product_category_name_translation" # orders summary if has_table(conn, T_ORDERS): orders_summary = sql_df(conn, """ SELECT order_status, COUNT(*) AS count FROM orders GROUP BY order_status ORDER BY count DESC """) orders_monthly = sql_df(conn, """ SELECT date(strftime('%Y-%m-01', order_purchase_timestamp)) AS month, COUNT(*) AS count FROM orders WHERE order_purchase_timestamp IS NOT NULL GROUP BY date(strftime('%Y-%m-01', order_purchase_timestamp)) ORDER BY month """) delivery = sql_df(conn, """ SELECT AVG( julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp) ) AS avg_delivery_days FROM orders WHERE order_delivered_customer_date IS NOT NULL AND order_purchase_timestamp IS NOT NULL """) delivery_hist_raw = sql_df(conn, """ SELECT CAST(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp) AS INTEGER) AS days FROM orders WHERE order_delivered_customer_date IS NOT NULL AND order_purchase_timestamp IS NOT NULL AND (julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp)) BETWEEN -1 AND 60 """) if not delivery_hist_raw.empty: bins = pd.IntervalIndex.from_breaks(range(-1, 62, 1)) s = pd.cut(delivery_hist_raw["days"], bins=bins) delivery_hist = s.value_counts().sort_index().reset_index() delivery_hist.columns = ["bin", "count"] delivery_hist["bin_days"] = delivery_hist["bin"].astype(str) else: delivery_hist = pd.DataFrame(columns=["bin_days","count"]) else: orders_summary = pd.DataFrame(columns=["order_status","count"]) orders_monthly = pd.DataFrame(columns=["month","count"]) delivery = pd.DataFrame([{"avg_delivery_days": None}]) delivery_hist = pd.DataFrame(columns=["bin_days","count"]) # payments if has_table(conn, T_PAY): payments = sql_df(conn, """ SELECT payment_type, COUNT(*) AS count FROM order_payments GROUP BY payment_type ORDER BY count DESC """) else: payments = pd.DataFrame(columns=["payment_type","count"]) # reviews if has_table(conn, T_REVIEWS): reviews = sql_df(conn, """ SELECT review_score AS review_score, COUNT(*) AS count FROM order_reviews GROUP BY review_score ORDER BY review_score """) review_text_stats = sql_df(conn, """ SELECT AVG(LENGTH(COALESCE(review_comment_message,'')) - LENGTH(REPLACE(COALESCE(review_comment_message,''),' ','')) + 1) AS avg_len_words, SUM(CASE WHEN LENGTH(COALESCE(review_comment_message,'')) > 0 THEN 1 ELSE 0 END) AS n_texts, AVG(review_score) AS avg_review_score FROM order_reviews """) texts = sql_df(conn, "SELECT review_comment_message FROM order_reviews WHERE review_comment_message IS NOT NULL LIMIT 5000") if not texts.empty: terms = ( texts["review_comment_message"] .astype(str) .str.lower() .str.replace(r"[^a-z0-9\s]", " ", regex=True) .str.split() ) from collections import Counter cnt = Counter() for lst in terms.tolist(): cnt.update([w for w in lst if len(w) >= 4]) top = cnt.most_common(100) review_top_terms = pd.DataFrame(top, columns=["term","count"]) else: review_top_terms = pd.DataFrame(columns=["term","count"]) else: reviews = pd.DataFrame(columns=["review_score","count"]) review_text_stats = pd.DataFrame([{"avg_len_words": None, "n_texts": 0, "avg_review_score": None}]) review_top_terms = pd.DataFrame(columns=["term","count"]) # categories & complaints by category categories = pd.DataFrame(columns=["product_category_name_english","items_sold"]) complaints_by_category = pd.DataFrame(columns=["product_category_name_english","complaint_rate"]) delays_by_category = pd.DataFrame(columns=["product_category_name_english","avg_delivery_days"]) has_items = has_table(conn, T_ITEMS) has_products = has_table(conn, T_PRODUCTS) has_trans = has_table(conn, T_CAT_TRANS) def categories_join_sql(): if has_products and has_trans: return """ SELECT p.product_id, COALESCE(t.product_category_name_english, p.product_category_name) AS cat_en FROM products p LEFT JOIN product_category_name_translation t ON t.product_category_name = p.product_category_name """ elif has_products: return """ SELECT product_id, product_category_name AS cat_en FROM products """ else: return None cat_sql = categories_join_sql() if has_items and cat_sql: categories = sql_df(conn, f""" WITH prod AS ({cat_sql}) SELECT prod.cat_en AS product_category_name_english, COUNT(*) AS items_sold FROM order_items oi JOIN prod ON prod.product_id = oi.product_id GROUP BY prod.cat_en ORDER BY items_sold DESC LIMIT 100 """) if has_items and has_products and has_table(conn, T_REVIEWS) and cat_sql: complaints_by_category = sql_df(conn, f""" WITH prod AS ({cat_sql}) , rev AS ( SELECT order_id, review_score FROM order_reviews ) SELECT prod.cat_en AS product_category_name_english, AVG(CASE WHEN rev.review_score <= 2 THEN 1.0 ELSE 0.0 END) AS complaint_rate FROM order_items oi JOIN prod ON prod.product_id = oi.product_id LEFT JOIN rev ON rev.order_id = oi.order_id GROUP BY prod.cat_en HAVING COUNT(*) >= 20 ORDER BY complaint_rate DESC LIMIT 100 """) # delays by state & category if has_table(conn, T_CUSTOMERS) and has_table(conn, T_ORDERS): delays_by_state = sql_df(conn, """ SELECT c.customer_state, AVG(julianday(o.order_delivered_customer_date) - julianday(o.order_purchase_timestamp)) AS avg_delay FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.order_delivered_customer_date IS NOT NULL AND o.order_purchase_timestamp IS NOT NULL GROUP BY c.customer_state ORDER BY avg_delay DESC """) else: delays_by_state = pd.DataFrame(columns=["customer_state","avg_delay"]) if has_items and has_table(conn, T_ORDERS) and cat_sql: delays_by_category = sql_df(conn, f""" WITH prod AS ({cat_sql}) , od AS ( SELECT order_id, CAST(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp) AS REAL) AS d FROM orders WHERE order_delivered_customer_date IS NOT NULL AND order_purchase_timestamp IS NOT NULL ) SELECT prod.cat_en AS product_category_name_english, AVG(od.d) AS avg_delivery_days FROM order_items oi JOIN prod ON prod.product_id = oi.product_id JOIN od ON od.order_id = oi.order_id GROUP BY prod.cat_en HAVING COUNT(*) >= 20 ORDER BY avg_delivery_days DESC LIMIT 100 """) # join coverage & table health (light) join_coverage = pd.DataFrame(columns=["table","rows"]) try: tables = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table'", conn)["name"].tolist() rows = [] for t in tables: try: n = pd.read_sql_query(f"SELECT COUNT(*) AS n FROM {t}", conn)["n"].iloc[0] except Exception: n = None rows.append({"table": t, "rows": n}) join_coverage = pd.DataFrame(rows) except Exception: pass table_health = pd.DataFrame(columns=["table","row_count"]) try: samples = ["orders","order_items","order_reviews","order_payments","products","customers"] recs = [] for t in samples: if has_table(conn, t): n = pd.read_sql_query(f"SELECT COUNT(*) AS n FROM {t}", conn)["n"].iloc[0] recs.append({"table": t, "row_count": n}) table_health = pd.DataFrame(recs) except Exception: pass return { "orders": orders_summary, "delivery": delivery, "delivery_hist": delivery_hist, "reviews": reviews, "payments": payments, "categories": categories, "orders_monthly": orders_monthly, "delays_by_state": delays_by_state, "delays_by_category": delays_by_category, "review_text_stats": review_text_stats, "review_top_terms": review_top_terms, "complaints_by_category": complaints_by_category, "join_coverage": join_coverage, "table_health": table_health, } @st.cache_data(show_spinner=False) def load_summaries(): need = [ "orders_summary.parquet", "delivery_times.parquet", "delivery_hist.parquet", "reviews_summary.parquet", "payments_summary.parquet", "top_categories.parquet", "orders_monthly.parquet", "delays_by_state.parquet", "delays_by_category.parquet", "review_text_stats.parquet", "review_top_terms.parquet", "complaints_by_category.parquet", "join_coverage.parquet", "table_health.parquet", ] missing = [f for f in need if not (PARQUET_BASE / f).exists()] if not missing: return ({ "orders": pd.read_parquet(PARQUET_BASE / "orders_summary.parquet"), "delivery": pd.read_parquet(PARQUET_BASE / "delivery_times.parquet"), "delivery_hist": pd.read_parquet(PARQUET_BASE / "delivery_hist.parquet"), "reviews": pd.read_parquet(PARQUET_BASE / "reviews_summary.parquet"), "payments": pd.read_parquet(PARQUET_BASE / "payments_summary.parquet"), "categories": pd.read_parquet(PARQUET_BASE / "top_categories.parquet"), "orders_monthly": pd.read_parquet(PARQUET_BASE / "orders_monthly.parquet"), "delays_by_state": pd.read_parquet(PARQUET_BASE / "delays_by_state.parquet"), "delays_by_category": pd.read_parquet(PARQUET_BASE / "delays_by_category.parquet"), "review_text_stats": pd.read_parquet(PARQUET_BASE / "review_text_stats.parquet"), "review_top_terms": pd.read_parquet(PARQUET_BASE / "review_top_terms.parquet"), "complaints_by_category": pd.read_parquet(PARQUET_BASE / "complaints_by_category.parquet"), "join_coverage": pd.read_parquet(PARQUET_BASE / "join_coverage.parquet"), "table_health": pd.read_parquet(PARQUET_BASE / "table_health.parquet"), }, True, str(PARQUET_BASE)) else: if not Path(DB_PATH).exists(): st.error( "EDA summary files not found: " + ", ".join(missing) + f"\\n\\nEither set EDA_PARQUET_DIR or provide a SQLite DB at {DB_PATH}." ) return (None, False, DB_PATH) try: return (build_summaries_from_db(DB_PATH), False, DB_PATH) except Exception as e: st.error(f"Failed to build EDA summaries from DB: {e}") return (None, False, DB_PATH) # ---------- Renderers ---------- def render_summary(): st.subheader("EDA Summary") data_pack = load_summaries() if not data_pack or data_pack[0] is None: return data, used_parquet, _dbp = data_pack _data_source_badge(used_parquet, _dbp) # Monthly series orders_monthly_all = data.get("orders_monthly", pd.DataFrame()).copy() if not orders_monthly_all.empty and "month" in orders_monthly_all.columns: try: orders_monthly_all["month"] = pd.to_datetime(orders_monthly_all["month"], errors="coerce").dt.floor("D") except Exception: pass if orders_monthly_all.empty or "month" not in orders_monthly_all.columns: min_dt, max_dt = _fallback_date_bounds() orders_monthly_f = pd.DataFrame(columns=["month","count"]) else: min_dt = orders_monthly_all["month"].min().date() max_dt = orders_monthly_all["month"].max().date() orders_monthly_f = orders_monthly_all.copy() # KPIs c1, c2, c3 = st.columns(3) try: ords = data.get("orders", pd.DataFrame()) total_orders = int(ords["count"].sum()) if "count" in ords.columns else None c1.metric("Total Orders", f"{total_orders:,}" if total_orders is not None else "—") except Exception: c1.metric("Total Orders", "—") try: delivery = data.get("delivery", pd.DataFrame([{"avg_delivery_days": None}])) avg_delivery = delivery["avg_delivery_days"].iloc[0] if "avg_delivery_days" in delivery.columns else None c2.metric("Avg Delivery (days)", round(float(avg_delivery), 2) if avg_delivery is not None else "—") except Exception: c2.metric("Avg Delivery (days)", "—") try: rv = data.get("reviews", pd.DataFrame()) if not rv.empty and all(col in rv.columns for col in ["review_score","count"]): avg_rev = (rv["review_score"] * rv["count"]).sum() / max(1, rv["count"].sum()) c3.metric("Avg Review", round(float(avg_rev), 2)) else: c3.metric("Avg Review", "—") except Exception: c3.metric("Avg Review", "—") st.markdown("### Orders per Month") if not orders_monthly_f.empty: st.line_chart(orders_monthly_f.set_index("month")["count"]) else: st.caption(f"(No monthly series found; typical range is {min_dt} to {max_dt})") st.markdown("### Order Status Distribution") ords = data.get("orders", pd.DataFrame()) if not ords.empty and "order_status" in ords.columns: st.bar_chart(ords.set_index("order_status")["count"]) st.markdown("### Top Product Categories") cats = data.get("categories", pd.DataFrame()) if not cats.empty: show = cats.rename(columns={"items_sold":"count"}) col = "count" if "count" in show.columns else ("items_sold" if "items_sold" in show.columns else None) if col: st.bar_chart(show.set_index("product_category_name_english")[col].head(20)) def render_eda(): st.subheader("Olist E-commerce — Interactive EDA") data_pack = load_summaries() if not data_pack or data_pack[0] is None: return data, used_parquet, _dbp = data_pack _data_source_badge(used_parquet, _dbp) orders_monthly_all = data.get("orders_monthly", pd.DataFrame()).copy() if "month" in orders_monthly_all.columns: orders_monthly_all["month"] = pd.to_datetime(orders_monthly_all["month"], errors="coerce").dt.floor("D") if orders_monthly_all.empty or "month" not in orders_monthly_all.columns: min_dt, max_dt = _fallback_date_bounds() else: min_dt = orders_monthly_all["month"].min().date() max_dt = orders_monthly_all["month"].max().date() # Persisted filters if "eda_filters" not in st.session_state: st.session_state.eda_filters = { "dataset": "All", "start": min_dt, "end": max_dt, "states": [], "categories": [], "payment_types": [], "min_review": 1, "review_kw": "", } # Filters UI st.markdown("#### Filters") c_top = st.columns([1,1,1,1]) with c_top[0]: dataset_choice = st.selectbox( "Dataset view", ["All", "Orders", "Payments", "Reviews", "Products", "Customers"], index=["All","Orders","Payments","Reviews","Products","Customers"].index(st.session_state.eda_filters["dataset"]), key="eda_dataset_choice" ) with c_top[1]: start_date = st.date_input("Start month", value=st.session_state.eda_filters["start"], min_value=min_dt, max_value=max_dt, key="eda_start_date") with c_top[2]: end_date = st.date_input("End month", value=st.session_state.eda_filters["end"], min_value=min_dt, max_value=max_dt, key="eda_end_date") with c_top[3]: st.caption(f"Data available: {min_dt} → {max_dt}") state_opts = sorted(data.get("delays_by_state", pd.DataFrame()).get("customer_state", pd.Series([], dtype=str)).dropna().unique().tolist()) if "delays_by_state" in data else [] cat_df = data.get("categories", pd.DataFrame()) cat_opts = cat_df.get("product_category_name_english", pd.Series([], dtype=str)).dropna().unique().tolist() if not cat_df.empty else [] pay_df = data.get("payments", pd.DataFrame()) pay_opts = pay_df.get("payment_type", pd.Series([], dtype=str)).dropna().unique().tolist() if not pay_df.empty else [] c_mid = st.columns(3) with c_mid[0]: chosen_states = st.multiselect("Customer states", state_opts, default=st.session_state.eda_filters["states"], key="eda_states") with c_mid[1]: chosen_cats = st.multiselect("Product categories", cat_opts[:200], default=st.session_state.eda_filters["categories"], key="eda_categories") with c_mid[2]: chosen_pay = st.multiselect("Payment types", pay_opts, default=st.session_state.eda_filters["payment_types"], key="eda_payments") c_bot = st.columns([1,2,1]) with c_bot[0]: min_review = st.slider("Min review score", 1, 5, st.session_state.eda_filters["min_review"], key="eda_min_review") with c_bot[1]: review_kw = st.text_input("Filter reviews by keyword", value=st.session_state.eda_filters["review_kw"], key="eda_review_kw") with c_bot[2]: apply_filters = st.button("Apply", type="primary", use_container_width=True, key="eda_apply") if apply_filters: if pd.Timestamp(start_date) > pd.Timestamp(end_date): start_date, end_date = end_date, start_date st.session_state.eda_filters.update({ "dataset": dataset_choice, "start": start_date, "end": end_date, "states": chosen_states, "categories": chosen_cats, "payment_types": chosen_pay, "min_review": int(min_review), "review_kw": review_kw, }) st.rerun() # Persisted values dataset_choice = st.session_state.eda_filters["dataset"] start_dt = pd.Timestamp(st.session_state.eda_filters["start"]).floor("D") end_dt = pd.Timestamp(st.session_state.eda_filters["end"]).floor("D") chosen_states = st.session_state.eda_filters["states"] chosen_cats = st.session_state.eda_filters["categories"] chosen_pay = st.session_state.eda_filters["payment_types"] min_review = st.session_state.eda_filters["min_review"] review_kw = st.session_state.eda_filters["review_kw"] # Filter frames # Use the already parsed monthly frame above; coerce type defensively and drop NaT before comparing if not orders_monthly_all.empty and "month" in orders_monthly_all.columns: if not pd.api.types.is_datetime64_any_dtype(orders_monthly_all["month"]): orders_monthly_all["month"] = pd.to_datetime(orders_monthly_all["month"], errors="coerce").dt.floor("D") _om = orders_monthly_all.dropna(subset=["month"]) orders_monthly_f = _om[(_om["month"] >= start_dt) & (_om["month"] <= end_dt)].copy() else: orders_monthly_f = pd.DataFrame(columns=["month","count"]) delays_by_state_f = data.get("delays_by_state", pd.DataFrame()).copy() if not delays_by_state_f.empty and chosen_states: delays_by_state_f = delays_by_state_f[delays_by_state_f["customer_state"].isin(chosen_states)] delays_by_category_f = data.get("delays_by_category", pd.DataFrame()).copy() if not delays_by_category_f.empty and chosen_cats: delays_by_category_f = delays_by_category_f[delays_by_category_f["product_category_name_english"].isin(chosen_cats)] payments_f = data.get("payments", pd.DataFrame()).copy() if not payments_f.empty and chosen_pay: payments_f = payments_f[payments_f["payment_type"].isin(chosen_pay)] reviews_summary_f = data.get("reviews", pd.DataFrame()).copy() if not reviews_summary_f.empty and "review_score" in reviews_summary_f.columns: reviews_summary_f = reviews_summary_f[reviews_summary_f["review_score"] >= min_review] top_terms_f = data.get("review_top_terms", pd.DataFrame()).copy() if not top_terms_f.empty and review_kw.strip() and "term" in top_terms_f.columns: top_terms_f = top_terms_f[top_terms_f["term"].str.contains(review_kw.strip(), case=False, na=False)] # Renderers def add_download(label: str, df: pd.DataFrame, filename: str, key: str): if df is not None and not df.empty: st.download_button(label, df_to_csv_bytes(df), file_name=filename, mime="text/csv", key=key) def show_orders(): c1, c2, c3 = st.columns(3) try: st.metric("Total Orders", int(data.get("orders", pd.DataFrame({"count":[0]}))["count"].sum())) except Exception: st.metric("Total Orders", "—") try: val = data.get("delivery", pd.DataFrame([{"avg_delivery_days": None}]))["avg_delivery_days"].iloc[0] st.metric("Avg Delivery (days)", round(val, 2) if val is not None else "—") except Exception: st.metric("Avg Delivery (days)", "—") try: rv = data.get("reviews", pd.DataFrame([{"review_score":0,"count":0}])) avg_rev = (rv["review_score"] * rv["count"]).sum() / max(1, rv["count"].sum()) st.metric("Avg Review", round(avg_rev, 2)) except Exception: st.metric("Avg Review", "—") st.markdown("### Orders per Month") if not orders_monthly_f.empty: st.line_chart(orders_monthly_f.set_index("month")["count"]) add_download("⬇️ Download Orders per Month (CSV)", orders_monthly_f, "orders_monthly_filtered.csv", "dl_orders_monthly") else: st.info("No data in selected date range.") st.markdown("### Order Status Distribution") ords = data.get("orders", pd.DataFrame()) if not ords.empty and "order_status" in ords.columns: st.bar_chart(ords.set_index("order_status")["count"]) add_download("⬇️ Download Order Status Summary (CSV)", ords, "orders_status_summary.csv", "dl_orders_status") st.markdown("### Delivery Time (days) — Histogram") d_hist = data.get("delivery_hist", pd.DataFrame()) if not d_hist.empty and "bin_days" in d_hist.columns: st.line_chart(d_hist.set_index("bin_days")["count"]) add_download("⬇️ Download Delivery Histogram (CSV)", d_hist, "delivery_hist.csv", "dl_delivery_hist") st.markdown("### Average Delivery Delay by Customer State (days)") if not delays_by_state_f.empty and "customer_state" in delays_by_state_f.columns: st.bar_chart(delays_by_state_f.set_index("customer_state")["avg_delay"]) with st.expander("🔎 View state delay table"): st.dataframe(delays_by_state_f, use_container_width=True) add_download("⬇️ Download State Delay (CSV)", delays_by_state_f, "delays_by_state_filtered.csv", "dl_delays_state") st.markdown("### Average Delivery Time by Category") if not delays_by_category_f.empty and "product_category_name_english" in delays_by_category_f.columns: st.bar_chart(delays_by_category_f.set_index("product_category_name_english")["avg_delivery_days"]) with st.expander("🔎 View category delivery table"): st.dataframe(delays_by_category_f, use_container_width=True) add_download("⬇️ Download Category Delivery (CSV)", delays_by_category_f, "delays_by_category_filtered.csv", "dl_delays_cat") def show_payments(): st.markdown("### Payment Methods (count)") if not payments_f.empty and "payment_type" in payments_f.columns: st.bar_chart(payments_f.set_index("payment_type")["count"]) add_download("⬇️ Download Payments Summary (CSV)", payments_f, "payments_summary_filtered.csv", "dl_payments") with st.expander("🔎 View payment summary table"): st.dataframe(payments_f, use_container_width=True) def show_reviews(): st.markdown("### Review Score Distribution (filtered)") if not reviews_summary_f.empty and "review_score" in reviews_summary_f.columns: st.bar_chart(reviews_summary_f.set_index("review_score")["count"]) add_download("⬇️ Download Review Score Summary (CSV)", reviews_summary_f, "reviews_summary_filtered.csv", "dl_reviews_summary") else: st.info("No reviews meet the current score filter or data unavailable.") rstats = data.get("review_text_stats", pd.DataFrame()) c1, c2, c3 = st.columns(3) with c1: try: st.metric("Avg review words", round(float(rstats["avg_len_words"].iloc[0]), 1)) except Exception: st.metric("Avg review words", "—") with c2: try: st.metric("Texts with content", int(rstats["n_texts"].iloc[0])) except Exception: st.metric("Texts with content", "—") with c3: try: st.metric("Avg review score (overall)", round(float(rstats["avg_review_score"].iloc[0]), 2)) except Exception: st.metric("Avg review score (overall)", "—") st.markdown("### Top Terms in Reviews") st.dataframe(top_terms_f.head(50), use_container_width=True) add_download("⬇️ Download Top Terms (CSV)", top_terms_f, "review_top_terms_filtered.csv", "dl_top_terms") st.markdown("### Complaint Rate by Category (score ≤ 2)") comp = data.get("complaints_by_category", pd.DataFrame()) if not comp.empty and "product_category_name_english" in comp.columns: st.bar_chart(comp.set_index("product_category_name_english")["complaint_rate"]) add_download("⬇️ Download Complaints by Category (CSV)", comp, "complaints_by_category.csv", "dl_complaints_cat") def show_products(): st.markdown("### Top Product Categories (by items sold)") cats_view = data.get("categories", pd.DataFrame()) if not cats_view.empty and chosen_cats: cats_view = cats_view[cats_view["product_category_name_english"].isin(chosen_cats)] st.dataframe(cats_view.head(30), use_container_width=True) add_download("⬇️ Download Top Categories (CSV)", cats_view, "top_categories_filtered.csv", "dl_top_categories") def show_customers(): st.markdown("### Knowledge Base Join Coverage") st.dataframe(data.get("join_coverage", pd.DataFrame()), use_container_width=True) add_download("⬇️ Download Join Coverage (CSV)", data.get("join_coverage", pd.DataFrame()), "join_coverage.csv", "dl_join_coverage") st.markdown("### Table Health (row counts)") st.dataframe(data.get("table_health", pd.DataFrame()), use_container_width=True) add_download("⬇️ Download Table Health (CSV)", data.get("table_health", pd.DataFrame()), "table_health.csv", "dl_table_health") # Routing if dataset_choice in ["All", "Orders"]: show_orders() if dataset_choice in ["All", "Payments"]: show_payments() if dataset_choice in ["All", "Reviews"]: show_reviews() if dataset_choice in ["All", "Products"]: show_products() if dataset_choice in ["All", "Customers"]: show_customers()