Below the Line Voting Data: Roll Your Own Analysis
I’ve had a few people ask where I got the data for my last couple of posts, so I thought I’d put together a quick how-to for performing the same analysis, or running your own queries. I’ve used MySQL for this, but you should be able to easily convert these statements to work with your data store of choice. (Feel free to post them in the comments!)
First of all, you’re going to need to get the raw data. The AEC provides this as a bunch of CSV files, available for download on their site. The 2010 data can be found here, the 2007 data can be found here.1 Grab the National list of Candidates, and the State Below the Line Preferences files.
Next up, create some tables to hold this data. Here’s the table definition for the candidates table:
CREATE TABLE `2010_candidates` ( `state` varchar(3) NOT NULL, `party` varchar(5) NOT NULL, `party_name` text NOT NULL, `candidate_id` bigint(20) NOT NULL, `surname` text NOT NULL, `name` text NOT NULL, `elected` varchar(1) NOT NULL, `historic_elected` varchar(1) NOT NULL, KEY `party` (`party`), KEY `candidate_state` (`candidate_id`,`state`) ) ENGINE=InnoDB;
You’ll also need to create a table for each state. You could put it in one big table, but I was doing all of this on my local machine with a limited amount of RAM, so I wanted to keep the tables small:
CREATE TABLE `2010_prefs_nsw` ( `candidate_id` bigint(20) NOT NULL, `preference` int(11) NOT NULL, `batch` int(11) NOT NULL, `paper` int(11) NOT NULL, KEY `candidate_pref` (`candidate_id`,`preference`) ) ENGINE=InnoDB;
Importing the data is pretty easy. Just run this query for the Candidate file:
LOAD DATA LOCAL INFILE "/path/to/SenateCandidatesDownload-15508.csv" INTO TABLE 2010_candidates FIELDS TERMINATED BY ',' IGNORE 2 LINES;
Similarly, run this query for each state (modifying for the correct file name and table name, of course).
LOAD DATA LOCAL INFILE "/path/to/SenateStateBTLPreferences-15508-NSW.csv" INTO TABLE 2010_prefs_nsw FIELDS TERMINATED BY ',' IGNORE 2 LINES;
You now have all of the raw data. I preferred to make a summary table to hold the preference count for each candidate:
CREATE TABLE `2010_prefs_summary` ( `candidate_id` bigint(20) NOT NULL, `preference` int(11) NOT NULL, `preference_total` bigint(20) NOT NULL, KEY `candidate_pref` (`candidate_id`,`preference`) ) ENGINE=InnoDB;
Populating this table is pretty easy. Just repeat this query for each state (note the reference to the 2010_prefs_nsw table, and the “NSW” WHERE clause):
INSERT INTO 2010_prefs_summary ( SELECT c.candidate_id, p.preference, count( p.preference ) AS preference_total FROM 2010_candidates c LEFT JOIN 2010_prefs_nsw p ON c.candidate_id=p.candidate_id WHERE p.state="NSW" );
Now, grabbing the preference data for any candidate is quite easy, you just need to know their candidate_id (look it up in the 2010_candidates table):
SELECT * FROM 2010_prefs_summary WHERE candidate_id=12345;
Have fun! If you come up with anything interesting, let me know on Twitter, I’d love to hear about it!
- I’ve contacted the AEC, but it seems they don’t have data available from before the 2007 federal election – I assume they just didn’t store the raw counting data. ↩
Conroy and Fielding: The Battle for Last Place
The 2010 Australian Federal Election saw a battleground being drawn – between those who advocated voting Stephen Conroy last (for his “insist[ence] on pressing ahead with a Mandatory Internet Filter for Australia”), and those advocating voting Steve Fielding last. As it turned out, voters were cool with letting them share last and second last places.
Thanks to the amazing amount of data the AEC make available, we’re able to analyse how people voted Below the Line on their senate ticket. This graph shows how many people placed these two candidates at each position (click for full-size):
There are several points of interest here. For Senator Conroy, his largest spikes by far were at 2 and 8. This suggests that a large chunk of people are voting Labor first or second, probably after the Greens. Similarly, the spike at 57 would coincide with voters putting Labor last. Senator Fielding sees a similar pattern, the spike at 1 being people putting Family First first on their preferences, the group of spikes at the end would be Family First being voted towards last, the final spike at 56 being a large group of voters putting Family First as the last party on their ballot.
Far more interesting, however, are the last few places on the ballot. If people were voting by party, this should drop off significantly. Instead, we see both candidates having a significant proportion of voters1 putting them last or second last.
As I mentioned in my previous post regarding Below the Line statistics, both NSW and Queensland saw an increase in the proportion of voters choosing to vote Below the Line, a 0.37% and 0.3% increase, respectively. As the proportion of Below the Line voters tends to increase as the number of candidates decreases, one would expect Victoria to see a similar increase. As it turns out, the push for people to vote Below the Line saw a greater effect in Victoria, with a 0.89% increase. I would attribute most, if not all, of this growth to the campaigns mentioned above, and the availability of tools like Vote Below the Line.
So, what can we take away from these numbers? First of all, given that both of these campaigns were entirely word-of-mouth based, with zero advertising, they were surprisingly effective. Contacting and convincing 7-9% of voters to vote in a particular way is no mean feat. Social media certainly played a large part of this, whether it be new media like Twitter and Facebook, blogs like Crikey, or forums like Whirlpool.
Secondly, voters should see this as clear evidence that every individual does have a voice, when put together, can add up to a shout!
Finally, both of these Senators, along with all candidates, should read it as a clear message – voters do associate particular behaviour with particular politicians, and are capable of organising to send a protest vote to them.
- Fielding: 8.9%, Conroy: 7% ↩
Voting Below the Line: It’s the New Black
Voting Below the Line. For a long time, it’s been the domain of the political nerds, the 3% of the population who know the policy of every party on any topic. However, thanks to the rise of sites like Below the Line, it’s suddenly become easier to plan your vote in advance, without the stress of standing in the little cardboard booth on election day, trying to remember the difference between the Australian Labor Party and the Democratic Labor Party, or the Socialist Alliance and the Socialist Equality parties.
Pushing this new trend is the idea of protest voting a particular candidate – movements have sprung up, encouraging people to vote Stephen Conroy last (for his “insist[ence] on pressing ahead with a Mandatory Internet Filter for Australia”), or to vote Steve Fielding last (for being Steve Fielding, I suspect). So, how effective were these campaigns? Well, we can’t know quite yet – the Australian Electoral Commission are yet to release the Below the Line voting data for Victoria. However, while waiting for the AEC to make it available, I’ve checked out what’s been happening around the country:
- Victoria isn’t the only state that saw protest voting a candidate as a way to get a message across – in Tasmania, Eric Abetz (Liberals) and Christine Milne (Greens) saw an unusually high proportion of people putting them last on the ballot (in comparison to other candidates, or their own party). Similarly, Gary Humphries (Liberal) in the ACT found himself put last by a large chunk of the voting populace. Unfortunately, I’m unfamiliar with all of these candidates – perhaps someone more knowledgeable can fill me in on why they ended up like this?
- One Nation, The Climate Sceptics and Family First are generally disliked around the country. They were by far the most common parties to be put last.
- Despite the unending news reports describing the epidemic of voter apathy in NSW and Queensland, both states saw an increase in the proportion of voters voting Below the Line – up from 1.78% and 2.68% to 2.15% and 2.98%, respectively.
- Around the nation, there was a general increase in the proportion of voters going Below the Line, up ~0.47% from 3.14% to 3.61%.
- Generally, the proportion of voters choosing to vote Below the Line increases as the number of candidates decreases.
- Tasmanians seem to be the most willing to vote Below the Line, with 19.53% of people taking the extra few minutes to do it.
Hopefully the Victoria Below the Line data will be released soon. Naturally, I’ll be analysing how Senators Conroy and Fielding fared, and bringing you the results.
As a slightly less serious aside, there are no statistics on the number of polling places with sausage sizzles. As my local church hall had no such BBQ-related facilities, I will be pushing at both a state and federal level for urgent electoral reform to be enacted – the AEC should be responsible for ensuring all polling venues have fair and equal access to the appropriate equipment and supplies required to provide voters with charcoaled meat in a slice of white bread. 
UPDATE 2010-09-21: Victorian stats released, corrected total BtL statistic to match.
Job Manager Highlight: Thinc Projects
Thinc Projects just launched their new careers site, with the help of the people at Zojo. They were after a job board plugin, and after evaluating other options, came to the conclusion that Job Manager was the obvious choice.
I found Job Manager easy to work with from day one, I appreciated the way it works straight out-of-the-box and I knew my client would understand it straight away upon handover. This allowed me to set up my fields and pages as required with full knowledge that everything was working and all I had to do was apply my design.
Customizing the form layout to the final design was easy. Using CSS, I was able to style and add classes to my liking by adding code into the page templates as I required them. From a developers view point I was able to customise the layout and fields according to my heart’s content (or my clients’ I should say).
— David Gross, Zojo
They were also after particular information from applicants, so the ability to customise the application form as invaluable. And just because I like to let the users speak for themselves, here’s a little more of what David had to say:
While evaluating other options, nothing came close in terms of freedom of options, customisation and support.
So there you have it – download Job Manager, you can easily create a job board for your company!
Are you doing creative things with Job Manager? Have some time to tell me how and why you’re using it? Drop me a line, you could be the next Job Manager Highlight!
The Bedroom Philosopher @ The Northcote Social Club
After a year away from Melbourne’s live music scene, it’s taking me a little bit to get back into the routine. Last time, I forgot my earplugs and camera. This time, I only forgot my camera. Hopefully the next gig will involve me not forgetting anything! But in the mean time, you get some slightly lower quality photos and video, courtesy of my phone.
First up for the evening’s lineup was Pinky Beecroft and The White Russians. As a long time fan of Pinky’s work, it’s always a pleasure to see them on stage, tonight was no exception.
As he mentioned in the above clip, Pinky was in a good mood, so didn’t grumble too much when the traditional Melbourne request for a Machine Gun Fellatio song came up (in this case, Unsent Letter). With a little luck it won’t scare The White Russians off from performing here again in the near future!
Up next were The Boat People. Sadly, they were a bit underwhelming after The White Russians. The music was okay, but not spectacular. Girl jeans are a bad look on guys – the lack of room for boy bits makes for the rather unattractive “pocket dong” look. That said, they did finish on a strong note, their last song, Born in the 80s (from their previous album) was pretty rockin’.
Finally, the main act: The Bedroom Philosopher. I hadn’t had the pleasure of seeing him live, having only heard a couple of songs on JJJ. The public transport theme was cool, with his backing band (The Awkwardstra) dressed in old MET uniforms. I’m also a big fan of any band that can bring on a brass section, even if they only managed a couple of trumpets.
Sadly, the gig was marred a little by a (Crazy fan? Jilted lover? Belligerent drunk? All of the above?), named Bridget, who felt the urge to interrupt, squeal and make a nuisance of herself, even when asked to stop by the band and the crowd. Bridget, if you’re reading this, stop doing that. You’re kind of ruining it for everyone else.
Luckily, the gig got right back on track, culminating in a magnificent “Pyramid of Power”!
A few more photos available here.
Gary Pendergast, noun: Gamer and developer. MySQL, WordPress, web and music geek. Makes interesting things at Automattic. Can be observed in his natural habit at pento.net, or usually in Melbourne, Australia.
My Photo Blog
