`scripts/build.py`

57 lines
import csv, re, collections
IN="data/MasterLicenseData.csv"
OUT="out/cslb_call_list_CA.csv"
csv.field_size_limit(10**7)
kw=re.compile(r'WALLCOVER|WALLPAPER|PAPER ?HANG', re.I)
BUCKETS={"C9":"Drywall (C-9, your 'C52')","C54":"Ceramic/Mosaic Tile (C-54)",
         "C33":"Painting & Decorating (C-33)","D06":"Legacy D-06","WALLCOVER":"Wallcovering/paper-hang (name)"}
def toks(s): return set(t.upper().replace('-','') for t in re.split(r'[\s,|/]+', s or '') if t)
rows_out=[]; per=collections.Counter(); phone_per=collections.Counter(); seen=set()
with open(IN,newline='',encoding='latin-1') as fh:
    r=csv.DictReader(fh)
    for row in r:
        if (row.get('State') or '').strip().upper()!='CA': continue
        if (row.get('PrimaryStatus') or '').strip().upper()!='CLEAR': continue
        cls=toks(row.get('Classifications(s)'))
        name=(row.get('FullBusinessName') or '')+' '+(row.get('BusinessName') or '')
        buckets=[]
        if 'C9' in cls: buckets.append('C9')
        if 'C54' in cls: buckets.append('C54')
        if 'C33' in cls: buckets.append('C33')
        if 'D06' in cls: buckets.append('D06')
        if kw.search(name): buckets.append('WALLCOVER')
        if not buckets: continue
        lic=row.get('LicenseNo','')
        if lic in seen: continue
        seen.add(lic)
        phone=(row.get('BusinessPhone') or '').strip()
        for b in buckets:
            per[b]+=1
            if phone: phone_per[b]+=1
        rows_out.append({
            'license_no':lic,
            'business_name':row.get('FullBusinessName') or row.get('BusinessName'),
            'matched_buckets':'|'.join(buckets),
            'all_classifications':(row.get('Classifications(s)') or '').replace('|',' ').strip(),
            'business_type':row.get('BusinessType'),
            'address':row.get('MailingAddress'),'city':row.get('City'),
            'county':row.get('County'),'state':row.get('State'),'zip':row.get('ZIPCode'),
            'business_phone':phone,
            'primary_status':row.get('PrimaryStatus'),
            'issue_date':row.get('IssueDate'),'expiration_date':row.get('ExpirationDate'),
            # enrichment placeholders (phase 2)
            'email':'','website':'','linkedin':'','yelp':'',
        })
cols=['license_no','business_name','matched_buckets','all_classifications','business_type',
      'address','city','county','state','zip','business_phone','primary_status',
      'issue_date','expiration_date','email','website','linkedin','yelp']
with open(OUT,'w',newline='',encoding='utf-8') as fh:
    w=csv.DictWriter(fh,fieldnames=cols); w.writeheader(); w.writerows(rows_out)
print("UNIQUE LICENSES WRITTEN:", len(rows_out))
print("OUTPUT:", OUT)
print("\nPER-BUCKET (a license can be in >1):")
for b in ["C9","C54","WALLCOVER","C33","D06"]:
    print(f"  {b:10s} {BUCKETS[b]:35s} count={per[b]:6d}  with_phone={phone_per[b]:6d}  ({100*phone_per[b]//max(per[b],1)}%)")
tot_phone=sum(1 for r in rows_out if r['business_phone'])
print(f"\nTOTAL with business phone: {tot_phone}/{len(rows_out)} ({100*tot_phone//max(len(rows_out),1)}%)")