spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# CSV Recipes78## Contents9- Create10- Read / filter / sort / dedupe11- Modify columns12- Convert (csv ↔ json, delimiter change, split/merge files)13- Streaming large files14- Gotchas1516## Create1718**From a list of dicts:**19```python20import csv21rows = [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]22with open("out.csv", "w", newline="", encoding="utf-8") as f:23    w = csv.DictWriter(f, fieldnames=list(rows[0]))24    w.writeheader(); w.writerows(rows)25```2627**From lists of lists (positional):**28```python29with open("out.csv", "w", newline="", encoding="utf-8") as f:30    csv.writer(f).writerows([["id", "name"], [1, "Alice"]])31```3233## Read / filter / sort / dedupe3435**Filter rows:**36```python37with open("in.csv", newline="", encoding="utf-8") as f:38    rows = [r for r in csv.DictReader(f) if r["status"] == "active"]39```4041**Sort (numeric column — cast, or you get lexicographic "10" < "9"):**42```python43rows.sort(key=lambda r: float(r["amount"]), reverse=True)44```4546**Dedupe on a key, keeping first occurrence:**47```python48seen, unique = set(), []49for r in rows:50    if r["email"] not in seen:51        seen.add(r["email"]); unique.append(r)52```5354**Count malformed rows without crashing:**55```python56with open("in.csv", newline="", encoding="utf-8") as f:57    reader = csv.reader(f)58    header = next(reader)59    bad = sum(1 for row in reader if len(row) != len(header))60```6162## Modify columns6364**Add a computed column:**65```python66for r in rows:67    r["total"] = f'{float(r["price"]) * int(r["qty"]):.2f}'68fieldnames = list(rows[0])                # includes the new column69```7071**Rename / drop columns:**72```python73RENAME = {"e-mail": "email"}74DROP = {"internal_id"}75rows = [{RENAME.get(k, k): v for k, v in r.items() if k not in DROP} for r in rows]76```7778**In-place edit, atomically:**79```python80import os, tempfile81fd, tmp = tempfile.mkstemp(dir=".", suffix=".csv")82with os.fdopen(fd, "w", newline="", encoding="utf-8") as f:83    w = csv.DictWriter(f, fieldnames=fieldnames)84    w.writeheader(); w.writerows(rows)85os.replace(tmp, "in.csv")86```8788## Convert8990**CSV → JSON array:**91```python92import json93with open("in.csv", newline="", encoding="utf-8") as f:94    rows = list(csv.DictReader(f))95json.dump(rows, open("out.json", "w", encoding="utf-8"), indent=2, ensure_ascii=False)96```9798**JSON array → CSV** (union of keys so ragged records don't crash `DictWriter`):99```python100records = json.load(open("in.json", encoding="utf-8"))101fields = sorted({k for r in records for k in r})102with open("out.csv", "w", newline="", encoding="utf-8") as f:103    w = csv.DictWriter(f, fieldnames=fields)104    w.writeheader(); w.writerows(records)105```106107**CSV → TSV (or any delimiter change):**108```python109with open("in.csv", newline="", encoding="utf-8") as fin, \110     open("out.tsv", "w", newline="", encoding="utf-8") as fout:111    csv.writer(fout, delimiter="\t").writerows(csv.reader(fin))112```113114**CSV ↔ Excel:** hand off to the `processing-xlsx` skill; the boundary belongs there.115116**Split one big CSV into N-row chunks:**117```python118CHUNK = 50_000                            # ~50k rows keeps each part loadable in spreadsheets119with open("in.csv", newline="", encoding="utf-8") as f:120    reader = csv.reader(f); header = next(reader)121    part, buf = 1, []122    for row in reader:123        buf.append(row)124        if len(buf) == CHUNK:125            with open(f"part-{part:03d}.csv", "w", newline="", encoding="utf-8") as out:126                w = csv.writer(out); w.writerow(header); w.writerows(buf)127            part, buf = part + 1, []128    if buf:129        with open(f"part-{part:03d}.csv", "w", newline="", encoding="utf-8") as out:130            w = csv.writer(out); w.writerow(header); w.writerows(buf)131```132133## Streaming large files134135Transform row-by-row without holding the file in memory:136```python137with open("in.csv", newline="", encoding="utf-8") as fin, \138     open("out.csv", "w", newline="", encoding="utf-8") as fout:139    reader = csv.DictReader(fin)140    writer = csv.DictWriter(fout, fieldnames=reader.fieldnames)141    writer.writeheader()142    for row in reader:                     # one row in memory at a time143        if row["country"] == "CA":144            writer.writerow(row)145```146147pandas alternative: `for chunk in pd.read_csv("in.csv", dtype=str, chunksize=100_000): ...`148149## Gotchas150- **`newline=""` is not optional.** Without it the `csv` module's `\r\n` handling stacks with Python's, producing blank lines between rows on Windows.151- **Never build CSV by `",".join(...)`** — a single value containing a comma, quote, or newline corrupts the file. The writer quotes correctly for free.152- **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.153- **Excel needs a BOM to detect UTF-8:** if the file is destined for Excel, write with `encoding="utf-8-sig"`.154- **`csv.Sniffer` can misfire on single-column files** or quoted samples — wrap in try/except and fall back to `,`.155- **Sorting strings numerically** gives `"10" < "9"`; cast before sorting.156- **`DictReader` with duplicate headers** silently keeps only the last column of that name — check `reader.fieldnames` for dupes when auditing unknown files.157- **Line numbers vs row numbers** differ when fields contain embedded newlines; use `reader.line_num` for error reporting, not your own counter.158