In this blog post, similar to a previous blog post
I am going to show you how we can go from an Excel workbook that contains data to flat file. I will
taking advantage of the structure of the tables inside the Excel sheets by writing a function
that extracts the tables and then mapping it to each sheet!
Last week, October 14th, Luxembourguish nationals went to the polls to elect the Grand Duke! No,
actually, the Grand Duke does not get elected. But Luxembourguish citizen did go to the polls
to elect the new members of the Chamber of Deputies (a sort of parliament if you will).
The way the elections work in Luxembourg is quite interesting; you can vote for a party, or vote
for individual candidates from different parties. The candidates that get the most votes will
then seat in the parliament. If you vote for a whole party,
each of the candidates get a vote. You get as many votes as there are candidates to vote for. So,
for example, if you live in the capital city, also called Luxembourg, you get 21 votes to distribute.
You could decide to give 10 votes to 10 candidates of party A and 11 to 11 candidates of party B.
Why 21 votes? The chamber of Deputies is made up 60 deputies, and the country is divided into four
legislative circonscriptions. So each voter in a circonscription gets an amount of votes that is
proportional to the population size of that circonscription.
Now you certainly wonder why I put the flag of Gambia on top of this post? This is because the
government that was formed after the 2013 elections was made up of a coalition of 3 parties;
the Luxembourg Socialist Worker’s Party, the Democratic Party and The Greens.
The LSAP managed to get 13 seats in the Chamber, while the DP got 13 and The Greens 6,
meaning 32 seats out of 60. So because they made this coalition, they could form the government,
and this coalition was named the Gambia coalition because of the colors of these 3 parties:
red, blue and green. If you want to take a look at the ballot from 2013 for the southern circonscription,
click here.
Now that you have the context, we can go back to some data science. The results of the elections
of last week can be found on Luxembourg’s Open Data portal, right here.
The data is trapped inside Excel sheets; just like I explained in a previous blog post
the data is easily read by human, but not easily digested by any type of data analysis software.
So I am going to show you how we are going from this big Excel workbook to a flat file.
First of all, if you open the Excel workbook, you will notice that there are a lot of sheets; there
is one for the whole country, named “Le Grand-Duché de Luxembourg”, one for the four circonscriptions,
“Centre”, “Nord”, “Sud”, “Est” and 102 more for each commune of the country (a commune is an
administrative division). However, the tables are all very similarly shaped, and roughly at the
same position.
This is good, because we can write a function to extracts the data and then map it over
all the sheets. First, let’s load some packages and the data for the country:
library(&quot;tidyverse&quot;)
library(&quot;tidyxl&quot;)
library(&quot;brotools&quot;)
# National Level 2018
elections_raw_2018 %
select(character, numeric) %__%
fill(numeric, .direction = &quot;up&quot;) %__%
filter(!is.na(character))
party_name %
str_split(&quot;-&quot;, simplify = TRUE) %__%
.[2] %__%
str_trim()
almost_clean$character[1]