Data Dictionary
This dictionary describes the public-facing analytic outputs used by the Tableau workbook. All geographic alignment uses county_fips as a 5-digit string with leading zeros preserved.
org_extract_with_county_fips.csv
Organization-level directory output. Each row represents one EIN assigned to a single county via ZIP crosswalk. Focus area is derived from NTEE and filtered to the project focus taxonomy.
| Column | Type | Meaning |
|---|---|---|
| ein | string | Employer Identification Number; used as an org-level unique identifier (deduped). |
| org_name | string | Organization legal name (as provided in registry extract). |
| city | string | Mailing city (registry field). |
| state | string | Mailing state abbreviation (registry field). |
| zip | string | 5-digit ZIP (cleaned). Used to assign county via HUD crosswalk. |
| ntee_code | string | NTEE classification code used to derive focus area. Missing NTEE rows are excluded. |
| focus_area | string | Project focus taxonomy label derived from NTEE. Filtered to allowed categories. |
| county_fips | string | 5-digit county FIPS assigned via ZIP crosswalk (deterministic primary-county rule). |
| source_file | string | Which EO regional file the record came from (eo1–eo4). |
county_focus_area.csv
County × focus area aggregation (concentration layer). Derived by grouping org_extract rows.
| Column | Type | Meaning |
|---|---|---|
| county_fips | string | 5-digit county FIPS. |
| focus_area | string | Focus taxonomy label (uppercase). |
| year | int | ACS year used for population denominator (for per-100k computations). |
| nonprofit_count | int | Count of orgs (EINs) assigned to county in this focus area. |
| nonprofits_per_100k_focus | float | nonprofit_count / population × 100,000 for the focus area (exploratory normalization). |
county_summary.csv
County baseline layer used to drive the national map and core per-capita normalization.
| Column | Type | Meaning |
|---|---|---|
| county_fips | string | 5-digit county FIPS. |
| county_name | string | County name parsed from ACS NAME field. |
| year | int | ACS year used for population denominator. |
| population | int | ACS population estimate (B01003_001E). |
| total_nonprofits | int | Total count of orgs (EINs) assigned to county (post-scope filter). |
| nonprofits_per_100k | float | total_nonprofits / population × 100,000 (exploratory normalization). |
needs_county_YYYY.csv (optional)
Optional county-level needs indicators used for KPI cards. Your version stores percent/rate fields as fractions (0–1), and Tableau formats them as percentages (0–100 display). If your file stores 0–100 instead, adjust your Tableau calc logic.
- Fips (5-digit county FIPS)
- poverty_rate (fraction 0–1)
- median_household_income (USD)
- unemployment_rate (fraction 0–1)
- snap_households_rate (fraction 0–1)
- pct_hs_or_higher (fraction 0–1)
- pct_bachelors_or_higher (fraction 0–1)
- broadband_rate (fraction 0–1)
- no_vehicle_rate (fraction 0–1)
- pct_under_18 (fraction 0–1)
- pct_65_plus (fraction 0–1)
Type + formatting notes
- FIPS fields should be strings to preserve leading zeros (e.g., “01001”).
- Per-capita fields (per_100k) can exceed raw counts; they are rates, not counts.
- ZIP → county assignment is deterministic “primary county per ZIP”; it is a reproducible approximation.