The data source

Getting this data to be compatible with the shapefile (one row per pollid) turned out to be much more of a pain than I first thought.

The data we use is downloaded from Election Canada website rather than the Open Canada website. There are two formats, with “format 2” being tidier.

There is one csv for each riding, and then there is 1 row per poll station*candidate as per this data dictionnary:

If you pay attention more attention than I did at first, you will notice that the “Polling Station Number” is not the same thing than the PD_NUM in the shapefile. “48-3” is actually the EMRP_NAME. Also, what is the letter doing in “45A”? This appears to be because polls were sometimes broken down in “subpools”.

The “Merge With” column is another indicator of incoming trouble. When this column is not missing the votes and rejected ballots for the pool (or subpool) are 0. My best bet is that pools (or subpools) were sometimes merged with other pools before the ballots were counted.

a painful alternative source (keep away!)

The first dataset that I found is the one on the Open Canada website. To say that it isnt tidy data is an euphemism.

Like “format 2”, there are over 300 CSV files, one per riding. However, we have 1 single row per polling station, instead of one per polling station times the number of candidates. Instead, there is one column for each candidate, such as “Lorraine E. Barnett” in riding 10001 in Newfoundland.

If you want to map the results by party, like I do, you would have to fetch the information in a second CSV file that records the candidate information. This CSV file, named table_tableau12.csv has a column “Candidate”. However, the content of this column is NOT the name of the candidate as shown in the first csv, but rather the concatenation of candidate and his affiliation. The record for Lorraine e. Barnett reads as follow : “Lorraine E. Barnett Conservative/Conservateur”. Sometimes, there would be double asterisks included between the name of the candidate and the party, such as for “Judy M. Foote ** Liberal/Libéral” in riding 10002. I initally used regular expressions to remove all the 25-ish possible affiliations, then strip the trailing space and the eventual "**" to be able to separate the candidate name from the party and merge it back to the results-by-poll table.

Then, I realised that Format 2 existed outside the Open Data portal. I was not impressed.

Downloading election results at the poll level

Since there are 328 district and one CSV file for each of them, I used the map function to generate a list of each district and download them.

I first tried to download them using download.file() but I got an http error. I believe this is because the government doesnt let robots download from its website. The solution was to use the httr::GET function, which can fake being a web browser to download the files.

Reading and EDA of poll results

I read all the csv files and concatenated them using purrr:map_df. I ran into a few issues:

• a character variable (“Polling Station Number”, or “poll_merged_with”) is wrongly guessed as integer. This means that some polling station numbers, such as 142A and 142B in district 24015 would be incorrectly read as NA. This was solved by reading all variables as characters then converting them using type_convert()
• Depending on which of the 328 district is being read, the columns poll_num and poll_merged_with could be integer or character. I mutate both of them to characters before binding the rows using map_df().
• Column names with apostrophes, such as Candidate’s Middle Name/Second prénom du candidat, need to be surrounded by single quotes when wrangling them using dplyr to prevent errors.
• Letters are sometimes appended as suffix to the “polling station number” or the “poll merged with”. I couldnt find an explanation in the documentation, but I believe these are “subpools”
• Some polls are named “SVR Group 1/RÉS Groupe 1” and “SVR Group 2 /RÉS Groupe 2”, I am not sure how to deal with them, so I ended up filtering them out.

Trailing letters in the poll name are a common occurrence: 29091 of the 396412 observations have a trailing letter in the poll name.

• Each “subpoll” has a different number of electors.
  
• Some of them may subpolls may or may not have been merged to another poll. (ex1 to 3)
  
• Some polls have had their subpolls merged to multiple different polls. (ex4)
  
• A poll doesnt have to be broken into subpools to be merged to another poll. (ex5)

Another important note mentionned before: when a poll is merged to another poll, the number of votes for each candidates and the number of rejected ballots is set to 0. This leads me to believe that they simply took the box containing the ballots and mixed it with the box from another poll before counting it.

Cleaning the poll results

Our desired output is a data frame containing one row per poll id, defined as the concatenation of FED_NUM and EMRP_NAME.

For each poll, we want to know the percentage of the total vote that was allocated to each of the main partys: Libéral, Convervateur, NPD, Bloc Québécois and Green Party.

I used a 2 step process:

1 - If the poll was not entirely merged to some other poll, then I use the data from the remaining subpoll(s) to know the vote distribution of that poll.
2 - If the poll was entirely merged to other poll(s), then I fetch the results of the poll where the most electors were sent.

How good is my link between poll_results and poll_shp?

Amazingly, we have results for ALL polls defined in the shapefile!
But what are these polls for which we dont have a shape? Mostly, this is the advance poll (numbers 600-630) for which we didnt expect to have a shapefile. There are a few exceptions. For example, why is there no PD_NUM==4 in FED_NUM 35058 in the shape file?

The really important thing for our purpose is to have results covering the whole map of Canada, which is the case.

## # A tibble: 3 x 15
##   FED_NUM dist_name EMRP_NAME poll_ballot_rej… poll_votes winner pct_winner
##     <dbl> <chr>     <chr>                <dbl>      <dbl> <chr>       <dbl>
## 1   35058 Mississa… 4                        0          0 conse…        NaN
## 2   35094 Scarboro… 4                        0          0 autre         NaN
## 3   48020 Edmonton… 4                        0          0 autre         NaN
## # … with 8 more variables: autre <dbl>, bloc <dbl>, conservateur <dbl>,
## #   libéral <dbl>, npd <dbl>, vert <dbl>, poll_merged_with <chr>, pollid <chr>
## # A tibble: 123 x 2
##    EMRP_NAME     n
##    <chr>     <int>
##  1 600         338
##  2 601         338
##  3 602         338
##  4 603         338
##  5 604         338
##  6 605         337
##  7 606         337
##  8 607         336
##  9 608         333
## 10 609         326
## # … with 113 more rows