Dropping duplicate identifiers before summarizing a CSV can quietly hide a disagreement. If two rows use the same identifier but contain different valid values, keeping the first one is a business decision, not a neutral cleanup step. This tutorial builds a small two-pass reconciliation plan that separates exact duplicates from conflicts before calculating a total.
Disclosure: prepared for Omnistu with AI assistance. The example and tests below were run on synthetic data. It is an educational data-cleaning exercise, not a report of customer work or a production import system. There are no affiliate links.
Choose the rule before the implementation
Our input has exactly three columns: id, region and amount. The rule is intentionally conservative:
- Trim leading and trailing whitespace. Identifiers and region names remain case-sensitive.
- Reject records with missing or extra fields, a blank identifier or region, or an amount that is negative, non-finite or not a decimal.
- Among valid records with one identifier, keep the first occurrence only if every occurrence agrees on region and numeric amount. Mark later equivalent rows as duplicates.
- If any valid occurrence disagrees, quarantine all valid occurrences of that identifier. Do not let the first row win.
- An invalid occurrence does not reserve its identifier. A later valid occurrence can still be kept.
This is a proposed rule for this exercise. A real dataset might need case folding, a currency column, a fixed number of decimal places, or a trusted timestamp to choose an update. Those changes belong in the contract and tests, not in an undocumented call to remove duplicates.
Start with an input that can expose a bad rule
id,region,amount
A1,North,2.00
A2,South,4
A1,North,2.0
A3,East,5
A3,East,7
,West,1
A4,West,NaN
A5,North,0
A6,South,-1
A7,"North, coastal",3.50
A8,West,1,unexpected
A1 appears twice with equivalent decimal values. A3 appears twice with different amounts, so neither valid record should be kept. A quoted comma in the A7 region must remain part of one field. The last record has an unexpected fourth field.
The empty identifier, NaN, negative amount and extra field are invalid under our chosen rule. Zero is allowed. Save the sample as the SAMPLE string shown in the run block below, or copy it into a triple-quoted Python string.
Parse first, then resolve each identifier group
The implementation uses only the Python standard library. Save this as reconcile.py.
import csv
from collections import defaultdict
from decimal import Decimal, InvalidOperation
from io import StringIO
def reconcile(text):
"""Plan a tiny CSV cleanup. Does not write a database or modify a file."""
reader = csv.DictReader(StringIO(text.removeprefix('\ufeff'), newline=''),
strict=True)
if reader.fieldnames != ['id', 'region', 'amount']:
raise ValueError('Expected exactly: id,region,amount')
groups = defaultdict(list)
decisions = []
for record, row in enumerate(reader, start=1):
decision = {'record': record, 'status': None, 'reason': None}
decisions.append(decision)
if None in row or any(value is None for value in row.values()):
decision.update(status='invalid', reason='wrong field count')
continue
key, region, raw = (row[name].strip()
for name in ['id', 'region', 'amount'])
try:
amount = Decimal(raw)
except InvalidOperation:
decision.update(status='invalid', reason='amount is not decimal')
continue
if not key or not region or not amount.is_finite() or amount < 0:
decision.update(status='invalid', reason='blank key/region or invalid amount')
continue
groups[key].append((decision, region, amount))
accepted = []
for key, items in groups.items():
values = {(region, amount) for _, region, amount in items}
if len(values) != 1:
for decision, _, _ in items:
decision.update(status='conflict', reason='id has different valid values')
continue
for position, (decision, region, amount) in enumerate(items):
if position == 0:
decision.update(status='keep', reason='first valid occurrence')
accepted.append({'id': key, 'region': region, 'amount': amount})
else:
decision.update(status='duplicate', reason='same normalized values')
return accepted, decisions
The first pass checks shape and values, then groups valid records. The second pass can see the complete group before making a decision. That is why it can quarantine an earlier row after a later row reveals a conflict.
The None checks cover how DictReader represents extra and missing values with its default settings. strict=True rejects some malformed CSV syntax, but it is not a complete schema validator. We perform the field-count and value checks separately.
Decimal is constructed from the CSV string, rather than converting through a binary float. Decimal('2.00') and Decimal('2.0') compare as equal here. is_finite() rejects NaN, signaling NaN and infinities before the less-than comparison. Our contract also accepts exponent notation such as 1e0; it is not a fixed-format currency parser.
The record number counts parsed data records, not physical file lines. A quoted field may contain a newline. Blank physical lines skipped by the CSV reader do not receive a decision.
Run the synthetic example and check the totals
Append this block to reconcile.py, then run python reconcile.py:
from collections import Counter
SAMPLE = '''id,region,amount
A1,North,2.00
A2,South,4
A1,North,2.0
A3,East,5
A3,East,7
,West,1
A4,West,NaN
A5,North,0
A6,South,-1
A7,"North, coastal",3.50
A8,West,1,unexpected
'''
accepted, decisions = reconcile(SAMPLE)
print(dict(Counter(d['status'] for d in decisions)))
print('accepted ids:', [row['id'] for row in accepted])
print('total:', sum((row['amount'] for row in accepted), Decimal('0')))
Observed output:
{'keep': 4, 'duplicate': 1, 'conflict': 2, 'invalid': 4}
accepted ids: ['A1', 'A2', 'A5', 'A7']
total: 9.50
The four decision counts sum to all eleven data records. That reconciliation matters: a plausible total alone would not reveal that the two conflicting A3 records were dropped deliberately.
Inspect a record-level audit trail when the aggregate is surprising:
for decision in decisions:
print(decision['record'], decision['status'], decision['reason'])
The accepted list preserves first-appearance order of valid identifier groups. That is a deterministic output choice, not a claim about database ordering.
Test the policy boundaries, not only the happy path
Save the following as test_reconcile.py beside the implementation. These focused tests cover the cases most likely to be confused with one another.
import unittest
from reconcile import reconcile
HEADER = 'id,region,amount\n'
class PolicyTests(unittest.TestCase):
def test_equivalent_decimal_is_duplicate(self):
accepted, audit = reconcile(HEADER + 'A,X,1.00\nA,X,1e0\n')
self.assertEqual(len(accepted), 1)
self.assertEqual([r['status'] for r in audit], ['keep', 'duplicate'])
def test_later_conflict_quarantines_whole_group(self):
accepted, audit = reconcile(HEADER + 'A,X,1\nA,X,1\nA,X,2\n')
self.assertEqual(accepted, [])
self.assertEqual([r['status'] for r in audit], ['conflict'] * 3)
def test_invalid_first_does_not_reserve_identifier(self):
accepted, audit = reconcile(HEADER + 'A,X,nope\nA,X,1\n')
self.assertEqual([r['status'] for r in audit], ['invalid', 'keep'])
def test_reject_nonfinite_before_comparison(self):
for value in ['NaN', 'sNaN', 'Infinity', '-Infinity']:
with self.subTest(value=value):
accepted, audit = reconcile(HEADER + f'A,X,{value}\n')
self.assertEqual(accepted, [])
self.assertEqual(audit[0]['status'], 'invalid')
if __name__ == '__main__':
unittest.main()
Run python -m unittest -v. These four published tests must pass before changing the policy. The development check also exercised eighteen tests in total: the full sample, malformed quotes, duplicate headers, missing and extra fields, a quoted comma, a quoted multiline field, whitespace trimming, case-sensitive identifiers, conflicting regions, an empty body with a valid header, a leading UTF-8 BOM, blank regions and CRLF newlines. The result was eighteen passed tests on Python 3.10.12. This does not claim cross-platform testing.
Know what this plan does not protect
This function reads the entire CSV into memory and has no input-size or field-length budget of its own. Do not expose it as an unrestricted upload endpoint. Decimal accepts more numeric forms than many business schemas want, and very large precision or exponents need explicit bounds. Totals use the active decimal context, so set and test a precision policy before using large real-world amounts. No currency conversion, date parsing or Unicode normalization is attempted.
A malformed CSV raises an exception rather than returning a partial audit. The sample never writes a database, overwrites an input file or exports a spreadsheet. It does not protect against concurrent writes, replay across files, formula injection in a later spreadsheet export, or sensitive-data exposure in logs. Those are separate boundaries to design and test.
The practical takeaway is small: separate syntactic validation, duplicate equivalence and conflict resolution. Keep the evidence for every parsed record beside the result, so a reviewer can explain why a row was kept or withheld.
References
- Python csv documentation, including
DictReader, newline handling and dialect strictness. - Python decimal documentation, including string construction, finite values and arithmetic context.