<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/getting-the-data-from-the-luxembourguish-elections-out-of-excel/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.brodrigues.co/blog/2018-10-21-lux_elections/"> Econometrics and Free Software</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p><a rel="nofollow" href="https://www.youtube.com/watch?v=yjzUxDhuXig" target="_blank"><br>
<img src="https://i0.wp.com/www.brodrigues.co/blog/2018-10-21-lux_elections/img/gambia.png?w=456" data-recalc-dims="1"></a></p>
</div>
<p>In this blog post, similar to a <a rel="nofollow" href="https://www.brodrigues.co/blog/2018-09-11-human_to_machine/" target="_blank">previous blog post</a><br>
I am going to show you how we can go from an Excel workbook that contains data to flat file. I will<br>
taking advantage of the structure of the tables inside the Excel sheets by writing a function<br>
that extracts the tables and then mapping it to each sheet!</p>
<p>Last week, October 14th, Luxembourguish nationals went to the polls to elect the Grand Duke! No,<br>
actually, the Grand Duke does not get elected. But Luxembourguish citizen did go to the polls<br>
to elect the new members of the Chamber of Deputies (a sort of parliament if you will).<br>
The way the elections work in Luxembourg is quite interesting; you can vote for a party, or vote<br>
for individual candidates from different parties. The candidates that get the most votes will<br>
then seat in the parliament. If you vote for a whole party,<br>
each of the candidates get a vote. You get as many votes as there are candidates to vote for. So,<br>
for example, if you live in the capital city, also called Luxembourg, you get 21 votes to distribute.<br>
You could decide to give 10 votes to 10 candidates of party A and 11 to 11 candidates of party B.<br>
Why 21 votes? The chamber of Deputies is made up 60 deputies, and the country is divided into four<br>
legislative circonscriptions. So each voter in a circonscription gets an amount of votes that is<br>
proportional to the population size of that circonscription.</p>
<p>Now you certainly wonder why I put the flag of Gambia on top of this post? This is because the<br>
government that was formed after the 2013 elections was made up of a coalition of 3 parties;<br>
the Luxembourg Socialist Worker’s Party, the Democratic Party and The Greens.<br>
The LSAP managed to get 13 seats in the Chamber, while the DP got 13 and The Greens 6,<br>
meaning 32 seats out of 60. So because they made this coalition, they could form the government,<br>
and this coalition was named the Gambia coalition because of the colors of these 3 parties:<br>
red, blue and green. If you want to take a look at the ballot from 2013 for the southern circonscription,<br>
click <a rel="nofollow" href="https://upload.wikimedia.org/wikipedia/commons/thumb/d/d6/Specimen_Elections_legislatives_Luxembourg_2013.png/1280px-Specimen_Elections_legislatives_Luxembourg_2013.png" target="_blank">here</a>.</p>
<p>Now that you have the context, we can go back to some data science. The results of the elections<br>
of last week can be found on Luxembourg’s Open Data portal, right <a rel="nofollow" href="https://data.public.lu/fr/datasets/elections-legislatives-du-14-octobre-2018-donnees-officieuses/" target="_blank">here</a>.<br>
The data is trapped inside Excel sheets; just like I explained in a <a rel="nofollow" href="https://www.brodrigues.co/blog/2018-09-11-human_to_machine/" target="_blank">previous blog post</a><br>
the data is easily read by human, but not easily digested by any type of data analysis software.<br>
So I am going to show you how we are going from this big Excel workbook to a flat file.</p>
<p>First of all, if you open the Excel workbook, you will notice that there are a lot of sheets; there<br>
is one for the whole country, named “Le Grand-Duché de Luxembourg”, one for the four circonscriptions,<br>
“Centre”, “Nord”, “Sud”, “Est” and 102 more for each <strong>commune</strong> of the country (a commune is an<br>
administrative division). However, the tables are all very similarly shaped, and roughly at the<br>
same position.</p>
<p><img src="https://i0.wp.com/www.brodrigues.co/blog/2018-10-21-lux_elections/img/elections_data.png?w=456" data-recalc-dims="1"></p>
<p>This is good, because we can write a function to extracts the data and then map it over<br>
all the sheets. First, let’s load some packages and the data for the country:</p>
<pre><code>library(&quot;tidyverse&quot;)
library(&quot;tidyxl&quot;)
library(&quot;brotools&quot;)</code></pre>
<pre><code># National Level 2018
elections_raw_2018 &lt;- xlsx_cells(&quot;leg-2018-10-14-22-58-09-737.xlsx&quot;,
                        sheets = &quot;Le Grand-Duché de Luxembourg&quot;)</code></pre>
<p><code>{brotools}</code> is my own package. You can install it with:</p>
<pre><code>devtools::install_github(&quot;b-rodrigues/brotools&quot;)</code></pre>
<p>it contains a function that I will use down below. The function I wrote to extract the tables<br>
is not very complex, but requires that you are familiar with how <code>{tidyxl}</code> imports Excel workbooks.<br>
So if you are not familiar with it, study the imported data frame for a few minutes. It will make<br>
understanding the next function easier:</p>
<pre><code>extract_party &lt;- function(dataset, starting_col, target_rows){

    almost_clean &lt;- dataset %>%
        filter(row %in% target_rows) %>%
        filter(col %in% c(starting_col, starting_col + 1)) %>%
        select(character, numeric) %>%
        fill(numeric, .direction = &quot;up&quot;) %>%
        filter(!is.na(character))

    party_name &lt;- almost_clean$character[1] %>%
        str_split(&quot;-&quot;, simplify = TRUE) %>%
        .[2] %>%
        str_trim()

    almost_clean$character[1] &lt;- &quot;Pourcentage&quot;

    almost_clean$party &lt;- party_name

    colnames(almost_clean) &lt;- c(&quot;Variables&quot;, &quot;Values&quot;, &quot;Party&quot;)

    almost_clean %>%
        mutate(Year = 2018) %>%
        select(Party, Year, Variables, Values)

}</code></pre>
<p>This function has three arguments, <code>dataset</code>, <code>starting_col</code> and <code>target_rows</code>. <code>dataset</code> is the<br>
data I loaded with <code>xlsx_cells</code> from the <code>{tidyxl}</code> package. I think the following picture illustrates<br>
easily what the function does:</p>
<p><img src="https://i0.wp.com/www.brodrigues.co/blog/2018-10-21-lux_elections/img/elections_logic.png?w=456" data-recalc-dims="1"></p>
<p>So the function first filters only the rows we are interested in, then the cols. I then select<br>
the columns I want which are called <code>character</code> and <code>numeric</code> (if the Excel cell contains characters then<br>
you will find them in the character column, if it contains numbers you will them in the numeric<br>
column), then I fill the empty cells with the values from the <code>numeric</code> column and the I remove<br>
the NA’s. These two last steps might not be so clear; this is how the data looks like up until the<br>
<code>select()</code> function:</p>
<pre><code>> elections_raw_2018 %>%
+     filter(row %in% seq(11,19)) %>%
+     filter(col %in% c(1, 2)) %>%
+     select(character, numeric)
# A tibble: 18 x 2
   character                       numeric
   </code></pre>