Skip to content

DAAF-Contribution-Community/daaf / .claude/skills/education-data-source-ccd

education-data-source-ccd

Verified

CCD — 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.

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)
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: 198kUpdated Claude CodeCodexVerifiedInstall
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: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
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: 194kUpdated Claude CodeCodexVerifiedInstall