"""Streamlit web interface for Table Comparator. Run directly:: streamlit run src/table_comparator/app.py Or via the CLI:: table-comparator serve """ from __future__ import annotations # --------------------------------------------------------------------------- # Path bootstrap – makes ``table_comparator`` importable even when streamlit # is started directly without the package installed via uv. # --------------------------------------------------------------------------- import sys from pathlib import Path _SRC = str(Path(__file__).resolve().parent.parent) if _SRC not in sys.path: # pragma: no cover sys.path.insert(0, _SRC) # --------------------------------------------------------------------------- import json # noqa: E402 import pandas as pd # noqa: E402 import streamlit as st # noqa: E402 from table_comparator.comparator import compare # noqa: E402 from table_comparator.config import ( # noqa: E402 DATE_FORMAT_EXAMPLES, ColumnMapping, ComparisonConfig, ) # --------------------------------------------------------------------------- # Helpers # --------------------------------------------------------------------------- def _load_uploaded(file) -> pd.DataFrame: """Read an uploaded file into a DataFrame.""" name = file.name.lower() if name.endswith(".tsv"): return pd.read_csv(file, sep="\t") if name.endswith(".csv"): return pd.read_csv(file) if name.endswith((".xlsx", ".xls")): return pd.read_excel(file) raise ValueError(f"Unsupported file type: {file.name}") def _format_picker(label: str, key: str) -> str: """A selectbox for common date formats with a custom-entry fallback. Each choice is displayed as ``%d/%m/%Y → 15/01/2024`` so users who are not familiar with strftime codes can pick by the example date. """ display = [f"{pat} → {ex}" for pat, ex in DATE_FORMAT_EXAMPLES] choices = display + ["Custom…"] sel = st.selectbox(label, choices, key=key) if sel == "Custom…": return st.text_input( "Custom strftime pattern", placeholder="%Y-%m-%d", key=key + "_custom", ) return sel.split(" → ")[0] # --------------------------------------------------------------------------- # Main app # --------------------------------------------------------------------------- def main() -> None: # noqa: C901 – kept as single function for Streamlit flow st.set_page_config(page_title="Table Comparator", layout="wide", page_icon="🔍") st.title("Table Comparator") st.caption( "Upload two tables and define column mappings to find which rows " "in **Table A** exist in **Table B**." ) # ── upload ───────────────────────────────────────────────────────────── u1, u2 = st.columns(2) with u1: file_a = st.file_uploader("Table A", type=["csv", "tsv", "xlsx", "xls"]) with u2: file_b = st.file_uploader("Table B", type=["csv", "tsv", "xlsx", "xls"]) if not (file_a and file_b): st.info("Upload both tables to continue.") return try: df_a = _load_uploaded(file_a) df_b = _load_uploaded(file_b) except Exception as exc: # noqa: BLE001 st.error(f"Failed to load a file: {exc}") return # ── previews ─────────────────────────────────────────────────────────── p1, p2 = st.columns(2) with p1: with st.expander(f"Table A – {len(df_a)} rows × {len(df_a.columns)} cols"): st.dataframe(df_a.head(10), use_container_width=True, hide_index=True) with p2: with st.expander(f"Table B – {len(df_b)} rows × {len(df_b.columns)} cols"): st.dataframe(df_b.head(10), use_container_width=True, hide_index=True) cols_a = list(df_a.columns) cols_b = list(df_b.columns) # ── column mappings ──────────────────────────────────────────────────── st.divider() st.subheader("Column Mappings") n = st.number_input( "Number of column pairs to compare", min_value=1, max_value=len(cols_a), value=1, step=1, ) mappings: list[ColumnMapping] = [] form_ok = True for i in range(n): with st.expander(f"Mapping {i + 1}", expanded=True): mc1, mc2, mc3 = st.columns([3, 3, 2]) col_a = mc1.selectbox("Column A", cols_a, key=f"ca_{i}") col_b = mc2.selectbox("Column B", cols_b, key=f"cb_{i}") col_type = mc3.selectbox( "Type", ["text", "number", "date"], key=f"ct_{i}" ) fmt_a = fmt_b = None rounding = None margin = None case_sensitive = True decimal_sep_a = "." decimal_sep_b = "." if col_type == "date": dc1, dc2 = st.columns(2) with dc1: fmt_a = _format_picker("Format A", f"fmta_{i}") with dc2: fmt_b = _format_picker("Format B", f"fmtb_{i}") if not fmt_a or not fmt_b: st.warning("Both date formats are required.") form_ok = False elif col_type == "number": # Rounding controls nc1, nc2 = st.columns(2) with nc1: do_round = st.toggle("Apply rounding", key=f"rt_{i}") with nc2: if do_round: rounding = st.number_input( "Decimal places", min_value=0, value=2, step=1, key=f"rd_{i}", ) # Near-match margin controls mg1, mg2 = st.columns(2) with mg1: do_margin = st.toggle("Near-match margin", key=f"mg_{i}") with mg2: if do_margin: margin = st.number_input( "Margin (\u00b1)", min_value=0.0, value=5.0, key=f"mgv_{i}", ) # Decimal separator controls st.caption( "Decimal separator: Specify which character is used as the " "decimal point (e.g., '.' for US format like 1,234.56 or ',' " "for European format like 1.234,56)" ) ds1, ds2 = st.columns(2) with ds1: decimal_sep_a = st.selectbox( "Decimal separator A", options=[".", ","], index=0, key=f"dsa_{i}", help="Decimal separator for Table A (default: '.' for US/UK format)" ) with ds2: decimal_sep_b = st.selectbox( "Decimal separator B", options=[".", ","], index=0, key=f"dsb_{i}", help="Decimal separator for Table B (default: '.' for US/UK format)" ) elif col_type == "text": case_sensitive = st.toggle( "Case sensitive", value=True, key=f"cs_{i}" ) mappings.append( ColumnMapping( column_a=col_a, column_b=col_b, col_type=col_type, date_format_a=fmt_a, date_format_b=fmt_b, rounding=rounding, case_sensitive=case_sensitive, decimal_separator_a=decimal_sep_a, decimal_separator_b=decimal_sep_b, margin=margin, ) ) # ── run button ───────────────────────────────────────────────────────── st.divider() if not form_ok: st.warning("Fix mapping issues above before comparing.") return if not st.button("Run Comparison", type="primary", use_container_width=True): return # --- validate ---------------------------------------------------------- config = ComparisonConfig(columns=mappings) errs = config.validate() if errs: for e in errs: st.error(e) return # --- run --------------------------------------------------------------- try: result = compare(config, df_a, df_b) except Exception as exc: # noqa: BLE001 st.error(str(exc)) return # ── warnings ─────────────────────────────────────────────────────────── for w in result.warnings: st.warning(w) # ── metrics ──────────────────────────────────────────────────────────── st.divider() st.subheader("Results") m1, m2, m3, m4 = st.columns(4) m1.metric("Table A rows", result.total) m2.metric("A not in B", result.unmatch_count) m3.metric("Table B rows", result.total_b) m4.metric("B not in A", result.unmatch_count_b) # ── result tables ────────────────────────────────────────────────────── tab_summary, tab_a_miss, tab_b_miss, tab_near, tab_all_a, tab_all_b = st.tabs( ["Summary", "A not in B", "B not in A", "Near matches", "All A rows", "All B rows"] ) with tab_summary: pct_a = ( f"{100 * result.match_count / result.total:.1f}%" if result.total else "–" ) pct_b = ( f"{100 * result.match_count_b / result.total_b:.1f}%" if result.total_b else "–" ) summary = pd.DataFrame({ "": ["Table A", "Table B"], "Total rows": [result.total, result.total_b], "Matched": [result.match_count, result.match_count_b], "Unmatched": [result.unmatch_count, result.unmatch_count_b], "Match rate": [pct_a, pct_b], }) st.dataframe(summary, use_container_width=True, hide_index=True) with tab_a_miss: if result.unmatch_count: st.dataframe( result.unmatched, use_container_width=True, hide_index=True ) else: st.success("All rows from Table A were found in Table B!") with tab_b_miss: if result.unmatch_count_b: st.dataframe( result.unmatched_b, use_container_width=True, hide_index=True ) else: st.success("All rows from Table B were found in Table A!") with tab_near: if not any(m.margin is not None for m in config.columns): st.info("No margins configured. Enable a near-match margin on a number column to see suggestions.") elif not result.near_matches: st.info("No near-matches found.") else: rows = [] for nm in result.near_matches: row: dict[str, object] = {"Row A": nm.row_a} for m in config.columns: row[f"A: {m.column_a}"] = result.df_a.at[nm.row_a - 1, m.column_a] row["Row B"] = nm.row_b for m in config.columns: row[f"B: {m.column_b}"] = result.df_b.at[nm.row_b - 1, m.column_b] for col, diff in nm.diffs.items(): row[f"Diff: {col}"] = diff rows.append(row) st.dataframe( pd.DataFrame(rows), use_container_width=True, hide_index=True ) with tab_all_a: view_a = result.df_a.copy() view_a.insert(0, "Row", range(1, len(view_a) + 1)) view_a.insert(1, "Matched", result.matched_mask.map({True: "\u2713", False: "\u2717"})) st.dataframe(view_a, use_container_width=True, hide_index=True) with tab_all_b: view_b = result.df_b.copy() view_b.insert(0, "Row", range(1, len(view_b) + 1)) view_b.insert(1, "Matched", result.matched_mask_b.map({True: "\u2713", False: "\u2717"})) st.dataframe(view_b, use_container_width=True, hide_index=True) # ── downloads ────────────────────────────────────────────────────────── st.divider() export_a = result.df_a.copy() export_a.insert(0, "Matched", result.matched_mask) export_a.insert(0, "Row", range(1, len(export_a) + 1)) export_b = result.df_b.copy() export_b.insert(0, "Matched", result.matched_mask_b) export_b.insert(0, "Row", range(1, len(export_b) + 1)) dl1, dl2, dl3 = st.columns(3) with dl1: st.download_button( "Download Table A results (CSV)", data=export_a.to_csv(index=False), file_name="comparison_table_a.csv", mime="text/csv", use_container_width=True, ) with dl2: st.download_button( "Download Table B results (CSV)", data=export_b.to_csv(index=False), file_name="comparison_table_b.csv", mime="text/csv", use_container_width=True, ) with dl3: st.download_button( "Download config (JSON)", data=json.dumps(config.to_dict(), indent=2), file_name="comparison_config.json", mime="application/json", use_container_width=True, ) if __name__ == "__main__": main()