Can 40 million Frenchmen – and women – be wrong? I don’t know – but how about 66,687 of them? That not-nearly-as-memorable, indubitably less-than-round number counts the Parisians who voted their preferences in this September’s Budget Participatif, a referendum aiming to peel off about 70 million euros for a raft of projects in the city depending on the voters’ ouis or nons. And the spreadsheet ticking off the winners and losers and appearing on the Open Data Paris site, is right here:
(If you’re in Google Translate mode you can also download the sheet directly from the Participatif site, with approximate English renderings of project names. It’s pretty messy, though.)
Yes, we’ve been here before, having spent some time last year on a spreadsheet detailing the 2014 outcomes, and we’re back; and if my cognates-reliant French and/or Google Translate have done what they’re supposed to do – and neither bet is sure – the Participatif site is telling me that eight Paris-wide and 179 arrondissement-specific (that is, one of Paris’ 20 districts) projects won a designated outlay, and so will be readied for implementation (you can throw a Google Translate at this page for Budget FAQs and the like, and hope that it sticks).
Note that voters, who were not beholden to age eligibilities (a peculiarity to which we called some attention in our previous Participatif piece), were enfranchised to endorse ten city-wide as well as ten arrondissement projects; but our spreadsheet reports the aggregated vote sums for each proposal across its 625 rows, aggregating itself into a most petite 69,600 bytes, or about one per voter.
The worksheet presents some standard Open Data Paris identifiers, e.g. wrapped-text headers (and some field cell contents) that need smoothing, and more important, vote-total data in columns E through G that purport a numeric standing but resolve themselves, at least on my Yankee screen, as text. But the sheet can’t be faulted for that disjuncture, because it delimits decimal points by the Gallic comma in lieu of the Anglo period/full stop, and as such reads textually here, at least for me.
That’s a problem, of course, but one easily attended to. I’d run a Find and Replace on the disrupted fields, something like this:
Press this remedy forward and the numbers come back, albeit with a three-decimal escort that we really don’t need and that should be formatted away. Indeed, I can’t account for the single decimal that attaches itself to the original French-formatted values either; the votes are votes, and as such can’t be fractionated, at least not for this sort of election. And you’ll observe at the same time the decimal-free Montant (Budgeted sums) field, requiring no numeric restitution at all. And the Identifiant (proposed project ID) field in A is similarly beset, and should probably be likewise cleared of its textual, decimal-laded baggage. But on the other hand, they’re IDs, and so we’re not going to add or subtract them.
Note as well the 75000 entries in Localisation, the field that supplies the arrondissement postal codes that position the project proposals in their places. 75000 then marks a Paris-wide project, also corroborated by their blank longitude/latitude coordinates in column J (XY).
Once the data have been put through these paces, some simple but informative findings are out there to be found. For example, perform a simple SUM of the vote totals in G and you’ll register 733,381; divide that overall count by the referendum’s 66,687 voters and you’ll realize an average of almost exactly 11 selections per voter – around 10.99736, if you take the number that far. Remember that each participant was in effect granted 20 votes, as it were, per the 10/10 city-arrondissement split described above and so it would seem that voters either knew or cared too little about some of the projects. But it’s a free country.
Next note the VOTES PAR INTERNET and VOTES PHYSIQUES data, in line with the option afforded voters to cast their ballot either online or at an in-person venue. If we draw up a new field in K, which I’m calling Internet/Physique, and enter in K2:
We will have thereby figured the ratio of cyber to land-based votes by project, once you copy down K, of course. We can then try this pivot table:
Row Labels: Projets Gagnant/Non retenus (that is, project winners and losers, respectively)
Values: Projets Gagnant/Non retenus (Count)
Decimals aside, we see that the Internet/Physical Place proportions for project winners and losers is nearly identical; that may merely substantiate a default, null hypothesis, but I would have suspected some disparity along winner-loser lines, for whatever reason, and it seems I’d have been wrong. On the other hand, I should have been wrong, because the multi-choice project vote option vested in each participant might have circulated sufficient variety among the per-voter selections to in effect swing us back to the default prediction. That is, given the pool of 600-plus projects from which to decide, the for/against distributions might well be expected to vary “randomly” by type of voter venue.
However, if you break out the ratio by arrondissement:
Row Labels: Localisation
Values: Internet/Physique (Average; here I’d format the numbers to two decimals)
The data give way to striking maldistributions:
This time the ratios careen all across the table, and I’m not entirely sure why (remember the large 75000 total reflects the vote for city-wide projects. In addition, it’s not entirely clear that the voters were required to live in the arrondissements for whose projects they voted. See the Participatif site, FAQs 31 and 32. And one more important in addition: note the above totals factor all 733,000 votes, and not the isolable individuals who cast them. Thus the numbers here could be roughly divided by 11, the average vote-per-voter, without any apparent impairment of the meaning of the results. But it’s also possible internet voters opted for more projects than those of the in-person cohort – or vice versa. That potential skew merits consideration, but it can’t be plotted directly from the spreadsheet data.)
It’s quite noteworthy that the voters in the posh 75007 and 75008 arrondissements favoured in-person balloting, as did participants in the more-or-less trendy 3rd and 4th. But the hip, Left-Banked 6th was most internet-partial, as was the 9th in the center of the city. Whether these skews are properly understood as attitudinal or logistical, i.e. a function of the accessibility of the on-the-ground polling places, is a matter for a round of research. We could add however, that the correlation between Internet/physique ratios and arrondissement participation sizes is an indifferent .12.
So at least that doesn’t matter.