Data plumbing · Extract and normalise

Normalise the export

Turn a claims export with three date formats, two amount formats and three spellings of open into canonical rows, and return the rows it refused.

codeeasyExercise · 4 min · 30 of 64

On this page

The claims platform's export is the pilot's feed until the read-only view arrives, and it carries three date formats, thousands separators and three spellings of "open" beside a "Re-opened". The normaliser is thirty lines. The thing it must never do is guess.

The situation

The insurer from getting the data out exports claims as a 40-column CSV, 1,200 rows a working day. Four columns feed the triage queue: claim_no, loss_date, amount and status. Two migrations left loss_date in three formats: 2026-03-04 from the current system, day-first 04/03/2026 from the previous one, and 4-Mar-26 from a spreadsheet era, where a two-digit year means 20YY. Amounts carry thousands separators and optional decimals: 1,250.50, 80, 0.5. Statuses arrive as Open, OPEN, open , Re-opened and CLOSED. A re-export repeats rows, the same claim_no twice with the later row the newer. The prototype's parser let a library guess the slash format; it guessed month-first, and every slash date with a day of 12 or under, about two in five, came out swapped without an error, and the model cited a policy term that had not started. The customer's reviewer now wants two things from every load: the canonical rows, and the claim numbers of the rows the loader refused, so a handler can fix them at the source.

The question

Implement solve against this contract so that a row the loader cannot read is refused and named, never guessed and never silently dropped.

def solve(rows):
    """Normalise export rows into canonical claim records.

    rows: list of dicts with string values and the keys
          'claim_no', 'loss_date', 'amount', 'status'.

    Returns (records, rejected):
      records  — one dict per distinct claim_no, sorted by claim_no, with
                 'claim_no'  stripped and upper-cased
                 'loss_date' ISO 'YYYY-MM-DD', read by exactly one of the
                             patterns 'YYYY-MM-DD', 'DD/MM/YYYY', 'D-Mon-YY' (20YY)
                 'amount'    integer minor units: '1,250.50' -> 125050
                 'status'    'open' | 'closed' | 'reopened'
                 When a claim_no repeats, the last valid row wins.
      rejected — the claim_no, as it appeared in the row, of every row whose
                 date, amount or status could not be read, in input order.
    """

rows = [
    {"claim_no": "c-1001", "loss_date": "2026-03-04", "amount": "1,250.50", "status": "Open"},
    {"claim_no": "C-1002", "loss_date": "04/03/2026", "amount": "80",       "status": "CLOSED"},
    {"claim_no": "C-1003 ", "loss_date": "4-Mar-26",  "amount": "0.5",      "status": "Re-opened"},
    {"claim_no": "C-1004", "loss_date": "2026-13-01", "amount": "10",       "status": "Open"},
]
solve(rows) == (
    [{"claim_no": "C-1001", "loss_date": "2026-03-04", "amount": 125050, "status": "open"},
     {"claim_no": "C-1002", "loss_date": "2026-03-04", "amount": 8000,   "status": "closed"},
     {"claim_no": "C-1003", "loss_date": "2026-03-04", "amount": 50,     "status": "reopened"}],
    ["C-1004"],
)

A strong answer · Rubric · Where answers go wrong · Solution

Write your answer first, then open

A strong answer

Three small parsers that each return a value or None, and a loop that refuses a row when any returns None.

Dates: try the three fixed patterns in turn with strptime. Each pattern either matches the whole string or raises, so 04/03/2026 can only be the fourth of March and 2026-13-01 and 31/02/2026 are refused rather than coerced. No pattern guesses the order of day and month, because the situation is the record of what happens when one does. %y guesses too, putting 75 in 1975, so the parser sets the century the contract names.

Amounts: accept a comma only between groups of three digits, so 1,25 is refused rather than read as 125; drop the commas, parse with Decimal, multiply by 100 and check the result is integral. Floats are out: 0.29 * 100 is 28.999… and truncates to 28, and about one two-decimal amount in fifteen loses a cent the same way, which finance finds first. A value with sub-cent precision, like 1.005, is refused, not rounded, because the ledger does not carry it.

Statuses: lower-case, keep the letters, look the result up in the set of three. Re-opened, REOPENED and re opened collapse to one value; pending is refused.

The loop keeps a dictionary keyed on the normalised claim_no, so the last valid row for a claim overwrites the earlier one. A refused row is appended to rejected, in input order, and skipped before the dictionary is touched, which is the invariant that matters: a bad later row can never replace a good earlier one. The output is the dictionary's values sorted by key, and the empty export returns two empty lists.

Rubric

parses each date format with its own fixed pattern and never with a guessing parser
converts amounts to integer minor units with decimal arithmetic, refusing sub-cent values and misplaced separators
normalises status by case and punctuation and refuses any value outside the three
keeps the last valid row per claim and never lets a refused row overwrite a valid one
returns the refused rows as a list of claim numbers in input order, not a count and not nothing
returns two empty lists for an empty export

Where answers go wrong

"Use the library's flexible date parser; it handles all three." It does, and it is the failure in the situation: 04/03/2026 parses as the third of April, nothing raises, and a reviewer finds it in week 3 when the model cites a term that has not started.

"Store amounts as floats and multiply by 100." 0.29 becomes 28 minor units. The error is one cent, it is systematic, and the first person to notice is the customer's finance lead reconciling the pilot's totals against the ledger.

"Skip a row the parser cannot read and move on." Forty skipped rows is a 3.3% hole in a promise of 90% coverage, and nobody can say which claims fell through. The reviewer asked for the claim numbers; a continue with no record is the silent row the lesson warned about.

Solution

Python 3 · standard library60 lines · 7 of 7 tests passing

"""Normalise the export — three date formats, minor-unit amounts, three statuses, last valid row wins."""
import re
from datetime import datetime
from decimal import Decimal

# One fixed pattern per format the export has carried. A pattern either
# matches exactly or raises; nothing here guesses whether 04/03 is April or March.
DATE_FORMATS = ("%Y-%m-%d", "%d/%m/%Y", "%d-%b-%y")
STATUSES = {"open", "closed", "reopened"}
# A comma is a thousands separator only between groups of three digits; "1,25" is refused, not read as 125.
AMOUNT = re.compile(r"-?(\d{1,3}(,\d{3})+|\d+)(\.\d+)?")


def parse_date(text):
    """ISO 'YYYY-MM-DD', or None when no fixed pattern reads the text."""
    text = text.strip()
    for fmt in DATE_FORMATS:
        try:
            parsed = datetime.strptime(text, fmt)
        except ValueError:
            continue                  # wrong pattern, or an impossible date like 31/02
        if fmt == "%d-%b-%y":
            # %y alone puts 69-99 in the 1900s, a guess; the contract says 20YY.
            # 29 February survives: 1969-1999 and 2069-2099 hold the same leap years.
            parsed = parsed.replace(year=2000 + parsed.year % 100)
        return parsed.strftime("%Y-%m-%d")
    return None


def parse_amount(text):
    """Integer minor units, or None. Decimal, never float: 0.29 must become 29, not 28."""
    text = text.strip()
    if not AMOUNT.fullmatch(text):
        return None                   # no misplaced separators, exponents, NaN or words
    minor = Decimal(text.replace(",", "")) * 100
    if minor != minor.to_integral_value():
        return None                   # sub-cent precision: refuse rather than round
    return int(minor)


def parse_status(text):
    """One of the three canonical statuses, or None. Case and punctuation are ignored."""
    key = "".join(ch for ch in text.lower() if ch.isalpha())
    return key if key in STATUSES else None


def solve(rows):
    records = {}                      # claim_no -> canonical row; insertion order is irrelevant, we sort
    rejected = []                     # every refused row, by its claim_no as given, in input order
    for row in rows:
        loss_date = parse_date(row["loss_date"])
        amount = parse_amount(row["amount"])
        status = parse_status(row["status"])
        if loss_date is None or amount is None or status is None:
            rejected.append(row["claim_no"])
            continue                  # a rejected row never reaches the dict, so it cannot overwrite a valid one
        claim_no = row["claim_no"].strip().upper()
        # Last valid row wins: a re-export lists the newer row later.
        records[claim_no] = {"claim_no": claim_no, "loss_date": loss_date, "amount": amount, "status": status}
    return [records[key] for key in sorted(records)], rejected

The cases that ran

TESTS = [
    # The page example: three date formats, one impossible month.
    (([{"claim_no": "c-1001", "loss_date": "2026-03-04", "amount": "1,250.50", "status": "Open"},
       {"claim_no": "C-1002", "loss_date": "04/03/2026", "amount": "80", "status": "CLOSED"},
       {"claim_no": "C-1003 ", "loss_date": "4-Mar-26", "amount": "0.5", "status": "Re-opened"},
       {"claim_no": "C-1004", "loss_date": "2026-13-01", "amount": "10", "status": "Open"}],),
     ([{"claim_no": "C-1001", "loss_date": "2026-03-04", "amount": 125050, "status": "open"},
       {"claim_no": "C-1002", "loss_date": "2026-03-04", "amount": 8000, "status": "closed"},
       {"claim_no": "C-1003", "loss_date": "2026-03-04", "amount": 50, "status": "reopened"}],
      ["C-1004"])),
    # A re-export: the later row for C-7 wins; a later rejected row does not overwrite it.
    (([{"claim_no": "C-7", "loss_date": "01/02/2026", "amount": "100", "status": "open"},
       {"claim_no": "c-7", "loss_date": "2026-02-03", "amount": "150", "status": "closed"},
       {"claim_no": "C-7", "loss_date": "31/02/2026", "amount": "999", "status": "closed"}],),
     ([{"claim_no": "C-7", "loss_date": "2026-02-03", "amount": 15000, "status": "closed"}],
      ["C-7"])),
    # Amounts: thousands separators, no decimals, a cent, a float trap, a negative.
    (([{"claim_no": "A", "loss_date": "2026-01-01", "amount": " 12,000 ", "status": "open"},
       {"claim_no": "B", "loss_date": "2026-01-01", "amount": "0.29", "status": "open"},
       {"claim_no": "C", "loss_date": "2026-01-01", "amount": "-12.30", "status": "open"}],),
     ([{"claim_no": "A", "loss_date": "2026-01-01", "amount": 1200000, "status": "open"},
       {"claim_no": "B", "loss_date": "2026-01-01", "amount": 29, "status": "open"},
       {"claim_no": "C", "loss_date": "2026-01-01", "amount": -1230, "status": "open"}],
      [])),
    # Every way a row is refused: an unknown status, a non-number, sub-cent precision, a US-style date that no pattern accepts,
    # a comma that is not a thousands separator, and an exponent.
    (([{"claim_no": "R1", "loss_date": "2026-01-01", "amount": "10", "status": "pending"},
       {"claim_no": "R2", "loss_date": "2026-01-01", "amount": "n/a", "status": "open"},
       {"claim_no": "R3", "loss_date": "2026-01-01", "amount": "1.005", "status": "open"},
       {"claim_no": "R4", "loss_date": "2026/01/01", "amount": "10", "status": "open"},
       {"claim_no": "R5", "loss_date": "2026-01-01", "amount": "1,25", "status": "open"},
       {"claim_no": "R6", "loss_date": "2026-01-01", "amount": "1e3", "status": "open"}],),
     ([], ["R1", "R2", "R3", "R4", "R5", "R6"])),
    # Two-digit years are 20YY, as the contract says, including the years %y alone would put in the 1900s.
    (([{"claim_no": "Y1", "loss_date": "4-Mar-75", "amount": "1", "status": "open"},
       {"claim_no": "Y2", "loss_date": "29-Feb-28", "amount": "1", "status": "open"}],),
     ([{"claim_no": "Y1", "loss_date": "2075-03-04", "amount": 100, "status": "open"},
       {"claim_no": "Y2", "loss_date": "2028-02-29", "amount": 100, "status": "open"}],
      [])),
    # Output is sorted by claim_no whatever the input order; whitespace and case are normalised.
    (([{"claim_no": " c-20 ", "loss_date": "9-Sep-26", "amount": "1", "status": " OPEN "},
       {"claim_no": "C-10", "loss_date": "09/09/2026", "amount": "1.00", "status": "Closed"}],),
     ([{"claim_no": "C-10", "loss_date": "2026-09-09", "amount": 100, "status": "closed"},
       {"claim_no": "C-20", "loss_date": "2026-09-09", "amount": 100, "status": "open"}],
      [])),
    # An empty export.
    (([],), ([], [])),
]