DAAF-Contribution-Community/daaf / .claude/skills/education-data-source-ccd
education-data-source-ccd
VerifiedCCD — federal universe of all U.S. public K-12 schools (~100K) and districts (~18K). Enrollment, staffing, finance, directory data (1986-present). Use for public school analysis by grade/race/sex. Public only; excludes private and postsecondary.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 22.0 KB |
| references/data-collection.md | markdown | 10.2 KB |
| references/data-quality.md | markdown | 12.7 KB |
| references/historical-changes.md | markdown | 18.1 KB |
| references/survey-components.md | markdown | 15.2 KB |
| references/variable-definitions.md | markdown | 22.0 KB |
references/historical-changes.md
View on GitHub# CCD Historical Changes
This reference documents significant changes to CCD data collection, definitions, and coding over time. Essential for longitudinal analysis and understanding data comparability.
> **Portal vs NCES Codes:** This document references historical NCES string codes (AM, BL, PK, etc.) when explaining transitions. The Education Data Portal uses **integer codes** — see `variable-definitions.md` for current Portal mappings.
## Major Milestones Timeline
| Year | Change | Impact |
|------|--------|--------|
| 1986-87 | CCD nonfiscal data begins | Historical baseline |
| FY 1989 | State finance survey (NPEFS) major revision; district finance (F-33, first conducted FY 1980) covers all districts annually from FY 1995 | State and district finance available |
| 1991-92 | Dropout/completer collection begins | Completion data available |
| 1998-99 | English Language Learner variable added | ELL tracking starts |
| 2002-03 | School status codes 6, 7 added | Inactive/future schools tracked |
| 2005-06 | School status code 8 added | Reopened schools tracked |
| 2005-06 | Nonfiscal collection via EDEN begins; EDFacts mandatory from 2007-08 | Data collection centralized |
| 2006-07 | **Locale codes revised** | Major discontinuity |
| 2007-08 | LEA type codes revised | Charter agencies distinguished |
| 2007-08 | School type code 5 added | Reportable programs tracked |
| 2008-09 to 2010-11 | **Race/ethnicity categories revised** (7 categories optional 2008-09 and 2009-10, required from 2010-11) | Major discontinuity across the whole span |
| 2010-11 | ACGR first collected by ED (EDFacts) | Graduation rate methodology change |
| 2013-14 | Virtual school indicator added | Virtual schools tracked |
| 2014-15 | CEP nationwide | FRPL interpretation affected |
| 2016-17 | Long file format | Data structure changed |
| 2017-18 | LEA membership guidance changed | LEA totals methodology |
| 2017-18 | School level assignment logic changed | LEVEL variable affected |
---
## Locale Codes (2006)
### The Change
In 2006, NCES completely revised the locale code system from a "metro-centric" to an "urban-centric" framework.
### Before 2006: Metro-Centric Codes
| Code | Category |
|------|----------|
| 1 | Large city |
| 2 | Midsize city |
| 3 | Urban fringe of large city |
| 4 | Urban fringe of midsize city |
| 5 | Large town |
| 6 | Small town |
| 7 | Rural, outside CBSA |
| 8 | Rural, inside CBSA |
### After 2006: Urban-Centric Codes
| Code | Category | Definition |
|------|----------|------------|
| 11 | City, Large | Principal city, pop ≥250,000 |
| 12 | City, Midsize | Principal city, pop 100,000-249,999 |
| 13 | City, Small | Principal city, pop <100,000 |
| 21 | Suburb, Large | Outside principal city, UA ≥250,000 |
| 22 | Suburb, Midsize | Outside principal city, UA 100,000-249,999 |
| 23 | Suburb, Small | Outside principal city, UA <100,000 |
| 31 | Town, Fringe | Urban cluster, ≤10 mi from UA |
| 32 | Town, Distant | Urban cluster, 10-35 mi from UA |
| 33 | Town, Remote | Urban cluster, >35 mi from UA |
| 41 | Rural, Fringe | ≤5 mi from UA |
| 42 | Rural, Distant | 5-25 mi from UA |
| 43 | Rural, Remote | >25 mi from UA |
### Why There's No Crosswalk
The systems use fundamentally different geographic concepts:
- Old: Based on Metropolitan Statistical Areas (MSAs)
- New: Based on urbanized areas and distance calculations
A school classified as "rural" in the old system might be "town" or "suburb" in the new system, and vice versa.
### Recommendations for Longitudinal Analysis
1. **Preferred**: Analyze pre-2006 and post-2006 as separate periods
2. **Alternative**: Use broad categories (urban/suburban/town/rural) with caution
3. **Advanced**: Obtain geocodes and calculate custom locale measures
### Transition Files
NCES provides files for 2003-04 through 2005-06 that include both old and new codes, useful for understanding the transition.
---
## Race/Ethnicity Categories (2008-09 to 2010-11)
### The Change
Between 2008-09 and 2010-11 (optional for states until 2010-11), CCD adopted new OMB standards for race/ethnicity reporting, adding a "Two or More Races" category and separating Asian and Pacific Islander.
### Through 2007-08: 5 Categories
States not yet reporting 7 categories in 2008-09 and 2009-10 also used these 5. On the pinned mirror, school and district enrollment files alike hold only codes `1`-`5` and `99` through file year 2007.
| Code | Category |
|------|----------|
| AI | American Indian/Alaska Native |
| AS | Asian/Pacific Islander (combined) |
| BL | Black, non-Hispanic |
| HI | Hispanic |
| WH | White, non-Hispanic |
### From 2008-09 (All States From 2010-11): 7 Categories
On the pinned mirror, school and district enrollment files alike carry `6` and `7` rows from file year 2008, with positive counts only from adopting states until 2010 (see Transition Period below and `variable-definitions.md` Key Points).
| Code | Category |
|------|----------|
| HI | Hispanic/Latino (any race) |
| AM | American Indian/Alaska Native (non-Hispanic) |
| AS | Asian (non-Hispanic) |
| BL | Black or African American (non-Hispanic) |
| HP | Native Hawaiian/Pacific Islander (non-Hispanic) |
| TR | Two or More Races (non-Hispanic) |
| WH | White (non-Hispanic) |
### Key Differences
1. **Two-question format**: Hispanic ethnicity asked separately from race
2. **Two or More Races**: New category for multiracial students
3. **Asian/Pacific Islander split**: Separate categories for AS and HP
4. **Collection order**: Hispanic status collected first
### Transition Period (2008-09 to 2010-11)
States transitioned at different times:
- 2008-09: 7-category reporting optional; 5 states used it
- 2009-10: still optional; 14 states used it
- 2010-11: NCES asked all SEAs to report seven categories; all 50 states and DC complied
**Data Issues During Transition**:
- Inconsistent categories across states
- Some students in "unknown" race
- Bridge coding attempts (mapping old to new)
### Impact on Time Series
| Analysis Need | Impact | Approach |
|---------------|--------|----------|
| Total by race | Can combine HP with AS for historical comparison | Loses HP detail |
| Multiracial students | TR category didn't exist before 2008-09 and was reported only by adopting states until 2010-11 (elsewhere `0` or a missing code means not reported) | Cannot track historically |
| Hispanic students | Relatively consistent | Generally comparable |
| Detailed race analysis | Not comparable across periods | Separate analyses |
### Recommended Approach
```python
import polars as pl
# Portal uses integer race codes (not NCES string codes)
# Current Portal codes: 1=White, 2=Black, 3=Hispanic, 4=Asian,
# 5=AIAN, 6=NHPI, 7=Two+, 9=Unknown, 99=Total
# For time series that include any of file years 2008-2010 (the 2008-09 to
# 2010-11 transition)
# Option 1: Collapse new categories to match old 5-category system
df = df.with_columns(
pl.col("race").replace_strict(
{
5: 5, # American Indian/Alaska Native stays same
4: 4, # Asian stays Asian
6: 4, # Pacific Islander → Asian (combined in the 5-category scheme)
2: 2, # Black stays Black
3: 3, # Hispanic stays Hispanic
1: 1, # White stays White
7: -1, # Two or more → cannot map (treat as missing)
9: 9, # Unknown stays Unknown (its own code, not missing)
99: 99, # Total stays Total, so race_collapsed == 99 still selects the totals
},
default=-1,
).alias("race_collapsed")
)
# Option 2: Analyze 5-category years (through file year 2007) and 7-category
# years (from file year 2010) separately; 2008-2009 mix both schemes by state
# Note the break in your time series documentation
```
```r
# R equivalent
library(dplyr)
# Portal uses integer race codes (not NCES string codes)
# Current Portal codes: 1=White, 2=Black, 3=Hispanic, 4=Asian,
# 5=AIAN, 6=NHPI, 7=Two+, 9=Unknown, 99=Total
# For time series that include any of file years 2008-2010 (the 2008-09 to
# 2010-11 transition)
# Option 1: Collapse new categories to match old 5-category system
df <- df |> mutate(
race_collapsed = recode_values(
race,
5 ~ 5L, # American Indian/Alaska Native stays same
4 ~ 4L, # Asian stays Asian
6 ~ 4L, # Pacific Islander -> Asian (combined in the 5-category scheme)
2 ~ 2L, # Black stays Black
3 ~ 3L, # Hispanic stays Hispanic
1 ~ 1L, # White stays White
7 ~ -1L, # Two or more -> cannot map (treat as missing)
9 ~ 9L, # Unknown stays Unknown (its own code, not missing)
99 ~ 99L, # Total stays Total, so race_collapsed == 99 still selects the totals
default = -1L # recode_values supersedes case_match (soft-deprecated dplyr 1.2.0)
)
)
# Option 2: Analyze 5-category years (through file year 2007) and 7-category
# years (from file year 2010) separately; 2008-2009 mix both schemes by state
# Note the break in your time series documentation
```
---
## LEA Type Codes (2007)
### The Change
In 2007-08, LEA type codes were revised to specifically identify charter school agencies.
### Before 2007-08
| Code | Type |
|------|------|
| 1-6 | Various types (same as current) |
| 7 | Other education agencies |
### After 2007-08
| Code | Type |
|------|------|
| 1-6 | Same as before |
| 7 | **Charter agency** (all schools are charters) |
| 8 | Other education agencies |
### Impact
- Pre-2007-08: Charter agencies coded as "7" (Other) or regular districts
- Post-2007-08: Charter agencies specifically identifiable
- Historical charter LEA identification requires additional logic
### Identifying Historical Charter LEAs
For years before 2007-08:
1. Check if all schools have charter=1
2. Cross-reference with state charter authorizer records
3. Document uncertainty in analysis
---
## School Type Codes (2007)
### The Change
In 2007-08, school type code 5 was added for "Reportable Programs."
### Before 2007-08
| Code | Type |
|------|------|
| 1 | Regular |
| 2 | Special Education |
| 3 | Vocational |
| 4 | Alternative |
### After 2007-08
| Code | Type |
|------|------|
| 1-4 | Same as before |
| 5 | Reportable Program (within another school) |
### Impact
Programs that were previously reported as separate schools may now be coded as programs. This affects school counts.
---
## Status Codes
### Additions Over Time
| Year | Code | Meaning |
|------|------|---------|
| Original | 1-5 | Continuing, Closed, New, Added, Changed Agency |
| 2002-03 | 6 | Inactive (temporarily closed, may reopen ≤3 years) |
| 2002-03 | 7 | Future (scheduled to open within 2 years) |
| 2005-06 | 8 | Reopened (was closed, now operational) |
### Impact on School Counts
When comparing school counts over time:
- Pre-2002-03: Temporarily closed schools may appear as "Closed"
- Post-2002-03: "Inactive" status distinguishes temporary closures
- Pre-2005-06: Reopened schools counted as "New"
- Post-2005-06: "Reopened" status tracks these separately
---
## Graduation Rate Methodology (2010-11)
### AFGR (Averaged Freshman Graduation Rate)
**Used**: Before ACGR implementation
**Calculation**:
```
AFGR = Diploma Recipients / Averaged 9th Grade Enrollment
Where averaged enrollment = (8th grade y-4 + 9th grade y-3 + 9th grade y-2) / 3
```
**Limitations**:
- Uses enrollment approximation, not cohort tracking
- Doesn't account for transfers
- State implementation varied
### ACGR (Adjusted Cohort Graduation Rate)
**Required**: Under ESEA/ESSA
**Calculation**:
```
ACGR = (Cohort who graduated) / (First-time 9th graders, adjusted for transfers)
```
**Features**:
- Tracks actual student cohorts
- Adjusts for documented transfers in/out
- Standardized methodology
### Transition
- ACGR first collected by ED in 2010-11 (EDFacts)
- Both measures available during transition
- ACGR is now the standard measure
---
## File Format Change (2016-17)
### The Change
Starting 2016-17, CCD files are published in "long" format instead of "wide" format.
### Wide Format (Pre-2016-17)
One row per school, columns for each variable:
```
NCESSCH, AM_MALE_K, AM_MALE_1, ..., WH_FEMALE_12
```
### Long Format (2016-17+)
Multiple rows per school, disaggregation as columns:
```
NCESSCH, GRADE, SEX, RACE, STUDENT_COUNT
```
### Impact
- Easier to filter/aggregate
- Different data processing required
- NCES provides conversion programs
---
## Unknown Race/Sex Categories in Enrollment (2016+)
### The Change
Starting in **2016**, CCD enrollment files include **Unknown** disaggregation rows —
`race = 9` (Unknown) and `sex = 9` (Unknown) — that **do not exist in 2015 and earlier**
files. These rows carry real (often small) enrollment counts for students whose race or sex
was not reported.
### Impact (field-confirmed)
In a native pipeline run, an apparent jump in enrollment row counts at 2016 was traced to
these newly-present `race = 9`/`sex = 9` rows rather than to any real enrollment change. If
not handled consistently, they distort year-over-year comparisons two ways:
- **Totals:** if you build a total by summing category rows (instead of using `grade = 99` /
the total-code rows), pre-2016 sums exclude Unknown students while 2016+ sums include them,
creating a spurious discontinuity.
- **Shares:** race/sex composition denominators shift when the Unknown category appears, so a
group's share can move purely because the denominator gained an Unknown bucket.
### Handling
- Keep the composition **base year-consistent**: decide once whether Unknown (`9`) is included
in the denominator, and apply that rule across all years.
- Prefer the explicit total code (`race = 99` / `sex = 99`, `grade = 99`) over summing
category rows, so totals are consistent across the 2016 boundary regardless of the Unknown
category's presence.
- When reporting a year-over-year trend that straddles 2016, note the Unknown-category
introduction as a comparability caveat.
> This is a row-*presence* change, not a code redefinition: `race = 9` and `sex = 9` have
> always meant "Unknown" (see `variable-definitions.md`); what changed in 2016 is that these
> rows started appearing in the enrollment files.
---
## Membership Reporting Guidance (2017-18)
### The Change
Guidance for reporting LEA-level membership changed between 2016-17 and 2017-18.
### Before 2017-18
States could report LEA totals that:
- Were independent of school sums
- Used different counting methods
- Might not equal sum of schools
### After 2017-18
LEA membership should equal sum of school membership within the LEA.
### Impact
Comparing LEA-level membership across this transition requires caution. Some states show apparent changes that reflect reporting methodology, not actual enrollment changes.
---
## School Level Assignment (2017-18)
### The Change
The logic for deriving the LEVEL variable (elementary, middle, high, other) changed in 2017-18.
### Impact
Some schools may be classified differently before and after this change. Check school-level time series for unexpected changes.
---
## FRPL Reporting Changes
### Timeline
| Year | Change |
|------|--------|
| Through 2015-16 | CCD published FRPL counts only |
| 2011-12 | CEP pilot begins (some states) |
| 2013-14+ | NSLP status (NSLPSTATUS) added; records the meal-program provision (CEP is one) |
| 2014-15 | CEP nationwide |
| 2016-17+ | States may report FRPL and/or direct certification; CCD publishes both |
### Impact on Poverty Analysis
- Pre-CEP: FRPL reasonably consistent poverty proxy
- Post-CEP: Schools with 100% FRPL may be CEP schools, not 100% poverty
- Current: Must interpret FRPL differently for CEP vs. non-CEP schools
---
## Handling Historical Changes in Analysis
### General Approach
1. **Document the relevant period** for your analysis
2. **Check for definition changes** during your period
3. **Account for changes** in methodology or interpretation
4. **Report limitations** clearly
### Code Pattern for Time-Sensitive Analysis
```python
# variable, start_year and end_year describe your series (e.g. "race", 2005, 2009)
# Check for locale code issues
if variable == "locale" and start_year < 2006 and end_year >= 2006:
print("WARNING: Locale code methodology changed in 2006")
print("Consider separate pre/post-2006 analysis")
# Check for race/ethnicity issues: file years 2008-2010 are the transition, so
# warn when the series includes any of them (a 2005-2009 series is affected too)
if variable in ["race", "enrollment_by_race"] and start_year <= 2010 and end_year >= 2008:
print("WARNING: Race/ethnicity categories changed between 2008-09 and 2010-11 (file years 2008-2010)")
print("Two or More Races (TR) and HP rows start in 2008; real counts only from adopting states until 2010")
# Check for FRPL issues
if variable in ["frpl", "free_lunch", "reduced_lunch"] and end_year >= 2011:
print("NOTE: CEP phased in from SY 2011-12 (Portal year 2011) in 11 jurisdictions; nationwide from SY 2014-15 (Portal year 2014). FRPL interpretation affected")
```
```r
# R equivalent
# variable, start_year and end_year describe your series (e.g. "race", 2005, 2009)
# Check for locale code issues
if (variable == "locale" && start_year < 2006 && end_year >= 2006) {
cat("WARNING: Locale code methodology changed in 2006\n")
cat("Consider separate pre/post-2006 analysis\n")
}
# Check for race/ethnicity issues: file years 2008-2010 are the transition, so
# warn when the series includes any of them (a 2005-2009 series is affected too)
if (variable %in% c("race", "enrollment_by_race") && start_year <= 2010 && end_year >= 2008) {
cat("WARNING: Race/ethnicity categories changed between 2008-09 and 2010-11 (file years 2008-2010)\n")
cat("Two or More Races (TR) and HP rows start in 2008; real counts only from adopting states until 2010\n")
}
# Check for FRPL issues
if (variable %in% c("frpl", "free_lunch", "reduced_lunch") && end_year >= 2011) {
cat("NOTE: CEP phased in from SY 2011-12 (Portal year 2011) in 11 jurisdictions; nationwide from SY 2014-15 (Portal year 2014). FRPL interpretation affected\n")
}
```
---
## Resources for Historical Research
### NCES Documentation
- Reference Library: Crosswalks and documentation
- Historical file documentation
- State notes by year
### Useful Publications
- "100 Years of American Education: A Statistical Portrait" (NCES)
- CCD overview documents
- Digest of Education Statistics (annual)
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: 198k | 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 |