The 80 percent rule: data wrangling in practise

One of the most oft-recited passages about the hyped up sport of data science is along the lines of 80 % of the work of data scientist consists of cleaning and preparing data. Data comes in many formats from many sources. Clean and ready-to-model data is often a pipe dream. In this blog post I decided to showcase my thus far most extreme example of this dictum.

Where did it all start? I had toyed around a bit with web scraping as I remembered an old blog post by one Finnish researcher from around the time of 2015 Finnish parliamentary elections about how pre-election poll ratings and actual election results have differed in the past. I decided to check out how it played out in the latest electoral showing and promptly went to the Wikipedia-page  (in Finnish) about the elections. There I found exactly the data I needed relatively nicely packaged in two tables. To make things more fun, I wanted to scrape the tables directly to R instead of resorting to any mechanical copy-paste work. Oh, not me! So I went and loaded up R and the rvest package for webscraping.

library(rvest)
html = html("https://fi.wikipedia.org/wiki/Eduskuntavaalit_2015")
polls = html_table(html_nodes(html, "table")[[14]],fill=TRUE)
result = html_table(html_nodes(html, "table")[[6]],fill=TRUE)

Looks easy enough. I save the url for the wikipedia page and then use scrape the specific "table" elements off the html code. But to have data in usable form, we are far from it! Let's see first the cleaning required for the poll data.

polls <- polls[,4:12]pollvars <- unlist(polls[1,])
colnames(polls) <- pollvarspolls <- polls[3:23,]

library(stringi)
commatodot <- function(x) {gsub(",",".",x) }
percentsoff <- function(x) {gsub("%","",x) }
slashtoNA <- function(x) {gsub("-",NA,x) }
trimming <- function(x) {substr(x,1,stri_length(x)-1) } 

polls[pollvars] <- sapply(polls[pollvars],commatodot)polls[pollvars] <- sapply(polls[pollvars],percentsoff)polls[pollvars] <- sapply(polls[pollvars],slashtoNA)polls[pollvars] <- sapply(polls[pollvars],trimming)polls[pollvars] <- sapply(polls[pollvars],as.numeric)

First I filtered out the columns I had no need for, leaving only the variables indicating the party in question. Next, receiving table with column names on first row rather than as proper column names i played around to get columns properly named. Finally I chose only the rows containing poll data I wanted.

Had my data come properly formatted, this would have been my end-point for manipulating this table. Instead, I got the data in character format, with commas instead of dots to designate decimal points, with percentage symbols and weird non-whitespace blanks. Thus a variety of functions had to be initialized to clean the data effectively until I finally was able to turn the data to numeric format.

result <- result[3:18,c(2,5)]
result <- t(as.matrix(result))

resultvars <- unlist(result[1,])
colnames(result) <- resultvars
result <- as.data.frame(result)
result <- result[2,]
result[resultvars] <- sapply(result[resultvars],commatodot)
result[resultvars] <- sapply(result[resultvars],as.numeric)
result$Muut <- sum(result[,9:16])

result <- result[,c(1:8,17)]

 The other wiki-table containing the actual election day results hardly came at any easier format. Resulting table had to be transposed, rows and columns filtered and and the cell values cleaned before there was anything of value. Unlike the table containign poll data, small parties were not lumped under "Muut" =("Others") so they had to be summed to one column so the two data frames could be lumped together.,

nams <- colnames(polls)
 

result <- result[c(3,4,2,1,6,5,7,8,9)]

colnames(result) <- nams

datum <- rbind(result, polls)
datum$index <- rep(1:22,1)
datum <- datum[order(-datum$index),1:9]

With two tables cleaned, the remaining challenge was to bind them together. Problem was that both the column names and their respective ordering differed between the tables. I picked the poll data format and renamed and reordered result data. After the two data sets were appended, I flipped the data upside down so as to have the oldest poll row at first row and the actual result at the final, 22nd row.

So, data was finally in usable order. What now? Well, I couldn't think of much. Calculate the difference between the final polling data and the actual election result and drawing a time series plot:

x <- unlist(datum[2,])
y <- unlist(datum[1,]) 

y-x

ts.plot(datum,col=1:ncol(datum))
legend("topleft", colnames(datum), col=1:ncol(datum), lty=1, cex=.65)

Kok.   SDP    PS Kesk.  Vas. Vihr.   RKP    KD  Muut 
  0.8   0.6   0.5  -2.0  -0.1   0.3  -0.9   0.4   0.4 

So of the top 4 parties, the eventual winners Centre Party underperformed in contrast to their very high polling levels, whereas the other 3 somewhat overperformed. 

Hardly exhilirating results and barely worth the effort! But such can be working with data.