03 — normalize.py: dates, signs, dedup

Duration: 12 min Prerequisites: chapter 02 (you have a raw DataFrame).

What normalize.py does

After ingestion, the DataFrame has all the right columns but the data is still messy:

Issue	Example
Date as string in mixed formats	"2027-12-27", "12/27/2027", "27 Dec 2027"
Two unsigned amount columns	debit=42.18 and credit=0
Strings with leading/trailing whitespace	" Coffee Gossip "
Empty rows	date="", description=""
Possible duplicates if user reloads	Same row inserted twice

normalize.normalize(df, profile) fixes all of this and adds a profile column.

The full module

csv-llm-shared/normalize.py

import pandas as pd


def normalize(df: pd.DataFrame, profile: str = "default") -> pd.DataFrame:
    out = df.copy()

    # 1. Parse dates → ISO YYYY-MM-DD
    out["date"] = pd.to_datetime(out["date"], errors="coerce").dt.strftime("%Y-%m-%d")

    # 2. Compute signed amount: debit > 0 = expense, credit > 0 = refund
    out["amount"] = out["debit"].astype(float) - out["credit"].astype(float)

    # 3. Trim strings
    for col in ("card", "description", "category"):
        out[col] = out[col].astype(str).str.strip()

    # 4. Drop empty / unparseable rows
    out = out[(out["description"] != "") & (out["date"].notna())]

    # 5. Attach profile
    out["profile"] = profile

    # 6. Final 6 columns, in canonical order
    return out[["profile", "date", "card", "description", "category", "amount"]]

That’s it. ~15 lines of real code.

Step 1 — pd.to_datetime(errors="coerce")

This is the most important trick:

pd.to_datetime(out["date"], errors="coerce").dt.strftime("%Y-%m-%d")

• pd.to_datetime accepts a huge variety of formats: ISO, US, European, with or without time, with or without timezone.
• errors="coerce" means: on parse failure, return NaT (not-a-time) instead of crashing. Step 4 later drops NaT rows.
• .dt.strftime("%Y-%m-%d") re-formats to ISO. Strings sort correctly; comparing dates as strings just works.

Input	Output
"2027-12-27"	"2027-12-27"
"12/27/2027"	"2027-12-27"
"27 Dec 2027"	"2027-12-27"
"2027-12-27 14:32:01"	"2027-12-27"
""	NaT (dropped)
"banana"	NaT (dropped)

Step 2 — Signed amount

out["amount"] = out["debit"].astype(float) - out["credit"].astype(float)

After this:

• amount > 0 means money went out (expense).
• amount < 0 means money came in (refund, payment received).
• amount == 0 is theoretically possible (zero-dollar transaction) — kept as-is.

We lose the original debit / credit split, but the SQL we generate later only ever needs amount. Trade-off: simpler SQL, no information loss for the use cases we care about.

Step 3 — Trim strings

for col in ("card", "description", "category"):
    out[col] = out[col].astype(str).str.strip()

.str.strip() removes leading and trailing whitespace only. Internal spaces (“Coffee Gossip” stays “Coffee Gossip”). The .astype(str) cast guards against NaN sneaking in.

Why this matters: a " Housing" and a "Housing" would be two different categories in SQL. One typo in the source CSV and your group-by produces garbage.

Step 4 — Drop empty / unparseable rows

out = out[(out["description"] != "") & (out["date"].notna())]

Three reasons a row gets dropped:

• description == "" — usually a blank trailing line from Excel.
• date is NaT — failed to parse in step 1 (typo, weird format, header row that snuck in).

We do not drop on null category or null card. Those are fine; they get categorized later by categorize.py.

Step 5 — Attach profile

out["profile"] = profile

The profile is a user-chosen label like "data1", "personal", "joint-account". All rows from a single normalize() call get the same profile.

The SQL layer uses this to scope queries: every WHERE profile = 'data1' clause exists because of this column.

Step 6 — Canonical column order

return out[["profile", "date", "card", "description", "category", "amount"]]

We return columns in a fixed order. The downstream db.load_dataframe() relies on this order matching the SQLite schema.

Deduplication — not done here

Wait, the chapter title mentions “dedup”. Where’s that?

Two strategies:

1. DELETE-then-INSERT by profile (in db.py). Whole profile gets replaced on each load. Simple and good for our case.
2. drop_duplicates on (date, description, amount). Useful if you append multiple files but a real merchant could legitimately charge twice on the same day.

We chose option 1 (in db.py, not here). If you want stricter dedup, add this line at the end of normalize:

out = out.drop_duplicates(subset=["date", "description", "amount"], keep="first")

But verify against your real data first — duplicates can be legitimate.

Live test

>>> import sys; sys.path.insert(0, "csv-llm-shared")
>>> import ingest, normalize
>>> df = ingest.read_csv("csv-llm-shared/data/data1-anonymized.csv")
>>> nf = normalize.normalize(df, profile="data1")
>>> nf.shape
(734, 6)
>>> nf.columns.tolist()
['profile', 'date', 'card', 'description', 'category', 'amount']
>>> nf.dtypes
profile         object
date            object
card            object
description     object
category        object
amount         float64
dtype: object
>>> nf.head(3)
  profile        date              card           description   category  amount
0   data1  2027-12-27  ************4382      Late Payment Fee       Fees   24.31
1   data1  2027-12-26  ************4382        Postbox Rental    Housing  489.53
2   data1  2027-12-25  ************4382      City Cab Network  Transport   73.34
>>> nf["amount"].describe()
count    734.000000
mean     102.069672
std      262.831421
min     -1095.000000
25%        0.000000
50%       55.180000
75%      144.510000
max     2718.880000

Notice the min: -1095.00 — that’s a refund or card payment (negative amount).

Why not split into expenses and refunds tables?

A common temptation: have two SQL tables. We don’t, because:

1. The LLM finds it easier to write WHERE amount > 0 than to remember “should I query the expenses table or the refunds one?”.
2. Some questions span both (“what’s my net spending this month?” needs both).
3. SQLite is fast enough that one filter doesn’t matter.

Takeaways

• 15 lines of code turn a raw DataFrame into a clean, ISO-dated, signed, profile-tagged one.
• pd.to_datetime(errors="coerce") is the unsung hero — handles any date format you’ll meet.
• Signed amount (one column) is simpler for the LLM than two columns.
• Dedup is done at the DB layer (DELETE-then-INSERT by profile), not here.

Next: db.py — SQLite + safe_query →