DAAF-Contribution-Community/daaf / .claude/skills/education-data-source-saipe
education-data-source-saipe
VerifiedSAIPE — annual Census poverty estimates for school districts (Portal; county/state not in Portal). Use for district poverty, Title I context, or trends. Released ~12-13 months after the income year ends (DAAF inference). No race/ethnicity disaggregation at district level — use ACS 5-year for that.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 16.8 KB |
| references/comparison-other-sources.md | markdown | 8.1 KB |
| references/data-quality.md | markdown | 8.2 KB |
| references/estimation-methodology.md | markdown | 7.7 KB |
| references/historical-changes.md | markdown | 7.8 KB |
| references/school-district-estimates.md | markdown | 8.9 KB |
| references/variable-definitions.md | markdown | 15.7 KB |
references/variable-definitions.md
View on GitHub# SAIPE Variable Definitions
Detailed definitions of SAIPE variables, population universes, and coding conventions.
> **CRITICAL: Portal vs Census Raw File Encoding**
>
> This document describes **Education Data Portal** integer encodings, which differ from Census Bureau raw file formats. The Portal converts categorical variables to integers for consistency across sources.
>
> | Context | FIPS Alabama | FIPS California | Missing | Suppressed |
> |---------|--------------|-----------------|---------|------------|
> | **Portal (integers)** | `1` | `6` | `-1` | `-3` |
> | Census raw files | `01` (string) | `06` (string) | varies | varies |
>
> **Key difference:** Portal FIPS codes are integers (no leading zeros). When filtering by state, use integers: `fips == 6` not `fips == "06"` or `fips == "6"`.
> **Codebook Authority:** The variable definitions in this document are summaries for convenience.
> The authoritative source for variable names, codes, and definitions is the codebook `.xls` file
> available in the data mirrors. Use `get_codebook_url("saipe/codebook_districts_saipe")` from `fetch-patterns.md`
> to download the codebook. If this document contradicts the codebook, trust the codebook and
> flag the discrepancy.
## School District Variables
### Core Estimates
| Variable | Definition | Universe |
|----------|------------|----------|
| `population_total` | Total population residing within school district boundaries | All persons in households and group quarters |
| `population_5_17` | Population ages 5-17 residing within district | All children 5-17, regardless of enrollment status |
| `population_5_17_poverty` | Related children ages 5-17 in families in poverty | Related children in families with income below poverty threshold |
### Derived Measures
| Variable | Calculation | Notes |
|----------|-------------|-------|
| `population_5_17_poverty_pct` | `population_5_17_poverty / population_5_17 * 100` | Not a true poverty "rate" - numerator excludes some in denominator. **NOTE:** the Portal's `est_population_5_17_poverty_pct` is delivered on a **0-1 proportion** scale despite the `_pct` name (MEPS is 0-100) — see the scale-trap warning under "Education Data Portal Variable Names" before combining with other poverty measures |
## State and County Variables
> **Not available in Portal mirrors.** The variables below describe SAIPE state and county estimates published by the Census Bureau. Only the **district-level** dataset (`saipe/districts_saipe`) is available in the Education Data Portal mirrors. These variables are listed for context only.
### Income Estimates
| Variable | Definition | Unit |
|----------|------------|------|
| `median_household_income` | Median income of all households | Dollars |
| `median_household_income_moe` | 90% margin of error for median income | Dollars |
### Poverty Estimates (Counts)
| Variable | Definition | Ages |
|----------|------------|------|
| `population_0_4_poverty` | Children under age 5 in poverty | 0-4 (states only) |
| `population_5_17_poverty` | Related children 5-17 in families in poverty | 5-17 |
| `population_0_17_poverty` | All children under 18 in poverty | 0-17 |
| `population_poverty` | All persons in poverty | All ages |
### Poverty Estimates (Rates/Percentages)
| Variable | Definition |
|----------|------------|
| `population_0_4_poverty_pct` | Percent of children 0-4 in poverty |
| `population_5_17_poverty_pct` | Percent of children 5-17 in poverty |
| `population_0_17_poverty_pct` | Percent of children 0-17 in poverty |
| `population_poverty_pct` | Percent of all persons in poverty |
### Confidence Intervals (States and Counties)
| Variable | Definition |
|----------|------------|
| `*_lb` | Lower bound of 90% confidence interval |
| `*_ub` | Upper bound of 90% confidence interval |
Example: `population_5_17_poverty_lb`, `population_5_17_poverty_ub`
## Population Universes
### Understanding "Related Children"
**Related children ages 5-17 in families** includes:
- Persons ages 5 through 17
- Related to the householder by birth, marriage, or adoption
- Living in family households
**Excludes**:
- Foster children
- Other unrelated individuals in household
- Children in group quarters (institutions, college dorms, military barracks)
- Children who are householders or spouses of householders
### "In Families" vs "In Households"
| Term | Definition |
|------|------------|
| **Family** | Householder plus one or more related persons |
| **Household** | All persons in a housing unit (may include unrelated) |
| **In families** | Only persons in family households, related to householder |
SAIPE school district estimates use "related children in families" - a more restrictive universe than "all children in households."
### Poverty Universe
The poverty universe excludes:
- Persons in military barracks
- Persons in institutional group quarters
- Unrelated individuals under age 15
- Foster children
This means the denominator for poverty rates differs from total population.
## Poverty Threshold Definition
### Official Census Bureau Poverty Thresholds
Poverty status determined by comparing family income to threshold based on:
- Number of family members
- Number of related children under 18
- Age of householder (under 65 or 65+)
### 2023 Poverty Thresholds (Selected)
| Family Size | Children Under 18 | Threshold |
|-------------|-------------------|-----------|
| 2 persons (householder under 65) | None | $20,404 |
| 2 persons (householder under 65) | 1 child | $21,002 |
| 3 persons | 1 child | $24,526 |
| 3 persons | 2 children | $24,549 |
| 4 persons | 2 children | $30,900 |
| 5 persons | 3 children | $36,363 |
Source: Census Bureau, "Poverty Thresholds for 2023 by Size of Family and Number of Related Children Under 18 Years" (`thresh23.xlsx`).
### What Counts as Income
**Included** in poverty determination:
- Wages and salaries
- Self-employment income
- Interest, dividends, rental income
- Social Security and retirement income
- Cash public assistance (TANF, SSI)
- Unemployment compensation
- Child support received
**Excluded** from poverty determination:
- SNAP (food stamps)
- Medicaid/Medicare
- Housing subsidies
- School lunch programs
- Non-cash benefits
### How Poverty Differs from FRPL Eligibility
| Aspect | Official Poverty | Free Lunch | Reduced Lunch |
|--------|------------------|------------|---------------|
| Income threshold | 100% of threshold | 130% of guidelines | 185% of guidelines |
| Threshold source | Census Bureau | HHS Guidelines | HHS Guidelines |
| Example: family of 4 with 2 children (2024 SAIPE threshold; FRPL SY 2025-26) | $31,812 | ≤ $41,795 | < $59,478 |
| Non-cash benefits | Not counted | Not counted | Not counted |
## Geographic Identifiers
### State FIPS Codes (Portal Integer Encoding)
The Portal stores FIPS codes as integers (no leading zeros). Use integers when filtering:
```python
# Correct - use integer
df.filter(pl.col("fips") == 6) # California
# WRONG - these will not match
df.filter(pl.col("fips") == "06") # String won't match integer
df.filter(pl.col("fips") == "6") # Still a string
```
```r
# R equivalent
library(dplyr)
# Correct - use integer
df |> filter(fips == 6) # California
# WRONG - these will not match
df |> filter(fips == "06") # String won't match integer
df |> filter(fips == "6") # Still a string
```
| Code | State | Code | State |
|------|-------|------|-------|
| 1 | Alabama | 27 | Minnesota |
| 2 | Alaska | 28 | Mississippi |
| 4 | Arizona | 29 | Missouri |
| 5 | Arkansas | 30 | Montana |
| 6 | California | 31 | Nebraska |
| 8 | Colorado | 32 | Nevada |
| 9 | Connecticut | 33 | New Hampshire |
| 10 | Delaware | 34 | New Jersey |
| 11 | District of Columbia | 35 | New Mexico |
| 12 | Florida | 36 | New York |
| 13 | Georgia | 37 | North Carolina |
| 15 | Hawaii | 38 | North Dakota |
| 16 | Idaho | 39 | Ohio |
| 17 | Illinois | 40 | Oklahoma |
| 18 | Indiana | 41 | Oregon |
| 19 | Iowa | 42 | Pennsylvania |
| 20 | Kansas | 44 | Rhode Island |
| 21 | Kentucky | 45 | South Carolina |
| 22 | Louisiana | 46 | South Dakota |
| 23 | Maine | 47 | Tennessee |
| 24 | Maryland | 48 | Texas |
| 25 | Massachusetts | 49 | Utah |
| 26 | Michigan | 50 | Vermont |
| | | 51 | Virginia |
| | | 53 | Washington |
| | | 54 | West Virginia |
| | | 55 | Wisconsin |
| | | 56 | Wyoming |
**Territories (in Portal data):**
| Code | Territory |
|------|-----------|
| 60 | American Samoa |
| 66 | Guam |
| 69 | Northern Mariana Islands |
| 72 | Puerto Rico |
| 78 | Virgin Islands |
### School District ID (LEAID)
In the Portal, `leaid` is stored as an **integer** (not a 7-character string):
- Conceptually 7 digits: first 2 = state FIPS, remaining 5 = district within state
- Example: `622710` (integer) = California district 22710
- Note: Leading zeros are not stored (state FIPS 06 → 6, so 0622710 → 622710)
```python
# Filtering by leaid - use integers
df.filter(pl.col("leaid") == 622710) # California district 22710
# If you need the 7-character format for joining with other sources
df.with_columns(
pl.col("leaid").cast(str).str.zfill(7).alias("leaid_str")
)
```
```r
# R equivalent
library(dplyr)
library(stringr)
# Filtering by leaid - use integers
df |> filter(leaid == 622710) # California district 22710
# If you need the 7-character format for joining with other sources
df <- df |> mutate(leaid_str = str_pad(as.character(leaid), 7, pad = "0"))
```
> **CSV fallback: pad-and-assert `leaid` before any join (field-confirmed).** SAIPE
> `leaid` is Int64 in the Portal (parquet round-trips it losslessly), but when a fetch
> falls back to the CSV source, a numeric read drops the leading zero for single-digit-FIPS
> districts. In a native R-mode pipeline run this affected **14.6% of rows**, splitting the
> school→district join key. Force-string on read is not always enough (a source file can ship
> an already-truncated ID), so pad `leaid` to 7 and assert the width *before* joining SAIPE
> district poverty to any school-level or MEPS frame:
```python
import polars as pl
# INTENT: enforce canonical 7-char leaid before school->district join.
# REASONING: CSV read drops leading zeros for FIPS 01-09 districts (14.6% of rows
# in a field run); a split key silently under-joins those districts.
# ASSUMES: force-string applied on read (schema_overrides={"leaid": pl.Utf8});
# a value wider than 7 is a genuine anomaly, not a pad candidate.
df = df.with_columns(pl.col("leaid").cast(pl.Utf8).str.zfill(7))
assert (df["leaid"].str.len_chars() == 7).all(), "leaid width != 7 after pad"
```
```r
library(stringr)
# INTENT: enforce canonical 7-char leaid before school->district join.
# REASONING: CSV read drops leading zeros for FIPS 01-09 districts (14.6% of rows
# in a field run); a split key silently under-joins those districts.
# ASSUMES: col_types = cols(leaid = col_character()) applied on read; pad+assert
# before the join so a split key never enters the joined frame.
df <- df |> mutate(leaid = str_pad(as.character(leaid), 7, pad = "0"))
stopifnot(all(nchar(df$leaid) == 7))
```
> See `education-data-query` → `fetch-patterns.md` § Format Handling ("Zero-padded ID
> columns") for the cross-source version of this pattern (EDFacts + CRDC + SAIPE).
### County FIPS Codes
- 5-character string
- First 2 = state FIPS
- Last 3 = county within state
- Example: `06037` = Los Angeles County, CA
## Missing Data Codes
### Missing Value Encoding
> **Empirical observation (2025):** The `districts_saipe` parquet file uses `null` for all missing/unavailable values. No negative integer codes (-1, -2, -3) were observed in any column. Verify against the live codebook if this changes in future releases.
| Code | Meaning | When Used |
|------|---------|-----------|
| `null` | Missing or unavailable | Estimate not produced for this district/year |
### Handling Missing Data
```python
import polars as pl
# Filter to valid (non-null) data only
df_valid = df.filter(pl.col("est_population_5_17_poverty").is_not_null())
# Check null rates per column
null_rates = df.null_count() / df.height
print(null_rates)
```
```r
# R equivalent
library(dplyr)
# Filter to valid (non-null) data only
df_valid <- df |> filter(!is.na(est_population_5_17_poverty))
# Check null rates per column
null_rates <- colMeans(is.na(df))
cat("Null rates per column:\n"); print(null_rates)
```
### Suppression Rules
SAIPE applies disclosure avoidance:
- Estimates may be suppressed if based on very small populations
- Complementary suppression to prevent inference
- School district pieces with zero population not published
## Age Assignment for Grade Relevance
For school district estimation, children are assigned grades based on age:
| Age | Assigned Grade |
|-----|----------------|
| 5 | Kindergarten |
| 6 | Grade 1 |
| 7 | Grade 2 |
| 8 | Grade 3 |
| 9 | Grade 4 |
| 10 | Grade 5 |
| 11 | Grade 6 |
| 12 | Grade 7 |
| 13 | Grade 8 |
| 14 | Grade 9 |
| 15 | Grade 10 |
| 16 | Grade 11 |
| 17 | Grade 12 |
This one-to-one mapping is used for allocating children to overlapping elementary/secondary districts.
## Time Reference
### Estimate Year vs Data Year
| Term | Meaning |
|------|---------|
| **Estimate year** | Calendar year the estimate represents (e.g., 2023) |
| **Release year** | Year estimates are published (typically estimate year + 1) |
| **Tax year** | IRS data year (typically estimate year - 1) |
| **ACS year** | ACS data year (same as estimate year) |
Example for 2023 SAIPE (released December 2024):
- Estimate year: 2023
- ACS data: 2023 ACS
- IRS tax data: Tax year 2022 (filed in 2023)
- SNAP data: 2023
### School Year vs Calendar Year
SAIPE estimates are for **calendar year** income, not school year:
- 2023 SAIPE = calendar year 2023 income
- School district boundaries for the 2023-2024 school year (districts identified in the 2024 school district mapping survey, as of January 1, 2024)
## Education Data Portal Variable Names
In the Education Data Portal, estimate variable names include the `est_` prefix:
| Census Variable | Portal Variable | Data Type |
|-----------------|-----------------|-----------|
| Total population | `est_population_total` | Integer |
| Population 5-17 | `est_population_5_17` | Integer |
| Children 5-17 in poverty | `est_population_5_17_poverty` | Integer |
| Percent 5-17 in poverty | `est_population_5_17_poverty_pct` | Float |
| Percent population 5-17 | `est_population_5_17_pct` | Float |
| District ID | `leaid` | Integer |
| State FIPS | `fips` | Integer |
| Year | `year` | Integer |
| Census district ID | `district_id` | Integer |
| Census district name | `district_name` | String |
> **Note:** All Portal variables are lowercase. The `est_` prefix indicates these are estimates, not direct counts.
> **SCALE TRAP: `est_population_5_17_poverty_pct` is a 0-1 PROPORTION, not a 0-100 percent (field-confirmed).**
> Despite the `_pct` suffix, the Portal SAIPE district poverty measure is expressed on a
> **0-1 proportion** scale (e.g., `0.185` = 18.5% child poverty), **not** 0-100. This is a
> double-scaling hazard when SAIPE is combined or compared with other poverty measures — in
> particular **MEPS `meps_poverty_pct`, which is on a 0-100 scale** (observed max ~60.5 in a
> field run). Naively treating both as the same units, or multiplying SAIPE by 100 twice,
> corrupts any triangulation, ratio, or side-by-side chart.
>
> - **Verify before scaling:** check the observed range (`df["est_population_5_17_poverty_pct"].max()` ≲ 1 confirms proportion).
> - **Scale at most once:** multiply SAIPE by 100 *only if* you need percent units to match MEPS, and never twice.
> - **Reconcile scales explicitly** in any SAIPE↔MEPS comparison or combined visualization.
>
> This differs from the *state/county* `*_poverty_pct` variables documented above (context
> only — not in Portal mirrors), which follow the Census "percent" convention. Trust the
> observed data range and the live codebook over any suffix-based assumption.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack." | Stars: 197k | Updated | Claude CodeCodex | Verified | Install |
| ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |