08 — Extending for a new CSV format

Duration: 15 min Prerequisites: chapters 02 and 03 (you understand ingest + normalize).

The scenario

A student has a real export from a French bank. Sample lines:

"Date Opération";"Libellé";"Catégorie Banque";"Montant"
"15/12/2027";"CARREFOUR PARIS 12";"COURSES";"-42,18"
"14/12/2027";"REMBOURSEMENT VOYAGE";"REMBOURSEMENT";"89,55"
"13/12/2027";"NETFLIX";"ABONNEMENT";"-13,49"

Four observations:

1. Separator is ; (good — already supported).
2. Date is dd/mm/yyyy with / (different from our ISO 2027-12-15).
3. Amount is a single signed column with comma decimals (-42,18).
4. Header names are in French and with accents (Libellé, Catégorie Banque).

Let’s adapt the library step by step.

Step 1 — Run ingest.read_csv and see what happens

>>> import io, ingest
>>> sample = '''"Date Opération";"Libellé";"Catégorie Banque";"Montant"
... "15/12/2027";"CARREFOUR PARIS 12";"COURSES";"-42,18"
... "14/12/2027";"REMBOURSEMENT VOYAGE";"REMBOURSEMENT";"89,55"
... "13/12/2027";"NETFLIX";"ABONNEMENT";"-13,49"'''
>>> df = ingest.read_csv(io.StringIO(sample))
>>> df.columns.tolist()
['date opération', 'libellé', 'catégorie banque', 'amount']
>>> df.head()

Two issues:

• Date Opération, Libellé, Catégorie Banque weren’t aliased — they ended up as their lowercased raw form.
• Montant → because montant IS in the alias table, it became amount. Good, that one was already supported.

Step 2 — Add header aliases

Open csv-llm-shared/ingest.py and add to HEADER_ALIASES:

HEADER_ALIASES = {
    # ... existing entries ...
    "date opération":    "date",
    "date operation":    "date",       # without accent, just in case
    "libellé":           "description",
    "libelle":           "description",
    "catégorie banque":  "category",
    "categorie banque":  "category",
}

Re-run:

>>> df = ingest.read_csv(io.StringIO(sample))
>>> df.columns.tolist()
['date', 'card', 'description', 'category', 'debit', 'credit']
>>> df.head()
         date card           description       category  debit  credit
0  15/12/2027            CARREFOUR PARIS 12        COURSES   42.18    0.00
1  14/12/2027        REMBOURSEMENT VOYAGE  REMBOURSEMENT    0.00   89.55
2  13/12/2027                  NETFLIX     ABONNEMENT    13.49    0.00

Three things to notice:

• Columns are right.
• card is empty (this bank doesn’t include it — fine, normalize.py won’t choke).
• The single signed Montant column got correctly split: negative values → debit, positive → credit. (Sign convention reminder: in the source, negative = expense; _parse_amount strips the minus and writes to debit. Wait, let me re-check.)

Sign convention check

Reading ingest.read_csv carefully: if there’s a single amount column, we do df["debit"] = df["amount"].clip(lower=0) (positive → debit) and df["credit"] = (-df["amount"]).clip(lower=0) (negative → credit).

But this French bank uses the opposite sign convention: negative = expense, positive = refund. So -42,18 for “CARREFOUR” should become a debit of 42.18, not a credit of 42.18.

Check the table above: yes, CARREFOUR PARIS 12 → debit = 42.18. The library got it right.

Why? Because our convention is also “positive amount = expense” in the internal signed amount column (chapter 03). The split here is just preliminary. After normalize, amount = debit - credit = 42.18 - 0 = +42.18 (expense). Correct.

Always sanity check the first 3 rows against the original.

Step 3 — Date format

15/12/2027 is not ISO. Will pd.to_datetime parse it?

>>> import pandas as pd
>>> pd.to_datetime("15/12/2027")
Timestamp('2027-12-15 00:00:00')          # OK on Linux with default locale
>>> pd.to_datetime("15/12/2027", dayfirst=True)
Timestamp('2027-12-15 00:00:00')          # always OK with dayfirst=True

Pandas usually figures it out, but the safest path is to pass dayfirst=True for European date strings. Let’s make normalize.normalize accept a dayfirst parameter:

def normalize(df: pd.DataFrame, profile: str = "default", dayfirst: bool = False):
    out = df.copy()
    out["date"] = pd.to_datetime(out["date"], errors="coerce",
                                  dayfirst=dayfirst).dt.strftime("%Y-%m-%d")
    # ... rest unchanged

Now usage:

nf = normalize.normalize(df, profile="french-personal", dayfirst=True)

For the demo CSV (2027-12-27 ISO format), dayfirst=False (default) still works fine.

Better: auto-detect dayfirst

A heuristic: if the first 100 dates have any month > 12 token in the first slot, you have dd/mm/yyyy (because there’s no 13th month). Add this auto-detection to normalize.py if you want to spare users the dayfirst=True flag.

Step 4 — Amount with comma decimals

Look at the demo input "-42,18". Goes through _parse_amount:

s = "-42,18".strip()                 # "-42,18"
s = s.replace(",", ".")              # "-42.18"
s = s.replace(" ", "")               # "-42.18"
s = s.replace("\xa0", "")            # "-42.18"
float(s)                             # -42.18

Already handled. That’s why we wrote _parse_amount the way we did in chapter 02.

Step 5 — Add category rules

The French bank already provides categories, but they’re in French and not in our whitelist. Two options:

Option A — Add new categories to the whitelist

Open categorize.py and extend the CATEGORIES list:

CATEGORIES = [
    "Housing", "Groceries", "Dining", ...,
    "COURSES", "REMBOURSEMENT", "ABONNEMENT", ...
]

Pros: respects source data. Cons: SQL queries written for English categories don’t transfer.

Option B — Map French categories to English ones

Add to category_rules.yaml:

rules:
  Groceries:
    - "courses"
    - "carrefour"
    - "monoprix"
  Refunds:
    - "remboursement"
  Entertainment:
    - "netflix"
    - "spotify"
    - "abonnement"

Then run categorize_dataframe to override the category column:

df = categorize.categorize_dataframe(df, rules, default="Other")

This is the recommended path: keep the English schema, translate at ingest time.

Step 6 — Update smoke_pipeline.py

Add a test for the new bank:

# In smoke_pipeline.py main():
print("\nFrench bank smoke test...")
sample = """..."""  # the sample CSV from above
df2 = ingest.read_csv(io.StringIO(sample))
nf2 = normalize.normalize(df2, profile="fr-test", dayfirst=True)
# Assert expected: 3 rows, 2 debits, 1 credit, ...

If your CI is green after adding this, the library is now safely extended.

Step 7 — Document

Update csv-llm-shared/README.md:

## Supported banks

- Generic (semicolon CSV with EN/FR headers) — works out of the box.
- French personal accounts (semicolon, dd/mm/yyyy, comma decimals) — pass `dayfirst=True` to `normalize.normalize()`.
- US Visa (comma CSV, ISO dates, signed amount) — works out of the box.
- ... your bank here ...

Future contributors will know what’s tested.

Adding a brand-new feature (not just a new bank)

Suppose you want to add currency support (multi-currency transactions). The right steps:

1. Add a currency column to ingest.py aliases ("currency", "devise", "devise étrangère").
2. Add currency to the SQLite SCHEMA in db.py (default 'CAD').
3. Add currency to the (profile, date, card, description, category, amount, currency) order in normalize.py.
4. Update dashboards.py to group by currency where it matters.
5. Add invariants to smoke_pipeline.py.
6. Update both apps’ app.py to show currency.

That’s 6 places to touch — but each touch is small. The library’s shape makes extensions linear, not quadratic.

When NOT to extend csv-llm-shared

If your use case starts to drift far from “credit-card CSV → SQLite → dashboards + chat”, fork instead:

• Multi-table schema (transactions + investments + accounts)? Fork.
• Postgres or DuckDB instead of SQLite? Fork.
• Streaming data instead of batch CSV? Fork.

The library is ~700 lines. Forking is cheap.

Takeaways

• Adding a new bank: 1 set of header aliases + maybe dayfirst=True. Done.
• The signed-amount logic and _parse_amount already handle most international quirks.
• For new categories, prefer mapping (rules.yaml) over adding (whitelist) — keeps SQL stable.
• Update smoke_pipeline.py with at least one assertion per new bank.
• Fork rather than over-stretch the library.

← Back to the Project 3 overview