Preserve raw values before cleaning a CSV

Preserve the original field value before cleaning an imported dataset. A normalized quantity of 7 cannot tell you whether the source contained 7, 007, or a value that was changed by a repair rule. Keeping the raw token beside the parsed value lets you explain the transformation and retry it when the rule changes.

For a developer building an analytical import, this is a practical debugging aid. A downstream total can be wrong because the source was wrong, parsing rejected valid input, or a cleaning rule guessed incorrectly. If the import overwrites its input, those causes become difficult to separate.

Give each input a visible outcome

This toy supplier feed stores quantities as strings. Its contract accepts nonnegative decimal integers after trimming surrounding whitespace. Leading zeros are allowed, but negative values and mixed letters are rejected. These are chosen business rules for the example, not universal rules for quantity fields.

import re

def parse_quantity(raw):
    token = raw.strip()
    if token == "":
        return None, "missing"
    if re.fullmatch(r"[0-9]+", token) is None:
        return None, "invalid-integer"
    return int(token), None

records = []
for record_number, raw in enumerate([" 12 ", "", "1O", "007", "-3"], 1):
    quantity, error = parse_quantity(raw)
    records.append({
        "record_number": record_number,
        "raw_quantity": raw,
        "quantity": quantity,
        "error": error,
        "rule_version": "quantity-v1",
    })

for row in records:
    print(row["record_number"], repr(row["raw_quantity"]),
          row["quantity"], row["error"])

The first record becomes 12 and the fourth becomes 7. The empty token is marked missing. The letter O in the third token and the minus sign in the fifth violate the accepted grammar. Neither is silently changed to a plausible number.

The regular expression uses ASCII digits deliberately. Python’s broader digit categories can recognize characters from additional scripts. An international dataset may need that support, but it should be an explicit normalization decision with examples. Accepting whichever forms a parser happens to understand makes compatibility hard to explain.

View full-size image ↗

The figure preserves the raw token even for accepted rows. Successful conversion can still remove information that a future audit needs.

Parse the file before cleaning fields

The function above receives already parsed field strings. CSV quoting belongs to the file parser, which must recognize commas, embedded quotes, and record boundaries before quantity cleaning begins. RFC 4180 describes the quoted-field convention and escaped double quotes.

Splitting each text line on commas can corrupt a field before the cleaner sees it. The resulting token may look malformed even though the original CSV was valid. Keep file parsing errors separate from domain-value errors so the corrective action is clear.

Source identity needs similar precision. Record the source object or file version and a logical record identifier. A physical line number is insufficient when a quoted CSV field contains a newline. If a supplier replaces a file under the same name, a filename alone cannot identify which bytes produced the cleaned table.

Rejected values should remain distinguishable

Set a reasonable maximum field length before integer conversion when importing untrusted files. A quantity field containing millions of digits is a resource problem even if every character matches the grammar. The permitted length should follow the source’s numeric bounds, and an oversized token should produce a specific rejection reason without copying its entire contents into ordinary logs.

Both a missing token and an invalid integer produce no parsed quantity in this example, but their reasons differ. A missing quantity may require clarification from the supplier. An invalid integer may reveal an export format change or a transcription defect. Collapsing both into a single null loses that operational distinction.

Libraries also represent missing data differently depending on the column type. The pandas missing-data guide documents null-like values and the need for explicit missingness checks. Whatever library you use, retain a reason field when the distinction affects acceptance or interpretation.

A partial import must define what happens to rejected rows. It might quarantine them while publishing accepted rows with a completeness warning, or reject the entire batch when a required quantity is missing. The threshold depends on the consumer’s contract. A financial reconciliation and an exploratory preview need not use the same policy.

A new cleaning rule needs a new version

Suppose a later supplier agreement permits negative quantities for returns. Reprocessing the retained raw token can turn the fifth record into a valid negative movement under a new rule. The stored version distinguishes that interpretation from the original import.

Do not edit the old parsed value without recording the rule change if historical reports must remain reproducible. A replay can create a new curated dataset version while preserving the previous one for comparison. A review can then inspect which records changed and how their changes affect aggregates.

Rule versions should identify actual behavior. Renaming a version while leaving undocumented exceptions in manual spreadsheet edits does not provide reproducibility. Keep manual corrections in a separate correction log with the original value, corrected value, reason, and applicable record identity.

Retention and privacy still apply to raw data

Keeping raw input increases storage and may retain sensitive content that the cleaned table removes. Limit access and retention according to the dataset’s purpose. The ability to debug an import does not justify keeping every supplier field forever.

For this quantity example, the smallest useful evidence is the source version, record identity, raw quantity token, parsed result, rejection reason, and transformation version. Additional columns should have a reason to remain. Test the cleaner with valid zero, surrounding whitespace, leading zeros, missing input, and rejected syntax. Then check that every source record produces an accepted or rejected outcome so a failed conversion cannot silently disappear from the batch count.

Compare a rule change before replacing a curated table

The return-quantity proposal can be tested against the same five retained tokens. The next parser makes its accepted grammar and resource bound explicit. This illustrative feed limits the raw field to 64 characters and the unsigned numeric portion to four digits. Those limits are chosen for this supplier contract, not inferred from Python’s integer capacity.

def parse_bounded_quantity(raw, allow_returns):
    if type(raw) is not str:
        return None, "invalid-type"
    if len(raw) > 64:
        return None, "field-too-long"
    token = raw.strip()
    if not token:
        return None, "missing"
    grammar = r"-?[0-9]+" if allow_returns else r"[0-9]+"
    if re.fullmatch(grammar, token) is None:
        return None, "invalid-integer"
    digits = token[1:] if token.startswith("-") else token
    if len(digits) > 4:
        return None, "too-many-digits"
    return int(token), None

comparison = []
for old in records:
    raw = old["raw_quantity"]
    before = parse_bounded_quantity(raw, allow_returns=False)
    after = parse_bounded_quantity(raw, allow_returns=True)
    comparison.append((old["record_number"], raw, before, after))

for record_number, raw, before, after in comparison:
    if before != after:
        print(record_number, repr(raw), before, "->", after)
for label, offset in [("bounded-v1", 2), ("returns-v2", 3)]:
    accepted = [row[offset][0] for row in comparison if row[offset][1] is None]
    print(label, "accepted", len(accepted), "sum", sum(accepted))

Only record five changes interpretation: the raw string -3 moves from an invalid integer to an accepted return of minus three. The bounded nonnegative policy accepts two records totaling 19. The returns policy accepts three totaling 16. Neither total describes a complete five-record batch because the empty value and 1O remain unresolved. A lower accepted total after the change is expected here and is not evidence that three units disappeared during transport.

View full-size image ↗

The comparison uses the same bounded parser with returns disabled or enabled, so it isolates the sign-policy change. The earlier minimal parser did not enforce these length bounds. Do not give a strengthened implementation an old version identifier merely because the five ordinary examples behave the same. Version the actual code and configuration, including field limits, normalization and rejected-value handling.

Notice that the four-digit rule concerns the source representation, not just the numeric value. 00007 is rejected despite representing seven, while 0007 is accepted. If the agreement instead limits the numeric value to 9999 but permits arbitrary leading zeros, that is a different grammar and resource policy. Test the distinction before declaring two parser versions equivalent. Similarly, -0 is accepted when returns are enabled and becomes integer zero; the retained token preserves the original sign even though the numeric representation does not.

Keep field evidence separate from file evidence

The raw quantity is raw relative to domain cleaning. It is already a decoded CSV field. Quotes used only for CSV framing have been removed, and doubled quotes have been interpreted by the parser. Re-exporting that field cannot reconstruct the exact input bytes, encoding or line endings. Retain an approved source object version or immutable file snapshot when byte-level reproduction is required, alongside the parser configuration and logical record number.

A digest can detect whether a file’s bytes changed, but it is neither a backup nor a privacy transformation. If the original file has been deleted, its digest cannot restore it for replay. If a digest is made from a predictable individual value, someone may compare guesses against it. Use file identity for provenance without treating that identity as permission to expose sensitive fields or keep them indefinitely.

For a reproducible exercise, feed the bounded parser "0", "-0", "00007", the full-width string "１２", and 65 ASCII spaces. With returns enabled, the outcomes are zero, zero, too many digits, invalid integer and field too long. The last case fails the raw-length check before trimming; an oversized blank field is not classified as an ordinary missing value. Add the original record identity to each result and verify that accepted plus rejected counts still equal the number of inputs. This test checks both the transformation and the evidence needed to explain it later.