Parsing Messy Files

Output:

{'read': 10, 'loaded': 6, 'repaired': 2, 'quarantined': 2, 'rejected': 2}
total weight_kg loaded: 3982
quarantine share 20%: load
S-306,"Vasko, Lind and Partners",two,388,,line 8: pallets='two'
S-307,Northgate Paper,2,line 9: 3 fields
Copy

The counts must add up: 6 loaded + 2 quarantined + 2 rejected = 10 records read. The assert stops the job when they do not, because a record that leaves the loop without being counted is a record that disappeared. The repaired count, 2, is a subset of the loaded count. The loaded weight is 812 + 455 + 610 + 130 + 725 + 1,250 = 3,982 kilograms.

The quarantine file is itself a CSV, written with csv.writer. The writer quoted Vasko, Lind and Partners again, so the file can be read back with the same reader. The reason column at the end lets the vendor see what to fix.

The script also stops the whole file when too many records fail. Here 2 of 10 records, or 20 percent, went to quarantine, which is under the 25 percent limit, so the six good records load. A file in which most records fail can mean that the vendor changed the format, such as adding a column, and loading the few records that pass would produce a partial and misleading table.

Each outcome has a cost:

• Cost of rejecting: the data is gone. If a rule rejects a record that held real data, nobody can recover it later. Reject only records that hold no data, and always count them.
• Cost of repairing: a wrong rule changes data without notice. Trimming spaces from an ID is safe, but reading two as 2 is a guess. A repair is acceptable only when there is exactly one correct reading, and the job should count every repair.
• Cost of quarantining: someone must review the file. Quarantined records are missing from today's totals until the vendor sends a fix, and the file must have an owner. It is the better choice whenever the correct value is unknown.
• Cost of failing the file: nothing loads that day. It is the better choice when the failure share suggests a format change, because then even the passing records may be wrong.

Using This in an Interview

Amazon's interview prep page for data roles says its Data Engineer technical interviews combine verbal and coding exercises. A parsing task fits both parts, because the code is short and the decisions about bad rows need explaining. While you write the parser, say three things: you use the csv module because fields can hold quoted commas, you check the field count before you read a value, and you count every record as loaded, quarantined, or rejected.

• "What if the file is too large to read into memory?" csv.reader and a loop over a file's lines both read one record at a time, so memory stays small. Write the quarantine records to a file as they appear instead of keeping them in a list.
• "How do you know the job did not lose records?" Assert that the records read equal loaded plus quarantined plus rejected, and log all four counts. For JSON Lines, compare the line count of the file with the same sum.

Key Takeaways

• Splitting a CSV line on commas gives wrong fields when a quoted value contains a comma; csv.reader follows the quoting rules, including doubled quotes and line breaks inside a field.
• line_num counts lines, not records, and DictReader fills short rows with None, so check the field count yourself.
• JSON Lines keeps one value per line, so a bad line loses only that record; Python's json module accepts NaN, so validate values after parsing.
• Give every bad record one outcome: reject records with no data, repair only when one reading is correct, and quarantine the rest with a reason and a line number.
• Count every outcome, assert that the counts add up to the records read, and fail the whole file when the failure share suggests a format change.

The six loaded shipments are now clean records with integer weights. The next lesson, Grouping and Aggregating in Python, totals records like these by key with defaultdict, Counter, and itertools.groupby.

Practice Questions

1. A vendor line is S-310,"Orin, Bell",3,"2,400",. How many fields does line.split(",") return, and how many does csv.reader return?

<details> <summary>Show answer</summary>

split returns 7 fields, and csv.reader returns 5. The split cuts at all six commas, including the one inside "Orin, Bell" and the one inside "2,400". The reader treats each quoted value as one field and removes the quotes, so the weight field is the text 2,400. A repair rule must still remove the separator before converting it to an integer.

</details>

2. A loader opens the vendor file without newline='' and reads it with csv.reader. Which record is at risk, and why?

<details> <summary>Show answer</summary>

S-303, whose note contains a line break inside quotes. The Python documentation says that without newline='', "newlines embedded inside quoted fields will not be interpreted correctly." Only records with a line break inside a quoted field are affected, which is why the defect can go unnoticed until one appears.

</details>

3. A file has 1,000 records after the header. The job loads 940, quarantines 35, and rejects 20. What is wrong, and what should the job do?

<details> <summary>Show answer</summary>

Five records are not counted: 940 + 35 + 20 = 995. A record left the loop without an outcome, usually because a code path such as an exception handler skipped it without counting. The job should fail the run instead of loading, because the missing records are not in any table and not in the quarantine file.

</details>

4. A JSON Lines feed has the line {"shipment_id": "S-311", "weight_kg": Infinity}. Does json.loads raise an error? What should the loader do?

<details> <summary>Show answer</summary>

No error is raised; the weight loads as the float inf. Python's json module accepts NaN, Infinity, and -Infinity, although the JSON specification does not. The loader should test the value with math.isfinite and quarantine the record with the reason, as the lesson's script does for NaN.

</details>

5. A shipment has the pallet count two. A teammate proposes a repair that maps number words to digits. What is the argument against it?

<details> <summary>Show answer</summary>

The value has more than one possible reading, so a repair would be a guess. The vendor may have meant 2, or the column may have shifted, which is the same symptom a format change produces. Quarantining the record with the reason pallets='two' keeps the original text and lets the vendor confirm the value. A safe repair, such as trimming spaces from an ID, has only one correct result.

</details>