# CSV Recipes

# Contents

Create
Read / filter / sort / dedupe
Modify columns
Convert (csv ↔ json, delimiter change, split/merge files)
Streaming large files
Gotchas

# Create

From a list of dicts:

python

import csv
rows = [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
with open("out.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=list(rows[0]))
    w.writeheader(); w.writerows(rows)

From lists of lists (positional):

python

with open("out.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows([["id", "name"], [1, "Alice"]])

# Read / filter / sort / dedupe

Filter rows:

python

with open("in.csv", newline="", encoding="utf-8") as f:
    rows = [r for r in csv.DictReader(f) if r["status"] == "active"]

Sort (numeric column — cast, or you get lexicographic "10" < "9"):

python

rows.sort(key=lambda r: float(r["amount"]), reverse=True)

Dedupe on a key, keeping first occurrence:

python

seen, unique = set(), []
for r in rows:
    if r["email"] not in seen:
        seen.add(r["email"]); unique.append(r)

Count malformed rows without crashing:

python

with open("in.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
    bad = sum(1 for row in reader if len(row) != len(header))

# Modify columns

Add a computed column:

python

for r in rows:
    r["total"] = f'{float(r["price"]) * int(r["qty"]):.2f}'
fieldnames = list(rows[0])                # includes the new column

Rename / drop columns:

python

RENAME = {"e-mail": "email"}
DROP = {"internal_id"}
rows = [{RENAME.get(k, k): v for k, v in r.items() if k not in DROP} for r in rows]

In-place edit, atomically:

python

import os, tempfile
fd, tmp = tempfile.mkstemp(dir=".", suffix=".csv")
with os.fdopen(fd, "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=fieldnames)
    w.writeheader(); w.writerows(rows)
os.replace(tmp, "in.csv")

# Convert

CSV → JSON array:

python

import json
with open("in.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.DictReader(f))
json.dump(rows, open("out.json", "w", encoding="utf-8"), indent=2, ensure_ascii=False)

JSON array → CSV (union of keys so ragged records don't crash DictWriter):

python

records = json.load(open("in.json", encoding="utf-8"))
fields = sorted({k for r in records for k in r})
with open("out.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=fields)
    w.writeheader(); w.writerows(records)

CSV → TSV (or any delimiter change):

python

with open("in.csv", newline="", encoding="utf-8") as fin, \
     open("out.tsv", "w", newline="", encoding="utf-8") as fout:
    csv.writer(fout, delimiter="\t").writerows(csv.reader(fin))

CSV ↔ Excel: hand off to the processing-xlsx skill; the boundary belongs there.

Split one big CSV into N-row chunks:

python

CHUNK = 50_000                            # ~50k rows keeps each part loadable in spreadsheets
with open("in.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f); header = next(reader)
    part, buf = 1, []
    for row in reader:
        buf.append(row)
        if len(buf) == CHUNK:
            with open(f"part-{part:03d}.csv", "w", newline="", encoding="utf-8") as out:
                w = csv.writer(out); w.writerow(header); w.writerows(buf)
            part, buf = part + 1, []
    if buf:
        with open(f"part-{part:03d}.csv", "w", newline="", encoding="utf-8") as out:
            w = csv.writer(out); w.writerow(header); w.writerows(buf)

# Streaming large files

Transform row-by-row without holding the file in memory:

python

with open("in.csv", newline="", encoding="utf-8") as fin, \
     open("out.csv", "w", newline="", encoding="utf-8") as fout:
    reader = csv.DictReader(fin)
    writer = csv.DictWriter(fout, fieldnames=reader.fieldnames)
    writer.writeheader()
    for row in reader:                     # one row in memory at a time
        if row["country"] == "CA":
            writer.writerow(row)

pandas alternative: for chunk in pd.read_csv("in.csv", dtype=str, chunksize=100_000): ...

# Gotchas

newline="" is not optional. Without it the csv module's \r\n handling stacks with Python's, producing blank lines between rows on Windows.
Never build CSV by ",".join(...) — a single value containing a comma, quote, or newline corrupts the file. The writer quotes correctly for free.
Excel mangles CSVs: strips leading zeros, converts big numbers to scientific notation, and reinterprets 1/2 as a date. Keep identifier-like columns as strings and warn users who round-trip through Excel.
Excel needs a BOM to detect UTF-8: if the file is destined for Excel, write with encoding="utf-8-sig".
csv.Sniffer can misfire on single-column files or quoted samples — wrap in try/except and fall back to ,.
Sorting strings numerically gives "10" < "9"; cast before sorting.
DictReader with duplicate headers silently keeps only the last column of that name — check reader.fieldnames for dupes when auditing unknown files.
Line numbers vs row numbers differ when fields contain embedded newlines; use reader.line_num for error reporting, not your own counter.