Skip to content

DAAF-Contribution-Community/daaf / .claude/skills/education-data-context

education-data-context

Verified

Interpretation 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.

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])
```
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: 471kUpdated Claude CodeCodexVerifiedInstall
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: 322kUpdated Claude CodeCodexVerifiedInstall
dispatching-parallel-agentsobra/superpowersUse when facing 2+ independent tasks that can be worked on without shared state or sequential dependenciesStars: 322kUpdated Claude CodeCodexVerifiedInstall
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 availableStars: 322kUpdated Claude CodeCodexVerifiedInstall