File size: 17,322 Bytes
531d507
 
 
 
 
 
 
 
 
 
 
4f3d4cf
 
 
 
 
531d507
 
 
 
fe0d1b4
531d507
 
fe0d1b4
531d507
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
fe0d1b4
 
 
 
 
 
 
 
 
7bd9057
 
 
 
 
 
 
531d507
 
 
 
 
 
7bd9057
 
531d507
 
fe0d1b4
531d507
 
7bd9057
531d507
 
 
7bd9057
 
531d507
 
 
7bd9057
 
531d507
 
 
 
 
 
 
 
 
 
 
 
 
7bd9057
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
531d507
 
 
 
 
 
0c971d1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
531d507
 
 
 
 
 
 
 
 
 
 
 
 
0c971d1
 
 
 
531d507
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
fe0d1b4
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
531d507
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
fe0d1b4
 
531d507
 
 
 
 
 
 
 
 
 
 
 
 
0c971d1
 
 
 
 
 
531d507
0c971d1
 
 
 
 
 
 
 
 
 
 
531d507
0c971d1
531d507
0c971d1
 
 
 
531d507
0c971d1
 
 
 
 
 
 
 
 
 
 
531d507
0c971d1
531d507
0c971d1
 
 
531d507
fe0d1b4
 
531d507
 
 
 
 
 
 
 
 
 
 
 
7bd9057
4f3d4cf
 
 
 
 
 
531d507
7bd9057
 
 
 
 
fe0d1b4
 
 
 
531d507
 
 
7bd9057
 
531d507
 
fe0d1b4
531d507
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
"""Core comparison logic.

Algorithm
---------
1. For every ``ColumnMapping`` in the config, normalise the corresponding
   column in both tables according to its declared type (text / number / date).
2. Stringfy each normalised column; replace every NaN / NaT with a fixed
   sentinel so that two missing values on the *same* key position still
   compare as equal.
3. Concatenate all per-column key strings with a null-byte separator into one
   composite key per row.
4. Count-aware multiset match: for each composite key count how many copies
   exist in B, then within each group of identical keys in A number them
   0, 1, 2 … with ``groupby.cumcount()``.  A row matches only when its
   within-group index is strictly less than the count available in B.
   Still O(n + m) β€” only hash-maps and vectorised comparisons.
"""

from __future__ import annotations

from collections import defaultdict
from dataclasses import dataclass, field

import numpy as np
import pandas as pd

from table_comparator.config import ColumnMapping, ComparisonConfig

# Sentinel that replaces every NaN / NaT / pd.NA before hashing.
# Uses null bytes so it cannot collide with real user data.
_NULL_SENTINEL = "\x00NULL\x00"

# Separator between the individual key components.
_KEY_SEP = "\x00\x00"


# ---------------------------------------------------------------------------
# Result container
# ---------------------------------------------------------------------------


@dataclass
class NearMatch:
    """A candidate near-match between an unmatched A row and an unmatched B row."""

    row_a: int                  # 1-based row number in A
    row_b: int                  # 1-based row number in B
    diffs: dict[str, float]     # column_a name -> signed difference (a_val - b_val)


def _with_row_number(df: pd.DataFrame, mask: pd.Series) -> pd.DataFrame:
    """Return filtered rows with a leading 1-based ``Row`` column."""
    out = df[mask].copy()
    out.insert(0, "Row", out.index + 1)
    return out.reset_index(drop=True)


@dataclass
class ComparisonResult:
    """Outcome of a single comparison run."""

    df_a: pd.DataFrame          # original Table A (all columns)
    matched_mask: pd.Series     # boolean; True  β†’ row exists in B
    df_b: pd.DataFrame          # original Table B (all columns)
    matched_mask_b: pd.Series   # boolean; True  β†’ row exists in A
    config: ComparisonConfig
    warnings: list[str] = field(default_factory=list)
    near_matches: list[NearMatch] = field(default_factory=list)

    # ------------------------------------------------------------------
    # A-side convenience views
    # ------------------------------------------------------------------
    @property
    def matched(self) -> pd.DataFrame:
        """Rows from A that have a match in B (with 1-based Row column)."""
        return _with_row_number(self.df_a, self.matched_mask)

    @property
    def unmatched(self) -> pd.DataFrame:
        """Rows from A that have NO match in B (with 1-based Row column)."""
        return _with_row_number(self.df_a, ~self.matched_mask)

    @property
    def total(self) -> int:
        return len(self.df_a)

    @property
    def match_count(self) -> int:
        return int(self.matched_mask.sum())

    @property
    def unmatch_count(self) -> int:
        return self.total - self.match_count

    # ------------------------------------------------------------------
    # B-side convenience views
    # ------------------------------------------------------------------
    @property
    def matched_b(self) -> pd.DataFrame:
        """Rows from B that have a match in A (with 1-based Row column)."""
        return _with_row_number(self.df_b, self.matched_mask_b)

    @property
    def unmatched_b(self) -> pd.DataFrame:
        """Rows from B that have NO match in A (with 1-based Row column)."""
        return _with_row_number(self.df_b, ~self.matched_mask_b)

    @property
    def total_b(self) -> int:
        return len(self.df_b)

    @property
    def match_count_b(self) -> int:
        return int(self.matched_mask_b.sum())

    @property
    def unmatch_count_b(self) -> int:
        return self.total_b - self.match_count_b


# ---------------------------------------------------------------------------
# Normalisation helpers
# ---------------------------------------------------------------------------


def _strip_thousand_separators(series: pd.Series, decimal_separator: str = ".") -> pd.Series:
    """Remove thousand separators from number strings to enable parsing.

    Handles multiple locale formats by using an explicit decimal separator:
    - US/UK format: 1,234.56 (comma thousands, dot decimal) β†’ decimal_separator="."
    - European format: 1.234,56 or 1 234,56 (dot/space thousands, comma decimal) β†’ decimal_separator=","
    - Swiss format: 1'234.56 (apostrophe thousands, dot decimal) β†’ decimal_separator="."

    Args:
        series: The pandas Series containing number strings
        decimal_separator: The character used as decimal separator ("." or ",")

    Strategy:
    1. Convert to string
    2. Remove all thousand separators (common separators that are NOT the decimal separator)
    3. Normalize decimal separator to dot for pd.to_numeric
    """
    # Convert to string, preserving nulls
    str_series = series.astype("string")

    def clean_number(val: str) -> str:
        if pd.isna(val) or val == "<NA>":
            return val

        val = val.strip()
        if not val:
            return val

        # Define common thousand separators
        thousand_separators = [",", ".", " ", "'", "_"]

        # Remove the decimal separator from the list of thousand separators
        if decimal_separator in thousand_separators:
            thousand_separators.remove(decimal_separator)

        # Remove all thousand separators
        cleaned = val
        for sep in thousand_separators:
            cleaned = cleaned.replace(sep, "")

        # Normalize decimal separator to dot (required by pd.to_numeric)
        if decimal_separator == ",":
            cleaned = cleaned.replace(",", ".")

        return cleaned

    return str_series.apply(clean_number)


def _normalize(series: pd.Series, mapping: ColumnMapping, side: str) -> pd.Series:
    """Normalise one column according to its declared type.

    *side* is ``"a"`` or ``"b"`` β€” used to pick the correct date format.
    """
    if mapping.col_type == "text":
        # Use pandas StringDtype so that str methods preserve NA
        result = series.astype("string").str.strip()
        if not mapping.case_sensitive:
            result = result.str.lower()
        return result

    if mapping.col_type == "number":
        # Strip thousand separators before parsing to handle locale formats
        decimal_sep = mapping.decimal_separator_a if side == "a" else mapping.decimal_separator_b
        cleaned = _strip_thousand_separators(series, decimal_sep)
        result = pd.to_numeric(cleaned, errors="coerce")
        if mapping.rounding is not None:
            result = result.round(mapping.rounding)
        # Cast to float64 unconditionally.  to_numeric returns int64 when all
        # values parse cleanly but float64 when any value coerces to NaN.
        # The two dtypes stringify differently ("100" vs "100.0"), which would
        # break key matching between the two sides.
        return result.astype("float64")

    if mapping.col_type == "date":
        fmt = mapping.date_format_a if side == "a" else mapping.date_format_b
        return pd.to_datetime(series, format=fmt, errors="coerce")

    return series  # fallback – should not happen after validation


def _to_key_str(series: pd.Series) -> pd.Series:
    """Stringify a normalised series; replace every null with *_NULL_SENTINEL*.

    This makes NaN == NaN for key purposes while keeping everything vectorised.
    """
    null_mask = series.isna()
    result = series.astype(str)
    result.loc[null_mask] = _NULL_SENTINEL
    return result


# ---------------------------------------------------------------------------
# Near-match search
# ---------------------------------------------------------------------------


def _find_near_matches(
    config: ComparisonConfig,
    norm_columns_a: dict[str, pd.Series],
    norm_columns_b: dict[str, pd.Series],
    matched_mask: pd.Series,
    matched_mask_b: pd.Series,
) -> list[NearMatch]:
    """Find candidate near-matches among unmatched rows.

    For each unmatched A row, find all unmatched B rows where:
    - all non-numeric mapped columns match exactly (using normalised values)
    - every numeric column with a margin has abs(diff) <= margin

    Returns a list of NearMatch objects.
    """
    # Identify which number columns have a margin configured
    margin_mappings: list[ColumnMapping] = [
        m for m in config.columns
        if m.col_type == "number" and m.margin is not None
    ]
    if not margin_mappings:
        return []

    # Non-numeric mappings (must match exactly) + numeric without margin
    exact_mappings: list[ColumnMapping] = [
        m for m in config.columns
        if not (m.col_type == "number" and m.margin is not None)
    ]

    # Get unmatched indices
    unmatched_idx_a = matched_mask[~matched_mask].index.tolist()
    unmatched_idx_b = matched_mask_b[~matched_mask_b].index.tolist()

    if not unmatched_idx_a or not unmatched_idx_b:
        return []

    # Build composite exact-match key for grouping (non-margin columns)
    if exact_mappings:
        exact_parts_a = []
        exact_parts_b = []
        for m in exact_mappings:
            exact_parts_a.append(
                _to_key_str(norm_columns_a[m.column_a]).loc[unmatched_idx_a]
            )
            exact_parts_b.append(
                _to_key_str(norm_columns_b[m.column_b]).loc[unmatched_idx_b]
            )

        exact_key_a = exact_parts_a[0]
        for part in exact_parts_a[1:]:
            exact_key_a = exact_key_a + _KEY_SEP + part

        exact_key_b = exact_parts_b[0]
        for part in exact_parts_b[1:]:
            exact_key_b = exact_key_b + _KEY_SEP + part

        # Group unmatched B rows by their exact key
        b_groups: dict[str, list[int]] = defaultdict(list)
        for idx in unmatched_idx_b:
            b_groups[exact_key_b.loc[idx]].append(idx)
    else:
        # All columns are numeric with margin β€” every unmatched B is a candidate
        exact_key_a = None
        b_groups = None

    # Extract numeric values for margin columns
    num_vals_a = {
        m.column_a: norm_columns_a[m.column_a].loc[unmatched_idx_a]
        for m in margin_mappings
    }
    num_vals_b = {
        m.column_a: norm_columns_b[m.column_b].loc[unmatched_idx_b]
        for m in margin_mappings
    }

    results: list[NearMatch] = []

    for idx_a in unmatched_idx_a:
        # Get candidate B indices
        if b_groups is not None:
            key = exact_key_a.loc[idx_a]
            candidates = b_groups.get(key, [])
        else:
            candidates = unmatched_idx_b

        for idx_b in candidates:
            diffs: dict[str, float] = {}
            match = True
            for m in margin_mappings:
                val_a = num_vals_a[m.column_a].loc[idx_a]
                val_b = num_vals_b[m.column_a].loc[idx_b]
                # If either value is NaN, can't compute a numeric diff
                if np.isnan(val_a) or np.isnan(val_b):
                    match = False
                    break
                diff = val_a - val_b
                if abs(diff) > m.margin:
                    match = False
                    break
                diffs[m.column_a] = diff
            if match:
                results.append(NearMatch(
                    row_a=idx_a + 1,  # 1-based
                    row_b=idx_b + 1,  # 1-based
                    diffs=diffs,
                ))

    return results


# ---------------------------------------------------------------------------
# Public API
# ---------------------------------------------------------------------------


def compare(
    config: ComparisonConfig,
    df_a: pd.DataFrame,
    df_b: pd.DataFrame,
) -> ComparisonResult:
    """Check which rows in *df_a* have a matching row in *df_b*.

    Matching is defined by the column mappings in *config*: a row in A matches
    a row in B when **all** mapped column pairs are equal after normalisation.

    Returns a :class:`ComparisonResult` that contains the original Table A,
    a boolean mask, and any parse warnings.
    """
    if not config.columns:
        raise ValueError("No column mappings configured.")

    # --- column-existence check -------------------------------------------
    for m in config.columns:
        if m.column_a not in df_a.columns:
            raise ValueError(
                f"Column '{m.column_a}' not found in Table A. "
                f"Available: {list(df_a.columns)}"
            )
        if m.column_b not in df_b.columns:
            raise ValueError(
                f"Column '{m.column_b}' not found in Table B. "
                f"Available: {list(df_b.columns)}"
            )

    warnings: list[str] = []
    key_parts_a: list[pd.Series] = []
    key_parts_b: list[pd.Series] = []
    norm_columns_a: dict[str, pd.Series] = {}
    norm_columns_b: dict[str, pd.Series] = {}

    for m in config.columns:
        orig_a = df_a[m.column_a]
        orig_b = df_b[m.column_b]
        orig_nulls_a = int(orig_a.isna().sum())
        orig_nulls_b = int(orig_b.isna().sum())

        norm_a = _normalize(orig_a, m, "a")
        norm_b = _normalize(orig_b, m, "b")

        # --- warn about values that failed to parse (new nulls) -----------
        if m.col_type in ("date", "number"):
            label = m.col_type
            # Find rows where parsing failed (was not null before, is null after)
            failed_a = ~orig_a.isna() & norm_a.isna()
            failed_b = ~orig_b.isna() & norm_b.isna()
            extra_a = int(failed_a.sum())
            extra_b = int(failed_b.sum())

            if extra_a > 0:
                # Get row indices and values that failed to parse
                failed_indices = failed_a[failed_a].index.tolist()
                failed_values = orig_a[failed_a].tolist()

                # Limit examples to first 10 to avoid overwhelming output
                max_examples = 10
                examples = []
                for idx, val in zip(failed_indices[:max_examples], failed_values[:max_examples]):
                    examples.append(f"  Row {idx}: {repr(val)}")

                warning = (
                    f"Table A '{m.column_a}': {extra_a} value(s) could not be "
                    f"parsed as {label}:\n" + "\n".join(examples)
                )
                if extra_a > max_examples:
                    warning += f"\n  ... and {extra_a - max_examples} more"
                warnings.append(warning)

            if extra_b > 0:
                # Get row indices and values that failed to parse
                failed_indices = failed_b[failed_b].index.tolist()
                failed_values = orig_b[failed_b].tolist()

                # Limit examples to first 10 to avoid overwhelming output
                max_examples = 10
                examples = []
                for idx, val in zip(failed_indices[:max_examples], failed_values[:max_examples]):
                    examples.append(f"  Row {idx}: {repr(val)}")

                warning = (
                    f"Table B '{m.column_b}': {extra_b} value(s) could not be "
                    f"parsed as {label}:\n" + "\n".join(examples)
                )
                if extra_b > max_examples:
                    warning += f"\n  ... and {extra_b - max_examples} more"
                warnings.append(warning)

        norm_columns_a[m.column_a] = norm_a
        norm_columns_b[m.column_b] = norm_b
        key_parts_a.append(_to_key_str(norm_a))
        key_parts_b.append(_to_key_str(norm_b))

    # --- build composite key (concat with separator) ----------------------
    key_a = key_parts_a[0]
    for part in key_parts_a[1:]:
        key_a = key_a + _KEY_SEP + part

    key_b = key_parts_b[0]
    for part in key_parts_b[1:]:
        key_b = key_b + _KEY_SEP + part

    # --- count-aware multiset match (A→B) -----------------------------------
    # How many copies of each key are available in B.
    count_b = key_b.value_counts()
    # Within each group of identical keys in A, assign indices 0, 1, 2 …
    cumcount_a = key_a.groupby(key_a).cumcount()
    # A row matches only if its within-group index < copies available in B.
    matched_mask = cumcount_a < key_a.map(count_b).fillna(0).astype(int)

    # --- count-aware multiset match (B→A) -----------------------------------
    count_a = key_a.value_counts()
    cumcount_b = key_b.groupby(key_b).cumcount()
    matched_mask_b = cumcount_b < key_b.map(count_a).fillna(0).astype(int)

    near_matches = _find_near_matches(
        config, norm_columns_a, norm_columns_b, matched_mask, matched_mask_b,
    )

    return ComparisonResult(
        df_a=df_a,
        matched_mask=matched_mask,
        df_b=df_b,
        matched_mask_b=matched_mask_b,
        config=config,
        warnings=warnings,
        near_matches=near_matches,
    )