DAAF-Contribution-Community/daaf / .claude/skills/education-data-context
education-data-context
VerifiedInterpretation guidance for Urban Institute Education Data Portal datasets. The Portal is a curation layer over federal data: lowercase variable names, integer-encoded categoricals, standardized missing codes (-1 missing, -2 not applicable, -3 suppressed). Covers year definitions, grade encoding (grade=-1 is Pre-K, not missing), suppression rates, ODC-By licensing, and cross-source join identifiers. Load before analyzing any Portal data. Routes to source-specific deep-dive skills for individual datasets.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 29.4 KB |
| references/ccd-context.md | markdown | 13.1 KB |
| references/crdc-context.md | markdown | 13.8 KB |
| references/data-relationships.md | markdown | 20.5 KB |
| references/edfacts-context.md | markdown | 11.9 KB |
| references/ipeds-context.md | markdown | 14.6 KB |
| references/scorecard-context.md | markdown | 13.4 KB |
references/data-relationships.md
View on GitHub# Education Data Portal: Data Relationships
This reference explains how Education Data Portal tables relate to each other, identifier formats, and how to properly join datasets.
## Identifier Relationships
### K-12 Identifier Hierarchy
```
State FIPS (2 digits)
└── LEAID (7 chars) = State FIPS + District ID
└── NCESSCH (12 chars) = LEAID + School ID
```
### NCESSCH (School Identifier)
**Format**: 12 characters
```
Position: [01-02][03-07][08-12]
Content: [FIPS ][DIST ][SCHL ]
Example: 06 00001 00100
├─ 06 = California
├─ 00001 = District portion
└─ 00100 = School portion
```
**Key properties**:
- First 2 characters = State FIPS code
- First 7 characters = LEAID
- Unique nationally
- Can change when school moves districts, merges, or reopens
### LEAID (District Identifier)
**Format**: 7 characters
```
Position: [01-02][03-07]
Content: [FIPS ][DIST ]
Example: 06 00001
├─ 06 = California
└─ 00001 = State-assigned district ID
```
**Key properties**:
- First 2 characters = State FIPS code
- Unique nationally
- Can change when districts merge, split
### Deriving Relationships
```python
import polars as pl
# Extract LEAID from NCESSCH
df = df.with_columns(
pl.col("ncessch").str.slice(0, 7).alias("leaid")
)
# Extract State FIPS from LEAID
df = df.with_columns(
pl.col("leaid").str.slice(0, 2).alias("fips")
)
# Extract State FIPS from NCESSCH
df = df.with_columns(
pl.col("ncessch").str.slice(0, 2).alias("fips")
)
```
```r
# Extract LEAID from NCESSCH
df <- df |> mutate(leaid = substr(ncessch, 1, 7))
# Extract State FIPS from LEAID
df <- df |> mutate(fips = substr(leaid, 1, 2))
# Extract State FIPS from NCESSCH
df <- df |> mutate(fips = substr(ncessch, 1, 2))
```
### Postsecondary Identifier Relationships
```
OPEID6 (6 digits) - Institution family
└── OPEID (8 digits) - Specific branch/location
└── UNITID - IPEDS reporting unit (may differ)
```
### UNITID
**Definition**: Unique identifier for IPEDS reporting unit
**Key properties**:
- Assigned by NCES
- Primary key for IPEDS data
- Generally one per campus
- Used by both IPEDS and College Scorecard
### OPEID
**Definition**: Office of Postsecondary Education ID
**Key properties**:
- Used for Title IV administration
- 8 digits (6-digit root + 2-digit branch)
- Main campus typically ends in 00
- Branches have different suffixes
### OPEID6
**Definition**: First 6 digits of OPEID
**Key properties**:
- Identifies institution family
- Main campus and all branches share same OPEID6
- Useful for grouping campuses
### UNITID vs. OPEID
| Scenario | UNITID | OPEID |
|----------|--------|-------|
| Single campus | 1 | 1 |
| Multi-branch (separate IPEDS reporting) | Multiple | 1 OPEID6, different full OPEIDs |
| Multi-branch (consolidated IPEDS reporting) | 1 | Multiple |
```python
# Note: UNITID and OPEID don't always have 1:1 relationship
# Always check your specific institutions
```
```r
# Note: UNITID and OPEID don't always have 1:1 relationship
# Always check your specific institutions
```
## Joining Tables
### Schools to Districts
```python
import polars as pl
# Load school-level CCD directory from mirror
schools = pl.read_parquet("data/raw/schools_ccd_directory.parquet").filter(
pl.col("year") == 2020
)
# Load district-level CCD directory from mirror
districts = pl.read_parquet("data/raw/school-districts_lea_directory.parquet").filter(
pl.col("year") == 2020
)
# Join on leaid and year
merged = schools.join(
districts,
on=["leaid", "year"],
how="left",
suffix="_district"
)
# Check for unmatched schools
unmatched = merged.filter(pl.col("lea_name_district").is_null()).height
print(f"Schools without district match: {unmatched}")
```
```r
library(arrow)
library(dplyr)
# Load school-level CCD directory from mirror
schools <- read_parquet("data/raw/schools_ccd_directory.parquet") |> filter(year == 2020)
# Load district-level CCD directory from mirror
districts <- read_parquet("data/raw/school-districts_lea_directory.parquet") |> filter(year == 2020)
# Join on leaid and year
merged <- schools |> left_join(districts, by = c("leaid", "year"), suffix = c("", "_district"))
# Check for unmatched schools
unmatched <- sum(is.na(merged$lea_name_district))
cat(sprintf("Schools without district match: %d\n", unmatched))
```
### Schools Across Sources (CCD to CRDC)
```python
import polars as pl
# Load CCD directory from mirror
ccd = pl.read_parquet("data/raw/schools_ccd_directory.parquet").filter(
pl.col("year") == 2017
)
# Load CRDC discipline from mirror (yearly files)
crdc = pl.read_parquet("data/raw/schools_crdc_discipline_k12_2017.parquet")
# Join on ncessch (CRDC is generally biennial - verify year alignment)
merged = ccd.join(
crdc,
on="ncessch",
how="left",
suffix="_crdc"
)
# Check coverage
crdc_coverage = merged.filter(
pl.col("tot_discw_iss").is_not_null()
).height / merged.height
print(f"CRDC coverage: {crdc_coverage:.1%}")
```
```r
# Load CCD directory from mirror
ccd <- read_parquet("data/raw/schools_ccd_directory.parquet") |> filter(year == 2017)
# Load CRDC discipline from mirror (yearly files)
crdc <- read_parquet("data/raw/schools_crdc_discipline_k12_2017.parquet")
# Join on ncessch (CRDC is generally biennial - verify year alignment)
merged <- ccd |> left_join(crdc, by = "ncessch", suffix = c("", "_crdc"))
# Check coverage
crdc_coverage <- sum(!is.na(merged$tot_discw_iss)) / nrow(merged)
cat(sprintf("CRDC coverage: %.1f%%\n", crdc_coverage * 100))
```
### Colleges Across Sources (IPEDS to Scorecard)
```python
import polars as pl
# Both IPEDS and Scorecard use UNITID
ipeds = pl.read_parquet("data/raw/colleges_ipeds_directory.parquet").filter(
pl.col("year") == 2020
)
scorecard = pl.read_parquet("data/raw/colleges_scorecard_earnings.parquet").filter(
pl.col("year") == 2014 # Measurement year (= cohort_year + years_after_entry)
)
# Join on unitid
merged = ipeds.join(
scorecard,
on="unitid",
how="left",
suffix="_scorecard"
)
# Note: Not all institutions in both sources
```
```r
# Both IPEDS and Scorecard use UNITID
ipeds <- read_parquet("data/raw/colleges_ipeds_directory.parquet") |> filter(year == 2020)
scorecard <- read_parquet("data/raw/colleges_scorecard_earnings.parquet") |> filter(year == 2014)
# Join on unitid
merged <- ipeds |> left_join(scorecard, by = "unitid", suffix = c("", "_scorecard"))
# Note: Not all institutions in both sources
```
## Year Alignment Issues
### Source Timing Differences
| Source 1 | Source 2 | Alignment Issue |
|----------|----------|-----------------|
| CCD | CRDC | CRDC is generally biennial |
| IPEDS enrollment | IPEDS grad rates | Grad rates are cohort-based |
| Scorecard earnings | IPEDS | Earnings lag enrollment by 6-10 years |
| CCD | EDFacts | Generally aligned |
| IPEDS | Scorecard | Cohort definitions differ |
### CRDC Year Alignment
```python
# CRDC available years (most topics): 2011, 2013, 2015, 2017, 2020, 2021
# Use matching CCD year
crdc_to_ccd_years = {
2011: 2011,
2013: 2013,
2015: 2015,
2017: 2017,
2020: 2020,
2021: 2021
}
def get_aligned_data(crdc_year):
crdc = pl.read_parquet(
f"data/raw/schools_crdc_discipline_k12_{crdc_year}.parquet"
)
ccd_year = crdc_to_ccd_years[crdc_year]
ccd = pl.read_parquet("data/raw/schools_ccd_directory.parquet").filter(
pl.col("year") == ccd_year
)
return crdc.join(ccd, on="ncessch", how="left")
```
```r
# CRDC available years (most topics): 2011, 2013, 2015, 2017, 2020, 2021
# Use matching CCD year
crdc_year <- 2017
crdc <- read_parquet(sprintf("data/raw/schools_crdc_discipline_k12_%d.parquet", crdc_year))
ccd <- read_parquet("data/raw/schools_ccd_directory.parquet") |> filter(year == crdc_year)
merged <- crdc |> left_join(ccd, by = "ncessch")
```
### IPEDS Cohort Alignment
```python
# Graduation-rate `year` is NOT the entry year; select the cohort with `cohort_year`
# (GR200 4-year: cohort_year = year - 7; 150% GR 4-year: year - 5; verified 2026-10-19)
def get_ipeds_with_outcomes(enrollment_year):
"""
Get enrollment data aligned with graduation outcomes
Note: Cohort outcomes not available until 6+ years later
"""
enrollment = pl.read_parquet(
"data/raw/colleges_ipeds_fall_enrollment.parquet"
).filter(pl.col("year") == enrollment_year)
# Select the cohort that entered in enrollment_year, whatever its Portal year label
# But outcomes measured later
grad_rates = pl.read_parquet(
"data/raw/colleges_ipeds_grad_rates_200pct.parquet"
).filter(pl.col("cohort_year") == enrollment_year)
return enrollment.join(grad_rates, on="unitid", how="left")
```
```r
# Graduation-rate `year` is NOT the entry year; select the cohort with `cohort_year`
# (GR200 4-year: cohort_year = year - 7; 150% GR 4-year: year - 5; verified 2026-10-19)
enrollment_year <- 2015
enrollment <- read_parquet("data/raw/colleges_ipeds_fall_enrollment.parquet") |>
filter(year == enrollment_year)
# Select the cohort that entered in enrollment_year, whatever its Portal year label
# But outcomes measured later
grad_rates <- read_parquet("data/raw/colleges_ipeds_grad_rates_200pct.parquet") |>
filter(cohort_year == enrollment_year)
merged <- enrollment |> left_join(grad_rates, by = "unitid")
```
### Scorecard Earnings Lag
```python
# Scorecard earnings are measured 6-10 years after entry
# Portal earnings `year` = measurement year (= cohort_year + years_after_entry); filter on years_after_entry
# Example: students who enrolled ~2008 → Portal `year` 2014 (6-year) or 2018 (10-year)
# Portal earnings years available: 2003-2014 and 2018 only
```
```r
# Scorecard earnings are measured 6-10 years after entry
# Portal earnings `year` = measurement year (= cohort_year + years_after_entry); filter on years_after_entry
# Example: students who enrolled ~2008 → Portal `year` 2014 (6-year) or 2018 (10-year)
# Portal earnings years available: 2003-2014 and 2018 only
```
## Common Join Pitfalls
### 1. ID Changes Over Time
```python
# Verify ID stability before joining across years
def check_id_stability(df_year1, df_year2, id_col, year1, year2):
ids_y1 = set(df_year1[id_col].to_list())
ids_y2 = set(df_year2[id_col].to_list())
dropped = ids_y1 - ids_y2
added = ids_y2 - ids_y1
stable = ids_y1 & ids_y2
print(f"IDs in {year1} but not {year2}: {len(dropped)}")
print(f"IDs in {year2} but not {year1}: {len(added)}")
print(f"Stable IDs: {len(stable)}")
print(f"Stability rate: {len(stable)/len(ids_y1):.1%}")
return {"dropped": dropped, "added": added, "stable": stable}
```
```r
# Verify ID stability before joining across years
ids_y1 <- unique(df_year1[[id_col]])
ids_y2 <- unique(df_year2[[id_col]])
cat(sprintf("IDs in %s but not %s: %d\n", year1, year2, length(setdiff(ids_y1, ids_y2))))
cat(sprintf("IDs in %s but not %s: %d\n", year2, year1, length(setdiff(ids_y2, ids_y1))))
cat(sprintf("Stable IDs: %d\n", length(intersect(ids_y1, ids_y2))))
cat(sprintf("Stability rate: %.1f%%\n", length(intersect(ids_y1, ids_y2)) / length(ids_y1) * 100))
```
### 2. Year Misalignment
```python
# WRONG: Joining without considering year
merged = source1.join(source2, on="ncessch") # May get wrong year match
# RIGHT: Always include year in join when merging same-year data
merged = source1.join(source2, on=["ncessch", "year"])
# OR when sources have different year granularity:
# Filter to specific years first, then join without year column
source1_2017 = source1.filter(pl.col("year") == 2017)
source2_2017 = source2.filter(pl.col("year") == 2017)
merged = source1_2017.join(source2_2017.drop("year"), on="ncessch")
```
```r
# WRONG: Joining without considering year
merged <- source1 |> left_join(source2, by = "ncessch") # May get wrong year match
# RIGHT: Always include year in join when merging same-year data
merged <- source1 |> left_join(source2, by = c("ncessch", "year"))
# OR when sources have different year granularity:
# Filter to specific years first, then join without year column
source1_2017 <- source1 |> filter(year == 2017)
source2_2017 <- source2 |> filter(year == 2017) |> select(-year)
merged <- source1_2017 |> left_join(source2_2017, by = "ncessch")
```
### 3. Missing Coverage
```python
# Not all schools/colleges appear in all sources
# Always check join coverage
def check_join_coverage(left_df, merged_df, right_key_col):
original = left_df.height
matched = merged_df.filter(pl.col(right_key_col).is_not_null()).height
coverage = matched / original * 100
print(f"Join coverage: {coverage:.1f}%")
if coverage < 90:
print("WARNING: Significant data loss from join")
return coverage
```
```r
# Not all schools/colleges appear in all sources
# Always check join coverage
original <- nrow(left_df)
matched <- sum(!is.na(merged_df[[right_key_col]]))
coverage <- matched / original * 100
cat(sprintf("Join coverage: %.1f%%\n", coverage))
if (coverage < 90) {
cat("WARNING: Significant data loss from join\n")
}
```
### 4. Duplicate Keys
```python
# Some sources have multiple rows per institution
# (e.g., branch campuses, reporting units)
# Check for duplicates before joining
def check_duplicates(df, key_col):
dup_count = df.group_by(key_col).count().filter(
pl.col("count") > 1
)
if dup_count.height > 0:
print(f"WARNING: {dup_count.height} duplicate keys found")
print("Consider aggregating or filtering before join")
return dup_count
```
```r
# Some sources have multiple rows per institution
# (e.g., branch campuses, reporting units)
# Check for duplicates before joining
dup_count <- df |> count(.data[[key_col]]) |> filter(n > 1)
if (nrow(dup_count) > 0) {
cat(sprintf("WARNING: %d duplicate keys found\n", nrow(dup_count)))
cat("Consider aggregating or filtering before join\n")
}
```
## Aggregation Relationships
### School to District Aggregation
```
Schools aggregate to → Districts → States
```
```python
# Aggregate school enrollment to district
district_enrollment = schools.group_by(["leaid", "year"]).agg(
pl.col("enrollment").sum().alias("total_enrollment"),
pl.col("ncessch").count().alias("school_count")
)
# IMPORTANT: Prefer district-level totals from district endpoints
# School sums may differ from official district totals
```
```r
# Aggregate school enrollment to district
district_enrollment <- schools |>
group_by(leaid, year) |>
summarise(
total_enrollment = sum(enrollment),
school_count = n(),
.groups = "drop"
)
# IMPORTANT: Prefer district-level totals from district endpoints
# School sums may differ from official district totals
```
### Why Aggregated ≠ Reported
| Reason | Impact |
|--------|--------|
| Non-school students | District serves students not in schools |
| Reporting timing | Schools and districts may report at different times |
| Definition differences | District total may use different counting rules |
| Missing schools | Some schools may not report |
**Recommendation**: Use district-level data from district endpoints when available.
## Census Geography Links
### NHGIS Endpoints
The Education Data Portal links schools/colleges to Census geography via NHGIS:
```python
import polars as pl
# Get school-census geography crosswalk from mirror
nhgis = pl.read_parquet("data/raw/schools_nhgis_geog_2010.parquet").filter(
pl.col("year") == 2020
)
# Join to get census tract for each school
schools_with_tract = schools.join(
nhgis,
on="ncessch",
how="left"
)
```
```r
# Get school-census geography crosswalk from mirror
nhgis <- read_parquet("data/raw/schools_nhgis_geog_2010.parquet") |> filter(year == 2020)
# Join to get census tract for each school
schools_with_tract <- schools |> left_join(nhgis, by = "ncessch")
```
### Census Vintage Matching
| Data Year | Census Vintage |
|-----------|----------------|
| 2011-2019 | 2010 Census |
| 2020+ | 2020 Census or 2010 (check documentation) |
| Pre-2011 | 2000 or 2010 Census |
Use appropriate vintage for your time period.
### Geographic Identifiers
| Level | Identifier Format |
|-------|-------------------|
| State | 2-digit FIPS |
| County | 5-digit FIPS (state + county) |
| Census tract | 11-digit GEOID |
| Block group | 12-digit GEOID |
## State FIPS Code Reference
Common FIPS codes:
| Code | State | Code | State |
|------|-------|------|-------|
| 01 | Alabama | 28 | Mississippi |
| 02 | Alaska | 29 | Missouri |
| 04 | Arizona | 30 | Montana |
| 05 | Arkansas | 31 | Nebraska |
| 06 | California | 32 | Nevada |
| 08 | Colorado | 33 | New Hampshire |
| 09 | Connecticut | 34 | New Jersey |
| 10 | Delaware | 35 | New Mexico |
| 11 | DC | 36 | New York |
| 12 | Florida | 37 | North Carolina |
| 13 | Georgia | 38 | North Dakota |
| 15 | Hawaii | 39 | Ohio |
| 16 | Idaho | 40 | Oklahoma |
| 17 | Illinois | 41 | Oregon |
| 18 | Indiana | 42 | Pennsylvania |
| 19 | Iowa | 44 | Rhode Island |
| 20 | Kansas | 45 | South Carolina |
| 21 | Kentucky | 46 | South Dakota |
| 22 | Louisiana | 47 | Tennessee |
| 23 | Maine | 48 | Texas |
| 24 | Maryland | 49 | Utah |
| 25 | Massachusetts | 50 | Vermont |
| 26 | Michigan | 51 | Virginia |
| 27 | Minnesota | 53 | Washington |
| | | 54 | West Virginia |
| | | 55 | Wisconsin |
| | | 56 | Wyoming |
| | | 72 | Puerto Rico |
Note: FIPS codes skip some numbers (e.g., no 03, 07, 14, etc.)
## Quick Reference: Common Joins
| Task | Join Key(s) | Notes |
|------|-------------|-------|
| Schools to districts | `leaid`, `year` | Same source |
| CCD to CRDC | `ncessch` | Check year alignment (CRDC generally biennial) |
| CCD to EDFacts | `ncessch`, `year` | Same year alignment |
| IPEDS to Scorecard | `unitid` | Different cohort definitions |
| Schools to census | `ncessch` | Via NHGIS; check vintage |
| Any source to state data | `fips` | Extract from identifiers |
## Portal Integer Encoding Note
When joining or filtering, remember that the Portal uses **integer codes**, not strings:
| Variable | Integer Values | NOT These Strings |
|----------|----------------|-------------------|
| Race | Differs by source (populated, newest files): CCD 1-7, 9, 99; CRDC 1-7, 99; EDFacts 1-5, 7, 99; IPEDS 1-9, 99 (see `variable-codes.md` in education-data-explorer) | WH, BL, HI |
| Sex | Differs by source (populated, newest files): CCD 1, 2, 9, 99; CRDC and EDFacts 1, 2, 99 (CRDC 2021 enrollment adds 3); IPEDS 1, 2, 99, plus 3 and 9 in some 2022+ files and an undeclared 4 in fall-enrollment-race 2022 (see variable-codes.md in education-data-explorer) | M, F |
| Grade | -1 to 15, 99 (total), 999 (not specified); 14 = Adult education, 15 = Ungraded | PK, KG, 01 |
| FIPS | Integers (6, 36, 48) | CA, NY, TX |
**SEMANTIC TRAP:** `grade = -1` means **Pre-K**, NOT missing data!
## Validation After Joining
```python
# --- Post-Join Validation ---
print(f"Join type: {join_type}")
print(f"Keys: {key_cols}")
print(f"Left rows: {left_df.height:,}")
print(f"Merged rows: {merged_df.height:,}")
if join_type == "left":
if merged_df.height != left_df.height:
print(f"[WARNING] Row count changed: {left_df.height:,} → {merged_df.height:,} (duplicates in right?)")
else:
print(f"[PASS] Row count preserved: {left_df.height:,} left rows == {merged_df.height:,} merged rows")
else:
# A row-count invariant only holds for a left join; say so rather than printing nothing.
print(f"[NOT VERIFIED] Row count: no preservation invariant defined for join_type '{join_type}'")
# Check for unexpected nulls
null_counts = merged_df.select([
pl.col(c).is_null().sum().alias(f"{c}_nulls")
for c in merged_df.columns
])
print("\nNull counts after join:")
print(null_counts)
```
```r
# --- Post-Join Validation ---
cat(sprintf("Join type: %s\n", join_type))
cat(sprintf("Left rows: %s\n", format(nrow(left_df), big.mark = ",")))
cat(sprintf("Merged rows: %s\n", format(nrow(merged_df), big.mark = ",")))
if (join_type == "left") {
if (nrow(merged_df) != nrow(left_df)) {
cat(sprintf("[WARNING] Row count changed: %s -> %s (duplicates in right?)\n",
format(nrow(left_df), big.mark = ","),
format(nrow(merged_df), big.mark = ",")))
} else {
cat(sprintf("[PASS] Row count preserved: %s left rows == %s merged rows\n",
format(nrow(left_df), big.mark = ","),
format(nrow(merged_df), big.mark = ",")))
}
} else {
# A row-count invariant only holds for a left join; say so rather than printing nothing.
cat(sprintf("[NOT VERIFIED] Row count: no preservation invariant defined for join_type '%s'\n",
join_type))
}
# Check for unexpected NAs
null_counts <- colSums(is.na(merged_df))
cat("\nNull counts after join:\n")
print(null_counts[null_counts > 0])
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| reverse-engineer-anythingmorluto/reaReverse engineer native, managed, Electron/JavaScript, packaged, firmware, and browser targets with REA, and build agent environments (AgentEnv bundles) of web applications from a live instance or a capture, and of Linux desktop apps with a task from a request. Use shipped-artifact or requested runtime evidence to explain features, compare versions, decompile code, or guide a reconstruction. Skip REA for ordinary source-repository architecture analysis. | Stars: 471k | Updated | Claude CodeCodex | Verified | Install |
| diagnosing-superpowersobra/superpowersUse when a superpowers session went wrong and your human partner wants to know why — repeated work, ignored plans, stumbles, poor results, a skill that didn't fire, "it took too long", "why is it so expensive", "what is it doing" — or wants to build a bug report for the superpowers maintainers, for the current session or a past one identified by id or path, on any harness. | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| dispatching-parallel-agentsobra/superpowersUse when facing 2+ independent tasks that can be worked on without shared state or sequential dependencies | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| executing-plansobra/superpowersUse when executing an implementation plan in the current session as the implementer yourself — your human partner chose inline execution, or no subagent tool is available | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |