Messy CSV Interview Exercise: Encoding, Quoted Fields, and Reconciliation

A messy CSV interview exercise is not solved by getting a DataFrame to load. A parser can return rows while silently replacing undecodable characters, splitting a quoted note at the wrong comma, or dropping records that fail validation. A stronger answer preserves the source, separates parsing from business rules, and reconciles what was accepted with what was quarantined.

Here is a small exercise you can work through without an employer-specific prompt. You receive a payment export, an alleged record count, and a claimed amount. Your task is to produce a usable table and explain every excluded record. For broader preparation, see Pandas Interview Questions for Data Analysts.

Evidence boundary: Official facts about CSV readers and parsing options come from Python and pandas documentation. Candidate reports: no verified report says a particular company uses this file or these questions. Inference: the sample export, reconciliation ledger, and review criteria are original practice material. Its invented manifest is a test condition, not a claim about a real dataset.

The exercise: five logical records, six physical lines

Assume the provider says the export contains five data records and a total of $30.00. The following display is a decoded view of the file. The note in A2 includes café; in the underlying example, its last character is the single byte 0xE9. A3 contains an actual newline inside a quoted field.

order_id,note,amount_usd
A1,"east, retail",12.50
A2,"café",7.00
A3,"line one
line two",3.25
A4,"bad amount",oops
A5,"west",4.00,extra

The first three data records are structurally valid and have numeric amounts. A4 has three fields but an invalid amount. A5 has four fields where the header promises three. A3 occupies two physical lines yet one CSV record. The first quick check is therefore five parsed data records, not five lines after the header.

The accepted amount is 12.50 + 7.00 + 3.25 = 22.75. The parseable amount in the quarantined A5 row is 4.00, but that row's extra field makes its intended mapping uncertain. A4's amount cannot be recovered from oops. The alleged $30.00 total cannot be certified from these rows. The difference after accepted plus visible quarantined numeric values is $3.25; it is a remaining unexplained gap, not proof that A4 should be assigned $3.25.

That distinction is the heart of the exercise. An answer that prints $22.75 without naming the two exclusions has produced a partial result. An answer that invents A4's amount to force the manifest to balance has corrupted the data.

How should you identify the encoding?

Start with the original bytes, provider contract, transport metadata, and known upstream system. Record a source checksum and file size before transforming anything. A decoding failure is evidence about your current hypothesis, not permission to replace bytes until parsing succeeds.

In this example, a strict UTF-8 decoder rejects 0xE9 in the constructed file. A single-byte Western encoding can decode it as é, but many byte values are decodable under such encodings, so successful decoding alone does not prove the provider's intended encoding. Ask for an export specification or a known reference field; document the chosen codec and remaining uncertainty.

Official facts: Python's text I/O documentation distinguishes decoding bytes with an encoding from working with text. The pandas read_csv reference exposes encoding and encoding_errors; the current default error mode is strict. errors="replace" or equivalent can make a file load while losing the original character, so it cannot be an invisible default in an auditable import.

Treat a UTF-8 byte-order mark separately from a guessed single-byte encoding. If one is present, test the corresponding UTF-8-SIG path. If files arrive from several suppliers, retain the supplier and codec decision per file. An encoding choice made for one batch should not be silently applied to a new supplier's export.

Why does line counting fail for quoted fields?

Official fact: Python's csv.reader uses a dialect, including delimiter and quoting rules, and can read records spanning physical lines. It recommends opening a file with newline='' so the CSV module handles embedded newlines correctly. A quoted comma in "east, retail" is data inside one field; the comma between the note and amount is a delimiter.

A3 shows why splitlines() followed by line.split(',') is not a CSV parser. Such code splits one logical record into two fragments. It can also mis-handle doubled quotes such as "She said ""go""". For this exercise, specify a comma delimiter, double-quote quoting, and doubled-quote escaping. Do not infer that all files use that dialect: Python's documentation explicitly notes real CSV variations.

The CSV format RFC describes a common format, including quoted fields that contain commas or line breaks. It is useful context, but your actual supplier contract remains decisive when files deviate from it. A candidate who says “RFC 4180, therefore every export is conforming” has skipped the diagnostic work.

When reporting an error location, distinguish the logical record number from the physical line where the parser ended that record. Python's reader exposes a line count, but it is not a durable byte offset. If you need exact byte ranges for replay or legal audit, design that capture explicitly rather than guessing offsets after decoding and newline normalization.

Separate parsing from business validation

A parser answers whether it can form fields. Business validation answers whether those fields make a usable order. Keeping the stages separate tells you why a row was excluded and whether a provider could repair it.

For each record, check field count before constructing a named row. Then check that order_id is present and unique under the agreed scope, and that amount_usd is a decimal in the permitted range. The allowed range is a business contract, not a CSV grammar rule. A refund feed might allow negative amounts; a sales-only feed might not.

Official fact: Python's csv.DictReader can place surplus fields under a special key and fill missing fields with a default value. That is useful for detection, but code that ignores the special key or treats a missing value as blank can accidentally turn a malformed record into an accepted one. In pandas, bad-line handling can skip or warn about certain rows, but you still need to account for them in your reconciliation.

Do not let on_bad_lines="skip" become the entire remediation. It can move the exception out of sight without producing a row-level quarantine file, and it does not replace your semantic checks. Likewise, type inference may coerce identifiers or monetary columns in unwanted ways. Start with explicit string or nullable types where the contract requires them, then parse amounts deliberately.

For money, this exercise uses Decimal rather than a binary floating-point accumulation. That choice keeps the invented cents exact. It does not handle locale or currency conversion by itself. A file with 1,234.56, 1.234,56, or a currency symbol needs an explicit supplier-specific rule before conversion.

A small parser that produces an audit ledger

The following fixture assumes the decoded text and dialect have already been chosen. In a real ingest, preserve the original bytes and write the ledger beside the cleaned output. Each logical record gets one ledger entry with a stable source identifier, status, and reason.

import csv
from decimal import Decimal, InvalidOperation
from io import StringIO

source = ('order_id,note,amount_usd\n'
          'A1,"east, retail",12.50\n'
          'A2,"café",7.00\n'
          'A3,"line one\nline two",3.25\n'
          'A4,"bad amount",oops\n'
          'A5,"west",4.00,extra\n')
reader = csv.reader(StringIO(source, newline=""), strict=True)
assert next(reader) == ["order_id", "note", "amount_usd"]
accepted, rejected = [], []
for record_number, fields in enumerate(reader, start=1):
    record_id = fields[0] if fields else f"record-{record_number}"
    if len(fields) != 3:
        rejected.append((record_id, "field_count", fields))
        continue
    try:
        amount = Decimal(fields[2])
    except InvalidOperation:
        rejected.append((record_id, "invalid_amount", fields))
        continue
    accepted.append((record_id, fields[1], amount))

assert len(accepted) == 3 and len(rejected) == 2
assert sum((r[2] for r in accepted), Decimal(0)) == Decimal("22.75")

The fixture is deliberately small. It does not catch a csv.Error caused by a broken quote, decide how to repair an absent ID, or persist raw byte offsets. A production design would quarantine parser failures at a carefully defined boundary, preserve the failing bytes, and refuse to publish totals when record boundaries themselves cannot be established.

There is an important failure mode in adding a broad except Exception: continue around this loop: coding errors, I/O failures, and broken input would all become silent exclusions. Catch the expected validation error, record it, and let unexpected failures stop the import for investigation.

Reconcile the records and the money separately

The row equation is 5 supplied = 3 accepted + 2 quarantined. It works because the parser identified five logical data records under the chosen dialect. The amount equation is not 30.00 = 22.75 + 4.00 + 3.25 unless the final $3.25 has evidence as an actual row amount. At present, it is just the algebraic difference.

An audit ledger should record the input file identity and hash; encoding and dialect; logical record ID; status; reason; fields or a reference to preserved raw data; and any approved manual correction. If a malformed record has no reliable ID, use a separate source-position key without pretending the first damaged field is a valid business identifier.

Reconcile at the right grain. A duplicated order ID can inflate a total even when every CSV row parses. A file split into several batches may satisfy per-file counts while double-loading a batch. Add uniqueness and load-id checks after syntax validation. Compare cents and counts to independently sourced control totals when available; do not derive both sides from the same cleaned table and call it reconciliation.

This exercise's source manifest gives a record count and amount. The count is explainable; the amount is not. A defensible handoff says the accepted dataset contains three rows totaling $22.75, two records are quarantined, and publication of the $30.00 source total awaits clarification. That is a useful answer even when it cannot force every control to green.

What would you ask the interviewer before “repairing” A4 or A5?

Ask who owns the export contract, whether the manifest total includes voids or adjustments, whether A5's fourth field belongs in a newer schema, and whether A4's oops can be recovered from an upstream ledger. Also ask whether the consumer needs partial data now or an all-or-nothing batch. These questions change the remediation policy.

If partial delivery is allowed, attach a conspicuous completeness flag and quarantine report. If it is not, keep the full batch unpublished while still exposing a diagnostic preview. In either case, do not erase rejected records. A correction should be versioned and replayable: original value, corrected value, approver or source evidence, and new reconciliation result.

The last interview follow-up is idempotency. If the same file is ingested tomorrow, a deterministic file ID or content hash should stop the accepted three rows from being appended again. If the provider sends a corrected version under the same name, distinguish it by content and an explicit supersession rule. “We will rerun the script” is not enough without saying whether it replaces or duplicates prior results.

Practice the full explanation

These verified PracHub exercises train parsing, validation, and data-pipeline reasoning. They are practice records, not predictions of a particular assessment.

Start with Parse a Raw CSV String Into a Structure for Downstream Teams, Handling Corrupted Rows. For each rejected record, state what the parser knows, what the business contract requires, and what evidence would permit a correction.

Sources and Further Reading

• Python documentation: CSV file reading and writing
• Python documentation: Text I/O and encodings
• pandas documentation: read_csv
• RFC Editor: RFC 4180