MATH2349 Data Wrangling

Assignment : Data Preprocessing

Required packages

# This is the R chunk for the required packages
library(dplyr)
library(tidyr)
library(readxl)
library(readr)
library(foreign)
library(knitr)
library(rvest)
library(deductive)
library(validate)
library(outliers)
library(MVN)
library(forecast)

Executive Summary

In this project I have merged three data sets (Country Information, Justification Among Adolescence and World Happiness) to form one cohesive data set providing data by country on attitudes towards violence against women, happiness ranking and socioeconomic status. This allows for future analysis between variables to be easily computed.

Data was downloaded from open source websites and imported using base r and readxl functions. Factor and numeric variables conversions were completed and column names set as necessary. Square bracket sub-setting was used to select only relevant variables. The Country Information and Justification Among Adolescence data sets were considered untidy and inappropriate values were removed from columns to ensure each column represented one variable.

Three new variables were created using the mutate function to give the average and difference of male and female respondents as well as the percentage difference.

To ensure data is suitable for future analysis, it is scanned for missing values, to ensure Male (%) or Female (%) and both Happiness Ranking and Income Group are present. Values that are missing in either gender are imputed with the respondent available for that country multiplied by percentage difference. A small number of rows still with missing values for income group or happiness ranking are then removed.

Outliers are scanned visually using boxplots and numerically using z.score normalisation. No outliers for univariate variables were identified. Bivariate outliers for Male (%) and Female (%) comparison are identified using Mahalabonis distance and remain for further investigation during analysis. In order to obtain a normal distribution , the Adolescent Average variable undergoes a square root transformation.

Finally, the resulting, tidy data frame is saved as a csv file to allow analysis to be readily completed.

Data

Three data sets containing information about the happiness levels of countries, attitudes towards violence towards women and socioeconomic and geographic details have been obtained. These data sets are preprocessed and merged to provide a complete data set for a range of analytical questions.

Happiness Levels Data Set

Data regarding the happiness levels of countries has been obtained from Kaggle. The data set was downloaded as a csv file from the following URL https://www.kaggle.com/unsdsn/world-happiness.

The data provided is obtained from responses to the Gallup World Poll.Countries are ranked compared to a fictional country ‘Dystopia’ which is given a score of zero for all variables. The original data set contains 9 variables. The following 5 variables have been identified as relevant.

Country Name: Name of the Country. Character Variable.

Overall Rank: Ranking of the country’s happiness indicator, with 1 being the country with the highest overall happiness score, based on a combination of the other variables. Numeric variable.

Score: Happiness score. Value between 0 and 10. 0 indicates the level of Dystopia, with no reported happiness. 10 indicates a perfectly happy respondent. Continuous numeric value.

GDP: GDP per capita. Refers to extent to which GDP refers to the happiness score. Value between 0 and 1.69.

Freedom: Freedom to make life choices. Refers to the extent of which freedom contributed to the happiness score.

Country Information Data Set

Information regarding the geographic region and socioeconomic status of the countries has been obtained from the World Bank data set “Women who believe a husband is justified in beating his wife (any of five reasons) (%)”. The data set was downloaded as a csv file from the following URL https://data.worldbank.org/indicator/SG.VAW.REAS.ZS?end=2018&name_desc=false&start=2018&view=bar This data set contains the supplementary information about the countries. Data, which were obtained in a separate but corresponding data set, here titles Adolescents Who Agree. The Country Information Data Set contains the following relevant variables.

Country Code A character variable identifying the Country

Region Identifies the geographical region of the country. A categorical, factor variable.

Country Name Identifies name of the country. A character variable.

Income Group Groups the countries into ordered factors based on average income. Factor levels are Low Income < Lower Middle Income < Middle Income < Upper Middle Income < High Income.

Adolescents Who Agree (AWA)

Information regarding ‘the proportion of adolescents who believe a husband is justified in beating his wife for any of the five given reasons’ has been obtained from the Unicef website at the following URL https://data.unicef.org/topic/gender/harmful-practices-and-intimate-partner-violence/.

The data is collected from a sample of 15 to 19 year old male and female adolescents between 2012-2019. The five reasons are given as arguing with their husband, burning the food, neglecting the children, refusing sexual relations and going out without telling him. The AWA data sets contains the following variables.

Country Name A character variable identifying the country.

Region Name A categorical variable identifying the geographic region of the country. An un-ordered factor.

Male Percentage of male respondents that believe wife beating is justified. A continuous numeric variable with a value between 0 and 100.

Female Percentage of female respondents that believe wife beating is justified. A continuous numeric variable with a value between 0 and 100.

The above data sets will undergo preliminary manipulation and tidying before being merged. In the following chunk data has been imported using baser and readxl functions. Data has been subsetted to include only relevant variable with the square bracket [] subsetting function. Column names are adjusted as necessary by reassigning with colnames().

# This is the R chunk for the Data Section

# Happiness Levels 
happy_2018<- read.csv("2018.csv")
colnames(happy_2018) = c("Overall Rank", "Country Name", "Score", "GDP", "Social Support", "Healthy LE", "Freedom", "Generosity", "Perceptions")
happy_2018<- happy_2018[ , c("Overall Rank", "Country Name", "Score", "GDP", "Freedom")]
head(happy_2018)

# Country Information
country_info<- read.csv("Justified_country_info.csv")
colnames(country_info)<- c("Country Code", "Region", "Income Group", "Notes", "Country.Region.To.Split")
country_info<- country_info[ , c("Country Code", "Region", "Income Group", "Country.Region.To.Split")] # Column containing notes on country that are not related to a variable has been omitted
head(country_info)

# Adolescents Who Agree
women_stats<- read_excel("Justification-of-wife-beating-among-adolescents-database_June-2020.xls", skip = 7, n_max = 203) #skip and n_max arguments are used to ensure only relevant rows with observations relating to countries are imported.

New names:
* `` -> ...3
* `` -> ...5

colnames(women_stats)<- c("Country Name", "Male", "3", "Female", "5", "Source")
women_stats<- women_stats[ , c("Country Name", "Male", "Female")] # Variable containing source information has been omitted. 
head(women_stats)

Understand

The World Happiness Data Set

The variable types in this data set are considered appropriate and do not need converting. Class type is data frame.

Overall Rank: Integer

Country Name: Character

Score: Numeric (Double)

GDP: Numeric (Double)

Freedom: Numeric (Double)

Country Information Data Set

The region and income group variables are converted from character variables into factored variables using the factor() function. The ordered = TRUE argument is applied to the Income Group variable to ensure the factors are in the appropriate order. Class type is data frame.

Country Code: Character

Region: Factor with 7 Levels: “Latin America & Caribbean”, “South Asia”, “Sub-Saharan Africa”, “Europe & Central Asia”, “Middle East & North Africa”, “East Asia & Pacific”
[7] “North America”

Income Group: Ordered Factor with 4 Levels: “Low income”, “Lower middle income”, “Upper middle income”, “High income”

Country.Region.To.Split: Character

Adolescents Who Agree Data Set

The Male and Female variables were automatically read as character due to presence of NA values. These are converted from character to numeric variables as they represent a percentage. This is completed using the as.numeric() function. Class type is data frame.

Male: Numeric (Double)

Female: Numeric (Double)

Country.Region.To.Split: Character

# This is the R chunk for the Understand Section

# Check variable type: Happiness Levels 
str(happy_2018)

'data.frame':   156 obs. of  5 variables:
 $ Overall Rank: int  1 2 3 4 5 6 7 8 9 10 ...
 $ Country Name: chr  "Finland" "Norway" "Denmark" "Iceland" ...
 $ Score       : num  7.63 7.59 7.55 7.5 7.49 ...
 $ GDP         : num  1.3 1.46 1.35 1.34 1.42 ...
 $ Freedom     : num  0.681 0.686 0.683 0.677 0.66 0.638 0.653 0.669 0.659 0.647 ...

typeof(happy_2018$Score)

[1] "double"

typeof(happy_2018$GDP)

[1] "double"

typeof(happy_2018$Freedom)

[1] "double"

# Check variable type: Country Info
str(country_info)

'data.frame':   263 obs. of  4 variables:
 $ Country Code           : chr  "ABW" "AFG" "AGO" "ALB" ...
 $ Region                 : chr  "Latin America & Caribbean" "South Asia" "Sub-Saharan Africa" "Europe & Central Asia" ...
 $ Income Group           : chr  "High income" "Low income" "Lower middle income" "Upper middle income" ...
 $ Country.Region.To.Split: chr  "Aruba" "Afghanistan" "Angola" "Albania" ...

# Convert to factor where necessary
country_info$Region<- factor(country_info$Region, levels = c("Latin America & Caribbean", "South Asia", "Sub-Saharan Africa", "Europe & Central Asia", "Middle East & North Africa", "East Asia & Pacific", "North America"), labels = c("Latin America & Caribbean", "South Asia", "Sub-Saharan Africa", "Europe & Central Asia", "Middle East & North Africa", "East Asia & Pacific", "North America"), ordered = FALSE)

country_info$`Income Group`<- factor(country_info$`Income Group`, levels = c("Low income", "Lower middle income", "Upper middle income", "High"), labels = c("Low income", "Lower middle income", "Upper middle income", "High income"), ordered = TRUE)

# Check levels and structure
str(country_info$`Income Group`)

 Ord.factor w/ 4 levels "Low income"<"Lower middle income"<..: NA 1 2 3 NA NA NA 3 3 3 ...

levels(country_info$`Income Group`)

[1] "Low income"          "Lower middle income" "Upper middle income" "High income"        

str(country_info$Region)

 Factor w/ 7 levels "Latin America & Caribbean",..: 1 2 3 4 4 NA 5 1 4 6 ...

levels(country_info$Region)

[1] "Latin America & Caribbean"  "South Asia"                 "Sub-Saharan Africa"        
[4] "Europe & Central Asia"      "Middle East & North Africa" "East Asia & Pacific"       
[7] "North America"             

# Check variable type: Adolescents Who Agree
str(women_stats)

tibble [203 × 3] (S3: tbl_df/tbl/data.frame)
 $ Country Name: chr [1:203] NA "Afghanistan" "Albania" "Algeria" ...
 $ Male        : chr [1:203] NA "70.599999999999994" "10.9" "−" ...
 $ Female      : chr [1:203] NA "78.299999999999997" "4.7000000000000002" "55" ...

# Convert percentage variables from character to numeric 
women_stats$Male<- as.numeric(women_stats$Male)

NAs introduced by coercion

women_stats$Female<- as.numeric(women_stats$Female)

NAs introduced by coercion

typeof(women_stats$Male)

[1] "double"

typeof(women_stats$Female)

[1] "double"

Tidy & Manipulate Data I

Of the above data sets, the World Happiness data set obtained from Kaggle is considered to be tidy. Each variable must has its own column, each observation must has its own row and each value has its own cell.

The Country Information data set is not considered to be tidy. This is because in the original data set Country and Region variables are found within the one column, titled ‘Table Name’. Additionally, there are rows which do not contain relevant observations.

Upon inspection of the Country Information data set, it can be seen that the rows that have missing values in the ‘Income Group’ column are consistent with the rows that have inappropriate values for the Country.Region.To.Split variable column. As we only need to keep data for country names from this column, we will omit those rows with missing values to tidy the data set. This is completed using the complete.cases() function which is appropriate for the sub-setted columns. This action will complete both of the steps required to tidy this data set. The column will be appropriately renamed to “Country Name” as it no longer contains unwanted information regarding the Region variable.

The Adolescents Who Agree dataset is not considered tidy. Two of the variables in columns 3 and 5 contains rows with multiple values in the one cell. However, the information in these columns is only given for a limited number of observations and pertains to the year or method of collection. It is not considered relevant to this data set, therefore these variables have been omitted (See Data section)

The data sets will be merged into one data frame.

# This is the R chunk for the Tidy & Manipulate Data I 

# Country Information Data Set - filter out names of Regions by sub-setting for only complete cases. 
country_info<- country_info[complete.cases(country_info), ] #This data frame should now contain no missing values when scanned
colnames(country_info)<- c("Country Code", "Region", "Income Group", "Country Name")
head(country_info)

# Merge Data sets
justified_full<- women_stats %>% full_join(country_info) %>% full_join(happy_2018)

Joining, by = "Country Name"
Joining, by = "Country Name"

head(justified_full)

# Subsetted to remove unnecessary variables
justified_full<- justified_full[ ,c(1:3,5:7)]

# Columns renamed for added clarity
colnames(justified_full)<- c("Country Name", "Male(%)", "Female(%)", "Region", "Income Group", "Happiness Ranking")

Tidy & Manipulate Data II

Three new variables are created using the mutate() function and simple maths functions. The variables are first converted back to type numeric as necessary.

• The average value by country of Male (%) and Female (%) is called ‘Adolescent Average’

• The difference in value in Male (%) and Female (%) respondents by country is called ‘Male-Female Difference’

• These two variables are then combined to create a Percentage Difference variable.

The mean of the Percentage Difference variable will be used for imputing missing values.

# This is the R chunk for the Tidy & Manipulate Data II 

# The structure of variables is checked and converted to ensure they are in numeric form 
str(justified_full)

tibble [240 × 6] (S3: tbl_df/tbl/data.frame)
 $ Country Name     : chr [1:240] NA "Afghanistan" "Albania" "Algeria" ...
 $ Male(%)          : num [1:240] NA 70.6 10.9 NA NA 24 NA NA NA 24.7 ...
 $ Female(%)        : num [1:240] NA 78.3 4.7 55 NA 24.7 NA NA 2.3 8.7 ...
 $ Region           : Factor w/ 7 levels "Latin America & Caribbean",..: NA 2 4 5 NA 3 NA NA 1 4 ...
 $ Income Group     : Ord.factor w/ 4 levels "Low income"<"Lower middle income"<..: NA 1 3 2 NA 2 NA NA 3 3 ...
 $ Happiness Ranking: int [1:240] NA 145 112 84 NA 142 NA NA 29 129 ...

justified_full$`Male(%)`<- as.numeric(justified_full$`Male(%)`)
justified_full$`Female(%)`<- as.numeric(justified_full$`Female(%)`)

# Create new variables using mutate()
justified_full<- justified_full %>% mutate('Adolescent Average'  = (`Male(%)` + `Female(%)`)/2,
                                           'Male-Female Difference' = `Male(%)` - `Female(%)`)
justified_full<- justified_full %>% mutate('Percentage Difference' = `Male-Female Difference`/`Adolescent Average`)

# Mean of Percentge Difference found
mean(justified_full$`Percentage Difference`, na.rm = TRUE) %>% round(3)

[1] -0.027

head(justified_full)

NA

Scan I

In order for this information to be useful analytically, only rows for countries that have values for either Male or Female respondents, as well as both Income Group and Happiness Ranking will be included.

The initial AWA data set containing the Male (%) and Female (%) variables, is missing data for many rows. This is because data was only collected for a limited number of countries as compared to the Happiness Ranking and Country Information sets. Therefore, values that are missing for either but not both of these variable will be removed. This is completed by identifying which values are missing for both Female (%) and Male (%) using is.na() nested within the which() function, and saving this is a vector.

Missing values from either the Male or Female respondents columns will be replaced with the value of the opposing gender multiplied by the percentage mean of the Male-Female difference. For simplicity, the mean value has been included in the rules as the corresponding numeric value for 2.7% increase or decrease.

Following this the rows that have missing values in the Happiness Ranking or Income Group columns will be omitted.

# This is the R chunk for the Scan I
#The following code identifies rows which are missing values for both male and female respondents and therefore need to be omitted.
missing_vals<- which(is.na(justified_full$`Female(%)` | justified_full$`Male(%)`))

# Save data frame without missing values
justified_narm<- justified_full[-missing_vals, ] # Sub-setting to remove missing values
head(justified_narm)

# Set rules for imputation
Rules<- validator(`Male(%)` == `Female(%)`*0.973,
                  `Female(%)` == `Male(%)`*1.027)
# Imputation
justified_imputed<- impute_lr(justified_narm, Rules)
head(justified_imputed)

#Remove NAs from Happiness Ranking and Income Group

#Find values that are not NA in Happiness Ranking and Subset for these
happiness_narm<- which(!is.na(justified_imputed$`Happiness Ranking`))# Save vector excluding missing values
justified_HR_narm<- justified_imputed[happiness_narm,]

#Find values that are not NA in Income Group and Subset for these 
missing_income<- which(!is.na(justified_HR_narm$`Income Group`))
justified_all_narm<- justified_HR_narm[missing_income,]

# Print out
head(justified_all_narm)

Scan II

All numeric data will be scanned for outliers. Univariate analysis is conducted of Happiness, Male (%) and Female (%) variables. This is done visually using a box plot. Outliers are also analyzed by z-score normalization using the outliers() package. As Happiness is a ranking variable there is no outliers, as expected. Univariate analysis of the Male(%) and Female (%) variables there are no observations that are considered outliers.

Bivariate analysis is completed using the Mahalanobis distance through the MVN() package. In the bivariate analysis of Male (%) and Female (%) values there is 23 outliers identified, however many of these lie close to the quantile line. Due to expected cultural differences between females and males within a population, these outliers are considered valuable anomalies and would be further investigated in the analysis stage.

Adolescent Average and Male-Female Difference values do not need to be scanned for outliers as they are combinations of original values.

# This is the R chunk for the Scan II

# Happiness Variable Univariate Analysis
justified_all_narm$`Happiness Ranking` %>% boxplot(main = "Happiness Ranking Distribution", ylab = "Happiness Ranking", col = "pink") # Create Boxplot

z.scores.happiness<- justified_all_narm$`Happiness Ranking` %>% scores(type = "z") # z.score normalization
z.scores.happiness %>% summary()

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
-2.4626 -0.6441  0.1824  0.0000  0.8161  1.4773 

#z.scores that are greater than 3 or less than -3 are considered outliers
which( abs(z.scores.happiness) >3 )

integer(0)

# Male(%) Variable
justified_all_narm$`Male(%)` %>% boxplot(main = "Male (%) Distribution", ylab = "Percentage Who Agree", col = "red")

z.scores.male<- justified_all_narm$`Male(%)` %>% scores(type = "z")
z.scores.male %>% summary()

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
-1.3493 -0.7915 -0.2337  0.0000  0.7249  2.5721 

#z.scores that are greater than 3 or less than -3 are considered outliers
which( abs(z.scores.male) >3 )

integer(0)

# Female(%) Variable
justified_all_narm$`Female(%)` %>% boxplot(main = "Female (%) Distribution", ylab = "Percentage Who Agree", col = "Orange")

z.scores.female<- justified_all_narm$`Female(%)` %>% scores(type = "z")
z.scores.female %>% summary()

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
-1.3406 -0.9729 -0.1151  0.0000  0.8244  2.0176 

#z.scores that are greater than 3 or less than -3 are considered outliers
which( abs(z.scores.female) >3 )

integer(0)

# Male (%) and Female (%) Bivariate Analysis
# Multivariate outlier detection using Mahalanobis distance with QQ plots
results.mvn<- mvn(data = justified_all_narm[, 2:3], multivariateOutlierMethod = "quan", showOutliers = TRUE)

Transform

The AWA variable, is right-skewed, as observed in the below histogram. To make this more useful for analysis a normal distribution is obtained by applying a square root transformation. The resulting distribution is observed to be close to normal. Prior to this transformation the ‘Adolescent Average’ column was adjusted to include values that had been imputed in the Scan: Missing Values section above.

# This is the R chunk for the Transform Section

# Create new adolescent average column to reflect replaced missing values
justified_withavg<- justified_all_narm[, c(1:6, 8)] %>% mutate('Adolescent Average' = (`Male(%)`+`Female(%)`)/2)

# Visual analysis of histogram for Adolescent Average
hist(justified_withavg$`Adolescent Average`, main = "Histogram of AWA Mean Value", xlab = "Percentage Agreement", col = "light blue")

# Apply a square root transformation to obtain a normal distribution
sqrt_agree<- sqrt(justified_withavg$`Adolescent Average`)

# Visual analysis of histogram for Adolescent Average after transformation
hist(sqrt_agree, main = "Histogram of AWA (Sqrt)", xlab = "Normalised Percentage Agreement", col = "light blue", breaks = 8)

# This Chunk writes the data frame as a .csv file for future use
write.csv(sqrt_agree, "Country_attitudes_information_normalised.csv")

References

“World Happiness Report”, Sustainable Development Solutions Network, 2018, accessed from https://www.kaggle.com/unsdsn/world-happiness

“Women who believe a husband is justified in beating his wife (any of the five reasons) %”, The World Bank Group, 2016, accessed from https://data.worldbank.org/indicator/SG.VAW.REAS.ZS?end=2018&name_desc=false&start=2018&view=bar

“Percentage of girls and boys 15–19 years old who consider a husband to be justified in hitting or beating his wife for at least one of the specified reasons, i.e., if his wife burns the food, argues with him, goes out without telling him, neglects the children or refuses sexual relations”, UNICEF, 2020, accessed from https://data.unicef.org/topic/gender/harmful-practices-and-intimate-partner-violence/