Documentation

Ingestor for Contest Result Files

This is a simple web app that downloads a precinct- and county-level election results files from N.C. State Board of Election and ingests them into a database. The code available at https://github.com/NCVotes/results-ingestor

What does this applicaton do?

ingestor_precinct.py is a simple web app that downloads a precinct result file and ingest it into the database. It runs on your local computer.

ingestor_county.py is a simple web app that downloads a county result file or a county contest file and ingest it into the database. It runs on your local computer

They are written in Python and use the Bokeh interactive visualization library. They can be run from the command line on your computer by typing bokeh serve ingestor_precinct.py ingestor_county.py --allow-websocket-origin=*

Database Structure

Precinct Results

Table: contest_precinct

Columns:

Contest related attributes

  • election_date
  • contest_group_id: an identifier to link a contest across multiple counties
  • contest_name
  • contest_type: state or county
  • is_partisan: whether the election is partisan or not
  • has_primary: whether a candidate for a particular contest first must compete in a primary before running in a general election. For partisan races, this value will be true if the number of candidates in a particular party in a particular a contest is greater than the value in the vote_for field (the number of seats that are up for election). If candidates > seats for a given party, then the board of elections will actually hold a primary, print ballots, etc. If candidates <= seats for a given party, then the board of elections will not bother with holding a primary, etc., since the result is a foregone conclusion. For non-partisan races (some of which still have primaries) this value will be true only if the number of candidates in any party exceeds the number of seats up for grabs. As this field exists in the candidate listing CSV found in #7, the value of TRUE only indicates the relationship between the number of candidates and the open seats. At least in this CSV, the field is not an indicator of whether rules for a contest require a primary. For example, if there is only one Republican candidate in a single-seat contest for which rules require a partisan primary then the value of has_primary would be FALSE.
  • party_contest: is null unless both has_primary and is_partisan are TRUE. The values of party_contest should be DEM, REP or LIB.
  • vote_for
  • term
  • is_unexpired: whether an contest is being held before the normal expiration of the previous incumbent's term.

County related attributes

  • district
  • county

Precinct related attributes

  • precinct: note that this column has vote type in it, such as ABSENTEE, ONE STOP 100, PROVISIONAL.

Candidate related attributes

  • candidate
  • first_name
  • middle_name
  • last_name
  • name_suffix_lbl
  • nick_name
  • party_candidate: party affiliation of a candidate
  • candidacy_date
  • election_day: number of votes received at the election day
  • one_stop: number of one-stop votes
  • absentee_by_mail: number of absentee-by-mail votes
  • provisional: number of provisional votes
  • total_votes: total number of votes
  • winner_flag: whether winner of the contest

County Results

Table: contest_county

Columns: same as contest_precinct without precinct

The county-level result table is aggregated from the precinct result table with the following code

CREATE TABLE contest_county
AS
SELECT election_date, 
	contest_group_id, 
    contest_name, 
    string_agg(distinct contest_type,'|') as contest_type, 
    string_agg(distinct party_contest,'|') as party_contest, 
    district, 
    county, 
    max(vote_for) as vote_for, 
    candidate, 
    string_agg(distinct first_name,'|') as first_name, 
    string_agg(distinct middle_name,'|') as middle_name, 
    string_agg(distinct last_name,'|') as last_name, 
    string_agg(distinct name_suffix_lbl,'|') as name_suffix_lbl, 
    string_agg(distinct nick_name,'|') as nick_name, 
    max(candidacy_date) as candidacy_date, 
    string_agg(distinct party_candidate,'|') as party_candidate, 
    bool_or(is_unexpired) as is_unexpired, 
    bool_or(has_primary) as has_primary, 
    bool_or(is_partisan) as is_partisan, 
    string_agg(distinct term,'|') as term, 
    sum(absentee_by_mail) as absentee_by_mail, 
    sum(one_stop) as one_stop, 
    sum(provisional) as provisional, 
    sum(election_day) as election_day, 
    sum(total_votes) as total_votes,
    sum(winner_flag) as winner_flag
FROM contest_precinct
GROUP BY election_date, contest_group_id, contest_name, district, county, candidate;

Ingestor for Voter Registration and History

This is a Django app to create and configure Postgres DB, then fetch and process public voter registration and voter history files from N.C. Board of Elections. The code is available at https://github.com/NCVotes/voters-ingestor

To setup the project for local development

  1. Clone this repo with git clone git@github.com:reesenewslab/ncvoter.git
  2. Install Python 3.6 and pip
  3. Install Postgres (On Mac, you can use brew install postgres) . Current version in this project is 9.6.2
  4. Create a virtualenv with Python 3.6.
  5. Activate the virtualenv and install requirements using pip install -r requirements.txt (from this folder)
  6. While you can run source setupdb.sh to create and configure Postgres DB for the project, you may need to consider if the database can fit on the hard disk you're working with. By default, postgres will use the drive it is installed on. If you intend to use an external or non-default drive, the setupdb.sh script takes an optional argument for a PostgreSQL TABLESPACE location. This argument is a fully qualified path to a folder on the external volume where you've created a folder for Postgres to use. E.g. /Volumes/SEAGATE/ncvoter-db. As an example, to use that folder for PostgreSQL storage, run source setupdb.sh /Volumes/SEAGATE/ncvoter-db. This script can be run at any later time, but any existing data will be deleted.
  7. To create the initially empty database tables, run python manage.py migrate inside the ncvoter folder where manage.py is.

Fetching and Processing Data

To fetch the voter data files run python manage.py voter_fetch. This will download, unzip and track any files not already downloaded. Any previously downloaded files that match are simply ignored.

To process and load the data for any existing downloaded files run python manage.py voter_process. This process can take a very long time, especially for the initial import of the files.