Vote early and often, the wags advise, their hard-bitten bromide borne of the storied, dodgy business conducted in certain American electoral districts. But it seems as if some jurisdictions actually promote the one-person-multiple-vote equation, and for the common good besides; witness the recent Paris’ Budget Participatif (for some unaccountable reason the web site is in French), a referendum on budget allocations to a range of projects conducted both online and in hard-copy mode (they used to call those ballots), in the week endpointed by September 24 to October 1 (ok, that’s eight days, but who’s counting?). And among other niceties, each Parisian was duly enfranchised to vote for up to five projects, the numbers from the online segment recorded on the Paris Open Data site and brought to you here:
Democracy is a messy business, and by fitting corollary perhaps, the workbook’s 104,000 votes (out of a grand total of 174,090) aren’t completely tidy either (remember that the workbook reports the online votes only). Sort the age field Smallest to Largest and a spray of negative numbers douses the top of the column, and the existential impossibility they portend needs to set aright. But those minus signs are simply errors attaching to the voters’ actual age (as confirmed to me by Paris Open Data); and because they are I’d jump into the unattended cell in F2 and pencil in the ABS function:
=ABS(E2)
Excel tells us that ABS “Returns the absolute value of a number, a number without its sign”, which sounds good enough to me, restoring as it does the putative negative ages to their intended, usable positive state. Copy the ABS down F, follow with a Copy> Paste Value > Values into and down E, and then delete all those ABSes, if that isn’t too ungrateful.
But one repair doesn’t fit all. Sort the ages again, this time by largest to smallest, and take note of the 6,200 or so voters aged zero, all too happy to add their squeaky sonorities to the vox populi. These clear and present mistakes give us literally nothing to go on, and because they do I’d bail out, by scampering to row 97750 and preparing to inlay the trusty blank row that’ll hold those these pretenders at arm’s length from the ranks of the usable. But redirect your glance just a mite upward and view the 19 votes cast by one-year olds, and the 352 more registered by a precocious electorate ranging from two to nine. Remember that Budget Participatif imposed no age limit on its potential constituents, and so apparently nothing could have detained these jejune activists from the process, unless it was past their bedtime. Absent verification of these junior politicos from the powers that be – and I’ve sought it – I’d provisionally aim that blank row right instead above 97374, where the nine-year-olds give way to the tens (again we’re assuming the ages have been sorted highest to lowest). Is that drawling line arbitrary? A bit, but I’m separating those records, not deleting them; if events warrant I’ll be happy to bring them back into the fold.
Have completed those prefatory chores we can buckle up take the data out for a spin. Start with something simple, say a pivot table that breaks turnout figures by the referendum’s eight dates:
Row Labels: date_de_vote
Values: date_de_vote
date_de_vote (again, this time via Show Values As > % of Column Total)
I get:
(Note: Your machine’s regional time format may trend the row-label chronology differently. My US format pushed 1/10/2014 to the top of the labels for starters, presumably treating October 1 as January 10.By right-clicking the date and selecting Move > Move “1/10/2014” to end, I succeeded in reassigning it to its rightful place.)
What’s noteworthy here is the voter shortfall for September 26 and 27, a Friday and Saturday respectively. Remember these are internet votes, an electoral medium whose greased access route to its virtual polling place should have flattened any and all daily variation in turnout. Not so, apparently; perhaps it was the Parisian fondness for le Weekend that succeeded in distracting the collective attention away from all things political for those couple of days (on the other hand, the participatory peak in the referendum’s final two days may attest a procrastinators’ late dash to their screens).
We can next take an age-graded look at the results by lining up average voter age by project preference, something like
Row Labels: projets
Values: age (by Average, formatted to two decimals, and sorted Largest to Smallest)
age (again, by Count)
Don’t, first of all, be flummoxed by the Count of Age heading, which in fact delivers the vote count for each project, and merely grabs onto the age data as a device for aligning the votes with their project (In fact any field could have served the same end and posted identical totals; think about what is, after all, a standard pivot table tack). But I was authentically surprised by what appear to be the meaningful associations between the projects and the average age of their sectaries.
Les Evenements Sur Grand Ecrans (Google-translated as Events on the Big Screen), a (losing) proposal to boom important events to the multitudes on a giant screen in a public space, captivated the youngest average cohort, even as Musees parisiens 3.0, i.e. an digital hoard of the holdings of fourteen city museums and another idea whose time hadn’t come, piqued a demographic more than 6.5 years older. As both failed ideas seem to have belonged to the more-or-less hi-tech species, the dissonance in age between them becomes all the more curious.
But vive la differences, or something like that.
spreadsheetjournalism 
Leave a Reply