`scrapers/ca_dre_subdivider.py`

66 lines
#!/usr/bin/env python3
# CA DRE New Subdivision Filing List (.xls) → CSV for realestate.rentv_licensed_targets (role=Developer).
# Subdivider = the developer. Feed-first, no auth. Filters SoCal counties, dedups by subdivider.
import urllib.request, csv, sys, io, hashlib, json
import xlrd

SOCAL = {'LOS ANGELES':'Greater LA','ORANGE':'Orange County','RIVERSIDE':'Inland Empire',
         'SAN BERNARDINO':'Inland Empire','SAN DIEGO':'San Diego','VENTURA':'Ventura'}
UA = {'User-Agent':'Mozilla/5.0 Chrome/126'}

def months():
    for yy in (2024, 2025, 2026):
        for mm in range(1, 13):
            if yy == 2026 and mm > 7: break
            yield yy, mm

def col_idx(header):
    h = {str(c).strip().lower(): i for i, c in enumerate(header)}
    def find(*names):
        for n in names:
            if n in h: return h[n]
        return None
    return {
        'file': find('file number'), 'county': find('county name'),
        'subtype': find('subdivision subtype code'), 'subdivider': find('subdivider'),
        'phone': find('phone number'), 'addr1': find('address line 1'), 'addr2': find('address line 2'),
        'city': find('city name'), 'state': find('usps state code'), 'zip': find('zip code'),
        'tract': find('tract number'), 'subname': find('subdivision name'), 'filed': find('filed'),
    }

seen = set()
rows = []
for yy, mm in months():
    url = f"https://www.dre.ca.gov/files/excel/{yy}/subdivider_list_{mm:02d}_{str(yy)[2:]}.xls"
    try:
        data = urllib.request.urlopen(urllib.request.Request(url, headers=UA), timeout=30).read()
        if len(data) < 5000: continue
        wb = xlrd.open_workbook(file_contents=data)
    except Exception:
        continue
    sh = wb.sheet_by_index(0)
    ci = col_idx(sh.row_values(0))
    if ci['subdivider'] is None or ci['county'] is None: continue
    for r in range(1, sh.nrows):
        row = sh.row_values(r)
        county = str(row[ci['county']]).strip().upper()
        if county not in SOCAL: continue
        sub = str(row[ci['subdivider']]).strip()
        if not sub: continue
        key = sub.lower()
        if key in seen: continue
        seen.add(key)
        g = lambda k: (str(row[ci[k]]).strip() if ci[k] is not None else '')
        rows.append([
            'ca_dre_subdivision', 'Developer', sub, '',
            g('file') or ('cadre-'+hashlib.md5(key.encode()).hexdigest()[:10]),
            g('subtype') or 'Subdivision', 'Filed',
            g('addr1'), g('city'), county.title(), g('state') or 'CA', g('zip'), g('phone'), '',
            SOCAL[county], 't', 't', url,
            json.dumps({"tract": g('tract'), "subdivision": g('subname')})
        ])

w = csv.writer(open('/tmp/cadre_dev.csv', 'w', newline=''))
for r in rows: w.writerow(r)
sys.stderr.write(f"CA DRE subdivider: {len(rows)} unique SoCal developers -> /tmp/cadre_dev.csv\n")