Jakub
Add fuzzy near-match suggestions for numeric columns
fe0d1b4
Raw History Blame Contribute Delete
14.4 kB
"""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()