MATH2349 Semester 2, 2018

Assignment 3

Required packages

library(readr)
library(tidyr)
library(dplyr)
library(Hmisc)
library(outliers)
library(lubridate)
library(stringr)
library(deductive)
library(validate)
library(forecast)

Executive Summary

Main objective of this report is to apply the functions that we have learnt throughout Data Preprocessing course to a real world dataset in order to fulfill Assignment 3 requirements. A dataset was collected from world bank dataset which contained 5 .csv files, but two were enough for our investigation. Dataset contained character, numeric and factor variables which is a major requirement for the assignment. Both the selected datasets had to be cleaned priior to joining. Renaming, Gather and Spread functions were used to tidy up the datasets which were joined later. Joined dataset was amalyzed using the structure of the variables and necessary transformations were performed such as character -> numeric, character -> factor, charactor -> ordered factor in order to maintain a relaiable dataset to perform any statistical analysis. Dataset was a tidy dataset after all and created a new variable using mutate function. All the NAs in all the columns were identified and performed imputation for required variables by getting the mean value of the variable with the group by function. Possible outliers were detected using z-scores function for all the numeric variables and imputation was performed by replacing the mean value. Finally, log transformation was performed to make the distibution of the selected variable to be normal which was acutually right skewed when inpecting histogram.

Data

Poverty Dataset was extracted from World Bank website which contains different countries across globe.

Data Source: https://datacatalog.worldbank.org/dataset/poverty-and-equity-database

There were 5 .csv files but we Selected two of the datasets which we thought are more important perform data preprocessing.

1. PovStatsCountry.csv contains 30 columns including Country_code, SHORT_NAME, ALPHA_CODE, Currency_Unit,Region, Income_Group. Dimensional Data with country code, short name of the country, cureency unit of the country, Region of the country in the world, Income group suggests whether it is Low, Medium or High income group can be understood from those columns. Data types of major columns in the dataset can be identified as follow.

Country_code -> Character SHORT_NAME -> Character ALPHA_CODE -> Character Currency_Unit -> Character Region -> Character (Supposed to be a factor) Income_Group -> Character (Supposed to be an ordered factor)

2. PovStatsData.csv contains 48 columns including Country_code, Indicator_code, and year column from 1974 - 2018. Measure or Fact data with country code, country name, Indicator code as measure type and 1974 to 2017 measures can be identified from these columns.

country code -> Character country name -> Character Indicator code -> Character Year column from 1974 - 2018 -> Numeric

#Import Data from PovStatsCountry.csv
POV_COUNTRY <- read_csv("PovStatsCountry.csv")

Missing column names filled in: 'X31' [31]Parsed with column specification:
cols(
  .default = col_character(),
  `National accounts reference year` = col_integer(),
  `Latest industrial data` = col_integer(),
  `Latest trade data` = col_integer()
)
See spec(...) for full column specifications.

head(POV_COUNTRY)

#Selection of required variables from the dataset and Renaming variables
POV_COUNTRY_RENAME <- POV_COUNTRY %>% select(Country_code = "Country Code", SHORT_NAME = "Short Name" ,ALPHA_CODE = "2-alpha code" , Currency_Unit = "Currency Unit",Region = "Region" , Income_Group = "Income Group" )
head(POV_COUNTRY_RENAME)

#Import Data from PovStatsData.csv
POV_STATS_DATA <- read_csv("PovStatsData.csv")

Missing column names filled in: 'X49' [49]Parsed with column specification:
cols(
  .default = col_double(),
  `Country Name` = col_character(),
  `Country Code` = col_character(),
  `Indicator Name` = col_character(),
  `Indicator Code` = col_character(),
  X49 = col_character()
)
See spec(...) for full column specifications.

head(POV_STATS_DATA)

#Selection of required variables from the dataset
POV_STATS_SELECT <- POV_STATS_DATA %>% select (2, 4, 41:44)
head(POV_STATS_SELECT)

#Renaming of some of the selected variables
POV_STATS_RENAME <- POV_STATS_SELECT %>% rename(Country_code = 'Country Code', Indicator_code = 'Indicator Code' )
head(POV_STATS_RENAME)

#Filteration to select only observations based on the Income.
POV_STATS_FILTER = POV_STATS_RENAME %>% filter(Indicator_code %in% c("SI.POV.NOP1" , "SI.POV.LMIC.NO", "SI.POV.UMIC.NO" , "SP.POP.TOTL" ))
head(POV_STATS_FILTER)

#Implemented data tidy techniques like Gather, Spread to tidy up data
POV_STATS_GATHER <- POV_STATS_FILTER %>% gather ('2010','2011','2012','2013', key = year, value = 'value' )
head(POV_STATS_GATHER)

POV_STATS_SPREAD <- POV_STATS_GATHER %>% spread(key = Indicator_code, value = 'value') 
head(POV_STATS_SPREAD)

#Renaming of some of the selected variables
POV_SPREAD_RENAME <- POV_STATS_SPREAD %>% rename(POOR_199c_DAY=SI.POV.NOP1,POOR_320c_DAY = SI.POV.LMIC.NO, POOR_550c_DAY=SI.POV.UMIC.NO, POP_TOTL = SP.POP.TOTL )
head(POV_SPREAD_RENAME)

#Joining of both the data sets using Country code to create final data set.
POV_COUNTRY_STATS_JOIN <- POV_COUNTRY_RENAME %>% inner_join (POV_SPREAD_RENAME, by = 'Country_code')
head(POV_COUNTRY_STATS_JOIN)

• Both PovStatsCountry.csv and PovStatsData.csv datasets were imported and only selected columns which are required to perform an analysis.

• Some of the selected columns from both the datasets were renamed for better identification.

• Filteration was performed to select values of ‘Indicator Code’ to be included as columns.

• Gather, Spread functions were used to tidy up the untidy data in both the datasets.

• Renaming of some of the columns was performed after tyding up daatasets.

• Finally both the datasets were joined using inner join.

Understand

#Description of the joined data set
str(POV_COUNTRY_STATS_JOIN)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   736 obs. of  11 variables:
 $ Country_code : chr  "AFG" "AFG" "AFG" "AFG" ...
 $ SHORT_NAME   : chr  "Afghanistan" "Afghanistan" "Afghanistan" "Afghanistan" ...
 $ ALPHA_CODE   : chr  "AF" "AF" "AF" "AF" ...
 $ Currency_Unit: chr  "Afghan afghani" "Afghan afghani" "Afghan afghani" "Afghan afghani" ...
 $ Region       : chr  "South Asia" "South Asia" "South Asia" "South Asia" ...
 $ Income_Group : chr  "Low income" "Low income" "Low income" "Low income" ...
 $ year         : chr  "2010" "2011" "2012" "2013" ...
 $ POOR_320c_DAY: num  NA NA NA NA NA NA NA NA NA NA ...
 $ POOR_199c_DAY: num  NA NA NA NA NA NA NA NA NA NA ...
 $ POOR_550c_DAY: num  NA NA NA NA NA NA NA NA NA NA ...
 $ POP_TOTL     : num  28803167 29708599 30696958 31731688 23369131 ...

#Converting year to numeric
POV_COUNTRY_STATS_JOIN$year = as.numeric(POV_COUNTRY_STATS_JOIN$year)
#Converting Region, Income Group to factor
POV_COUNTRY_STATS_JOIN$Region = as.factor(POV_COUNTRY_STATS_JOIN$Region)
POV_COUNTRY_STATS_JOIN$Income_Group = as.factor(POV_COUNTRY_STATS_JOIN$Income_Group)
#Converting Income Group factor variable to ordered factor variable
POV_COUNTRY_STATS_JOIN$Income_Group<- factor(POV_COUNTRY_STATS_JOIN$Income_Group,levels=c("High income","Upper middle income","Lower middle income","Low income"),labels=c("High income","Upper middle income","Lower middle income","Low income"),ordered=TRUE)
# The final structure of the dataset
str(POV_COUNTRY_STATS_JOIN) 

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   736 obs. of  11 variables:
 $ Country_code : chr  "AFG" "AFG" "AFG" "AFG" ...
 $ SHORT_NAME   : chr  "Afghanistan" "Afghanistan" "Afghanistan" "Afghanistan" ...
 $ ALPHA_CODE   : chr  "AF" "AF" "AF" "AF" ...
 $ Currency_Unit: chr  "Afghan afghani" "Afghan afghani" "Afghan afghani" "Afghan afghani" ...
 $ Region       : Factor w/ 7 levels "East Asia & Pacific",..: 6 6 6 6 7 7 7 7 2 2 ...
 $ Income_Group : Ord.factor w/ 4 levels "High income"<..: 4 4 4 4 3 3 3 3 2 2 ...
 $ year         : num  2010 2011 2012 2013 2010 ...
 $ POOR_320c_DAY: num  NA NA NA NA NA NA NA NA NA NA ...
 $ POOR_199c_DAY: num  NA NA NA NA NA NA NA NA NA NA ...
 $ POOR_550c_DAY: num  NA NA NA NA NA NA NA NA NA NA ...
 $ POP_TOTL     : num  28803167 29708599 30696958 31731688 23369131 ...

• Structure of the joined dataset was reviewed.

• Year column was converted to a numeric column which was a character column before.

• Region column and Income_Group columns were converted to factor variables which were in character format.

• At the end, Income_Group factor variable was convereted to an ordered factor.

Tidy & Manipulate Data I

This part have been done in The first section of the assignment (Data). Therefore, the POV_COUNTRY_STATS_JOIN is in tidy format because of the following reasons:

1)Every variable in its own column.

2)Every observation in its own row.

3)Every value have its own cell for easy understanding and further analysis.

Tidy & Manipulate Data II

#Creation of a new column to represent country wise population earning more than 550 cents per day.
POV_STATS_MUTATE = POV_COUNTRY_STATS_JOIN %>% mutate( POP_ABOVE_550c_DAY = POP_TOTL - (POOR_550c_DAY)*1000000)
head(POV_STATS_MUTATE) 

• POOR_550c_DAY represents cumulative sum of population who are earning less than are equal to 550 cents (i.e. This variable included who are earning 199c per day and 320 cents per day) in millions.

• We calculated a new variable to identify who are earning more than 550 cents per day by subracting POOR_550c_DAY multiplied by a million from total population.

• Used mutate to include new variable “POP_ABOVE_550c_DAY”"

Scan I

#Checking for  total values existing in each column in the above data set
colSums(is.na(POV_STATS_MUTATE))

      Country_code         SHORT_NAME         ALPHA_CODE      Currency_Unit             Region 
                 0                  0                  4                 60                 60 
      Income_Group               year      POOR_320c_DAY      POOR_199c_DAY      POOR_550c_DAY 
                60                  0                372                372                372 
          POP_TOTL POP_ABOVE_550c_DAY 
                 2                372 

#Checking for  inconsistencies in numeric columns
sum(is.nan(POV_STATS_MUTATE$year))

[1] 0

sum(is.nan(POV_STATS_MUTATE$POOR_199c_DAY))

[1] 0

sum(is.nan(POV_STATS_MUTATE$POOR_320c_DAY))

[1] 0

sum(is.nan(POV_STATS_MUTATE$POOR_550c_DAY))

[1] 0

sum(is.nan(POV_STATS_MUTATE$POP_TOTL))

[1] 0

#Convertion of NA values with mean of the measure group by year and Income  to get the most appropriate mean value of similar contries.
POV_STATS_IMPUTE <- POV_STATS_MUTATE %>% group_by(Income_Group, year) %>% mutate(POOR_199c_DAY =  ifelse(is.na(POOR_199c_DAY) ,mean(POOR_199c_DAY,na.rm = TRUE), POOR_199c_DAY ), POOR_320c_DAY =  ifelse(is.na(POOR_320c_DAY) ,mean(POOR_320c_DAY,na.rm = TRUE), POOR_320c_DAY ), POOR_550c_DAY =  ifelse(is.na(POOR_550c_DAY) ,mean(POOR_550c_DAY,na.rm = TRUE), POOR_550c_DAY ), POP_ABOVE_550c_DAY =  ifelse(is.na(POP_ABOVE_550c_DAY) ,mean(POP_ABOVE_550c_DAY,na.rm = TRUE), POP_ABOVE_550c_DAY )) 
# Convertion of NA values with mean of the measure group by year Country_code to get the most appropriate mean value of same country
POV_STATS_IMPUTE_FINAL <- POV_STATS_IMPUTE %>% group_by(Country_code) %>% mutate(POP_TOTL =  ifelse(is.na(POP_TOTL) ,mean(POP_TOTL,na.rm = TRUE), POP_TOTL ))
head(POV_STATS_IMPUTE_FINAL)

#Checking the NAs after Imputation
colSums(is.na(POV_STATS_IMPUTE_FINAL))

      Country_code         SHORT_NAME         ALPHA_CODE      Currency_Unit             Region 
                 0                  0                  4                 60                 60 
      Income_Group               year      POOR_320c_DAY      POOR_199c_DAY      POOR_550c_DAY 
                60                  0                  0                  0                  0 
          POP_TOTL POP_ABOVE_550c_DAY 
                 0                  0 

• Scanned data set for NA, inconsistencies and errors using is.na, is.nan functions.

• Imputed na values with mean of variable group by year, Income Group for POOR_199c_DAY, POOR_320c_DAY, POOR_550c_DAY, POP_ABOVE_550c_DAY variables. Because it is better to get values according to the year and Income Group rather than Region which does not make any sense and not adequate.

• Imputed na values with mean of variable group by Country_code for POP_TOTL variable. Because imputing values with other variables is not going to be accurate.

• Neglected imputation of other column which are not having a statistical impact according to the scenario.

Scan II

#Finding the locations of outliers in the POOR_320c_DAY variable
POOR_320c_DAY_outliers <- POV_STATS_IMPUTE_FINAL$POOR_320c_DAY%>% scores(type = "z")
which( abs(POOR_320c_DAY_outliers) >3 )

 [1] 381 382 383 384 385 386 387 388 557 558 559 709 710 711 712

length (which( abs(POOR_320c_DAY_outliers) >3 ))

[1] 15

#Finding the locations of outliers in the POOR_199c_DAY variable
POOR_199c_DAY_outliers <- POV_STATS_IMPUTE_FINAL$POOR_199c_DAY%>% scores(type = "z")
which( abs(POOR_199c_DAY_outliers) >3 )

 [1] 281 282 283 284 381 382 383 384 385 386 387 388 557 589 590 591 592 709 710 711 712

length (which( abs(POOR_199c_DAY_outliers) >3 ))

[1] 21

#Finding the locations of outliers in the POOR_550c_DAY variable
POOR_550c_DAY_outliers <- POV_STATS_IMPUTE_FINAL$POOR_550c_DAY%>% scores(type = "z")
which( abs(POOR_550c_DAY_outliers) >3 )

 [1] 381 382 383 384 385 386 387 388 709 710 711 712

length (which( abs(POOR_550c_DAY_outliers) >3 ))

[1] 12

#Finding the locations of outliers in the POP_TOTL variable
POP_TOTL_outliers <- POV_STATS_IMPUTE_FINAL$POP_TOTL%>% scores(type = "z")
which( abs(POP_TOTL_outliers) >3 )

 [1] 384 385 386 387 388 429 430 431 432 709 710 711 712

length (which( abs(POP_TOTL_outliers) >3 ))

[1] 13

#Finding the locations of outliers in the POP_ABOVE_550c_DAY  variable
POP_ABOVE_550c_DAY_outliers <- POV_STATS_IMPUTE_FINAL$POP_ABOVE_550c_DAY%>% scores(type = "z")
which( abs(POP_ABOVE_550c_DAY_outliers) >3 )

 [1] 170 171 172 385 386 387 388 681 682 683 684 709 710 711 712

length (which( abs(POP_ABOVE_550c_DAY_outliers) >3 ))

[1] 15

#Imputing Outliers of numeric variables with mean assuming that outliers are a result of data entry/processing error.
POV_STATS_IMPUTE_FINAL$POOR_320c_DAY [which(abs(POOR_320c_DAY_outliers) >3 )] <- mean(POV_STATS_IMPUTE_FINAL$POOR_320c_DAY, na.rm = TRUE)
POOR_320c_DAY_outliers_imputed <- POV_STATS_IMPUTE_FINAL$POOR_320c_DAY%>% scores(type = "z")
length (which( abs(POOR_320c_DAY_outliers_imputed) >3 ))

[1] 23

#Imputing Outliers of numeric variables with mean assuming that outliers are a result of data entry/processing error.
POV_STATS_IMPUTE_FINAL$POOR_199c_DAY [which(abs(POOR_199c_DAY_outliers) >3 )] <- mean(POV_STATS_IMPUTE_FINAL$POOR_199c_DAY, na.rm = TRUE)
POOR_199c_DAY_outliers_imputed <- POV_STATS_IMPUTE_FINAL$POOR_199c_DAY%>% scores(type = "z")
length (which( abs(POOR_199c_DAY_outliers_imputed) >3 ))

[1] 24

#Imputing Outliers of numeric variables with mean assuming that outliers are a result of data entry/processing error.
POV_STATS_IMPUTE_FINAL$POOR_550c_DAY [which(abs(POOR_550c_DAY_outliers) >3 )] <- mean(POV_STATS_IMPUTE_FINAL$POOR_550c_DAY, na.rm = TRUE)
POOR_550c_DAY_outliers_imputed <- POV_STATS_IMPUTE_FINAL$POOR_550c_DAY%>% scores(type = "z")
length (which( abs(POOR_550c_DAY_outliers_imputed) >3 ))

[1] 26

#Imputing Outliers of numeric variables with mean assuming that outliers are a result of data entry/processing error.
POV_STATS_IMPUTE_FINAL$POP_TOTL [which(abs(POP_TOTL_outliers) >3 )] <- mean(POV_STATS_IMPUTE_FINAL$POP_TOTL, na.rm = TRUE)
POP_TOTL_outliers_imputed <- POV_STATS_IMPUTE_FINAL$POP_TOTL%>% scores(type = "z")
length (which( abs(POP_TOTL_outliers_imputed) >3 ))

[1] 26

#Imputing Outliers of numeric variables with mean assuming that outliers are a result of data entry/processing error.
POV_STATS_IMPUTE_FINAL$POP_ABOVE_550c_DAY [which(abs(POP_ABOVE_550c_DAY_outliers) >3 )] <- mean(POV_STATS_IMPUTE_FINAL$POP_ABOVE_550c_DAY, na.rm = TRUE)
POP_ABOVE_550c_DAY_outliers_imputed <- POV_STATS_IMPUTE_FINAL$POP_ABOVE_550c_DAY %>% scores(type = "z")
length (which( abs(POP_ABOVE_550c_DAY_outliers_imputed) >3 ))

[1] 20

• Found the outliers of all the numeric variables using z-scores method except ‘year’ column which is not requrired to be considered for searching outliers.

• There were some ouliers which might had been due to data entering.

• imputation was used to remove the outliers of all the numeric vatiables by respective mean values.

• However, for some reason outlier count got increased after the imputation. It might be due to the fact that data entry error might contain far larger values than existing values.

Transform

#Displaying histogram of 'total population' coulumn
hist(POV_STATS_IMPUTE_FINAL$POP_TOTL, main = "Histogram of POOR_320c_DAY")

#Application of log transformation for 'total population' coulumn
log_total_population <- log10(POV_STATS_IMPUTE_FINAL$POP_TOTL)
hist(log_total_population)

• Histogram of the selected variable ‘POP_TOTL’ was displayed to view the distribution.

• It was more of a right skewed distribution and log transformation was used to remove the right skewness since log trnasformation is ideal for removing right skewness.