MATH2349 Semester 2, 2019

Assignment 3

---

Executive Summary

Data preprocessing is a procedure that takes a raw, incomplete/inconsistent and noisy set of data and transforms it into a tidy format of data. This tidy form of data is more understandable which will make our task of doing an analysis on the data set easier. By cleaning the data we will automatically get a more refined and appropriate result of our analysis.

Our data set includes the information on the FIFA Worldcup Matches from 1930 to 2014 as well as the players with the number of goals conceded by each team. Using this data in a tidy format we would like to predict/ analyze the trends of these matches. For example, how a team performs on its home ground versus an away ground (known as Home Field Advantage)? Can a team qualify for the next WorldCup match depending on their previous performances? Data analysis is also changing the recruitment process in football. The odds of a team to win a match are tremendously based on the Match Analysis.

• Two datasets WorldCupMatches with 20 variables and WorldCupPlayers with 9 variables belonging to the period between 1930 to 2014 are considered for the investigation.These datasets will later be cleaned, subsetted and merged to aid the objective of our analysis.

• Post merging, checks will be performed on the joined dataset for the verification of datatypes of each variable. This is followed by creating new variables for second half-time goals and total number of goals which will help us understand the trend better.

• Missing values are thoroughly checked. All numerical variables are investigated against outliers. Resulting outliers are then excluded using filteration.

• The variables for total number of goals and attendances are then observed through a histogram. To further normalize, the distribution transformation technique is applied.

---

Required packages

All the necessary packages are installed and loaded for the purpose of reproducing the report in here:

#install.packages(c("readr","magrittr","ggplot2","lubridate","Hmisc","dplyr",
#"car","knitr","outliers","kableExtra","rmarkdown","tidyr","forecast","editrules")
#install packages only if required
library(readr) # Useful for importing data 
library(magrittr) # Useful for pipe %>% Operator etc 
library(ggplot2) # Useful for data Visualisations & creating plots 
library(lubridate) # Useful for date and time manipulation 
library(Hmisc) # Useful for value imputation 
library(dplyr) # Useful for data manipulation 
library(car) # Useful for creating Anova tables 
library(knitr) # Useful for creating nice tables 
library(outliers) # Useful for outlier detection 
library(kableExtra) # Useful to build complex tables 
library(rmarkdown) # Useful to create neat records of analysis 
library(tidyr) # Useful for tidy up the dataset
library(forecast) # Useful for displaying and analysing univariate time series
library(editrules) # Useful for detecting obvious errors 

---

Data

• The dataset for this study is acquired from the website https://www.kaggle.com which is an online platform for users to download, upload/publish datasets, explore and much more. This open dataset has Creative Commons Licence and can be found on : https://www.kaggle.com/abecklas/fifa-world-cup

• The dataset WorldCupMatches contains 20 variables with 852 rows of records. The highlighted variables have been considered important and these variables are then subsetted for a more apt analysis with respect to our objective. The rest of the variables are not necessary for our analysis at this point. Variables like Year are not needed as this data can be derived from the variable Datetime. The resulting subset WorldCupMatches_1 now has 11 variables.

  • Year: The year in which the match was played
  • Datetime: The date on which the match was played along with a 24 hour format time
  • Stage: The stage at which the match was played
  • Stadium: Stadium name where the match was held
  • City: The city name where the match was played
  • Home Team Name: Home team country name
  • Home Team Goals: Total goals scored by the home team by the end of the match
  • Away Team Goals: Total goals scored by the away team by the end of the match
  • Away Team Name: Away team country name
  • Win conditions: Special win condition (if any)
  • Attendance: Total crowd present at the stadium
  • Half-time Home Goals: Goals scored by the home team until half time
  • Half-time Away Goals: Goals scored by the away team until half time
  • Referee: Name of the first referee
  • Assistant 1: Name of the first assistant referee (linesman)
  • Assistant 2: Name of the second assistant referee (linesman)
  • RoundID: Unique ID of the round
  • MatchID: Unique ID of the match
  • Home Team Initials: Home team country’s three letter initials
  • Away Team Initials: Away team country’s three letter initials

• The dataset WorldCupPlayers contains 9 variables with 37784 rows of records.The highlighted variables have been considered important and these variables are then subsetted for a more apt analysis with respect to our objective. The rest of the vraiables are not necessary for our analysis at this point. The resulting subset WorldCupPlayers_1 now has 4 variables.

  • RoundID: Unique ID of the round
  • MatchID: Unique ID of the match
  • Team Initials: Player’s team initials
  • Coach Name: Name and country of the team coach
  • Line-up: S=Line-up, N=Substitute
  • Shirt Number: Shirt number if available
  • Player Name: Name of the player
  • Position: C= Captain, GK= Goalkeeper
  • Event: G=Goal, OG=Own Goal, Y=Yellow Card, R=Red Card, SY = Red Card by second yellow, P=Penalty, MP=Missed Penalty, I = Substitution In, O=Substitute Out

Steps performed:

Step 1: Using the readr function read_csv() both the .csv files: WorldcupMatches & Worldcupplayers are read/imported and appropriate variables are assigned for further use.

Step 2: Datasets are checked for any duplicate rows and only distinct rows are kept. After removal of duplicates, the dimensions of both the datasets are checked.

Step 3: Subsets of both the datasets are created using the subset() function to include the above highlighted variables, in the dataframes WorldCupMatches_1 and WorldCupPlayers_1 respectively. The head() function displays the first 6 rows of the subsets.

Step 4: The inner_join() function is used to merge both the datasets using the common attribute as MatchID. The Inner Join selects all rows from both participating tables as long as there is a match between the columns. Our final dataset is now ready for further analysis with 14 columns and 37048 rows.

#set working directory
setwd("~/preprocessing_3")
      
#Reading/Importing the two datasets from .csv files
WorldCupMatches <- read_csv(file = "WorldCupMatches.csv")

Parsed with column specification:
cols(
  .default = col_character(),
  Year = col_double(),
  `Home Team Goals` = col_double(),
  `Away Team Goals` = col_double(),
  Attendance = col_double(),
  `Half-time Home Goals` = col_double(),
  `Half-time Away Goals` = col_double(),
  RoundID = col_double(),
  MatchID = col_double()
)
See spec(...) for full column specifications.

WorldCupPlayers <- read_csv(file = "WorldCupPlayers.csv")

Parsed with column specification:
cols(
  RoundID = col_double(),
  MatchID = col_double(),
  `Team Initials` = col_character(),
  `Coach Name` = col_character(),
  `Line-up` = col_character(),
  `Shirt Number` = col_double(),
  `Player Name` = col_character(),
  Position = col_character(),
  Event = col_character()
)

#check for duplicates and subset only distinct rows
WorldCupMatches_distinct <- WorldCupMatches %>%  distinct()
WorldCupMatches_distinct

WorldCupPlayers_distinct <- WorldCupPlayers %>% distinct()
WorldCupPlayers_distinct

#check dimensions
dim(WorldCupMatches_distinct)

[1] 837  20

dim(WorldCupPlayers_distinct)

[1] 37048     9

#Subsetting the Worldcup Matches dataset 
WorldCupMatches_1 <- subset( WorldCupMatches_distinct[,c(2,5:9,11,12,13,17,18)] )
#Viewing the subset
head(WorldCupMatches_1)

#Subsetting the Worldcup Players dataset
WorldCupPlayers_1 <- subset( WorldCupPlayers_distinct[,c(2,4,5,7)] )
#Viewing the subset
head(WorldCupPlayers_1)

#Left joining the datasets of Match and players details by the common attribute "MatchID"
Worldcup <- WorldCupMatches_1 %>% inner_join(WorldCupPlayers_1, by = "MatchID")
#Viewing the final dataset
dim(Worldcup)

[1] 37048    14

head(Worldcup)

---

Understand Data

In this section, we will be investigating the structure of the dataset. Datatype of each variable is observed.

• While investigating the datatypes, it was found that the variable Datetime is a Character variable. Moreover the Date and time information is stored in a single variable.

• Also, the variable Line-up is a character with only two categories ‘S=Line-up’ and ‘N=Substitute’.

Steps performed:

Step 1: The structure of the dataset Worldcup is displayed using the str() function.

Step 2: To confirm the structure of the variable Datetime the typeof() function is used. We first use the tidyr function Separate() to split the ‘Date’ and ‘Time’ as 2 different columns. We then format the separated columns. The as.date() function is used to format the date into a ‘DateMonthYear’ form and the parse_hm() function is used to format the time into ‘HH:MM:SS’ form. The structure of both are then displayed to verify the formating.

Step 3: The datatype of the variable Line-up is checked using the function typeof().

Step 4: To convert the character datatype to a factor data type, we use the function factor() with two levels ‘N’ and ‘S’ for ‘Substitute’ andd ‘Line-up’. The function is.factor() will confirm this conversion.

Step 5: The first 6 rows of the formatted dataset is displayed using head() function.

#Checking the structure of the dataset
str(Worldcup)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   37048 obs. of  14 variables:
 $ Datetime            : chr  "13 Jul 1930 - 15:00" "13 Jul 1930 - 15:00" "13 Jul 1930 - 15:00" "13 Jul 1930 - 15:00" ...
 $ City                : chr  "Montevideo" "Montevideo" "Montevideo" "Montevideo" ...
 $ Home Team Name      : chr  "France" "France" "France" "France" ...
 $ Home Team Goals     : num  4 4 4 4 4 4 4 4 4 4 ...
 $ Away Team Goals     : num  1 1 1 1 1 1 1 1 1 1 ...
 $ Away Team Name      : chr  "Mexico" "Mexico" "Mexico" "Mexico" ...
 $ Attendance          : num  4444 4444 4444 4444 4444 ...
 $ Half-time Home Goals: num  3 3 3 3 3 3 3 3 3 3 ...
 $ Half-time Away Goals: num  0 0 0 0 0 0 0 0 0 0 ...
 $ RoundID             : num  201 201 201 201 201 201 201 201 201 201 ...
 $ MatchID             : num  1096 1096 1096 1096 1096 ...
 $ Coach Name          : chr  "CAUDRON Raoul (FRA)" "LUQUE Juan (MEX)" "CAUDRON Raoul (FRA)" "LUQUE Juan (MEX)" ...
 $ Line-up             : chr  "S" "S" "S" "S" ...
 $ Player Name         : chr  "Alex THEPOT" "Oscar BONFIGLIO" "Marcel LANGILLER" "Juan CARRENO" ...

#Checking the data type of Column `Datetime`
typeof(Worldcup$Datetime)

[1] "character"

#Split column 'Datetime' into `Date` and `Time` columns 
Worldcup <- Worldcup %>% tidyr::separate(Datetime,into = c("Date", "Time"),sep =" - ")
#Formating column `Date` into Day/Month/Year format of date
Worldcup$Date <- as.Date(Worldcup$Date, format = "%d %b %Y")
#Confirming the structure of the 'Date' Column
str(Worldcup$Date)

 Date[1:37048], format: "1930-07-13" "1930-07-13" "1930-07-13" "1930-07-13" "1930-07-13" "1930-07-13" "1930-07-13" ...

#Formating column `Time` into Hour/Month/Sec format of time
Worldcup$Time <- hms::parse_hm(Worldcup$Time)
#Confirming the structure of `Time` Column
str(Worldcup$Time)

 'hms' num [1:37048] 15:00:00 15:00:00 15:00:00 15:00:00 ...
 - attr(*, "units")= chr "secs"

#Checking the data type of Column `Line-up` 
typeof(Worldcup$`Line-up`)

[1] "character"

#Converting variables from character to factor data type
Worldcup$`Line-up`<-factor(Worldcup$`Line-up`,
                           levels=c("N","S"), 
                           labels=c("Substitute","Line-up"))
#To confirm the conversion
is.factor(Worldcup$`Line-up`)

[1] TRUE

#Displaying the formated dataset 
head(Worldcup)

---

Tidy & Manipulate Data - I

In this section, the dataset is checked against following characteristics established by Hadley Wickham and Grolemund:

1. Each variable must have its own column.
2. Each unique observation has its own row.
3. All values have its own cell.

Post examination, it can be observed that the dataset is readily available in a tidy format. Thus it can be inferred that the dataset has all the above mentioned characteristics and therefore, it needs no further tidying.

#Displaying the Tidy dataset 
head(Worldcup)

---

Tidy & Manipulate Data - II

In this section, we use the dplyr function mutate() to create 3 new variables that will help us analyse the dataset further. The new variables are Home_Sec_half, Away_Sec_half and Total_Goals. Using the Second half goals we can find out if the goals are scored in the last half of the time remaining in the game. It is commonly noticed that most of the goals are indeed scored within the last few minutes. The total number of goals will help us look at their total score after each match.

Steps performed:

Step 1: Using the mutate() function we are creating the new variables Home_Sec_half, Away_Sec_half by subtracting total team goals with the half time goals and Total_Goals by adding both the Home and Away team goals together.

Step 2: head() function is used to display the dataset after creation of the new variables.

#Creating a new variable with second half time score by using the mutate function
Worldcup <- mutate(Worldcup,
                   Home_Sec_Half = Worldcup$`Home Team Goals` - Worldcup$`Half-time Home Goals`,
                   Away_Sec_Half= Worldcup$`Away Team Goals`- Worldcup$`Half-time Away Goals`,
                   Total_Goals = Worldcup$`Home Team Goals` + Worldcup$`Away Team Goals`)
#remove spaces from column names
names(Worldcup) <- make.names(names(Worldcup), unique=TRUE)
#Displaying the dataset 
head(Worldcup)

---

Scan - I

• To pre-process the data effectively, dataset is checked for missing values,inconsistencies and obvious errors. Through the baseR functions colSums() and is.na(), we find that there are only 184 missing values in Attendance variable.

• Since this is only approximately 0.6% of the size of the variable data set, we can exclude these values while performing mathematical operations (e.g., calculating mean & standard deviation for plotting normal curve) in order to not bias the analysis. This can be done by using the na.rm=TRUE argument inside the mathematical functions.

• To check for special values : -Inf, Inf and NaN, we define a function inside sapply() and calculate the total number of special values for each column. We find that there are no such values in the dataset.

• To check for obvious inconsistencies/errors, we define a set of rules using editset() funcion from editrules package. The dataset Worldcup is checked against these rules using the violatedEdits() function and its summary is checked. We find that there are no obvious errors in the dataset.

#check for missing values
colSums(is.na(Worldcup))

                Date                 Time                 City       Home.Team.Name 
                   0                    0                    0                    0 
     Home.Team.Goals      Away.Team.Goals       Away.Team.Name           Attendance 
                   0                    0                    0                   46 
Half.time.Home.Goals Half.time.Away.Goals              RoundID              MatchID 
                   0                    0                    0                    0 
          Coach.Name              Line.up          Player.Name        Home_Sec_Half 
                   0                    0                    0                    0 
       Away_Sec_Half          Total_Goals 
                   0                    0 

percent_na <- (sum(is.na(Worldcup$Attendance)) / length(Worldcup$Attendance))*100
percent_na

[1] 0.1241632

#check for special values : infinite (-inf or +inf) and NaN
sapply(Worldcup, function(x) sum(if (is.numeric(x)) (is.infinite(x) | is.nan(x) )))

                Date                 Time                 City       Home.Team.Name 
                   0                    0                    0                    0 
     Home.Team.Goals      Away.Team.Goals       Away.Team.Name           Attendance 
                   0                    0                    0                    0 
Half.time.Home.Goals Half.time.Away.Goals              RoundID              MatchID 
                   0                    0                    0                    0 
          Coach.Name              Line.up          Player.Name        Home_Sec_Half 
                   0                    0                    0                    0 
       Away_Sec_Half          Total_Goals 
                   0                    0 

#check for obvious errors
Rules <- editset(c("Home.Team.Goals >=0",
                   "Away.Team.Goals >= 0", 
                   "Attendance >=0",
                   "Half.time.Home.Goals >=0",
                   "Half.time.Away.Goals >=0",
                   "Half.time.Home.Goals <= Home.Team.Goals",
                   "Half.time.Away.Goals <= Away.Team.Goals",
                   "Line.up %in% c('Line-up','Substitute')"))
Rules

Data model:
dat1 : Line.up %in% c('Line-up', 'Substitute') 

Edit set:
num1 : 0 <= Home.Team.Goals
num2 : 0 <= Away.Team.Goals
num3 : 0 <= Attendance
num4 : 0 <= Half.time.Home.Goals
num5 : 0 <= Half.time.Away.Goals
num6 : Half.time.Home.Goals <= Home.Team.Goals
num7 : Half.time.Away.Goals <= Away.Team.Goals 

violated <- violatedEdits(Rules, Worldcup)
summary(violated)

No violations detected, 46 checks evaluated to NA

NULL

---

Scan - II

In this section we will examine all the numeric variables in the dataset for outliers using the Univariate Outlier Detection Method of boxplot. We will then filter out the outliers from the dataset to obtain a more focussed and clean dataset.

Steps performed:

Step 1: The Boxplot of the following numeric variables have been plotted respectively with their Y-axis labels and then stored into appropriate variables for the ease of further steps. - Home Team Goals - Away Team Goals - Half-Time Home Goals - Half-Time Away Goals - Second Half Home Goals - Second Half Away Goals

Step 2: The out() function along with the variable names we assigned in the last step gives us the outliers of each numeric variable.

Step 3: The baseR function min() of the outliers will give us the minima of the outliers of each of the numeric variable which we can use in the next step to filter them out.

Step 4: We are now using the dplyr function filter() to filter out the meaningful and important data from the outliers. The above mentioned 6 variables are filtered using the Minima values from Step 3 and assigned back to the main dataset Worldcup.

Step 5: Boxplots of the clarified variables are displayed without any outliers.

#Drawing a boxplot for the numeric variables to check for outliers
#Outliers of the numeric variables are assigned to a new variable
#Finding the minimum value of the outliers of each variable
BP_1 <- boxplot(Worldcup$Home.Team.Goals,main="Boxplot of Home Team Goals",ylab ="Home team score",col = "palegreen")

min(BP_1$out)

[1] 7

BP_2 <- boxplot(Worldcup$Away.Team.Goals,main="Boxplot of Away Team Goals",ylab ="Away team score", col = "palegreen")

min(BP_2$out)

[1] 7

BP_3 <-boxplot(Worldcup$Half.time.Home.Goals,main="Boxplot of Home Team Half time Goals",ylab ="Home team score",col = "palegreen")

min(BP_3$out)

[1] 3

BP_4 <-boxplot(Worldcup$Half.time.Away.Goals,main="Boxplot of Away Team Half time Goals",ylab ="Away team score",col = "palegreen")

min(BP_4$out)

[1] 3

BP_5 <-boxplot(Worldcup$Home_Sec_Half,main="Boxplot of Home Team Second half Goals",ylab ="Second half score",col = "palegreen")

min(BP_5$out)

[1] 6

BP_6 <-boxplot(Worldcup$Away_Sec_Half,main="Boxplot of Away Team Second half Goals",ylab ="Second half score",col = "palegreen")

min(BP_6$out)

[1] 3

#Filtering the variables using the minimum values of the outliers 
Worldcup %<>% filter(Home.Team.Goals< min(BP_1$out) )
Worldcup %<>% filter(Away.Team.Goals< min(BP_2$out) ) 
Worldcup %<>% filter(Half.time.Home.Goals< min(BP_3$out) )
Worldcup %<>% filter(Half.time.Away.Goals< min(BP_4$out) ) 
Worldcup %<>% filter(Home_Sec_Half< min(BP_5$out) ) 
Worldcup %<>% filter(Away_Sec_Half< min(BP_6$out) ) 
#Displaying the Boxplots of the filtered variables
boxplot(Worldcup$Home.Team.Goals,main="Boxplot of Home Team Goals",ylab ="Home team score",col = "Skyblue")

boxplot(Worldcup$Away.Team.Goals,main="Boxplot of Away Team Goals",ylab ="Away team score", col = "Skyblue")

boxplot(Worldcup$Half.time.Home.Goals,main="Boxplot of Home Team Half time Goals",ylab ="Home team score",col = "Skyblue")

boxplot(Worldcup$Half.time.Away.Goals,main="Boxplot of Away Team Half time Goals",ylab ="Away team score",col = "Skyblue")

boxplot(Worldcup$Home_Sec_Half,main="Boxplot of Home Team Second-half Goals",ylab ="Second-half score",col = "Skyblue")

boxplot(Worldcup$Away_Sec_Half,main="Boxplot of Away Team Second-half Goals",ylab ="Second-half score",col = "Skyblue")

---

Transform

Data transformation is applied on the variables : Attendance and Total goals.

Steps performed:

Step 1: We check the distribution of both the variables using the hist() function. We can see in both the graphs that the data is skewed to the right.

Step 2: We overlay a normal distribution curve over the histograms in order to visualise the trends better. It is to be noted that since Attendance variable contains 184 NA values [as discussed previously], we use na.rm=TRUE argument to exclude them.

Step 3: To decrease the skewness and normalize the data we aaply:

1. Square-root transformation for Attendance (to reduce right-skewness and applying to the zero values in the histogram)

2. Box-Cox transformation for Total_Goals with lambda = "auto"

Step 4: Checking the transformed data, we observe that the right-skewness has significantly reduced and it has converted to a normal distribution.

#Visualising Attendances
AttendanceHist <- hist(Worldcup$Attendance,
                       col="firebrick",
                       main ="Histogram of Attendances",
                       xlab ="Attendances",
                       prob=TRUE) #or freq=FALSE
curve(dnorm(x, mean=mean(Worldcup$Attendance, na.rm=TRUE),sd=sd(Worldcup$Attendance, na.rm=TRUE) ),
      add=TRUE, col="green", lwd=2 )

legend("topright",legend=c("Normal Curve"),fill=c("green"),
       text.font = 4,cex = 0.8,border= F, box.lty = 3)

#Visualising Total No. of Goals
TotalGoalHist <- hist(Worldcup$Total_Goals,
                      col="yellowgreen",
                      main ="Histogram of Goals",
                      xlab="Total No. of Goals",
                      prob=TRUE )
curve(dnorm(x, mean=mean(Worldcup$Total_Goals),sd=sd(Worldcup$Total_Goals) ),
      add=TRUE, col="red", lwd=2 )

legend("topright",legend=c("Normal Curve"),fill=c("red"),
       text.font = 4,cex = 0.8,border= F, box.lty = 3)

# Normalising using square-root transformation
Norm1 <- sqrt(Worldcup$Attendance)
# Normalising using Box-Cox transformation
Norm2 <- BoxCox(Worldcup$Total_Goals, lambda = "auto")
#Displaying the normalised histograms 
hist(Norm1, col="firebrick", main="Normalised Histogram of Attendances", 
     xlab="Attendances", prob=TRUE)
curve(dnorm(x, mean=mean(Norm1, na.rm=T),sd=sd(Norm1, na.rm=T)),
      add=TRUE, col="green", lwd=2 )

legend("topright",legend=c("Normal Curve"),fill=c("green"),
       text.font = 4,cex = 0.8,border= F, box.lty = 3)

hist(Norm2, col="yellowgreen", main="Normalised Histogram of Goals", xlab="Total No. of Goals",
     prob=TRUE)
curve(dnorm(x, mean=mean(Norm2),sd=sd(Norm2) ),
      add=TRUE, col="red", lwd=2 )

legend("topright",legend=c("Normal Curve"),fill=c("red"),
       text.font = 4,cex = 0.8,border= F, box.lty = 3)

---

Conclusion

With respect to the results above and the tasks performed throughout the report, the dataset on FIFA Worldcup Matches from 1930 to 2014 (excluding 1942 & 1946) is now tidy and ready for further match analysis. With these normalised data , we can further explore questions on how a team performs on its home ground , whether a team can qualify for the next worldcup match depending on previous perfomances and future match strategies using methods like linear regression analysis, anova, correlation and hypothesis testing.