table-comparator / tests /test_comparator.py
Jakub
Add fuzzy near-match suggestions for numeric columns
fe0d1b4
Raw History Blame Contribute Delete
42.8 kB
"""Tests for comparator.py – normalisation, key-matching, warnings, edges.""" from pathlib import Path import pandas as pd import pytest from table_comparator.comparator import NearMatch, compare from table_comparator.config import ColumnMapping, ComparisonConfig # --------------------------------------------------------------------------- # helper # --------------------------------------------------------------------------- def _cfg(columns: list[ColumnMapping]) -> ComparisonConfig: return ComparisonConfig(columns=columns) # --------------------------------------------------------------------------- # text matching # --------------------------------------------------------------------------- class TestTextMatching: def test_exact_match(self): a = pd.DataFrame({"name": ["Alice", "Bob", "Charlie"]}) b = pd.DataFrame({"name": ["Bob", "Alice"]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name")]), a, b ) assert result.match_count == 2 assert list(result.unmatched["name"]) == ["Charlie"] def test_whitespace_is_stripped(self): a = pd.DataFrame({"name": [" Alice ", " Bob"]}) b = pd.DataFrame({"name": ["Alice", "Bob"]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name")]), a, b ) assert result.match_count == 2 def test_case_sensitive_by_default(self): a = pd.DataFrame({"name": ["alice"]}) b = pd.DataFrame({"name": ["Alice"]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name", case_sensitive=True)]), a, b, ) assert result.match_count == 0 def test_case_insensitive(self): a = pd.DataFrame({"name": ["alice", "BOB"]}) b = pd.DataFrame({"name": ["Alice", "bob"]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name", case_sensitive=False)]), a, b, ) assert result.match_count == 2 def test_case_insensitive_with_whitespace(self): a = pd.DataFrame({"name": [" ALICE "]}) b = pd.DataFrame({"name": ["alice"]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name", case_sensitive=False)]), a, b, ) assert result.match_count == 1 # --------------------------------------------------------------------------- # number matching # --------------------------------------------------------------------------- class TestNumberMatching: def test_exact_numbers_match(self): a = pd.DataFrame({"val": [100.50, 200.75]}) b = pd.DataFrame({"val": [100.50, 200.75, 300.00]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 def test_rounding_zero_makes_values_match(self): a = pd.DataFrame({"val": [150.25, 90.75]}) b = pd.DataFrame({"val": [150.00, 91.00]}) # raw β†’ no match assert compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ).match_count == 0 # rounding=0 β†’ 150.25β†’150, 150.00β†’150 and 90.75β†’91, 91.00β†’91 assert compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=0)]), a, b, ).match_count == 2 def test_rounding_one_decimal(self): a = pd.DataFrame({"val": [10.44]}) b = pd.DataFrame({"val": [10.40]}) # both round to 10.4 result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=1)]), a, b, ) assert result.match_count == 1 def test_rounding_two_decimals_preserves_difference(self): a = pd.DataFrame({"val": [150.25]}) b = pd.DataFrame({"val": [150.00]}) # rounding=2 keeps 150.25 vs 150.00 β†’ still different result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=2)]), a, b, ) assert result.match_count == 0 def test_string_numbers_are_coerced(self): a = pd.DataFrame({"val": ["100", "200"]}) b = pd.DataFrame({"val": ["100", "200"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 def test_unparseable_number_warns(self): a = pd.DataFrame({"val": ["100", "abc", "200"]}) b = pd.DataFrame({"val": ["100", "200"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 assert len(result.warnings) == 1 assert "val" in result.warnings[0] assert "1 value(s) could not be parsed" in result.warnings[0] assert "Row 1" in result.warnings[0] # Should show the row index assert "'abc'" in result.warnings[0] # Should show the actual value def test_negative_numbers_match(self): """Negative numbers match correctly.""" a = pd.DataFrame({"val": [-100.50, -200.75, 50.00]}) b = pd.DataFrame({"val": [-100.50, -200.75, 50.00]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 3 def test_negative_numbers_with_rounding(self): """Negative numbers round correctly.""" a = pd.DataFrame({"val": [-150.25, -90.75]}) b = pd.DataFrame({"val": [-150.00, -91.00]}) # Without rounding, no match assert compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ).match_count == 0 # With rounding=0: -150.25β†’-150, -90.75β†’-91 assert compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=0)]), a, b, ).match_count == 2 def test_thousand_separator_us_format(self): """Numbers with comma thousand separators parse correctly (US format).""" a = pd.DataFrame({"val": ["1,234.56", "2,210.21", "4,800.00"]}) b = pd.DataFrame({"val": ["1234.56", "2210.21", "4800.00"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 3 assert len(result.warnings) == 0 def test_thousand_separator_european_format(self): """Numbers with dot thousand separators parse correctly (European format).""" a = pd.DataFrame({"val": ["1.234,56", "2.210,21", "4.800,00"]}) b = pd.DataFrame({"val": ["1234.56", "2210.21", "4800.00"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", decimal_separator_a=",")]), a, b, ) assert result.match_count == 3 assert len(result.warnings) == 0 def test_thousand_separator_space_format(self): """Numbers with space thousand separators parse correctly.""" a = pd.DataFrame({"val": ["1 234,56", "2 210,21", "4 800,00"]}) b = pd.DataFrame({"val": ["1234.56", "2210.21", "4800.00"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", decimal_separator_a=",")]), a, b, ) assert result.match_count == 3 assert len(result.warnings) == 0 def test_thousand_separator_swiss_format(self): """Numbers with apostrophe thousand separators parse correctly (Swiss format).""" a = pd.DataFrame({"val": ["1'234.56", "2'210.21", "4'800.00"]}) b = pd.DataFrame({"val": ["1234.56", "2210.21", "4800.00"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 3 assert len(result.warnings) == 0 def test_thousand_separator_with_negative(self): """Negative numbers with thousand separators parse correctly.""" a = pd.DataFrame({"val": ["-1,234.56", "-2,210.21"]}) b = pd.DataFrame({"val": ["-1234.56", "-2210.21"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 assert len(result.warnings) == 0 def test_thousand_separator_different_per_side(self): """Different decimal separators can be specified for each table.""" # Table A: European format (1.234,56) # Table B: US format (1,234.56) a = pd.DataFrame({"val": ["1.234,56", "2.210,21"]}) b = pd.DataFrame({"val": ["1,234.56", "2,210.21"]}) result = compare( _cfg([ColumnMapping( column_a="val", column_b="val", col_type="number", decimal_separator_a=",", decimal_separator_b="." )]), a, b, ) assert result.match_count == 2 assert len(result.warnings) == 0 def test_thousand_separator_with_rounding(self): """Thousand separators work with rounding.""" a = pd.DataFrame({"val": ["1,234.567", "2,210.211"]}) b = pd.DataFrame({"val": ["1234.57", "2210.21"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=2)]), a, b, ) assert result.match_count == 2 assert len(result.warnings) == 0 def test_negative_string_numbers_coerced(self): """Negative numbers as strings are parsed correctly.""" a = pd.DataFrame({"val": ["-100", "-200.50"]}) b = pd.DataFrame({"val": ["-100", "-200.50"]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 def test_mixed_positive_negative_numbers(self): """Positive and negative numbers don't cross-match.""" a = pd.DataFrame({"val": [100, -100, 200]}) b = pd.DataFrame({"val": [-100, 100]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 # 100 and -100 match assert result.unmatched["val"].iloc[0] == 200 def test_negative_in_composite_key(self): """Negative numbers work correctly in multi-column keys.""" a = pd.DataFrame({"name": ["Alice", "Bob"], "amount": [-100.50, 200.75]}) b = pd.DataFrame({"name": ["Alice", "Bob"], "amount": [-100.50, 200.75]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="amount", column_b="amount", col_type="number"), ]), a, b, ) assert result.match_count == 2 def test_zero_vs_negative_zero(self): """Zero and negative zero are treated as equal (IEEE 754 behavior).""" a = pd.DataFrame({"val": [0.0, -0.0]}) b = pd.DataFrame({"val": [-0.0, 0.0]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) # Both 0.0 and -0.0 stringify to "0.0" assert result.match_count == 2 # --------------------------------------------------------------------------- # date matching # --------------------------------------------------------------------------- class TestDateMatching: def test_different_formats_match(self): a = pd.DataFrame({"date": ["15/01/2024", "20/02/2024"]}) b = pd.DataFrame({"date": ["2024-01-15", "2024-02-20"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%d/%m/%Y", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 2 def test_dot_separated_format(self): a = pd.DataFrame({"date": ["15.01.2024"]}) b = pd.DataFrame({"date": ["2024-01-15"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%d.%m.%Y", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 1 def test_compact_format_no_separators(self): a = pd.DataFrame({"date": ["20240115"]}) b = pd.DataFrame({"date": ["15/01/2024"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%Y%m%d", date_format_b="%d/%m/%Y", )]), a, b, ) assert result.match_count == 1 def test_us_vs_eu_format(self): """01/15/2024 (US m/d/Y) vs 15-01-2024 (EU d-m-Y) β€” same date.""" a = pd.DataFrame({"date": ["01/15/2024"]}) b = pd.DataFrame({"date": ["15-01-2024"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%m/%d/%Y", date_format_b="%d-%m-%Y", )]), a, b, ) assert result.match_count == 1 def test_same_format_both_sides(self): a = pd.DataFrame({"date": ["2024-01-15"]}) b = pd.DataFrame({"date": ["2024-01-15", "2024-02-20"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%Y-%m-%d", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 1 def test_date_mismatch_no_match(self): a = pd.DataFrame({"date": ["2024-01-15"]}) b = pd.DataFrame({"date": ["2024-01-16"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%Y-%m-%d", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 0 def test_unparseable_date_warns(self): a = pd.DataFrame({"date": ["2024-01-15", "not-a-date", "2024-03-01"]}) b = pd.DataFrame({"date": ["2024-01-15", "2024-03-01"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%Y-%m-%d", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 2 assert len(result.warnings) == 1 assert "1" in result.warnings[0] def test_unparseable_date_on_b_side_warns(self): a = pd.DataFrame({"date": ["2024-01-15"]}) b = pd.DataFrame({"date": ["2024-01-15", "garbage"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%Y-%m-%d", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 1 assert len(result.warnings) == 1 assert "Table B" in result.warnings[0] def test_two_digit_year_dot_format(self): """dd.mm.yy format (2-digit year) matches correctly.""" a = pd.DataFrame({"date": ["15.01.24", "20.02.24"]}) b = pd.DataFrame({"date": ["15.01.24", "20.02.24"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%d.%m.%y", date_format_b="%d.%m.%y", )]), a, b, ) assert result.match_count == 2 def test_two_digit_year_slash_format(self): """dd/mm/yy format (2-digit year) matches correctly.""" a = pd.DataFrame({"date": ["15/01/24"]}) b = pd.DataFrame({"date": ["15/01/24", "20/02/24"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%d/%m/%y", date_format_b="%d/%m/%y", )]), a, b, ) assert result.match_count == 1 def test_two_digit_vs_four_digit_year_match(self): """2-digit year in A matches 4-digit year in B for the same date.""" a = pd.DataFrame({"date": ["15.01.24", "20.02.25"]}) b = pd.DataFrame({"date": ["2024-01-15", "2025-02-20"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%d.%m.%y", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 2 def test_two_digit_year_us_format(self): """US format mm/dd/yy (2-digit year) works correctly.""" a = pd.DataFrame({"date": ["01/15/24"]}) b = pd.DataFrame({"date": ["2024-01-15"]}) result = compare( _cfg([ColumnMapping( column_a="date", column_b="date", col_type="date", date_format_a="%m/%d/%y", date_format_b="%Y-%m-%d", )]), a, b, ) assert result.match_count == 1 # --------------------------------------------------------------------------- # null / NaN handling # --------------------------------------------------------------------------- class TestNullHandling: def test_nan_matches_nan_in_number_column(self): a = pd.DataFrame({"val": [1.0, float("nan")]}) b = pd.DataFrame({"val": [1.0, float("nan")]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 2 def test_nan_does_not_match_zero(self): a = pd.DataFrame({"val": [float("nan")]}) b = pd.DataFrame({"val": [0.0]}) result = compare( _cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]), a, b, ) assert result.match_count == 0 def test_null_text_matches_null_text(self): a = pd.DataFrame({"name": ["Alice", None]}) b = pd.DataFrame({"name": ["Alice", None]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name", col_type="text")]), a, b, ) assert result.match_count == 2 def test_null_text_does_not_match_empty_string(self): a = pd.DataFrame({"name": [None]}) b = pd.DataFrame({"name": [""]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name", col_type="text")]), a, b, ) # stripped empty string is "" which is not the null sentinel assert result.match_count == 0 def test_null_in_composite_key_position(self): """NaN in one key slot must not collapse distinctions in other slots.""" a = pd.DataFrame({"name": ["Alice", "Alice"], "val": [1.0, float("nan")]}) b = pd.DataFrame({"name": ["Alice"], "val": [1.0]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number"), ]), a, b, ) # only the row with val=1.0 matches; val=NaN row does not assert result.match_count == 1 # --------------------------------------------------------------------------- # multiple mappings – all must agree # --------------------------------------------------------------------------- class TestMultipleMappings: def test_all_columns_must_match(self): a = pd.DataFrame({"name": ["Alice", "Alice"], "val": [100, 200]}) b = pd.DataFrame({"name": ["Alice"], "val": [100]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number"), ]), a, b, ) assert result.match_count == 1 assert result.unmatched["val"].iloc[0] == 200 def test_different_column_names_across_tables(self): a = pd.DataFrame({"Name": ["Alice"], "Amount": [100.0]}) b = pd.DataFrame({"Customer": ["Alice"], "Value": [100.0]}) result = compare( _cfg([ ColumnMapping(column_a="Name", column_b="Customer", col_type="text"), ColumnMapping(column_a="Amount", column_b="Value", col_type="number"), ]), a, b, ) assert result.match_count == 1 def test_mixed_types_in_mappings(self): a = pd.DataFrame({ "name": ["Alice"], "date": ["15/01/2024"], "amount": [100.0], }) b = pd.DataFrame({ "customer": ["Alice"], "txn_date": ["2024-01-15"], "value": [100.0], }) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="customer", col_type="text"), ColumnMapping( column_a="date", column_b="txn_date", col_type="date", date_format_a="%d/%m/%Y", date_format_b="%Y-%m-%d", ), ColumnMapping(column_a="amount", column_b="value", col_type="number"), ]), a, b, ) assert result.match_count == 1 # --------------------------------------------------------------------------- # error handling # --------------------------------------------------------------------------- class TestErrors: def test_no_mappings_raises(self): a = pd.DataFrame({"x": [1]}) b = pd.DataFrame({"x": [1]}) with pytest.raises(ValueError, match="No column mappings"): compare(_cfg([]), a, b) def test_missing_column_in_a_raises(self): a = pd.DataFrame({"x": [1]}) b = pd.DataFrame({"y": [1]}) with pytest.raises(ValueError, match="missing"): compare(_cfg([ColumnMapping(column_a="missing", column_b="y")]), a, b) def test_missing_column_in_b_raises(self): a = pd.DataFrame({"x": [1]}) b = pd.DataFrame({"y": [1]}) with pytest.raises(ValueError, match="missing"): compare(_cfg([ColumnMapping(column_a="x", column_b="missing")]), a, b) # --------------------------------------------------------------------------- # edge cases # --------------------------------------------------------------------------- class TestEdgeCases: def test_all_rows_match(self): a = pd.DataFrame({"x": [1, 2, 3]}) b = pd.DataFrame({"x": [1, 2, 3, 4]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count == 3 assert result.unmatch_count == 0 def test_no_rows_match(self): a = pd.DataFrame({"x": [1, 2, 3]}) b = pd.DataFrame({"x": [4, 5, 6]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count == 0 assert result.unmatch_count == 3 def test_empty_table_a(self): a = pd.DataFrame({"x": pd.Series([], dtype="int64")}) b = pd.DataFrame({"x": [1, 2]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.total == 0 assert result.match_count == 0 def test_empty_table_b(self): a = pd.DataFrame({"x": [1, 2]}) b = pd.DataFrame({"x": pd.Series([], dtype="int64")}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count == 0 assert result.unmatch_count == 2 def test_duplicate_rows_in_a_count_aware(self): """Duplicates in A can only consume as many copies as exist in B.""" a = pd.DataFrame({"x": [1, 1, 2]}) b = pd.DataFrame({"x": [1]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) # B has one copy of 1 β†’ only one of the two 1-rows in A matches assert result.match_count == 1 assert result.unmatch_count == 2 def test_single_row_tables(self): a = pd.DataFrame({"x": [42]}) b = pd.DataFrame({"x": [42]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count == 1 # --------------------------------------------------------------------------- # ComparisonResult properties # --------------------------------------------------------------------------- class TestComparisonResult: def test_matched_and_unmatched_are_disjoint(self): a = pd.DataFrame({"x": [1, 2, 3, 4]}) b = pd.DataFrame({"x": [2, 4]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count + result.unmatch_count == result.total assert set(result.matched["x"]) == {2, 4} assert set(result.unmatched["x"]) == {1, 3} def test_matched_resets_index(self): a = pd.DataFrame({"x": [10, 20, 30]}) b = pd.DataFrame({"x": [20]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert list(result.matched.index) == [0] assert list(result.unmatched.index) == [0, 1] # --------------------------------------------------------------------------- # integration – shipped sample data # --------------------------------------------------------------------------- class TestSampleData: def test_sample_config_rounding_2(self, df_a, df_b): """The shipped sample with rounding=2 β†’ 6 matched, 4 unmatched.""" cfg = ComparisonConfig.load( str(Path(__file__).resolve().parent.parent / "sample_data" / "sample_config.json") ) result = compare(cfg, df_a, df_b) assert result.match_count == 6 assert result.unmatch_count == 4 assert set(result.unmatched["Name"]) == {"David", "Eve", "Grace", "Heidi"} def test_sample_rounding_0_matches_david_and_grace(self, df_a, df_b): """Rounding=0 β†’ David and Grace match too β†’ 8 matched.""" cfg = _cfg([ ColumnMapping(column_a="Name", column_b="Customer", col_type="text"), ColumnMapping( column_a="Transaction Date", column_b="Date", col_type="date", date_format_a="%d/%m/%Y", date_format_b="%Y-%m-%d", ), ColumnMapping(column_a="Amount", column_b="Value", col_type="number", rounding=0), ]) result = compare(cfg, df_a, df_b) assert result.match_count == 8 assert set(result.unmatched["Name"]) == {"Eve", "Heidi"} def test_sample_name_only_case_insensitive(self, df_a, df_b): """Name-only, case-insensitive β†’ 8 match (Eve + Heidi absent from B).""" cfg = _cfg([ ColumnMapping(column_a="Name", column_b="Customer", col_type="text", case_sensitive=False), ]) result = compare(cfg, df_a, df_b) assert result.match_count == 8 # --------------------------------------------------------------------------- # bidirectional matching (Bβ†’A) # --------------------------------------------------------------------------- class TestBidirectional: def test_b_side_mask_computed(self): """B-side mask identifies which B rows exist in A.""" a = pd.DataFrame({"x": [1, 2, 3]}) b = pd.DataFrame({"x": [2, 3, 4]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) # Aβ†’B: 1 not in B, 2 and 3 match assert result.match_count == 2 assert result.unmatch_count == 1 # Bβ†’A: 4 not in A, 2 and 3 match assert result.match_count_b == 2 assert result.unmatch_count_b == 1 def test_rows_only_in_b_identified(self): """Rows only present in B appear in unmatched_b.""" a = pd.DataFrame({"name": ["Alice"]}) b = pd.DataFrame({"name": ["Alice", "Zara"]}) result = compare( _cfg([ColumnMapping(column_a="name", column_b="name")]), a, b, ) assert result.unmatch_count_b == 1 assert list(result.unmatched_b["name"]) == ["Zara"] def test_symmetric_same_tables(self): """Identical tables β†’ all match both ways.""" a = pd.DataFrame({"x": [1, 2, 3]}) b = pd.DataFrame({"x": [1, 2, 3]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count == 3 assert result.unmatch_count == 0 assert result.match_count_b == 3 assert result.unmatch_count_b == 0 def test_asymmetric_extra_rows_in_b(self): """Extra rows in B are identified as unmatched_b.""" a = pd.DataFrame({"x": [1]}) b = pd.DataFrame({"x": [1, 2, 3]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.match_count == 1 assert result.unmatch_count == 0 assert result.match_count_b == 1 assert result.unmatch_count_b == 2 assert set(result.unmatched_b["x"]) == {2, 3} def test_count_aware_duplicates_b_to_a(self): """Duplicates in B can only consume as many copies as exist in A.""" a = pd.DataFrame({"x": [1]}) b = pd.DataFrame({"x": [1, 1, 2]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) # A has one copy of 1 β†’ only one of B's two 1-rows matches assert result.match_count_b == 1 assert result.unmatch_count_b == 2 def test_row_numbers_1_based(self): """Row numbers in convenience properties are 1-based from original position.""" a = pd.DataFrame({"x": [10, 20, 30, 40]}) b = pd.DataFrame({"x": [20, 40]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) # Unmatched A rows are at original positions 1 and 3 (0-indexed) β†’ Row 1, 3 assert list(result.unmatched["Row"]) == [1, 3] # Matched A rows are at original positions 2 and 4 (0-indexed 1 and 3) β†’ Row 2, 4 assert list(result.matched["Row"]) == [2, 4] def test_row_numbers_b_side(self): """Row numbers on B side are correct.""" a = pd.DataFrame({"x": [1, 2]}) b = pd.DataFrame({"x": [2, 3, 4]}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) # B row 1 (x=2) matches, rows 2-3 (x=3,4) don't assert list(result.matched_b["Row"]) == [1] assert list(result.unmatched_b["Row"]) == [2, 3] def test_empty_tables_bidirectional(self): """Empty tables produce correct bidirectional results.""" a = pd.DataFrame({"x": pd.Series([], dtype="int64")}) b = pd.DataFrame({"x": pd.Series([], dtype="int64")}) result = compare( _cfg([ColumnMapping(column_a="x", column_b="x", col_type="number")]), a, b, ) assert result.total == 0 assert result.total_b == 0 assert result.match_count == 0 assert result.match_count_b == 0 # --------------------------------------------------------------------------- # near-match suggestions # --------------------------------------------------------------------------- class TestNearMatches: def test_no_margin_configured(self): """Without margin, near_matches is empty.""" a = pd.DataFrame({"name": ["Alice"], "val": [150.25]}) b = pd.DataFrame({"name": ["Alice"], "val": [150.00]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number"), ]), a, b, ) assert result.near_matches == [] def test_margin_finds_candidate(self): """A row within margin is reported as near-match.""" a = pd.DataFrame({"name": ["Alice"], "val": [150.25]}) b = pd.DataFrame({"name": ["Alice"], "val": [150.00]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=1.0), ]), a, b, ) assert len(result.near_matches) == 1 nm = result.near_matches[0] assert nm.row_a == 1 assert nm.row_b == 1 assert nm.diffs == {"val": pytest.approx(0.25)} def test_outside_margin_no_match(self): """Difference exceeding margin is not a near-match.""" a = pd.DataFrame({"name": ["Alice"], "val": [150.25]}) b = pd.DataFrame({"name": ["Alice"], "val": [150.00]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=0.1), ]), a, b, ) assert result.near_matches == [] def test_exact_matched_rows_excluded(self): """Rows that already exact-match are not in the near-match pool.""" a = pd.DataFrame({"name": ["Alice", "Bob"], "val": [100.0, 150.25]}) b = pd.DataFrame({"name": ["Alice", "Bob"], "val": [100.0, 150.00]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=1.0), ]), a, b, ) # Alice exact-matches; Bob is near-match assert result.match_count == 1 assert len(result.near_matches) == 1 nm = result.near_matches[0] assert nm.row_a == 2 # Bob assert nm.row_b == 2 def test_multiple_candidates(self): """One A row can have multiple B candidates.""" a = pd.DataFrame({"name": ["Alice"], "val": [100.0]}) b = pd.DataFrame({"name": ["Alice", "Alice"], "val": [99.5, 100.3]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=1.0), ]), a, b, ) # None exact-match (name+val composite key differs) assert result.match_count == 0 assert len(result.near_matches) == 2 b_rows = sorted(nm.row_b for nm in result.near_matches) assert b_rows == [1, 2] def test_multiple_numeric_columns_all_within_margin(self): """All numeric columns must be within their respective margins.""" a = pd.DataFrame({"name": ["Alice"], "x": [100.0], "y": [200.0]}) b = pd.DataFrame({"name": ["Alice"], "x": [100.5], "y": [200.3]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="x", column_b="x", col_type="number", margin=1.0), ColumnMapping(column_a="y", column_b="y", col_type="number", margin=1.0), ]), a, b, ) assert len(result.near_matches) == 1 assert result.near_matches[0].diffs == { "x": pytest.approx(-0.5), "y": pytest.approx(-0.3), } def test_multiple_numeric_columns_one_outside_margin(self): """If any numeric column exceeds its margin, no near-match.""" a = pd.DataFrame({"name": ["Alice"], "x": [100.0], "y": [200.0]}) b = pd.DataFrame({"name": ["Alice"], "x": [100.5], "y": [205.0]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="x", column_b="x", col_type="number", margin=1.0), ColumnMapping(column_a="y", column_b="y", col_type="number", margin=1.0), ]), a, b, ) assert result.near_matches == [] def test_non_numeric_columns_must_match_exactly(self): """Different text/date values prevent near-match even if number is within margin.""" a = pd.DataFrame({"name": ["Alice"], "val": [100.0]}) b = pd.DataFrame({"name": ["Bob"], "val": [100.5]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=1.0), ]), a, b, ) assert result.near_matches == [] def test_all_columns_numeric(self): """When all columns are numeric with margin, cross-check all unmatched pairs.""" a = pd.DataFrame({"x": [100.0], "y": [200.0]}) b = pd.DataFrame({"x": [100.5], "y": [200.3]}) result = compare( _cfg([ ColumnMapping(column_a="x", column_b="x", col_type="number", margin=1.0), ColumnMapping(column_a="y", column_b="y", col_type="number", margin=1.0), ]), a, b, ) assert len(result.near_matches) == 1 def test_margin_with_rounding_interaction(self): """Margin applies to already-rounded values.""" a = pd.DataFrame({"name": ["Alice"], "val": [150.254]}) b = pd.DataFrame({"name": ["Alice"], "val": [150.004]}) # rounding=2: 150.254β†’150.25, 150.004β†’150.00 β†’ diff = 0.25 result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=2, margin=0.3), ]), a, b, ) assert len(result.near_matches) == 1 assert result.near_matches[0].diffs["val"] == pytest.approx(0.25) def test_margin_zero_only_exact_diff(self): """Margin=0 only matches rows with exactly equal numeric values (already exact-matched).""" a = pd.DataFrame({"name": ["Alice"], "val": [100.0]}) b = pd.DataFrame({"name": ["Alice"], "val": [100.01]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=0.0), ]), a, b, ) assert result.near_matches == [] def test_nan_in_margin_column_no_near_match(self): """NaN in a margin column cannot produce a near-match.""" a = pd.DataFrame({"name": ["Alice"], "val": [float("nan")]}) b = pd.DataFrame({"name": ["Alice"], "val": [100.0]}) result = compare( _cfg([ ColumnMapping(column_a="name", column_b="name", col_type="text"), ColumnMapping(column_a="val", column_b="val", col_type="number", margin=1000.0), ]), a, b, ) assert result.near_matches == [] def test_sample_data_margin(self, df_a, df_b): """David (150.25 vs 150.00) and Grace (90.75 vs 91.00) appear as near-matches.""" cfg = ComparisonConfig(columns=[ ColumnMapping(column_a="Name", column_b="Customer", col_type="text"), ColumnMapping( column_a="Transaction Date", column_b="Date", col_type="date", date_format_a="%d/%m/%Y", date_format_b="%Y-%m-%d", ), ColumnMapping( column_a="Amount", column_b="Value", col_type="number", rounding=2, margin=0.5, ), ]) result = compare(cfg, df_a, df_b) # David and Grace should be near-matches near_a_rows = {nm.row_a for nm in result.near_matches} # Find David and Grace's row numbers (1-based) names_a = df_a["Name"].tolist() david_row = names_a.index("David") + 1 grace_row = names_a.index("Grace") + 1 assert david_row in near_a_rows assert grace_row in near_a_rows def test_numeric_column_without_margin_treated_as_exact(self): """A number column without margin is treated as exact-match requirement.""" a = pd.DataFrame({"x": [100.0], "y": [200.0]}) b = pd.DataFrame({"x": [100.5], "y": [200.0]}) # x has margin, y does not β†’ y must match exactly for near-match result = compare( _cfg([ ColumnMapping(column_a="x", column_b="x", col_type="number", margin=1.0), ColumnMapping(column_a="y", column_b="y", col_type="number"), ]), a, b, ) # y matches exactly, x is within margin β†’ near-match assert len(result.near_matches) == 1 def test_numeric_column_without_margin_blocks_near_match(self): """A number column without margin that doesn't match blocks near-match.""" a = pd.DataFrame({"x": [100.0], "y": [200.0]}) b = pd.DataFrame({"x": [100.5], "y": [200.5]}) result = compare( _cfg([ ColumnMapping(column_a="x", column_b="x", col_type="number", margin=1.0), ColumnMapping(column_a="y", column_b="y", col_type="number"), ]), a, b, ) # y doesn't match exactly β†’ no near-match assert result.near_matches == []
"""Tests for comparator.py – normalisation, key-matching, warnings, edges."""
from pathlib import Path
import pandas as pd
import pytest
from table_comparator.comparator import NearMatch, compare
from table_comparator.config import ColumnMapping, ComparisonConfig
# ---------------------------------------------------------------------------
# helper
# ---------------------------------------------------------------------------
def _cfg(columns: list[ColumnMapping]) -> ComparisonConfig:
return ComparisonConfig(columns=columns)
# ---------------------------------------------------------------------------
# text matching
# ---------------------------------------------------------------------------
class TestTextMatching:
def test_exact_match(self):
a = pd.DataFrame({"name": ["Alice", "Bob", "Charlie"]})
b = pd.DataFrame({"name": ["Bob", "Alice"]})
result = compare(
_cfg([ColumnMapping(column_a="name", column_b="name")]), a, b
)
assert result.match_count == 2
assert list(result.unmatched["name"]) == ["Charlie"]
def test_whitespace_is_stripped(self):
a = pd.DataFrame({"name": [" Alice ", " Bob"]})
b = pd.DataFrame({"name": ["Alice", "Bob"]})
result = compare(
_cfg([ColumnMapping(column_a="name", column_b="name")]), a, b
)
assert result.match_count == 2
def test_case_sensitive_by_default(self):
a = pd.DataFrame({"name": ["alice"]})
b = pd.DataFrame({"name": ["Alice"]})
result = compare(
_cfg([ColumnMapping(column_a="name", column_b="name", case_sensitive=True)]),
a, b,
)
assert result.match_count == 0
def test_case_insensitive(self):
a = pd.DataFrame({"name": ["alice", "BOB"]})
b = pd.DataFrame({"name": ["Alice", "bob"]})
result = compare(
_cfg([ColumnMapping(column_a="name", column_b="name", case_sensitive=False)]),
a, b,
)
assert result.match_count == 2
def test_case_insensitive_with_whitespace(self):
a = pd.DataFrame({"name": [" ALICE "]})
b = pd.DataFrame({"name": ["alice"]})
result = compare(
_cfg([ColumnMapping(column_a="name", column_b="name", case_sensitive=False)]),
a, b,
)
assert result.match_count == 1
# ---------------------------------------------------------------------------
# number matching
# ---------------------------------------------------------------------------
class TestNumberMatching:
def test_exact_numbers_match(self):
a = pd.DataFrame({"val": [100.50, 200.75]})
b = pd.DataFrame({"val": [100.50, 200.75, 300.00]})
result = compare(
_cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]),
a, b,
)
assert result.match_count == 2
def test_rounding_zero_makes_values_match(self):
a = pd.DataFrame({"val": [150.25, 90.75]})
b = pd.DataFrame({"val": [150.00, 91.00]})
# raw β†’ no match
assert compare(
_cfg([ColumnMapping(column_a="val", column_b="val", col_type="number")]),
a, b,
).match_count == 0
# rounding=0 β†’ 150.25β†’150, 150.00β†’150 and 90.75β†’91, 91.00β†’91
assert compare(
_cfg([ColumnMapping(column_a="val", column_b="val", col_type="number", rounding=0)]),
a, b,
).match_count == 2