1) Purpose & analytic scope

#!/usr/bin/env python3
"""
org_extract_with_county_fips.py

Build an org-level extract from IRS EO BMF regional files (eo1–eo4),
attach a 5-digit county_fips using a HUD ZIP crosswalk, and filter to
a defined set of focus areas used by this project.

Inputs (default: current directory):
- eo1*.csv, eo2*.csv, eo3*.csv, eo4*.csv
- hud_zip_county_crosswalk.csv  (ZIP->TRACT with ratios)

Output (default: current directory):
- org_extract_with_county_fips.csv
"""

import argparse
import glob
import os
import re
import sys
from typing import List, Optional

import pandas as pd

OUTPUT_COLUMNS = [
    "ein",
    "org_name",
    "city",
    "state",
    "zip",
    "ntee_code",
    "focus_area",
    "county_fips",
    "source_file",
]

FILTER_TO_501C3_IF_POSSIBLE = True
FILTER_TO_ACTIVE_IF_POSSIBLE = False

ALLOWED_FOCUS_AREAS = {
    "HEALTHCARE",
    "BEHAVIORAL_HEALTH",
    "HOUSING",
    "HUMAN_SERVICES",
    "FOOD_SECURITY",
    "TRANSPORTATION",
    "EMPLOYMENT",
    "EDUCATION",
    "LEGAL_AID",
    "YOUTH_SERVICES",
    "DISABILITY_SERVICES",
    "SUBSTANCE_USE",
    "FAMILY_SUPPORT",
}

NTEE_MAJOR_TO_FOCUS = {
    "E": "HEALTHCARE",
    "P": "HUMAN_SERVICES",
    "L": "HOUSING",
    "N": "EMPLOYMENT",
    "B": "EDUCATION",
    "O": "YOUTH_SERVICES",
    "R": "LEGAL_AID",
    "G": "DISABILITY_SERVICES",
    "F": "BEHAVIORAL_HEALTH",
}

NTEE_CODE_REGEX_TO_FOCUS = [
    (r"^F2\d", "SUBSTANCE_USE"),
]


def read_csv_flexible(path: str) -> pd.DataFrame:
    try:
        return pd.read_csv(path, dtype=str, low_memory=False)
    except Exception:
        return pd.read_csv(path, dtype=str, low_memory=False, sep=None, engine="python")


def clean_zip5(x: object) -> Optional[str]:
    if pd.isna(x):
        return None
    s = str(x).strip()
    if not s:
        return None
    digits = re.sub(r"\D", "", s)
    if len(digits) < 5:
        return None
    return digits[:5]


def normalize_state(x: object) -> Optional[str]:
    if pd.isna(x):
        return None
    s = str(x).strip().upper()
    return s if s else None


def pad_fips5(x: object) -> Optional[str]:
    if pd.isna(x):
        return None
    s = str(x).strip()
    if not s:
        return None
    digits = re.sub(r"\D", "", s)
    if not digits:
        return None
    return digits.zfill(5)[-5:]


def pick_first_col(df: pd.DataFrame, candidates: List[str]) -> Optional[str]:
    cols_upper = {c.upper(): c for c in df.columns}
    for cand in candidates:
        if cand.upper() in cols_upper:
            return cols_upper[cand.upper()]
    return None


def choose_ratio_col(hud: pd.DataFrame) -> Optional[str]:
    for p in ["RES_RATIO", "TOT_RATIO", "BUS_RATIO"]:
        col = pick_first_col(hud, [p])
        if col:
            return col
    ratio_like = [c for c in hud.columns if "RATIO" in c.upper()]
    return ratio_like[0] if ratio_like else None


def tract_to_county_fips(v: object) -> Optional[str]:
    s = re.sub(r"\D", "", str(v).strip()) if not pd.isna(v) else ""
    if len(s) < 5:
        return None
    return s[:5]


def load_hud_primary_zip_to_county(hud_path: str) -> pd.DataFrame:
    hud = read_csv_flexible(hud_path)
    zip_col = pick_first_col(hud, ["ZIP", "ZIPCODE", "ZCTA5", "ZCTA"])
    tract_col = pick_first_col(hud, ["TRACT", "GEOID", "TRACT_GEOID", "CENSUS_TRACT"])
    ratio_col = choose_ratio_col(hud)

    if not zip_col or not tract_col:
        raise ValueError(
            "HUD crosswalk must include ZIP and TRACT (or GEOID) columns.\n"
            f"Detected zip_col={zip_col}, tract_col={tract_col}\n"
            f"Columns={list(hud.columns)}"
        )

    keep_cols = [zip_col, tract_col] + ([ratio_col] if ratio_col else [])
    hud2 = hud[keep_cols].copy()
    hud2["zip5"] = hud2[zip_col].apply(clean_zip5)
    hud2["county_fips"] = hud2[tract_col].apply(tract_to_county_fips)

    if ratio_col:
        hud2["ratio"] = pd.to_numeric(hud2[ratio_col], errors="coerce")
    else:
        hud2["ratio"] = 1.0

    hud2 = hud2.dropna(subset=["zip5", "county_fips"])
    hud2 = hud2.sort_values(["zip5", "ratio"], ascending=[True, False])
    primary = hud2.drop_duplicates(subset=["zip5"], keep="first")[["zip5", "county_fips"]].copy()
    return primary


def map_focus_area(ntee_code: str) -> Optional[str]:
    if not ntee_code or pd.isna(ntee_code):
        return None
    code = str(ntee_code).strip().upper()
    if not code:
        return None

    for pattern, focus in NTEE_CODE_REGEX_TO_FOCUS:
        if re.match(pattern, code):
            return focus

    major = code[:1]
    return NTEE_MAJOR_TO_FOCUS.get(major)


def load_and_stack_eo_files(input_dir: str) -> pd.DataFrame:
    patterns = [
        os.path.join(input_dir, "eo1*.csv"),
        os.path.join(input_dir, "eo2*.csv"),
        os.path.join(input_dir, "eo3*.csv"),
        os.path.join(input_dir, "eo4*.csv"),
    ]
    files: List[str] = []
    for p in patterns:
        files.extend(glob.glob(p))

    if not files:
        raise FileNotFoundError(f"No EO files found in {input_dir} matching eo1*.csv ... eo4*.csv")

    frames = []
    for f in sorted(files):
        print(f"Reading: {os.path.basename(f)}")
        df = read_csv_flexible(f)
        df["_source_file"] = os.path.basename(f)
        frames.append(df)

    return pd.concat(frames, ignore_index=True)


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--input-dir", default=".", help="Directory containing eo1–eo4 and HUD crosswalk.")
    parser.add_argument("--hud-file", default="hud_zip_county_crosswalk.csv", help="HUD crosswalk filename (within input-dir) or path.")
    parser.add_argument("--out", default="org_extract_with_county_fips.csv", help="Output CSV path.")
    args = parser.parse_args()

    input_dir = args.input_dir
    hud_path = args.hud_file
    if not os.path.isabs(hud_path):
        hud_path = os.path.join(input_dir, hud_path)

    out_path = args.out

    if not os.path.exists(hud_path):
        print(f"\n❌ HUD crosswalk not found at: {hud_path}", file=sys.stderr)
        return 1

    print("\n[1/6] Loading HUD ZIP→(primary)County mapping…")
    zip_to_county = load_hud_primary_zip_to_county(hud_path)
    print(f"ZIP mappings loaded: {len(zip_to_county):,}")

    print("\n[2/6] Loading EO BMF regional files (eo1–eo4)…")
    eo = load_and_stack_eo_files(input_dir)
    print(f"EO rows loaded: {len(eo):,}")

    ein_col = pick_first_col(eo, ["EIN"])
    name_col = pick_first_col(eo, ["NAME", "ORG_NAME", "ORGANIZATION_NAME", "PRIMARY_NAME", "NAME1"])
    state_col = pick_first_col(eo, ["STATE", "STATECD", "STATE_CD"])
    city_col = pick_first_col(eo, ["CITY"])
    zip_col = pick_first_col(eo, ["ZIP", "ZIPCD", "ZIP_CODE", "ZIP5", "ZIP_CODE_5"])
    ntee_col = pick_first_col(eo, ["NTEE_CD", "NTEE_CODE", "NTEE"])
    subsection_col = pick_first_col(eo, ["SUBSECTION", "SUBSECTION_CD"])
    status_col = pick_first_col(eo, ["STATUS", "ORG_STATUS", "STATUS_CD"])

    if not zip_col:
        print("\n❌ Could not detect a ZIP column in EO files.", file=sys.stderr)
        return 1
    if not ntee_col:
        print("\n❌ Could not detect an NTEE code column in EO files.", file=sys.stderr)
        return 1

    print("\n[3/6] Cleaning EO data…")
    work = eo.copy()
    work["zip5"] = work[zip_col].apply(clean_zip5)
    work["state"] = work[state_col].apply(normalize_state) if state_col else ""
    work["ein_norm"] = work[ein_col].astype(str).str.replace(r"\D", "", regex=True) if ein_col else ""
    work["ntee_norm"] = work[ntee_col].astype(str).str.strip().str.upper()

    if FILTER_TO_501C3_IF_POSSIBLE and subsection_col:
        before = len(work)
        sub = work[subsection_col].astype(str).str.strip()
        work = work[sub.isin(["03", "3", "003"])]
        print(f"Filtered to subsection 03 (501(c)(3)) where available: {before:,} → {len(work):,}")

    if FILTER_TO_ACTIVE_IF_POSSIBLE and status_col:
        vals = work[status_col].astype(str).str.upper().str.strip()
        if (vals.str.contains("ACTIVE")).any():
            before = len(work)
            work = work[vals.str.contains("ACTIVE")]
            print(f"Filtered to ACTIVE (text match): {before:,} → {len(work):,}")

    before = len(work)
    work = work[work["ntee_norm"].str.len() > 0].copy()
    print(f"Removed rows missing NTEE: {before:,} → {len(work):,}")

    print("\n[4/6] Attaching county_fips via ZIP…")
    work = work.merge(zip_to_county, how="left", left_on="zip5", right_on="zip5")

    total = len(work)
    missing_county = work["county_fips"].isna().sum()
    print(f"Rows missing county_fips after HUD merge: {missing_county:,} ({missing_county/total:.2%})")

    before = len(work)
    work = work.dropna(subset=["county_fips"]).copy()
    print(f"Dropped rows without county_fips: {before:,} → {len(work):,}")

    print("\n[5/6] Classifying focus areas and filtering to project scope…")
    work["focus_area"] = work["ntee_norm"].apply(map_focus_area)

    before = len(work)
    work = work.dropna(subset=["focus_area"]).copy()
    print(f"Removed rows not classifiable to a focus area: {before:,} → {len(work):,}")

    before = len(work)
    work["focus_area"] = work["focus_area"].astype(str).str.upper().str.strip()
    allowed = {x.upper() for x in ALLOWED_FOCUS_AREAS}
    work = work[work["focus_area"].isin(allowed)].copy()
    print(f"Filtered to allowed focus areas: {before:,} → {len(work):,}")

    out = pd.DataFrame()
    out["ein"] = work["ein_norm"].astype(str).str.strip()
    out["org_name"] = work[name_col].astype(str).str.strip() if name_col else ""
    out["city"] = work[city_col].astype(str).str.strip() if city_col else ""
    out["state"] = work["state"].astype(str).str.strip()
    out["zip"] = work["zip5"].astype(str).str.strip()
    out["ntee_code"] = work["ntee_norm"].astype(str).str.strip()
    out["focus_area"] = work["focus_area"].astype(str).str.strip()
    out["county_fips"] = work["county_fips"].apply(pad_fips5)
    out["source_file"] = work["_source_file"].astype(str)

    if out["ein"].str.len().gt(0).any():
        before = len(out)
        out = out.sort_values(["ein", "org_name"]).drop_duplicates(subset=["ein"], keep="first")
        print(f"Deduped by EIN: {before:,} → {len(out):,}")

    out = out[OUTPUT_COLUMNS].copy()
    out = out.sort_values(["county_fips", "focus_area", "org_name"]).reset_index(drop=True)

    print("\n[6/6] Writing output…")
    out.to_csv(out_path, index=False)
    print(f"✅ Wrote: {out_path}")
    print(f"✅ Rows: {len(out):,}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

#!/usr/bin/env python3
"""
county_focus_area.py

Build county_focus_area.csv (county × focus_area) using:
- org_extract_with_county_fips.csv (org-level output; filtered + classified)
- ACS 5-year county population from Census API

Output:
- county_focus_area.csv
"""

import argparse
import re
import sys

import pandas as pd
import requests


def pad_fips(x: object, width: int = 5):
    if pd.isna(x):
        return None
    s = str(x).strip()
    if not s:
        return None
    s = re.sub(r"\D", "", s)
    if not s:
        return None
    return s.zfill(width)[-width:]


def fetch_acs_population_by_county(year: int) -> pd.DataFrame:
    url = f"https://api.census.gov/data/{year}/acs/acs5"
    params = {"get": "B01003_001E", "for": "county:*", "in": "state:*"}
    resp = requests.get(url, params=params, timeout=45)
    resp.raise_for_status()

    data = resp.json()
    header = data[0]
    rows = data[1:]
    df = pd.DataFrame(rows, columns=header)

    df["state_fips"] = df["state"].apply(lambda v: pad_fips(v, 2))
    df["county_fips"] = (df["state_fips"] + df["county"].apply(lambda v: pad_fips(v, 3))).astype(str)
    df["population"] = pd.to_numeric(df["B01003_001E"], errors="coerce")

    out = df[["county_fips", "population"]].dropna(subset=["county_fips", "population"]).copy()
    out["population"] = out["population"].astype(int)
    return out


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--year", type=int, default=2022)
    parser.add_argument("--org", default="org_extract_with_county_fips.csv")
    parser.add_argument("--out", default="county_focus_area.csv")
    args = parser.parse_args()
    year = args.year

    if not pd.io.common.file_exists(args.org):
        print(f"❌ Missing input file: {args.org}", file=sys.stderr)
        print("Run org_extract_with_county_fips.py first.", file=sys.stderr)
        return 1

    print(f"[1/4] Reading org extract: {args.org}")
    org = pd.read_csv(args.org, dtype=str, low_memory=False)

    required = {"county_fips", "focus_area"}
    missing = required - set(org.columns)
    if missing:
        print(f"❌ org extract missing columns: {sorted(missing)}", file=sys.stderr)
        return 1

    org["county_fips"] = org["county_fips"].apply(lambda v: pad_fips(v, 5))
    org["focus_area"] = org["focus_area"].astype(str).str.strip().str.upper()

    org = org.dropna(subset=["county_fips"])
    org = org[org["focus_area"].str.len() > 0].copy()

    print("[2/4] Aggregating counts by county × focus_area…")
    counts = (
        org.groupby(["county_fips", "focus_area"], as_index=False)
           .size()
           .rename(columns={"size": "nonprofit_count"})
    )

    print(f"[3/4] Fetching ACS {year} county population…")
    pop = fetch_acs_population_by_county(year)

    print("[4/4] Joining + computing per-100k by focus…")
    out = counts.merge(pop, how="left", on="county_fips")
    out = out.dropna(subset=["population"]).copy()
    out = out[out["population"] > 0].copy()

    out["nonprofits_per_100k_focus"] = (out["nonprofit_count"] / out["population"]) * 100000.0
    out["year"] = year

    out = out[[
        "county_fips",
        "focus_area",
        "year",
        "nonprofit_count",
        "nonprofits_per_100k_focus",
    ]].sort_values(["county_fips", "focus_area"]).reset_index(drop=True)

    out.to_csv(args.out, index=False)
    print(f"✅ Wrote: {args.out} (rows={len(out):,})")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

#!/usr/bin/env python3
"""
county_summary.py

Build county_summary.csv (1 row per county) using:
- org_extract_with_county_fips.csv (org-level output; focus-filtered)
- ACS 5-year county population from Census API

Output:
- county_summary.csv
"""

import argparse
import re
import sys

import pandas as pd
import requests


def pad_fips(x: object, width: int = 5):
    if pd.isna(x):
        return None
    s = str(x).strip()
    if not s:
        return None
    s = re.sub(r"\D", "", s)
    if not s:
        return None
    return s.zfill(width)[-width:]


def fetch_acs_population_by_county(year: int) -> pd.DataFrame:
    url = f"https://api.census.gov/data/{year}/acs/acs5"
    params = {"get": "NAME,B01003_001E", "for": "county:*", "in": "state:*"}
    resp = requests.get(url, params=params, timeout=45)
    resp.raise_for_status()

    data = resp.json()
    header = data[0]
    rows = data[1:]
    df = pd.DataFrame(rows, columns=header)

    df["state_fips"] = df["state"].apply(lambda v: pad_fips(v, 2))
    df["county_fips"] = (df["state_fips"] + df["county"].apply(lambda v: pad_fips(v, 3))).astype(str)

    name_split = df["NAME"].str.split(", ", n=1, expand=True)
    df["county_name"] = name_split[0].fillna(df["NAME"])

    df["population"] = pd.to_numeric(df["B01003_001E"], errors="coerce")

    out = df[["county_fips", "county_name", "population"]].copy()
    out["year"] = year
    out = out.dropna(subset=["county_fips", "population"])
    out["population"] = out["population"].astype(int)
    return out


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--year", type=int, default=2022)
    parser.add_argument("--org", default="org_extract_with_county_fips.csv")
    parser.add_argument("--out", default="county_summary.csv")
    args = parser.parse_args()
    year = args.year

    if not pd.io.common.file_exists(args.org):
        print(f"❌ Missing input file: {args.org}", file=sys.stderr)
        print("Run org_extract_with_county_fips.py first.", file=sys.stderr)
        return 1

    print(f"[1/4] Reading org extract: {args.org}")
    org = pd.read_csv(args.org, dtype=str, low_memory=False)

    if "county_fips" not in org.columns:
        print("❌ org extract missing county_fips column.", file=sys.stderr)
        return 1

    org["county_fips"] = org["county_fips"].apply(lambda v: pad_fips(v, 5))
    org = org.dropna(subset=["county_fips"])

    print("[2/4] Aggregating org counts by county…")
    counts = org.groupby("county_fips", as_index=False).size().rename(columns={"size": "total_nonprofits"})

    print(f"[3/4] Fetching ACS {year} county population…")
    pop = fetch_acs_population_by_county(year)

    print("[4/4] Joining + computing per-100k…")
    out = pop.merge(counts, how="left", on="county_fips")
    out["total_nonprofits"] = out["total_nonprofits"].fillna(0).astype(int)

    out = out[out["population"] > 0].copy()
    out["nonprofits_per_100k"] = (out["total_nonprofits"] / out["population"]) * 100000.0

    out = out[[
        "county_fips",
        "county_name",
        "year",
        "population",
        "total_nonprofits",
        "nonprofits_per_100k",
    ]].sort_values("county_fips").reset_index(drop=True)

    out.to_csv(args.out, index=False)
    print(f"✅ Wrote: {args.out} (rows={len(out):,})")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())