bot / eda.py
SuhasGholkar's picture
Update eda.py
48afa38 verified
Raw History Blame Contribute Delete
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,
}
@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()