<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# 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.