← back to La Permits School
Add enrich_contractors.py: dedupe permits to unique contractors ranked by build value, with CA SOS + CSLB lookup links (filters owner-builder noise)
4e1bc46badfed160cc2951c43b1ce9710e2fe393 · 2026-08-10 13:05:15 -0700 · Steve Abrams
Files touched
M README.mdA enrich_contractors.py
Diff
commit 4e1bc46badfed160cc2951c43b1ce9710e2fe393
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 10 13:05:15 2026 -0700
Add enrich_contractors.py: dedupe permits to unique contractors ranked by build value, with CA SOS + CSLB lookup links (filters owner-builder noise)
---
README.md | 23 +++++++--
enrich_contractors.py | 138 ++++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 157 insertions(+), 4 deletions(-)
diff --git a/README.md b/README.md
index 61491f6..bdc1b22 100644
--- a/README.md
+++ b/README.md
@@ -32,10 +32,25 @@ python3 pull_permits.py --limit 500 --out sample.csv
- `--app-token TOKEN` — optional free Socrata token (only if you hit rate limits)
## The pipeline
-1. **LADBS** (this tool) → bulk CSV of significant permits with the party names.
-2. **CA SOS** (free lookup) → open any row's `ca_sos_lookup_url` to resolve the
- contractor's legal entity + agent of record. Join on the business name (or use
- the `apn` column to tie back to the county assessor).
+1. **`pull_permits.py`** → bulk CSV of significant permits with the party names.
+2. **`enrich_contractors.py`** → dedupe those permits to **unique contractors**,
+ ranked by total project value, each with ready-to-click **CA SOS** (by name) and
+ **CSLB** (by license #) lookup links:
+ ```bash
+ python3 enrich_contractors.py # permits.csv -> contractors.csv
+ python3 enrich_contractors.py --min-permits 2 # firms with 2+ permits only
+ ```
+ Example: 1,000 permits → ~290 unique firms; top row = Hensel Phelps ($1.6B / 10
+ permits). This deduped list IS your "top LA contractors" ranking.
+3. **CA SOS + CSLB** (free lookups) → open the URLs on the firms you care about to
+ resolve the legal entity + agent (SOS) and license status/classification (CSLB).
+
+### Why the enrichment is a worklist, not an auto-scrape
+There is no reliable *free* HTTP endpoint for entity data: CA SOS bizfile sits behind
+Imperva/Incapsula bot protection, OpenCorporates now requires a paid token, and CSLB
+session-gates its license page. Rather than fight three anti-bot walls (or drive a real
+browser, a ToS gray area), the tool **dedupes to the handful of unique firms** so a
+guided manual lookup is trivial — and ranks them so you check the important ones first.
## Data notes
- `d9aa-v8bm` is a **stable historical snapshot: 2013-01 → 2023-05, ~317k permits**.
diff --git a/enrich_contractors.py b/enrich_contractors.py
new file mode 100644
index 0000000..2bc6035
--- /dev/null
+++ b/enrich_contractors.py
@@ -0,0 +1,138 @@
+#!/usr/bin/env python3
+"""
+enrich_contractors.py — dedupe permits.csv down to UNIQUE contractors and attach
+free entity-lookup links (school project). $0, standard library only.
+
+Why this shape: the CA Secretary of State bizfile site is behind Imperva/Incapsula
+bot protection (blocks plain HTTP), OpenCorporates now needs a paid token, and the
+CSLB license page is session-gated. So there is no reliable *free* HTTP endpoint to
+auto-scrape entity data. The high-leverage move instead:
+
+ 1. collapse thousands of permit rows to the handful of UNIQUE contractors,
+ 2. rank them by total project value (the "top contractors" list you actually want),
+ 3. hand each one ready-to-click lookups for CA SOS (by name) AND CSLB (by license).
+
+You then click a small, deduped worklist — not thousands of rows.
+
+Usage:
+ python3 enrich_contractors.py # reads permits.csv -> contractors.csv
+ python3 enrich_contractors.py --in sample.csv --out firms.csv
+ python3 enrich_contractors.py --min-permits 2 # only firms with >=2 permits
+"""
+
+import argparse
+import csv
+import re
+import urllib.parse
+
+CA_SOS_SEARCH = "https://bizfileonline.sos.ca.gov/search/business"
+CSLB_LOOKUP = "https://www.cslb.ca.gov/OnlineServices/CheckLicenseII/CheckLicense.aspx"
+
+
+def norm_name(name):
+ """Normalize a business name for grouping (uppercase, collapse space/punct)."""
+ n = (name or "").upper().strip()
+ n = re.sub(r"[.,]", " ", n)
+ n = re.sub(r"\s+", " ", n)
+ return n.strip()
+
+
+def money(v):
+ try:
+ return float(v)
+ except (TypeError, ValueError):
+ return 0.0
+
+
+def sos_url(name):
+ return CA_SOS_SEARCH + "?" + urllib.parse.urlencode({"searchType": "BUSINESS", "q": name}) if name else ""
+
+
+def cslb_url(license_no):
+ # CSLB gates the direct license-detail page, so link the public lookup and
+ # carry the number in its own column for a one-paste check.
+ return CSLB_LOOKUP + "?" + urllib.parse.urlencode({"LicNum": license_no}) if license_no else CSLB_LOOKUP
+
+
+def main():
+ ap = argparse.ArgumentParser(description="Dedupe permits to unique contractors + entity lookups.")
+ ap.add_argument("--in", dest="infile", default="permits.csv", help="input permits CSV (from pull_permits.py)")
+ ap.add_argument("--out", default="contractors.csv", help="output unique-contractor CSV")
+ ap.add_argument("--min-permits", type=int, default=1, help="only keep firms with >= this many permits")
+ args = ap.parse_args()
+
+ firms = {} # norm_name -> aggregate dict
+ total_rows = 0
+ with open(args.infile, newline="", encoding="utf-8") as f:
+ for row in csv.DictReader(f):
+ name = (row.get("contractors_business_name") or "").strip()
+ if not name:
+ continue
+ key = norm_name(name)
+ # "OWNER-BUILDER" is a placeholder (owner pulled their own permit), not a
+ # firm — it aggregates many unrelated owners, so drop it from the ranking.
+ if key in ("OWNER-BUILDER", "OWNER BUILDER", "OWNER/BUILDER"):
+ continue
+ total_rows += 1
+ fm = firms.setdefault(key, {
+ "contractor": name, "permits": 0, "total_valuation": 0.0,
+ "first_seen": "", "last_seen": "", "licenses": set(),
+ "city": row.get("contractor_city", ""), "state": row.get("contractor_state", ""),
+ "top_projects": [], # (valuation, address)
+ })
+ fm["permits"] += 1
+ val = money(row.get("valuation"))
+ fm["total_valuation"] += val
+ d = (row.get("issue_date") or "")[:10]
+ if d:
+ fm["first_seen"] = min(fm["first_seen"], d) if fm["first_seen"] else d
+ fm["last_seen"] = max(fm["last_seen"], d) if fm["last_seen"] else d
+ lic = (row.get("license") or "").strip()
+ if lic and lic.strip("0"): # ignore "0"/"00000" placeholders
+ fm["licenses"].add(lic)
+ fm["top_projects"].append((val, row.get("address", "")))
+
+ rows_out = []
+ for fm in firms.values():
+ if fm["permits"] < args.min_permits:
+ continue
+ top = sorted(fm["top_projects"], reverse=True)[:3]
+ best_license = sorted(fm["licenses"])[0] if fm["licenses"] else ""
+ rows_out.append({
+ "contractor": fm["contractor"],
+ "permits": fm["permits"],
+ "total_valuation": int(fm["total_valuation"]),
+ "first_seen": fm["first_seen"],
+ "last_seen": fm["last_seen"],
+ "licenses": ";".join(sorted(fm["licenses"])),
+ "city": fm["city"],
+ "state": fm["state"],
+ "top_projects": " | ".join(f"${int(v):,} {a}" for v, a in top if a),
+ "ca_sos_lookup_url": sos_url(fm["contractor"]),
+ "cslb_license": best_license,
+ "cslb_lookup_url": cslb_url(best_license),
+ })
+
+ rows_out.sort(key=lambda r: r["total_valuation"], reverse=True)
+
+ cols = ["contractor", "permits", "total_valuation", "first_seen", "last_seen",
+ "licenses", "city", "state", "top_projects",
+ "ca_sos_lookup_url", "cslb_license", "cslb_lookup_url"]
+ with open(args.out, "w", newline="", encoding="utf-8") as f:
+ w = csv.DictWriter(f, fieldnames=cols)
+ w.writeheader()
+ w.writerows(rows_out)
+
+ print("Contractor enrichment — cost: $0 (local dedupe + free lookup links)")
+ print(f" {total_rows} permit rows -> {len(rows_out)} unique contractors")
+ print(f" written to {args.out} (sorted by total project value)")
+ if rows_out:
+ print("\n Top firms by total build value:")
+ for r in rows_out[:5]:
+ print(f" ${r['total_valuation']:>14,} {r['permits']:>3} permits {r['contractor']}")
+ print("\n Enrich: open each row's ca_sos_lookup_url (legal entity + agent) and")
+ print(" cslb_lookup_url (license status/classification). Small deduped worklist.")
+
+
+if __name__ == "__main__":
+ main()
← 5f33d18 LADBS+SOS free permit puller (school project): stdlib Socrat
·
back to La Permits School
·
Add summary.py: class-ready descriptive stats (by year, top 7057299 →