Methodology and Code of New Hampshire Analysis

OK, here's where I outline exactly how I did the analysis of the New
Hampshire Democratic primary including code. If you don't know what I'm
talking about, see the previous entry in this blog.

The short version is that I
downloaded the HTML tables of precinct results from the Secretary
of State website and loaded them into Access using its HTML import
facility (yes, yes, I know, but Access is what I had at hand, and I was
in a hurry). I then entered the voting machine data. Finally, I derived
answers using straightforward SQL code (Access has an SQL interface,
and that's pretty much what I used). SQL has built-in functions to
derive aggregates (such as totals) over groups defined by a common
value (like vote counting technique).

I got the voting total per precinct from this URL (click on the county name at the bottom to get the totals for precincts in that county).

http://www.state.nh.us/sos/presprim%202004/dpresbelk.htm

I used the following page to determine which precincts were using which counting techniques.

http://www.nh.gov/sos/voting%20machines.htm

Here are the gory details:

First of all HTML tables are basically creatures of layout, while SQL tables have a logical structure. The HTML tables on the SOS site are broken horizontally for readability, which results in Bateman and Hamm being in the same column (because they are vertically alligned), same for Moseley-Braun and Kerry, etc. (Look at the HTML source to see what I mean). That won't do, so I inserted end and begin table markup to divide these into separate tables. I also deleted the totals, as they would distort my own. Then I imported them into Access. The table structure I used was as follows:

Table votetally
ID autonumber,
municipality text,
VotingTechUsed text,
Bateman number,
Moseley-Braun number,
..etc, for the rest of the candidates.

The ID field is called a "primary key" and is the standard database device to uniquely identify rows in a table. Autonumber means the database generates these values automatically.

This works, but the result is poorly normalized (technical term, look up "database" "normal form" if you're curious). You get multiple rows for each municipality, each having the votes for some of the candidates and zeroes for the others. To clean this up, I coalesced the votes into another table. I also took this opportunity to reduce the field I was considering to the top 5. It wasn't just Kucinich and Sharpton. The New Hampshire ballot had 22 Democrats on it, and I wanted a readable result. So I create a new table, substantially the same, but reduced to the top five:

Table Big5ivecoalesced
ID autonumber,
municipality text,
VotingTechUsed text,
Clarkvotes number,
Deanvotes number,
Edwardsvotes number,
Kerryvotes number,
Liebermanvotes number,
PopThreshold number);

PopThreshold I use later when I examine the vote in terms of town population. I filled this table with a concise version of the data from the first as follows:

Insert into Big5ivecoalesced(municipality, Clarkvotes, Deanvotes, Edwardsvotes, Kerryvotes, Liebermanvotes)
Select municipality, sum(Clark), sum(Dean), sum(Edwards), sum(Kerry), sum(Lieberman)
From votetally
Group by municipality;

This doesn't really sum the votes. It just adds the real votes to the superfluous zeroes generated by the dummy rows. It does, however, give me the real vote totals per city of these candidates. I should note here that some cities have more than one "precinct". I treated these as though they were separate municipalities, which is also how they were listed by the SOS.

Next I put in the VotingTechUsed values. You could save typing by defining a default value for this column of 'hand' in the table design window. I put these in by going into the table in the datasheet view.

Now the fun.

Here's how to calculate the percentages:

SELECT [VotingTechUsed], sum([Kerryvotes]) AS Kerry, ((sum([Kerryvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) * 100) AS Kperc,
sum([Deanvotes]) AS Dean, ((sum([Deanvotesvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes)+sum([Clarkvotes])+sum([Liebermanvotes]))) * 100) AS Dperc,
sum([Edwardsvotes]) AS Edwards, ((sum([Edwardsvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) * 100) AS Eperc,
sum([Clarkvotes]) AS Clark, ((sum([Clarkvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) * 100) AS Cperc,
sum([Liebermanvotes]) AS Lieberman, ((sum([Liebermanvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) * 100) AS Lperc
FROM Big5ivecoalesced
GROUP BY [VotingTechUsed];

Looks scary, but the key is the GROUP BY. This divides the data into groups based on the value of VotingTechUsed. The aggregate functions (all sum in this case) are applied to each such group, i.e., each group of records with the same VotingTechUsed value. Each percentage divides what the particular candidate got by what they all got (again within the groups) to get a decimal ratio. It then multiplies this by 100 to convert it to a percentage.


The query that I used to calculate the percentage by which Kerry beat Dean is as follows:

SELECT [VotingTechUsed], sum([Kerryvotes]) AS Kerry,
( ((sum([Kerryvotes])/(sum([Deanvotes])))-1) * 100) AS Kerrymargin, sum([Deanvotes]) AS Dean
FROM Big5iveCoalesce
GROUP BY [VotingTechUsed];

To eliminate the towns with more than 20000 voters, I first went to http://bbs.vcsnet.com/State.php4?NH

This is a service that provides demographic information on voters. A little material is on their site for free, including the number of voters in each town.

For every town that had a population of over 20,000 voters, I set the population threshold to 20,000. The others I set to zero (all had a population greater than zero, presumably). I may revisit this with a more granular analysis. Here is the code:

SELECT [VotingTechUsed], sum([Kerryvotes]) AS Kerry, (sum([Kerryvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) AS Kperc, sum([Deanvotes]) AS Dean, (sum([Deanvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) AS Dperc, sum([Edwardsvotes]) AS Edwards, (sum([Edwardsvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) AS Eperc, sum([Clarkvotes]) AS Clark, (sum([Clarkvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) AS Cperc, sum([Liebermanvotes]) AS Lieberman, (sum([Liebermanvotes])/(sum([Kerryvotes])+sum([Deanvotes])+sum([Edwardsvotes])+sum([Clarkvotes])+sum([Liebermanvotes]))) AS Lperc
FROM Big5iveCoalesce
WHERE PopThreshold < 20000
GROUP BY [VotingTechUsed];

The only thing new here is the WHERE clause near the bottom, which eliminates from consideration records that do not meet this criterion.

Code in this entry, such as it is, is released under the GNU Public License (GPL).