Skip to content

Latest commit

 

History

History
218 lines (152 loc) · 7.33 KB

README.md

File metadata and controls

218 lines (152 loc) · 7.33 KB

AZ Precinct Code Normalizer (pctnum)

I use this in conjunction with geo-precincts to build a uniformly coded WKT AZ precinct file for mapping in Looker Studio.

This project standardizes Arizona voting precinct identifiers into a uniform 6‑character pctnum code:

<COUNTY_PREFIX><ZERO-PADDED 4-DIGIT PRECINCT NUMBER>
e.g., PM0025  (PIMA county, precinct 25)

It helps with mapping and joining disparate program datasets where precinct fields differ (numeric vs. text, decimals in CSV exports, embedded numbers in names, etc.).

What the script does

  1. Loads a CSV of precinct‑level records (typically exported from BigQuery).
  2. Maps county names → two‑letter prefixes (see table below).
  3. Extracts the numeric precinct code, handling common formats:
    • Plain integers (25)
    • Decimals from CSV exports (25.0)
    • Text names with trailing digits (e.g., "Precinct 87" → 0087) — optional regex mode
  4. Builds pctnum as <prefix><number padded to 4 digits>, e.g., MO0102.
  5. Flags unparseable rows as ERROR.
  6. Writes a new CSV with the pctnum column added.

County prefix dictionary

county_codes = {
    'YUMA': 'YU', 'MARICOPA': 'MC', 'SANTA CRUZ': 'SC', 'GILA': 'GI',
    'PIMA': 'PM', 'PINAL': 'PN', 'APACHE': 'AP', 'GRAHAM': 'GM',
    'LA PAZ': 'LP', 'MOHAVE': 'MO', 'NAVAJO': 'NA', 'COCHISE': 'CH',
    'YAVAPAI': 'YA', 'COCONINO': 'CN', 'GREENLEE': 'GN'
}

You could also use FIPS county prefixes in this dictionary, but I prefer alphabetical because it lets me easily see at a glance which county any precinct is in.

Assumption: County names in your CSV match these keys (uppercased, spacing as shown). If not, normalize first (e.g., df['countyname'] = df['countyname'].str.upper().str.strip()).

Inputs & Outputs

  • Input CSV: Exported dataset with at least:

    • A county column (e.g., countyname or county_name)
    • A precinct column:
      • Numeric mode: precinctcode like 25, 25.0
      • Regex mode (embedded digits): a string field like svi_registration_national_precinct_code or precinct_name that ends with digits

  • Output CSV: Same rows plus a new pctnum column. Rows that cannot be parsed get pctnum = 'ERROR'.

Quickstart

A) Numeric precinct codes (simplest)

Use this when the precinct column is numeric (or numeric‐formatted strings like 25.0).

import pandas as pd

precincts = pd.read_csv('/path/to/RAZA_c4_2024_Door_Attempts.csv')

county_codes = {
    'YUMA':'YU','MARICOPA':'MC','SANTA CRUZ':'SC','GILA':'GI','PIMA':'PM','PINAL':'PN',
    'APACHE':'AP','GRAHAM':'GM','LA PAZ':'LP','MOHAVE':'MO','NAVAJO':'NA','COCHISE':'CH',
    'YAVAPAI':'YA','COCONINO':'CN','GREENLEE':'GN'
}

def extract_pctnum(countyname, precinctcode, county_codes):
    try:
        precinct_int = int(float(precinctcode))  # handles '25' and '25.0'
    except (ValueError, TypeError):
        return 'ERROR'

    num_part = str(precinct_int).zfill(4)
    county_prefix = county_codes.get(str(countyname).upper().strip())
    return county_prefix + num_part if county_prefix else 'ERROR'

precincts['pctnum'] = precincts.apply(
    lambda row: extract_pctnum(row['countyname'], row['precinctcode'], county_codes),
    axis=1
)

precincts.to_csv('/content/modified_precincts.csv', index=False)

Examples

  • countyname='PIMA', precinctcode='25.0' → PM0025
  • countyname='MOHAVE', precinctcode='102' → MO0102

B) Text precinct names with trailing digits (regex mode)

Use this if the precinct identifier is embedded in a string (e.g., "PRECINCT 87"), or for fields like svi_registration_national_precinct_code.
Also skips UNCODED rows (returns ERROR).

import re
import pandas as pd

precincts = pd.read_csv('/path/to/2024_Door_Attempts.csv')

county_codes = { ... }  # same dict as above

def extract_pctnum(county_name, precinctcode, county_codes):
    s = str(precinctcode).upper()
    if 'UNCODED' in s:
        return 'ERROR'

    # capture 1–3 trailing digits, then zero-pad to 4
    match = re.search(r'(\d{1,3})$', s)
    if not match:
        return 'ERROR'

    num_part = match.group(1).zfill(4)
    county_prefix = county_codes.get(str(county_name).upper().strip())
    return county_prefix + num_part if county_prefix else 'ERROR'

precincts['pctnum'] = precincts.apply(
    lambda row: extract_pctnum(row['county_name'], row['svi_registration_national_precinct_code'], county_codes),
    axis=1
)

precincts.to_csv('/content/modified_c4precincts.csv', index=False)
print(precincts[['county_name','svi_registration_national_precinct_code','pctnum']].head())

Examples

  • county_name='PIMA', svi_registration_national_precinct_code='PRECINCT 7' → PM0007
  • county_name='MARICOPA', ...='PC 123' → MC0123
  • ...='UNCODED' → ERROR

Data quality checks

After writing your output CSV:

  1. Length check
    precincts['pctnum'].str.len().value_counts() should show 6 for valid rows (2 letters + 4 digits).

  2. Error flag audit
    precincts[precincts['pctnum'] == 'ERROR']

    • Missing or mismatched county names?
    • Non‐numeric or missing precinct digits?
    • “UNCODED” values?

  3. Uniqueness (within county)
    precincts.groupby(['countyname','pctnum']).size().loc[lambda s: s>1] — duplicates should be investigated.

  4. Crosswalk spot‑check
    If you maintain a master pctnum crosswalk, inner join and review any non‑matches.

Common pitfalls & fixes

  • County string mismatches
    Normalize: df['countyname'] = df['countyname'].str.upper().str.strip().
    Ensure names match keys exactly (e.g., "SANTA CRUZ" not "SantaCruz").

  • CSV decimals
    BigQuery CSV exports often render integers like 25 as 25.0. The numeric mode handles this via int(float(...)).

  • Leading zeros lost
    Never store the numeric precinct as an integer alone; always recompute zfill(4) when generating pctnum.

  • “UNCODED” rows
    Regex mode explicitly returns ERROR for these—decide whether to drop or repair upstream.

Why this exists

Different sources encode precincts inconsistently (numbers, decimals, text labels, or missing). A single normalized pctnum key:

  • Simplifies joins across voter file outputs, canvassing exports, and survey data.
  • Prevents silent mismatches in mapping and aggregation.
  • Keeps downstream models and reports stable.

Adapting to your file

  • Column names
    Update the .apply(...) call to use your column headers (e.g., county_name vs. countyname).

  • County map changes
    If a county label differs, either normalize input or add a new mapping key.

  • Alternate parsing rules
    If your precinct codes don’t end with digits, adjust the regex accordingly (e.g., capture the first run of digits).

Example validation snippet

# Basic validation after build
ok = precincts.query("pctnum != 'ERROR' and pctnum.str.len() == 6", engine="python")
err = precincts[~precincts.index.isin(ok.index)]

print(f"Valid rows: {len(ok):,}  |  Errors: {len(err):,}")
if not err.empty:
    display(err.head(20))

Maintainer: Christina Marikos christina@ruralazaction.org Scope: Arizona precinct normalization for mapping/joining
Output key: pctnum (2‑letter county prefix + 4‑digit precinct number)