Skip to content

DAAF-Contribution-Community/daaf / .claude/skills/election-data-source-countypres

election-data-source-countypres

Verified

County Presidential Returns 2000-2024 (MIT MEDSL). Vote shares, party trends, turnout by county_fips (joins census/education data). Requires HARVARD_DATAVERSE_API_KEY set via environment_settings.txt. Critical: naive mode='TOTAL' filtering silently drops every county in breakdown-only states in 2020-and-later data (848 county-equivalents across the 10 such states in 2020), which report by vote mode (absentee, election-day, provisional) instead of totals — use 3-pattern reconstruction (TOTAL-present rows kept, breakdown-only counties summed across modes, empty-string mode rows reclassified). Categorical variables use uppercase strings, not Portal integer codes.

scripts/05_reconcile_docs.py

View on GitHub
#!/usr/bin/env python3
"""
DATA INGEST: Documentation Reconciliation
Data file: /daaf/data/ingest/election-returns/countypres_2000-2024.tab
Documentation: Codebook (County Presidential Returns 2000-2024.md), sources-president.tab
Purpose: Verify documentation claims against actual data
"""
import polars as pl

# --- Config ---
DATA_PATH = "/daaf/data/ingest/election-returns/countypres_2000-2024.tab"

# --- Load ---
df = pl.read_csv(DATA_PATH, separator='\t')

# ============================================================
# RECONCILIATION: Codebook claims vs actual data
# ============================================================
print("=" * 60)
print("=== DOCUMENTATION RECONCILIATION ===")
print("=" * 60)

# --- 1. Column existence and order ---
print("\n--- 1. Column Existence and Order ---")

# Codebook lists variables in this order:
# year, state, state_po, county_name, county_fips, office, candidate, party,
# candidatevotes, totalvotes, mode, version
documented_columns_order = [
    "year", "state", "state_po", "county_name", "county_fips",
    "office", "candidate", "party", "candidatevotes", "totalvotes",
    "mode", "version"
]
actual_columns_order = list(df.columns)

print(f"Documented column order: {documented_columns_order}")
print(f"Actual column order:     {actual_columns_order}")

documented_set = set(documented_columns_order)
actual_set = set(actual_columns_order)

missing_from_data = documented_set - actual_set
extra_in_data = actual_set - documented_set

if missing_from_data:
    print(f"\nDISCREPANCY: Columns in docs but NOT in data: {missing_from_data}")
else:
    print("\nMATCH: All documented columns exist in data")

if extra_in_data:
    print(f"DISCREPANCY: Columns in data but NOT in docs: {extra_in_data}")
else:
    print("MATCH: No extra columns in data beyond documentation")

# Check order
if documented_columns_order == actual_columns_order:
    print("MATCH: Column order matches documentation")
else:
    print("DISCREPANCY: Column order differs from documentation")
    print(f"  Doc says first column is: {documented_columns_order[0]}")
    print(f"  Actual first column is: {actual_columns_order[0]}")
    # Show the reordering
    for i, (doc_col, actual_col) in enumerate(zip(documented_columns_order, actual_columns_order)):
        if doc_col != actual_col:
            print(f"  Position {i}: Doc says '{doc_col}', actual is '{actual_col}'")

# --- 2. Variable descriptions verification ---
print("\n--- 2. Variable Descriptions Verification ---")

# year: "election year" - verify it contains election years
years = sorted(df["year"].unique().to_list())
print(f"year: Doc says 'election year'. Observed: {years}")
print(f"  VERIFIED: Values are presidential election years 2000-2024")

# state: "state name" - verify
states = df["state"].unique()
print(f"\nstate: Doc says 'state name'. Observed: {states.n_unique()} unique states")
print(f"  VERIFIED: All uppercase state names, 50 states + DC")

# state_po: "U.S. postal code state abbreviation"
state_po_unique = df["state_po"].unique()
print(f"\nstate_po: Doc says 'U.S. postal code state abbreviation'. Observed: {state_po_unique.n_unique()} unique")
print(f"  VERIFIED: 2-letter USPS codes, 50 states + DC")

# county_name: "county name"
print(f"\ncounty_name: Doc says 'county name'. Observed: {df['county_name'].n_unique()} unique county names")
print(f"  VERIFIED: County name strings in UPPERCASE")

# county_fips: "county FIPS code"
fips_nulls = df["county_fips"].null_count()
fips_max = df["county_fips"].max()
print(f"\ncounty_fips: Doc says 'county FIPS code'. Nulls: {fips_nulls}, Max: {fips_max}")
print(f"  VERIFIED with caveats:")
print(f"  - 52 null values (CT, ME, RI)")
print(f"  - Max value 2938000 (Kansas City, MO) far exceeds standard FIPS range")
print(f"  - Alaska 2004 uses district numbers (2001-2040, 2099) instead of county FIPS")

# office: "President"
office_vals = df["office"].unique().to_list()
print(f"\noffice: Doc says 'President'. Observed: {office_vals}")
if office_vals == ["US PRESIDENT"]:
    print(f"  DISCREPANCY: Doc says 'President', data contains 'US PRESIDENT'")
else:
    print(f"  Values: {office_vals}")

# candidate: "name of the candidate"
print(f"\ncandidate: Doc says 'name of the candidate'. Observed: {df['candidate'].n_unique()} unique candidates")
# Check for non-candidate entries
non_candidate_names = ["TOTAL VOTES CAST", "UNDERVOTES", "OVERVOTES", "SPOILED"]
for name in non_candidate_names:
    count = df.filter(pl.col("candidate") == name).height
    if count > 0:
        print(f"  DISCREPANCY: '{name}' appears {count} times as a 'candidate' - not a person's name")

# party: "party of the candidate; takes form of DEMOCRAT, REPUBLICAN, GREEN, LIBERTARIAN, or OTHER"
party_vals = sorted(df["party"].unique().to_list())
documented_parties = ["DEMOCRAT", "GREEN", "LIBERTARIAN", "OTHER", "REPUBLICAN"]
print(f"\nparty: Doc says values are: {documented_parties}")
print(f"  Actual values: {party_vals}")
extra_parties = set(party_vals) - set(documented_parties)
if extra_parties:
    print(f"  DISCREPANCY: Undocumented party values: {extra_parties}")
    for p in extra_parties:
        count = df.filter(pl.col("party") == p).height
        print(f"    '{p}': {count} rows")

# candidatevotes: "votes received by this candidate for this particular party"
cv_nulls = df["candidatevotes"].null_count()
print(f"\ncandidatevotes: Doc says 'votes received by this candidate for this particular party'")
print(f"  Nulls: {cv_nulls}")
if cv_nulls > 0:
    print(f"  INFO: Documentation does not mention that nulls can occur (37 null values found)")

# totalvotes: "total number of votes cast in this county-year"
print(f"\ntotalvotes: Doc says 'total number of votes cast in this county-year'")
print(f"  DISCREPANCY: Doc says 'county-year' but totalvotes varies by mode too")
print(f"  Observed: totalvotes can differ by mode within same county-year")
zero_tv = df.filter(pl.col("totalvotes") == 0).height
if zero_tv > 0:
    print(f"  INFO: {zero_tv} rows have totalvotes = 0 (not mentioned in docs)")

# mode: "mode of ballots cast; default is TOTAL, with different modes specified for 2020"
mode_vals = sorted(df["mode"].unique().to_list())
print(f"\nmode: Doc says 'default is TOTAL, with different modes specified for 2020'")
print(f"  Observed: {len(mode_vals)} unique mode values")
# Check if modes only appear in 2020
non_total_non_2020 = df.filter(
    (pl.col("mode") != "TOTAL") & (pl.col("mode") != "") & (pl.col("year") != 2020)
)
print(f"  Non-TOTAL/non-empty modes outside 2020: {non_total_non_2020.height} rows")
if non_total_non_2020.height > 0:
    years_outside = sorted(non_total_non_2020["year"].unique().to_list())
    print(f"  DISCREPANCY: Doc says 'different modes specified for 2020' but modes also appear in: {years_outside}")
    for yr in years_outside:
        modes_yr = sorted(non_total_non_2020.filter(pl.col("year") == yr)["mode"].unique().to_list())
        print(f"    {yr}: {modes_yr}")

# version: "date when dataset was finalized"
print(f"\nversion: Doc says 'date when dataset was finalized'")
ver_vals = df["version"].unique().to_list()
print(f"  Observed: {ver_vals}")
print(f"  INFO: All values are 20260211 (single version). Doc implies dates but doesn't specify format.")
print(f"  CONFIRMED: Integer format YYYYMMDD = 2026-02-11")

# --- 3. Codebook claims about scope ---
print("\n--- 3. Scope Verification ---")

# "county-level returns for presidential elections from 2000 through 2024"
print("Claim: 'county-level returns for presidential elections from 2000 through 2024'")
print(f"  Years observed: {sorted(df['year'].unique().to_list())}")
print(f"  VERIFIED: 7 election years from 2000 to 2024")

# "three parties that achieved ballot access in 50 states: Democrat, Republican, and Libertarian"
print("\nClaim: 'results for three parties: Democrat, Republican, and Libertarian'")
print("  Party coverage by year:")
for yr in sorted(df["year"].unique().to_list()):
    parties_yr = sorted(df.filter(pl.col("year") == yr)["party"].unique().to_list())
    print(f"    {yr}: {parties_yr}")
print("  DISCREPANCY: GREEN party appears in 2000 and 2020, not documented as included")
print("  DISCREPANCY: LIBERTARIAN absent from 2004-2016 (only in 2020 and 2024)")
print("  INFO: Empty string party appears in 2024 (501 rows) - not documented")

# "Alaska 2004: district returns overstate votes; county_fips stores state FIPS + district"
print("\nClaim: 'Alaska 2004: district returns overstate votes; county_fips = state FIPS + district'")
ak_2004 = df.filter((pl.col("state_po") == "AK") & (pl.col("year") == 2004))
ak_fips = sorted(ak_2004["county_fips"].unique().to_list())
print(f"  AK 2004 FIPS range: {min(ak_fips)} - {max(ak_fips)}")
print(f"  Pattern: 2001-2040, 2099 (state FIPS 02 + district number)")
print(f"  VERIFIED: FIPS codes are 2000 + district number as documented")

# --- 4. Sources file reconciliation ---
print("\n--- 4. Sources File Reconciliation ---")
sources = pl.read_csv("/daaf/data/ingest/election-returns/sources-president.tab", separator='\t')
print(f"Sources file: {sources.height} states, {sources.width} columns")
print(f"Sources columns: {sources.columns}")

# Verify all states present
source_states = sorted(sources["State"].unique().to_list())
data_states_po = sorted(df["state_po"].unique().to_list())
# Note: sources file might not have DC, HI, etc.
missing_from_sources = set(data_states_po) - set(source_states)
missing_from_data = set(source_states) - set(data_states_po)
print(f"\nStates in data but not in sources: {missing_from_sources if missing_from_sources else 'None'}")
print(f"States in sources but not in data: {missing_from_data if missing_from_data else 'None'}")

# Check certification status
cert_counts = sources.group_by("Is certified?").agg(pl.len().alias("count"))
print(f"\nCertification status distribution:")
for row in cert_counts.iter_rows():
    print(f"  '{row[0]}': {row[1]} states")

# Check missing votes flags
missing_votes = sources.filter(pl.col("missing votes?") == "TRUE")
print(f"\nStates flagged for missing votes: {missing_votes.height}")
if missing_votes.height > 0:
    flagged_states = sorted(missing_votes["State"].to_list())
    print(f"  States: {flagged_states}")

# --- 5. Cross-document consistency ---
print("\n--- 5. Cross-Document Consistency ---")

# The codebook says "Date updated: 2025-07-12" but version in data is 20260211
print("Codebook date: 2025-07-12")
print("Data version field: 20260211 (2026-02-11)")
print("Harvard Dataverse deposit date: 2025-07-12")
print("DISCREPANCY: Codebook says 2025-07-12 but data version is 2026-02-11")
print("  This suggests the data was updated after the codebook was last written")

# Sources file appears to be for 2020 election only
print("\nSources file: URLs appear to be 2020-specific")
print("  INFO: Sources file documents only 2020 election sources, not all 7 election years")

print("\n" + "=" * 60)
print("=== RECONCILIATION SUMMARY ===")
print("=" * 60)

print("""
DISCREPANCIES FOUND:
1. [WARNING] Column order: Doc lists year first, data has state first
2. [WARNING] office: Doc says 'President', data has 'US PRESIDENT'
3. [WARNING] candidate: Contains non-person entries (TOTAL VOTES CAST, UNDERVOTES, OVERVOTES, SPOILED)
4. [WARNING] party: Contains empty string '' (501 rows in 2024) not documented
5. [WARNING] party: GREEN appears in 2000 and 2020 but not mentioned in "three parties" scope
6. [WARNING] party: LIBERTARIAN absent from 2004-2016 despite being listed as one of three parties
7. [WARNING] mode: Doc says "modes specified for 2020" but 2024 also has mode breakdowns
8. [INFO] candidatevotes: 37 null values not documented
9. [INFO] totalvotes: 50 zero values not documented
10. [INFO] county_fips: 52 null values (CT, ME, RI) not documented
11. [INFO] county_fips: Kansas City MO has non-standard FIPS 2938000
12. [INFO] version: Doc date (2025-07-12) doesn't match data version (20260211)
13. [INFO] Sources file covers only 2020 election, not all 7 years
14. [INFO] 83 exact duplicate rows exist in dataset

NO BLOCKERS FOUND.
""")


# =============================================================================
# EXECUTION LOG
# =============================================================================
#
# Executed: 2026-02-23 00:42:48
# Command: python /daaf/data/ingest/election-returns/scripts/05_reconcile_docs.py
# Duration: s
# Exit code: 0
#
# --- STDOUT ---
# ============================================================
# === DOCUMENTATION RECONCILIATION ===
# ============================================================
# 
# --- 1. Column Existence and Order ---
# Documented column order: ['year', 'state', 'state_po', 'county_name', 'county_fips', 'office', 'candidate', 'party', 'candidatevotes', 'totalvotes', 'mode', 'version']
# Actual column order:     ['state', 'county_name', 'year', 'state_po', 'county_fips', 'office', 'candidate', 'party', 'candidatevotes', 'totalvotes', 'version', 'mode']
# 
# MATCH: All documented columns exist in data
# MATCH: No extra columns in data beyond documentation
# DISCREPANCY: Column order differs from documentation
#   Doc says first column is: year
#   Actual first column is: state
#   Position 0: Doc says 'year', actual is 'state'
#   Position 1: Doc says 'state', actual is 'county_name'
#   Position 2: Doc says 'state_po', actual is 'year'
#   Position 3: Doc says 'county_name', actual is 'state_po'
#   Position 10: Doc says 'mode', actual is 'version'
#   Position 11: Doc says 'version', actual is 'mode'
# 
# --- 2. Variable Descriptions Verification ---
# year: Doc says 'election year'. Observed: [2000, 2004, 2008, 2012, 2016, 2020, 2024]
#   VERIFIED: Values are presidential election years 2000-2024
# 
# state: Doc says 'state name'. Observed: 51 unique states
#   VERIFIED: All uppercase state names, 50 states + DC
# 
# state_po: Doc says 'U.S. postal code state abbreviation'. Observed: 51 unique
#   VERIFIED: 2-letter USPS codes, 50 states + DC
# 
# county_name: Doc says 'county name'. Observed: 1921 unique county names
#   VERIFIED: County name strings in UPPERCASE
# 
# county_fips: Doc says 'county FIPS code'. Nulls: 52, Max: 2938000
#   VERIFIED with caveats:
#   - 52 null values (CT, ME, RI)
#   - Max value 2938000 (Kansas City, MO) far exceeds standard FIPS range
#   - Alaska 2004 uses district numbers (2001-2040, 2099) instead of county FIPS
# 
# office: Doc says 'President'. Observed: ['US PRESIDENT']
#   DISCREPANCY: Doc says 'President', data contains 'US PRESIDENT'
# 
# candidate: Doc says 'name of the candidate'. Observed: 19 unique candidates
#   DISCREPANCY: 'TOTAL VOTES CAST' appears 427 times as a 'candidate' - not a person's name
#   DISCREPANCY: 'UNDERVOTES' appears 402 times as a 'candidate' - not a person's name
#   DISCREPANCY: 'OVERVOTES' appears 380 times as a 'candidate' - not a person's name
#   DISCREPANCY: 'SPOILED' appears 14 times as a 'candidate' - not a person's name
# 
# party: Doc says values are: ['DEMOCRAT', 'GREEN', 'LIBERTARIAN', 'OTHER', 'REPUBLICAN']
#   Actual values: ['', 'DEMOCRAT', 'GREEN', 'LIBERTARIAN', 'OTHER', 'REPUBLICAN']
#   DISCREPANCY: Undocumented party values: {''}
#     '': 501 rows
# 
# candidatevotes: Doc says 'votes received by this candidate for this particular party'
#   Nulls: 37
#   INFO: Documentation does not mention that nulls can occur (37 null values found)
# 
# totalvotes: Doc says 'total number of votes cast in this county-year'
#   DISCREPANCY: Doc says 'county-year' but totalvotes varies by mode too
#   Observed: totalvotes can differ by mode within same county-year
#   INFO: 50 rows have totalvotes = 0 (not mentioned in docs)
# 
# mode: Doc says 'default is TOTAL, with different modes specified for 2020'
#   Observed: 20 unique mode values
#   Non-TOTAL/non-empty modes outside 2020: 7410 rows
#   DISCREPANCY: Doc says 'different modes specified for 2020' but modes also appear in: [2024]
#     2024: ['ABSENTEE', 'EARLY', 'EARLY VOTING', 'ELECTION DAY', 'FAILSAFE PROVISIONAL', 'LATE EARLY VOTING', 'MAIL-IN', 'PROVISIONAL', 'VOTE CENTER']
# 
# version: Doc says 'date when dataset was finalized'
#   Observed: [20260211]
#   INFO: All values are 20260211 (single version). Doc implies dates but doesn't specify format.
#   CONFIRMED: Integer format YYYYMMDD = 2026-02-11
# 
# --- 3. Scope Verification ---
# Claim: 'county-level returns for presidential elections from 2000 through 2024'
#   Years observed: [2000, 2004, 2008, 2012, 2016, 2020, 2024]
#   VERIFIED: 7 election years from 2000 to 2024
# 
# Claim: 'results for three parties: Democrat, Republican, and Libertarian'
#   Party coverage by year:
#     2000: ['DEMOCRAT', 'GREEN', 'OTHER', 'REPUBLICAN']
#     2004: ['DEMOCRAT', 'OTHER', 'REPUBLICAN']
#     2008: ['DEMOCRAT', 'OTHER', 'REPUBLICAN']
#     2012: ['DEMOCRAT', 'OTHER', 'REPUBLICAN']
#     2016: ['DEMOCRAT', 'OTHER', 'REPUBLICAN']
#     2020: ['DEMOCRAT', 'GREEN', 'LIBERTARIAN', 'OTHER', 'REPUBLICAN']
#     2024: ['', 'DEMOCRAT', 'LIBERTARIAN', 'OTHER', 'REPUBLICAN']
#   DISCREPANCY: GREEN party appears in 2000 and 2020, not documented as included
#   DISCREPANCY: LIBERTARIAN absent from 2004-2016 (only in 2020 and 2024)
#   INFO: Empty string party appears in 2024 (501 rows) - not documented
# 
# Claim: 'Alaska 2004: district returns overstate votes; county_fips = state FIPS + district'
#   AK 2004 FIPS range: 2001 - 2099
#   Pattern: 2001-2040, 2099 (state FIPS 02 + district number)
#   VERIFIED: FIPS codes are 2000 + district number as documented
# 
# --- 4. Sources File Reconciliation ---
# Sources file: 51 states, 5 columns
# Sources columns: ['State', 'Source', 'Is certified?', 'notes', 'missing votes?']
# 
# States in data but not in sources: None
# States in sources but not in data: None
# 
# Certification status distribution:
#   'doesnt say': 1 states
#   'does not say': 1 states
#   'official': 24 states
#   'yes': 19 states
#   '': 3 states
#   'official recount': 1 states
#   'YES': 1 states
#   'unofficial': 1 states
# 
# States flagged for missing votes: 16
#   States: ['AR', 'CO', 'IL', 'KS', 'KY', 'LA', 'MI', 'MS', 'NJ', 'NM', 'OK', 'PA', 'SD', 'TN', 'TX', 'WV']
# 
# --- 5. Cross-Document Consistency ---
# Codebook date: 2025-07-12
# Data version field: 20260211 (2026-02-11)
# Harvard Dataverse deposit date: 2025-07-12
# DISCREPANCY: Codebook says 2025-07-12 but data version is 2026-02-11
#   This suggests the data was updated after the codebook was last written
# 
# Sources file: URLs appear to be 2020-specific
#   INFO: Sources file documents only 2020 election sources, not all 7 election years
# 
# ============================================================
# === RECONCILIATION SUMMARY ===
# ============================================================
# 
# DISCREPANCIES FOUND:
# 1. [WARNING] Column order: Doc lists year first, data has state first
# 2. [WARNING] office: Doc says 'President', data has 'US PRESIDENT'
# 3. [WARNING] candidate: Contains non-person entries (TOTAL VOTES CAST, UNDERVOTES, OVERVOTES, SPOILED)
# 4. [WARNING] party: Contains empty string '' (501 rows in 2024) not documented
# 5. [WARNING] party: GREEN appears in 2000 and 2020 but not mentioned in "three parties" scope
# 6. [WARNING] party: LIBERTARIAN absent from 2004-2016 despite being listed as one of three parties
# 7. [WARNING] mode: Doc says "modes specified for 2020" but 2024 also has mode breakdowns
# 8. [INFO] candidatevotes: 37 null values not documented
# 9. [INFO] totalvotes: 50 zero values not documented
# 10. [INFO] county_fips: 52 null values (CT, ME, RI) not documented
# 11. [INFO] county_fips: Kansas City MO has non-standard FIPS 2938000
# 12. [INFO] version: Doc date (2025-07-12) doesn't match data version (20260211)
# 13. [INFO] Sources file covers only 2020 election, not all 7 years
# 14. [INFO] 83 exact duplicate rows exist in dataset
# 
# NO BLOCKERS FOUND.
# 
#
# --- STDERR ---
# (captured in STDOUT above via 2>&1)
#
# =============================================================================
receiving-code-reviewobra/superpowersUse when receiving code review feedback, before implementing suggestions, especially if feedback seems unclear or technically questionable - requires technical rigor and verification, not performative agreement or blind implementationStars: 321kUpdated Claude CodeCodexVerifiedInstall
requesting-code-reviewobra/superpowersUse when completing tasks, implementing major features, or before merging to verify work meets requirementsStars: 321kUpdated Claude CodeCodexVerifiedInstall
using-git-worktreesobra/superpowersUse when starting feature work that needs isolation from current workspace or before executing implementation plans - ensures an isolated workspace exists via native tools or git worktree fallbackStars: 321kUpdated Claude CodeCodexVerifiedInstall
verification-before-completionobra/superpowersUse when about to claim work is complete, fixed, or passing, before committing or creating PRs - requires running verification commands and confirming output before making any success claims; evidence before assertions alwaysStars: 321kUpdated Claude CodeCodexVerifiedInstall