Use of lattice, RPostgreSQL for Whatcom County 2013 Elections

The political part of this post was originally posted here.

Create View Not_Returned AS
Select PrecinctID,VotingMethoddesc_1,Count(RegistrationNumber) As NotReturn from voterdb Where VotingMethoddesc_1 LIKE 'Mail Ballot Issued but not Returned' Group BY VotingMethoddesc_1,PrecinctID;

Create View Voted_by_Mail AS 
Select PrecinctID,VotingMethoddesc_1,Count(RegistrationNumber) As Voted from voterdb Where VotingMethoddesc_1 LIKE 'Voted by Mail Ballot' Group BY VotingMethoddesc_1,PrecinctID;

Create View Joined AS
Select Voted_by_Mail.PrecinctID,Voted_by_Mail.Voted,Not_Returned.NotReturn, ((Not_Returned.NotReturn + Voted_by_Mail.Voted)) As Total from Voted_by_Mail,Not_Returned WHERE Voted_by_Mail.PrecinctID = Not_Returned.PrecinctID;


library(RPostgreSQL)
library(lattice)

con <- dbConnect(PostgreSQL(), user= "postgres", password="password", port="2345", host="localhost", dbname="WC2013_12_03_2013")
dbGetQuery(con, "select count(*) from voterdb")

df <- dbGetQuery(con, "Select VotingMethoddesc_1,BallotCounted_1,Count(*) from voterdb where ballotcounted_1 LIKE '0' Group BY VotingMethoddesc_1,BallotCounted_1 ORDER BY COUNT DESC")
barchart(df$votingmethoddesc_1 ~ df$count,xlab="Votes Not Counted")

df <- dbGetQuery(con, "Select PrecinctID,(Voted::float4 / Total::float4) * 100 as PCT_Voted FROM Joined ORDER BY PCT_VOTED DESC LIMIT 40")
barchart(as.factor(df$precinctid) ~ as.numeric(df$pct_voted),xlab="percentage voted")

# using barchart and barplot. Note that 'ORDER BY precinctid' seems important for accurate data.frame reordering

df <- dbGetQuery(con, "Select PrecinctID,Voted,Total,(Voted::float4 / Total::float4) * 100 as PCT_Voted FROM Joined where precinctID::int >= 100 AND precinctId::int <= 199 ORDER BY precinctid")
barchart(as.factor(df$precinctid) ~ as.numeric(df$voted),xlab="total votes",ylab="Precinct ID")
barchart(as.factor(df$precinctid) ~ as.numeric(df$pct_voted),col="red",xlab="Percent Voted",ylab="Precinct ID")
barplot(as.vector(df$voted),names.arg=as.numeric(df$precinctid),ylab="total votes",xlab="Precinct ID")
barplot(as.vector(df$pct_voted),names.arg=as.numeric(df$precinctid),col="red",ylab="percentage voted",xlab="Precinct ID")

df <- dbGetQuery(con, "Select PrecinctID,Voted,Total,(Voted::float4 / Total::float4) * 100 as PCT_Voted FROM Joined where precinctID::int >= 200 AND precinctId::int <= 299 ORDER BY precinctid")
barchart(as.factor(df$precinctid) ~ as.numeric(df$voted),xlab="total votes",ylab="Precinct ID")
barchart(as.factor(df$precinctid) ~ as.numeric(df$pct_voted),col="red",xlab="Percent Voted",ylab="Precinct ID")
barplot(as.vector(df$voted),names.arg=as.numeric(df$precinctid),ylab="total votes",xlab="Precinct ID")
barplot(as.vector(df$pct_voted),names.arg=as.numeric(df$precinctid),col="red",ylab="percentage voted",xlab="Precinct ID")


# Install (both ggplot2) and plyr and use Hadley Wickersham's 'arrange' to presort dataframe
library(RPostgreSQL)
library(lattice)
library(plyr)
df <- dbGetQuery(con, "Select PrecinctID,Voted,Total,(Voted::float4 / Total::float4) * 100 as PCT_Voted FROM Joined ORDER BY precinctid")
arrange_df <- (arrange(df,voted))
barplot(as.numeric(arrange_df$voted),names.arg=as.numeric(arrange_df$precinctid),ylab="total votes",xlab="Precinct ID")
arrange_df <- (arrange(df,pct_voted))
barplot(as.numeric(arrange_df$pct_voted),names.arg=as.numeric(arrange_df$precinctid),col="red",ylab="Percent Voted",xlab="Precinct ID")

# sorting by precinct with barchart
df <- dbGetQuery(con, "Select PrecinctID,Voted,Total,(Voted::float4 / Total::float4) * 100 as PCT_Voted FROM Joined where precinctID::int >= 100 AND precinctId::int <= 199 ORDER BY precinctid")
barchart(~ as.numeric(df$voted) | as.factor(df$precinctid),xlab="Total Votes",ylab="Precinct ID")
barchart(~ as.numeric(df$pct_voted) | as.factor(df$precinctid),col="red",xlab="Percentage Voted",ylab="Precinct ID")
df <- dbGetQuery(con, "Select PrecinctID,Voted,Total,(Voted::float4 / Total::float4) * 100 as PCT_Voted FROM Joined where precinctID::int >= 200 AND precinctId::int <= 299 ORDER BY precinctid")
barchart(~ as.numeric(df$voted) | as.factor(df$precinctid),xlab="Total Votes",ylab="Precinct ID")
barchart(~ as.numeric(df$pct_voted) | as.factor(df$precinctid),col="red",xlab="Percentage Voted",ylab="Precinct ID")