Compare Precinct Voting Lists over time

precinctid	cnt_db090514	cnt_db091914	cnt_db100214	cnt_db102014	difference	variance
101	939	907	911	911	28	1.0307354555
102	632	622	624	627	5	1.0079744817
103	689	680	681	682	7	1.0102639296
104	428	426	427	427	1	1.0023419204
105	414	404	408	408	6	1.0147058824
106	778	768	772	774	4	1.0051679587
107	946	914	920	922	24	1.0260303688
108	1082	1054	1056	1063	19	1.0178739417
110	687	683	685	688	-1	0.9985465116

...

Pro I594 Contributions

Post on pro I594 Contributions is here. Code below.

Scripts for PDC Data:10.07.2014 Contributions to WA LD 42

Political piece is here.

SQL for flushing Inactive Voters

count | ballotcounted_1 | ballotcounted_2
------+-----------------+-----------------
  634 | 0               | 1
  506 | 0               | 0
  333 | 1               | 1
  211 | 0               |
  153 |                 |
   51 | 1               |
   22 | 1               | 0
    8 |                 | 1
    7 |                 | 0

We can filter 'lost' voters like this:

~14K inactive in Whatcom County
~8K inactive in 42nd LD
~2K marked inactive since Certification of Primary e.g '08/20/2014'

That filter gives us these targets listed by priority

• 333 of that remaining ~2K who voted in both of the last General Elections.
• 73 of that remaining ~2K who voted only in the last General Election.
• 642 of that remaining ~2K who voted only in the General Election before last.

Political piece is here.

Summary view of a voterdb

Summary voterdb statistics for this political piece.

avg(age::int4) OVER (partition by precinctid)

Political piece is here.

 precinctid | activeaverage | inactiveaverage | diffaverage
------------+---------------+-----------------+-------------
        268 |  53.6         |  36.1           |  17.5
        203 |  52.9         |  36.4           |  16.5
        139 |  45.9         |  30.6           |  15.4
        202 |  60.1         |  45.3           |  14.8
        167 |  53.9         |  39.2           |  14.7
        103 |  58.9         |  44.5           |  14.4
        205 |  50.7         |  36.4           |  14.3
        201 |  52.5         |  39.2           |  13.3
        144 |  49.6         |  36.6           |  13.1
        131 |  52.2         |  39.5           |  12.7
....

There are several new Postgres moves here for me. At this point, I expect my joins to become more sophisticated in the future.  Postgres syntax:

avg(age::int4) OVER (partition by precinctid)

allows a statistical slice of a factor similar to xtabs  (cross tabulation) in R.  Quite frankly, I think R does this with greater fluidity and less code.

SQL for comparing Active vs Inactive voters over time

/* 6:20 PM 9/23/2014 -RMF Political piece for this code is here.
I create query to dump out the various (voter history) databases I wish to compare to Postgres 'views'.  A 'view' in Postgres is essentially and 'in memory' table. There's more to than that but...:

Select * from voterdb where statuscode = 'I'; -- 'I' for 'inactive' 

The unique voter registration number isn't quite reliable as a primary key over time (in my humble opinion) so I use a 'unique tuple' (ARRAY[lastname,firstname,middlename]) of my own invention.
Something like ARRAY[lastname,firstname,middlename,registrationnumber::TEXT] would be even more unique.  You also need the 119 precincts of LD 42.
*/