Spaces:
Paused
Paused
Download eda.py from SuhasGholkar/bot: direct link, hf CLI and curl.
- Browser
- Download file 30 kB
-
https://huggingface.co/spaces/SuhasGholkar/bot/resolve/main/eda.py
- Command line
-
hf download hf://spaces/SuhasGholkar/bot/eda.py
-
curl -L -o eda.py https://huggingface.co/spaces/SuhasGholkar/bot/resolve/main/eda.py
30 kB
| 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, | |
| } | |
| 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() | |