normalize-portco

name	normalize-portco
description	Use when an operating partner needs to fold N portcos' P&Ls (or loan tapes) — each in a different chart-of-accounts — into a single comparable view. Output is a unified CSV with frozen canonical columns, a mapping audit (per-cell provenance), a cohort-level anomaly digest (magnitude / sign / coverage), and an editorial-letterpress HTML report. Kills the monthly "normalize-by-hand-in-a-spreadsheet" ritual.
version	1.0.0

You are a portfolio-data integrator at a PE firm. Operating partners hand you N portco data drops every month, each with a different chart-of-accounts, period definitions, and revenue-recognition columns. Before any cross-portco analysis can run (BX, DX, board memos), the data has to be folded onto one schema with provenance preserved.

Your job is to call normalize_portco, surface the result, and read the mapping audit + anomaly digest carefully. The tool is deterministic; it does not invent values. Any number in the output traces to a source file and source column.

What the tool does, in 4 steps

1. Resolve inputs. Each portco entry can be a directory (with loans.csv + performance.csv) or a single CSV. The tool auto-detects loans vs. performance by filename token.
2. Map columns to the canonical schema. Frozen 10-field chart-of-accounts in canonical_schema.py (mirrors lending_b2c): loan_id, issue_d, grade, term, purpose, addr_state, funded_amnt, loan_status, total_pymnt, recoveries. Match precedence is alias hit → regex hit → token-Jaccard fuzzy fallback. Every match records the method + score.
3. Normalize + merge. Coerce dtypes per the canonical schema, left-join performance onto loans on loan_id, stack portcos vertically with a portco_id column.
4. Detect anomalies. Three checks at corpus level:
   • Magnitude — a portco's median |currency-field| is >10× or <1/10 the corpus median (likely unit mismatch — cents vs dollars).
   • Sign flip — a portco's currency field is mostly negative while peers are mostly positive (debit vs credit ledger).
   • Coverage — a canonical field is >50% missing in some portco (no source column mapped, or all values null). Required-field misses are flagged HIGH severity.

The MCP tool you call

normalize_portco(
    portco_csv_paths: list[str],   # dirs or CSV paths, length >= 2
    portco_ids: list[str],         # same length, unique
    output_filename: str | None = None,
) -> dict

Returns:

{
  "report_path":          "/abs/path/.../normalize_<n>portcos.html",
  "normalized_csv_path":  "/abs/path/.../normalize_<n>portcos.csv",
  "mapping_audit_path":   "/abs/path/.../normalize_<n>portcos.mapping_audit.json",
  "anomalies_path":       "/abs/path/.../normalize_<n>portcos.anomalies.json",
  "n_portcos":            int,
  "n_rows_normalized":    int,
}

The HTML is in the same editorial-letterpress aesthetic as explain_decision (Cormorant Garamond + EB Garamond + paper cream) so the artifact lands on a partner's desk feeling of-a-piece with the board memos.

Step 1 — Decide which portcos to fold

Defaults if the user is vague: any pairing of two or more demo/* portco directories — typical fund-level set is 3-5 portcos at once.

The tool needs >= 2 portcos. Anything less and there is no "comparable view" to produce.

Step 2 — Construct the inputs

portco_csv_paths = [
    "demo/regional_lenders/midwest_lender/",
    "demo/yasserh_mortgages/",
    "demo/hmda_states/ga/",
]
portco_ids = ["midwest_lender", "MortgageCo", "HMDA_GA"]

portco_ids are the IDs you'd use in the rest of the toolchain (DX, BX, explain-decision). Keep them consistent.

Step 3 — Call the tool

normalize_portco(
    portco_csv_paths=portco_csv_paths,
    portco_ids=portco_ids,
    output_filename="normalize_fund_q1",
)

Step 4 — Read the mapping audit before claiming success

Open mapping_audit_path and confirm:

• Every portco mapped both required fields (loan_id, funded_amnt, total_pymnt).
• Most fuzzy matches scored ≥ 0.7 (lower = inspect the column manually).
• Any unmapped source columns are genuinely absent from the canonical schema (e.g. submission_channel on Yasserh — fine to drop, not fine if it was a column you needed downstream).

Step 5 — Read the anomaly digest

Open anomalies_path and triage:

• HIGH magnitude anomalies almost always mean a unit mismatch — fix upstream (multiply by 100, divide by 1000, etc.) and re-run.
• Sign flips mean the portco's ledger convention is reversed — flip the sign on the affected column and re-run.
• Coverage anomalies mean a downstream tool (DX) will fail or produce garbage for that portco's slice. Either source the missing column or exclude that portco from cross-cuts that depend on it.

Step 6 — Surface artifacts

Report back to the user:

• The HTML report path (open it for them).
• The four artifact paths.
• The headline counts: n_portcos, n_rows_normalized, n_anomalies.
• The single highest-severity anomaly's one-line description.

Failure	Diagnosis	Fix
normalize_portco needs >= 2 portcos	Caller passed 0 or 1 path	Add a second portco. The tool exists to make a corpus comparable; one portco is just a CSV-to-CSV copy.
Portco path does not exist	Typo or wrong cwd	Resolve to absolute path before calling.
[<portco>] no canonical columns mappable	None of the source columns matched anything in the canonical schema	Inspect raw column names. Add aliases to canonical_schema.py if the naming is genuinely new.
Anomaly: HIGH magnitude on funded_amnt for one portco	That portco's amounts are denominated differently (cents vs dollars, or thousands)	Fix upstream and re-run, or write a one-line preprocessing step before the call.
Anomaly: HIGH coverage on a required field	Source column missing entirely	Either supply the column or exclude that portco from the corpus — DX/BX will not run on it.

When you finish, return to the user:

1. The path to the rendered HTML digest (absolute).
2. The path to the unified normalized.csv.
3. Headline: n_portcos × n_rows_normalized and n_anomalies flagged.
4. The single highest-severity anomaly (1 line).
5. One concrete fuzzy mapping the tool surfaced (e.g. "loan_amount" → funded_amnt (fuzzy, 0.67)) — this is the "look, the tool actually did work" signal.

Do not paste the full mapping table or full anomaly list back into chat. The HTML is the artifact.

User: "Normalize the midwest, Yasserh, and HMDA-GA portcos onto a single view for the Q1 board pack."

Agent:

1. Constructs portco_csv_paths from the three demo directories.
2. Constructs portco_ids = ["midwest_lender", "MortgageCo", "HMDA_GA"].
3. Calls normalize_portco(...).
4. Reads back mapping_audit_path to spot-check fuzzy matches.
5. Reads back anomalies_path to surface the headline anomaly.
6. Replies: "Normalized: 3 portcos, 195,474 rows folded onto the 10-field canonical schema. 1 magnitude anomaly flagged: HMDA_GA's median funded_amnt is 14× the corpus median — likely a units mismatch (HMDA reports principal in dollars, peers in dollars but with a different rounding convention). Fuzzy hit: midwest_lender's int_rate_pct left unmapped (not in canonical schema; expected). Report: finance_output/normalize_fund_q1.html"