--- title: Table Comparator sdk: streamlit sdk_version: "1.54.0" app_file: app.py --- # Table Comparator Compare two tables and find which rows from **Table A** exist in **Table B**. The two tables do not need to share column names or even column count — you define an explicit mapping between the columns you want compared, declare each one's data type, and the tool handles the rest. Available as both a **command-line tool** (interactive or config-driven) and a **Streamlit web app**. --- ## Purpose A common task when reconciling data between systems is checking whether every row in one export also appears in another. The tables coming out of those systems typically differ in: - **Column names** — one calls it `Customer`, the other `Name`. - **Date formats** — one stores dates as `15/01/2024`, the other as `2024-01-15`. In CSV exports both are plain text and need explicit parsing. - **Numeric precision** — one system rounds amounts to whole euros, the other keeps two decimal places. A comparison on the raw values would flag every single row as a mismatch. Table Comparator lets you declare all of that up front so the actual comparison is a single click (or command). --- ## Prerequisites | Tool | Minimum version | |---|---| | Python | 3.10 | | uv | any recent | --- ## Installation ```sh uv sync ``` `uv sync` reads `pyproject.toml`, creates `.venv`, resolves all dependencies, and installs the `table-comparator` script into the environment. Everything after this is run through `uv run`. --- ## Project structure ``` table-comparator/ ├── pyproject.toml # project metadata, deps, script entry-point ├── README.md ├── src/ │ └── table_comparator/ │ ├── __init__.py # public API re-exports │ ├── config.py # ColumnMapping & ComparisonConfig dataclasses │ ├── loader.py # CSV / TSV / Excel → pandas DataFrame │ ├── comparator.py # normalisation + composite-key matching │ ├── cli.py # click CLI (interactive, --config, serve) │ ├── __main__.py # enables python -m table_comparator │ └── app.py # Streamlit web UI └── sample_data/ ├── table_a.csv # 10-row reference table ├── table_b.csv # 9-row lookup table (different columns) └── sample_config.json # ready-to-run config for the sample tables ``` ### Module responsibilities | Module | Role | |---|---| | `config.py` | Defines the two dataclasses that fully describe a comparison: `ColumnMapping` (one column-pair) and `ComparisonConfig` (the whole job). Both serialise to/from JSON and validate themselves. | | `loader.py` | Thin wrapper around pandas that picks the right reader based on file extension (`.csv`, `.tsv`, `.xlsx`, `.xls`). | | `comparator.py` | The core engine. Normalises columns, builds composite keys, does the count-aware multiset match, and returns a `ComparisonResult`. | | `cli.py` | Click-based CLI with an interactive guided mode, a config-file mode, and a `serve` subcommand that launches Streamlit. | | `app.py` | The Streamlit page: file upload, mapping UI, results dashboard, and CSV/JSON downloads. | --- ## Usage — Command Line ### Interactive mode (no config file needed) ```sh uv run table-comparator ``` The tool prompts you for: 1. Path to Table A and Table B. 2. One or more column mappings — for each one you pick the column from A, the column from B, the type, and any type-specific options (date format, rounding, case sensitivity). 3. Whether to add another mapping. Then it runs the comparison and prints the results. ### Config-file mode ```sh uv run table-comparator --config sample_data/sample_config.json ``` All prompts are skipped; everything is read from the JSON file. File paths inside the config are resolved **relative to the config file's own directory**, so you can move the config + data around together and nothing breaks. ### Full option reference | Flag | Short | Description | |---|---|---| | `--config PATH` | `-c` | JSON config file. Omit to enter interactive mode. | | `--output PATH` | `-o` | Write results to a CSV or Excel file (format detected from extension). The output contains all original columns from Table A plus a leading `Matched` boolean column. | | `--save-config PATH` | | Persist the current run's config to a JSON file (useful after an interactive session — run it once to generate the file, then reuse it). | | `--verbose` | `-v` | Print matched rows in addition to unmatched ones. | | `--help` | | Show help. | ### `serve` subcommand ```sh uv run table-comparator serve ``` Launches the Streamlit web UI in the default browser. Equivalent to running `streamlit run src/table_comparator/app.py` directly (the app includes a `sys.path` bootstrap so it works either way). ### Alternative entry point ```sh uv run python -m table_comparator --config sample_data/sample_config.json ``` Identical behaviour to the `table-comparator` script. --- ## Usage — Streamlit Web UI ```sh uv run table-comparator serve ``` The page guides you through the same steps the CLI does, entirely in the browser: 1. **Upload** — drop or browse for Table A and Table B (CSV, TSV, or Excel). 2. **Preview** — collapsible previews of both tables appear immediately. 3. **Mappings** — set the number of column pairs, then configure each one in an expandable card: pick columns from dropdowns, choose the type, set the type-specific options. 4. **Run** — hit the button; results appear below. 5. **Results** — three tabs: *All rows* (with a checkmark column), *Not found*, and *Matched*. Metric cards at the top show totals and the match percentage. 6. **Download** — grab the results as CSV or the current config as JSON for later reuse on the command line. --- ## Configuration file format A config file is plain JSON. `sample_data/sample_config.json` is a working example. The schema: ```json { "table_a_path": "", "table_b_path": "", "columns": [ { "column_a": "Name in Table A", "column_b": "Name in Table B", "col_type": "text | number | date", // date only — both required when col_type is "date" "date_format_a": "%d/%m/%Y", "date_format_b": "%Y-%m-%d", // number only — omit or set to null to compare raw values "rounding": 2, // text only "case_sensitive": true } ] } ``` Every field that does not apply to the chosen `col_type` is ignored; you can set it to `null` or leave it out entirely. --- ## Column types ### `text` The values are stripped of leading/trailing whitespace before comparison. If `case_sensitive` is `false` both sides are lowercased first. ### `number` Values are coerced to numeric (anything that cannot be parsed becomes NaN and will only match another NaN in the same key position). If `rounding` is set, both sides are rounded to that many decimal places **before** comparison. This is the knob that handles differences like `150.25` vs `150.00` — set `rounding` to `0` and both become `150`. ### `date` Each side can use a completely different date format (common in CSV exports). `date_format_a` and `date_format_b` are Python `strftime`/`strptime` pattern strings and both are required. Values that cannot be parsed with the given format become NaT and behave like NaN (match only other nulls). Common patterns for reference: | Pattern | Example | |---|---| | `%Y-%m-%d` | `2024-01-15` | | `%d/%m/%Y` | `15/01/2024` | | `%d/%m/%y` | `15/01/24` | | `%m/%d/%Y` | `01/15/2024` | | `%m/%d/%y` | `01/15/24` | | `%d-%m-%Y` | `15-01-2024` | | `%d-%m-%y` | `15-01-24` | | `%d.%m.%Y` | `15.01.2024` | | `%d.%m.%y` | `15.01.24` | | `%Y%m%d` | `20240115` | --- ## How matching works The comparison engine in `comparator.py` is fully vectorised — it never iterates row-by-row in Python. The steps are: 1. **Normalise** — each mapped column is normalised independently according to its declared type (strip + optional lowercase for text; `to_numeric` + optional round for numbers; `to_datetime` with a per-side format for dates). 2. **Stringify** — every normalised column is cast to a string Series. Any NaN / NaT value is replaced with a fixed sentinel string (`\x00NULL\x00`). This sentinel uses null bytes so it cannot collide with real data, and it means two missing values in the same key position compare as equal — which is the expected behaviour for reconciliation. 3. **Composite key** — the per-column string Series are concatenated with a two-null-byte separator (`\x00\x00`) into a single string per row. This turns an N-column match problem into a single-column equality problem. 4. **Count-aware multiset match** — `value_counts()` records how many copies of each key exist in Table B. Within each group of identical keys in Table A, `groupby.cumcount()` numbers the rows 0, 1, 2 … A row in A is marked as matched only when its within-group index is strictly less than the number of copies available in B. This means duplicates are consumed one-to-one: if A contains three rows with the same key and B contains two, exactly two match and the third is flagged as unmatched. The whole operation is O(n + m) — only hash-maps and vectorised comparisons, no row-by-row iteration. 5. **Warnings** — if `to_numeric` or `to_datetime` produce more NaNs than were in the original column, the extra failures are reported as warnings so the user knows some values did not parse correctly with the chosen format or type. ### Why composite string keys? Pandas does not have a built-in "match on multiple columns simultaneously" that is both fast and handles mixed types cleanly. `merge` can do it but returns a full joined DataFrame and allocates memory proportional to the number of matches. A composite string key reduces the problem to a single column, which makes the subsequent count-aware match simple, produces only a boolean mask, and has predictable O(n + m) performance regardless of match count. ### Why a NaN sentinel instead of dropping nulls? If a row in Table A has a null in one of the mapped columns and a row in Table B has the same null in the same position, they *should* match — the null is part of the identity. Dropping nulls would silently skip those rows; a sentinel keeps them in the comparison. ### Mapping fewer columns than the table has (partial-key duplicates) When you map only a subset of columns — for example `date` and `amount` but not `name` — rows that differ only in the unmapped columns become identical from the comparator's perspective. Table A might contain three rows that differ only in `name`; all three share the same composite key. The count-aware matching handles this correctly: if Table B has only two rows with that key, exactly two rows in A are marked as matched and the third appears in the *Not found* list. This is the intended workflow when free-text fields such as names or descriptions vary in wording between systems and must be reviewed manually — simply leave those columns out of the mappings and use the *Not found* tab to spot-check the remainder. --- ## Sample data walkthrough The files in `sample_data/` are designed to exercise several edge cases at once. **Table A** (`table_a.csv`) has 10 rows with columns `Name`, `Transaction Date` (format `dd/mm/yyyy`), `Amount`, and `City`. **Table B** (`table_b.csv`) has 9 rows with columns `Customer`, `Date` (format `yyyy-mm-dd`), `Value`, `Location`, and `Status`. Note the deliberately different column names and date format, plus one extra column (`Status`) that is not mapped and therefore ignored. **Config** (`sample_config.json`) maps three column pairs: | Table A | Table B | Type | Options | |---|---|---|---| | `Name` | `Customer` | text | case-sensitive | | `Transaction Date` | `Date` | date | A: `%d/%m/%Y`, B: `%Y-%m-%d` | | `Amount` | `Value` | number | rounding: 2 | Running the sample produces **6 matched, 4 unmatched**: | Row | Why it did not match | |---|---| | David | Amount differs: `150.25` in A vs `150.00` in B | | Eve | Not present in Table B at all | | Grace | Amount differs: `90.75` in A vs `91.00` in B | | Heidi | Not present in Table B at all | Changing `rounding` from `2` to `0` in the config causes both `150.25` and `90.75` to round to the same values as their B counterparts (`150` and `91`), bringing the match count up to **8**. --- ## Dependencies | Package | Used for | |---|---| | `pandas` | DataFrame I/O and all vectorised operations | | `openpyxl` | Excel file reading/writing (pandas backend) | | `click` | CLI framework — prompts, options, colours | | `streamlit` | Web UI | All managed by `uv`; no manual `pip install` needed.