02 — ingest.py: the multi-bank reader

Duration: 15 min Prerequisites: Python + pandas. Chapter 04 of Project 1 introduces this briefly; here we go deep.

The contract

ingest.read_csv(source) accepts:

a path (str or pathlib.Path),
or a file-like object (e.g. streamlit.UploadedFile),

and returns a pandas.DataFrame with exactly these 6 columns, in this order:

['date', 'card', 'description', 'category', 'debit', 'credit']

Whatever the input’s encoding, delimiter, header language, or amount format.

The full module annotated

from io import StringIO
from pathlib import Path
import csv
import pandas as pd

Standard imports. No third-party except pandas.

HEADER_ALIASES = {
    # date
    "date":               "date",
    "transaction date":   "date",
    "posted date":        "date",
    "post date":          "date",
    "date de transaction":"date",
    # card
    "card number":        "card",
    "card":               "card",
    "carte":              "card",
    "numero de carte":    "card",
    "numéro de carte":    "card",
    # description
    "description":        "description",
    "merchant":           "description",
    "merchant name":      "description",
    "marchand":           "description",
    # category
    "category":           "category",
    "categorie":          "category",
    "catégorie":          "category",
    # amounts
    "debit":              "debit",
    "credit":             "credit",
    "débit":              "debit",
    "crédit":             "credit",
    "amount":             "amount",
    "montant":            "amount",
}

The alias table. Keys are lowercased, accent-sensitive but tolerant. Add a new entry here when you encounter a new bank.

def _detect_delimiter(sample: str) -> str:
    return ";" if sample.count(";") > sample.count(",") else ","

A 2-line heuristic. Works because real-world bank CSVs don’t put ; in their values (they’d quote them or use |).

def _normalize_header(h: str) -> str:
    return HEADER_ALIASES.get(h.strip().lower(), h.strip().lower())

Lowercase + strip + lookup. If a header isn’t aliased, we keep it as-is (don’t drop it) — useful for debugging an unknown bank.

def _parse_amount(value) -> float:
    if value is None:
        return 0.0
    s = str(value).strip()
    if not s:
        return 0.0
    s = s.replace(",", ".").replace(" ", "").replace("\xa0", "")
    try:
        return float(s)
    except ValueError:
        return 0.0

_parse_amount is fearless: it accepts anything stringy and returns a float, defaulting to 0.0 on garbage. The 3 .replace() calls handle:

"," → "." (French decimals).
" " → "" (thousand separators).
"\xa0" → "" (non-breaking spaces — common in Excel exports).

def read_csv(source) -> pd.DataFrame:
    """Read a CSV (path or file-like) and return a standardized DataFrame."""
    if isinstance(source, (str, Path)):
        text = Path(source).read_text(encoding="utf-8-sig")
    else:
        data = source.read()
        if isinstance(data, bytes):
            data = data.decode("utf-8-sig")
        text = data

Two paths to load the text: file path, or file-like. Both end up with text: str.

utf-8-sig is crucial: it’s utf-8 but strips the BOM (\ufeff) Excel inserts. Without it, your first column is named \ufeffDate instead of Date.

    delim = _detect_delimiter(text.splitlines()[0])
    rows = list(csv.reader(StringIO(text), delimiter=delim, quotechar='"'))
    if not rows:
        return pd.DataFrame(
            columns=["date", "card", "description", "category", "debit", "credit"]
        )

Detect delimiter from the header line only (we don’t want a ; in a description to skew the count). Parse with csv.reader which handles quotes properly.

If the file is empty, return an empty DataFrame with the right schema — never None.

    raw_header = rows[0]
    header = [_normalize_header(h) for h in raw_header]
    df = pd.DataFrame(rows[1:], columns=header)

The header row → canonical column names. The body → DataFrame.

    for col in ("date", "card", "description", "category"):
        if col not in df.columns:
            df[col] = ""

If a column is missing (e.g. some banks don’t ship a card column), we create it empty rather than crash later.

    if "debit" not in df.columns and "credit" not in df.columns and "amount" in df.columns:
        # Single-column signed format: amount > 0 = expense, amount < 0 = refund
        df["amount"] = df["amount"].apply(_parse_amount)
        df["debit"] = df["amount"].clip(lower=0)
        df["credit"] = (-df["amount"]).clip(lower=0)
    else:
        df["debit"] = df.get("debit", 0).apply(_parse_amount)
        df["credit"] = df.get("credit", 0).apply(_parse_amount)

Two amount conventions — single signed column or two unsigned columns. We always end up with two unsigned columns (debit, credit); normalization in chapter 03 will compute the signed amount from those.

    return df[["date", "card", "description", "category", "debit", "credit"]]

Return the standardized shape. Always 6 columns, always in this order.

`read_many` — batching across files

def read_many(sources, profile: str) -> pd.DataFrame:
    """Read several CSVs and concatenate them, tagging each row with `profile`."""
    frames = []
    for src in sources:
        df = read_csv(src)
        df["profile"] = profile
        frames.append(df)
    if not frames:
        return pd.DataFrame(
            columns=["date", "card", "description", "category", "debit", "credit", "profile"]
        )
    return pd.concat(frames, ignore_index=True)

Used by the Streamlit apps when the user uploads multiple files at once.

Live test on the demo CSV

>>> import sys; sys.path.insert(0, "csv-llm-shared")
>>> import ingest
>>> df = ingest.read_csv("csv-llm-shared/data/data1-anonymized.csv")
>>> len(df)
734
>>> df.columns.tolist()
['date', 'card', 'description', 'category', 'debit', 'credit']
>>> df.dtypes
date           object
card           object
description    object
category       object
debit          float64
credit         float64
dtype: object
>>> df.head(2)
         date              card           description   category   debit  credit
0  2027-12-27  ************4382      Late Payment Fee       Fees   24.31     0.0
1  2027-12-26  ************4382        Postbox Rental    Housing  489.53     0.0

Notice: debit and credit are float64. date stays as text (parsing happens in normalize.py).

Test with a different bank

A made-up RBC export with comma-separated headers and signed amounts:

csv_text = """Transaction Date,Description,Amount
2027-12-15,SOBEYS WESTBORO,-42.18
2027-12-14,REFUND TRAVEL,89.55
"""
import io
df = ingest.read_csv(io.StringIO(csv_text))
print(df)

Output:

         date  card        description  category  debit  credit
0  2027-12-15        SOBEYS WESTBORO            42.18    0.00
1  2027-12-14        REFUND TRAVEL              0.00    89.55

Note:

The card and category columns were created empty (no input columns).
The single signed Amount column was split into debit (when > 0) and credit (when < 0).

What `ingest.py` deliberately doesn’t do

Parse dates (normalize.py’s job).
Categorize (categorize.py’s job).
Deduplicate (normalize.py’s job).
Write to a database (db.py’s job).

Single responsibility: CSV bytes → DataFrame with strings, debit, credit, and one date as raw string.

Common bugs and their fixes

Symptom	Likely cause	Fix
First column named `\ufeffDate`	Used `utf-8` instead of `utf-8-sig`	Already fixed — confirm you’re on latest
All amounts come out 0.0	Bank uses `−` (unicode minus) instead of `-`	Add `s.replace("−", "-")` in `_parse_amount`
Header `Type` got dropped	Not in `HEADER_ALIASES`	Add it (and decide what to map it to)
700 rows expected, only 500 came in	Delimiter detection wrong (e.g. file has both `,` and `;`)	Override: `delim = ";"` explicitly for that bank

Takeaways

ingest.read_csv() is the entry point. One function, six output columns, robust to bank dialects.
Three building blocks: alias dict, delimiter heuristic, fearless amount parser.
utf-8-sig is the single biggest cause of saved hours.
Single responsibility — no dates, no categories, no DB.

Next: normalize.py — dates, signs, dedup →