Ballot Database Fields

Common fields and keys

Some of the fields (i.e., variables) that appear repetatedly in the tables, and are useful for table joining and identification (i.e. keys), are:

Field	Class	Description	Example
elec	char(10)	Election (in yyyy-mm-dd format)	2018-11-06
voter_id	varchar	Voter identifier within an election (in countyFIPS-xxxxx format)	45077-07088
precinct_id	varchar	Precinct identifier within an election (in countyFIPS-xxx format)	45083-049
precinct	varchar	Precinct name	PRECINCT 64 - Three & Twenty

The offices are often abbreviated with the following keywords. The ballot::recode_abbrv() function translates the codes to full names.

Code	Description
PTY	Straight Ticket Option (Party Lever)
PRS	President
USSEN	U.S. Senator
USSEN1	2014 U.S. Senator regular (Graham)
USSEN2	2014 U.S. Senator special (Scott)
USHOU	U.S. House Representative
GOV	Governor
LGV	Lieutenant Governor
ATG	Attorney General
SOS	Secretary of State
SSI	State Superintendent of Education
SEN	State Senate
HOU	State House
CTRES	County (not state) Treasurer
AUD	County Auditor
CLR	County Clerk of Court
SOL	Solicitor
SHF	Sheriff
JPRB	Probate Judge
CCD	County Council (District)
CCC	Councyt Council Chair (or At Large)
CAP	Referendum for Captial Projects
SCH	REferendum for School Projects
ALC	Referendum for Alocohol Sales

`vote`

Original data is 115,129,066 rows. Each row is an observation in EL155, or a vote cast for an office.

`person`

Each row is a voter, and contains information at the person level (such as geography).

Field	Class
elec	char(10)
st	char(2)
county	varchar
fips	int
precinct	varchar
precinct_id	varchar
voter_id	varchar
machine	int
ballot_style	int

`candidate`

This table is metadata about each candidate (in a given election), which is used to identify the party of each choice in vote and count the number of candidates in a contest. Currently there are 5,130 rows.

Field	Class	Description
elec	char(10)	Election
contest_type	varchar	Code for office
dist	int	District number (if applicable)
county	varchar	County (if uniquely identified)
ballot_name	varchar	Candidate name, as it appears on the ballot
party_num	int	Party (numeric code)

`precinct`

Each row is a election-precinct-ballot style combination, and contains information on which precincts have which races. Currently there are 34,757 rows.

`ballot`

The ballot table is a heavily formatted table that serves as the main dataset for analysis. It is in “wide” form, each row is an election-day voter. Original data is 6,643,882 rows.

Columns show offices, and each office has several pieces of information: The name of the chosen candidate as it appears on the ballot, the party of that chosen candidate, and the number of Democrat and Republican candidates for that particular contest. Specifically:

_vote variables show the name of the candidate.
_party variables show the value of the party_num in the candidate table. -1 are for Democrats, and 1 for Republicans. Importantly, 0 is for another choice – a third party, write in, or abstention. Missings are only reserved for indicating that the office was not on the ballot. To disambiguate between 0s, see the _vote column.
_ncand variables show the number of Democrat or Republican candidates for that specificity office. A value of 2 is contested, 1 is uncontested.
_dist is a district indicator. These distinguish different races in offices for the U.S. House, State Legislature, and County Council.

The variables are below. In SQL they may be coerced to lower case.

Field	Class
elec	char(10)
county	varchar
precinct_id	varchar
ballot_style	int
voter_id	varchar
PTY_vote	varchar
PTY_party	int
PRS_vote	varchar
PRS_party	int
GOV_vote	varchar
GOV_party	int
LGV_vote	varchar
LGV_party	int
ATG_vote	varchar
ATG_party	int
SOS_vote	varchar
SOS_party	int
SSI_vote	varchar
SSI_party	int
USSEN_vote	varchar
USSEN_party	int
USSEN1_vote	varchar
USSEN2_vote	varchar
USSEN1_party	int
USSEN2_party	int
USHOU_dist	varchar
USHOU_vote	varchar
USHOU_party	int
USHOU_ncand	int
HOU_dist	varchar
HOU_vote	varchar
HOU_party	int
HOU_ncand	int
SEN_dist	varchar
SEN_vote	varchar
SEN_party	int
SEN_ncand	int
CCD_dist	varchar
CCD_vote	varchar
CCD_party	int
CCD_ncand	int
SHF_vote	varchar
SHF_party	int
SHF_ncand	int
CLR_vote	varchar
CLR_party	int
CLR_ncand	int
COR_vote	varchar
COR_party	int
COR_ncand	int
ALC	int
BND	int
CAP	int
CTRES_vote	varchar
CTRES_party	int
CTRES_ncand	int
AUD_vote	varchar
AUD_party	int
AUD_ncand	int
JPRB_vote	varchar
JPRB_party	int
JPRB_ncand	int