Jakub
Add fuzzy near-match suggestions for numeric columns
fe0d1b4
Raw History Blame Contribute Delete
10.3 kB
"""Command-line interface.
Usage patterns
--------------
Interactive (prompts for everything)::
table-comparator
From a saved config file::
table-comparator --config sample_data/sample_config.json
With output export::
table-comparator --config cfg.json --output results.csv
Launch the Streamlit web UI::
table-comparator serve
"""
from __future__ import annotations
import subprocess
import sys
from pathlib import Path
import click
import pandas as pd
from table_comparator.comparator import ComparisonResult, NearMatch, compare
from table_comparator.config import (
DATE_FORMAT_EXAMPLES,
VALID_TYPES,
ColumnMapping,
ComparisonConfig,
)
from table_comparator.loader import load_table
# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------
def _print_results(result: ComparisonResult, *, verbose: bool = False) -> None:
"""Pretty-print the comparison outcome to the terminal."""
pct_a = 100 * result.unmatch_count / result.total if result.total else 0
pct_b = 100 * result.unmatch_count_b / result.total_b if result.total_b else 0
click.echo()
click.echo("=" * 62)
click.echo(" TABLE COMPARATOR – RESULTS")
click.echo("=" * 62)
click.echo(
f" Table A {result.total:>6} rows"
f" {result.match_count:>6} matched"
f" {result.unmatch_count:>6} not in B"
f" ({pct_a:.1f}%)"
)
click.echo(
f" Table B {result.total_b:>6} rows"
f" {result.match_count_b:>6} matched"
f" {result.unmatch_count_b:>6} not in A"
f" ({pct_b:.1f}%)"
)
click.echo("=" * 62)
if result.warnings:
click.echo()
click.secho(" Warnings:", fg="yellow", bold=True)
for w in result.warnings:
click.secho(f" Β· {w}", fg="yellow")
if result.unmatch_count:
click.echo()
click.secho(
f" Rows from A not found in B ({result.unmatch_count}):", bold=True
)
click.echo(result.unmatched.to_string(index=False))
else:
click.echo()
click.secho(
" All rows from Table A were found in Table B.", fg="green", bold=True
)
if result.unmatch_count_b:
click.echo()
click.secho(
f" Rows from B not found in A ({result.unmatch_count_b}):", bold=True
)
click.echo(result.unmatched_b.to_string(index=False))
else:
click.echo()
click.secho(
" All rows from Table B were found in Table A.", fg="green", bold=True
)
if result.near_matches:
click.echo()
click.secho(
f" Possible near-matches ({len(result.near_matches)}):", bold=True
)
for nm in result.near_matches:
# Gather mapped column values from both sides
vals_a = []
vals_b = []
for m in result.config.columns:
vals_a.append(str(result.df_a.at[nm.row_a - 1, m.column_a]))
vals_b.append(str(result.df_b.at[nm.row_b - 1, m.column_b]))
diff_parts = [
f"{col}: {d:+g}" for col, d in nm.diffs.items()
]
click.echo(
f" A Row {nm.row_a} ({', '.join(vals_a)})"
f" <-> B Row {nm.row_b} ({', '.join(vals_b)})"
f" [{', '.join(diff_parts)}]"
)
if verbose and result.match_count:
click.echo()
click.secho(f" Matched rows from A ({result.match_count}):", bold=True)
click.echo(result.matched.to_string(index=False))
if verbose and result.match_count_b:
click.echo()
click.secho(f" Matched rows from B ({result.match_count_b}):", bold=True)
click.echo(result.matched_b.to_string(index=False))
click.echo()
def _save_results(result: ComparisonResult, path: str) -> None:
"""Write both tables with Row number and Matched columns."""
df_a = result.df_a.copy()
df_a.insert(0, "Matched", result.matched_mask)
df_a.insert(0, "Row", range(1, len(df_a) + 1))
df_b = result.df_b.copy()
df_b.insert(0, "Matched", result.matched_mask_b)
df_b.insert(0, "Row", range(1, len(df_b) + 1))
p = Path(path)
if p.suffix.lower() in (".xlsx", ".xls"):
with pd.ExcelWriter(p) as writer:
df_a.to_excel(writer, sheet_name="Table A", index=False)
df_b.to_excel(writer, sheet_name="Table B", index=False)
else:
# For CSV, write both tables separated by a blank line
stem = p.stem
suffix = p.suffix
p_a = p.with_name(f"{stem}_table_a{suffix}")
p_b = p.with_name(f"{stem}_table_b{suffix}")
df_a.to_csv(p_a, index=False)
df_b.to_csv(p_b, index=False)
click.echo(f" Table A β†’ {p_a}")
click.echo(f" Table B β†’ {p_b}")
# ---------------------------------------------------------------------------
# Interactive mode
# ---------------------------------------------------------------------------
def _interactive() -> tuple[ComparisonConfig, pd.DataFrame, pd.DataFrame]:
"""Walk the user through the full setup via click prompts."""
click.echo()
click.secho(" TABLE COMPARATOR – Interactive Setup", bold=True)
click.echo()
path_a: str = click.prompt(
" Path to Table A", type=click.Path(exists=True)
)
path_b: str = click.prompt(
" Path to Table B", type=click.Path(exists=True)
)
df_a = load_table(path_a)
df_b = load_table(path_b)
click.echo()
click.echo(f" Table A columns: {list(df_a.columns)}")
click.echo(f" Table B columns: {list(df_b.columns)}")
click.echo()
mappings: list[ColumnMapping] = []
while True:
click.echo(f" ── Column mapping {len(mappings) + 1} ──")
col_a: str = click.prompt(
" Column from A",
type=click.Choice(list(df_a.columns), case_sensitive=False),
)
col_b: str = click.prompt(
" Column from B",
type=click.Choice(list(df_b.columns), case_sensitive=False),
)
col_type: str = click.prompt(
" Type",
type=click.Choice(list(VALID_TYPES)),
default="text",
show_default=True,
)
kwargs: dict = {
"column_a": col_a,
"column_b": col_b,
"col_type": col_type,
}
if col_type == "date":
click.echo(" Common formats:")
for pat, example in DATE_FORMAT_EXAMPLES:
click.echo(f" {pat:12s} β†’ {example}")
kwargs["date_format_a"] = click.prompt(
" Date format A (strftime)"
)
kwargs["date_format_b"] = click.prompt(
" Date format B (strftime)"
)
elif col_type == "number":
if click.confirm(" Apply rounding?", default=False):
kwargs["rounding"] = click.prompt(
" Decimal places", type=int, default=2, show_default=True
)
elif col_type == "text":
kwargs["case_sensitive"] = click.confirm(
" Case sensitive?", default=True
)
mappings.append(ColumnMapping(**kwargs))
click.echo()
if not click.confirm(" Add another mapping?", default=True):
break
click.echo()
cfg = ComparisonConfig(
table_a_path=path_a, table_b_path=path_b, columns=mappings
)
return cfg, df_a, df_b
# ---------------------------------------------------------------------------
# CLI entry-point
# ---------------------------------------------------------------------------
@click.group(invoke_without_command=True)
@click.option(
"--config", "-c", "config_path",
type=click.Path(exists=True), default=None,
help="Path to a JSON config file.",
)
@click.option(
"--output", "-o",
type=click.Path(), default=None,
help="Save results to CSV or Excel.",
)
@click.option(
"--save-config",
type=click.Path(), default=None,
help="Persist the current config to a JSON file.",
)
@click.option(
"--verbose", "-v", is_flag=True, default=False,
help="Also print the matched rows.",
)
@click.pass_context
def main(ctx, config_path, output, save_config, verbose):
"""Compare two tables – find which rows in Table A exist in Table B."""
if ctx.invoked_subcommand is not None:
return # a sub-command (e.g. ``serve``) was given
# --- load tables -------------------------------------------------------
if config_path:
cfg = ComparisonConfig.load(config_path)
# Resolve relative paths from config file's directory
base = Path(config_path).resolve().parent
df_a = load_table(str(base / cfg.table_a_path))
df_b = load_table(str(base / cfg.table_b_path))
else:
cfg, df_a, df_b = _interactive()
# --- validate ----------------------------------------------------------
errors = cfg.validate()
if errors:
click.secho("Configuration errors:", fg="red", bold=True)
for e in errors:
click.secho(f" Β· {e}", fg="red")
sys.exit(1)
# --- run ---------------------------------------------------------------
result = compare(cfg, df_a, df_b)
_print_results(result, verbose=verbose)
# --- persist -----------------------------------------------------------
if save_config:
cfg.save(save_config)
click.echo(f" Config saved β†’ {save_config}")
if output:
_save_results(result, output)
p = Path(output)
if p.suffix.lower() in (".xlsx", ".xls"):
click.echo(f" Results saved β†’ {output}")
# CSV paths are printed inside _save_results
# ---------------------------------------------------------------------------
# Streamlit launcher sub-command
# ---------------------------------------------------------------------------
@main.command()
def serve():
"""Launch the Streamlit web UI."""
app = Path(__file__).resolve().parent / "app.py"
subprocess.run(
[sys.executable, "-m", "streamlit", "run", str(app)],
check=True,
)