MATH2349 Semester 1, 2020

Assignment 2

Required packages

# This is the R chunk for the required packages
library(readr)
library(tidyr)
library(readxl)
library(stringr)
library(lubridate)
library(dplyr)
library(forecast)

Executive Summary

The World Bank Development Indicators are able to provide significant insight into the economics and demographics of countries around the world. However this data is split into multiple files, with inconsistent formatting and much missing data. For combining the dataset of Country Information with Population and Health data and then preparing it for analysis, the following steps were used.
* The datasets were joined using the unique country codes
* The data was tidied by creating a new observation for each year, and placing each variable measured each year into its own column
* Variables which were originally stored as a single string were split into their own variables
* Fields containing no data were formatted as NA
* Fields containing a range of multiple years were changed to only show the latest year in the range
* Other obviously incorrect data was manually corrected
* Columns containing no data at all were removed
* Columns containing data with ordinal data were formatted as ordered factors
* Years and other numeric data were converted from character to string to allow mathematical manipulation
* New variables were calculated to indicate the population change over 5 year periods, and the contribution of the net migration to this number
* The contribution of net migration to population change was used to create a new categorical variable
* Numerical data was re-scanned for outlier values, however the only outliers found were actually correct and significant
* To allow for linear regression using the population totals, a Box-Cox transformation was applied to it to ensure it was normally distributed

Data

A clear description of data sets, their sources, and variable descriptions should be provided. In this section, you must also provide the R codes with outputs (head of data sets) that you used to import/read/scrape the data set. You need to fulfil the minimum requirement #1 and merge at least two data sets to create the one you are going to work on. In addition to the R codes and outputs, you need to explain the steps that you have taken.

This investigation is combining two different datasets obtained from the World Bank Open Data repository.

The first dataset is a list of economic information about each country of the world, as well as aggregate information for certain groups of countries. The details of each variable are as below:

• Country Code Character This is a 3 character alphabetical unique identifier for the country or group
  
• Short Name Character This is an informal shortening of the full country name or group
  
• Table Name Character This is the name of the country or group used when referred to elsewhere in World Bank Data, and is typically similar to the Short Name
  
• Long Name Character This is the formal, full name of the country or group which has been chosen by the country itself
  
• 2-alpha code Character This is a 2 character code which can also function as a unique country identifier as per ISO 3166
  
• Currency Unit Character This indicates the unit of currency used by the country
  
• Special Notes Character This contains a variety of notes about the entry, such as the timings of its fiscal year and historical notes
  
• Region Character This indicates which broader region of the world the country or group is located in
  
• Income Group Character This indicates the rough level of income for the country, and is divided into four different levels. This is left blank for groups.
  
• WB-2 Code Character This is identical to the 2-alpha code except for instances where a 2-alpha code does not exist
• National Accounts Base Year Character This is the year used as the starting point for price calculations in the country’s national accounts if these calculations have never been rescaled for a later year.
  
• National Accounts Reference Year Character Is the year used for price calculations in the country’s national accounts if these calculations have been rescaled from a different year previously.
  
• SNA Price Valuation Character This is the method by which the prices of goods in the country is calculated
  
• Lending Category Character This indicates which types of loans the country is eligible for
  
• Other Groups Character This indicates which other significant groupings of countries the country may belong to
  
• System of National Accounts Character This indicates which system the country uses for its national financial reporting, typically referring to the system of a particular year
  
• Alternative Conversion Factor Character This indicates which period’s exchange rates are used when converting the country’s GDP into USD
  
• PPP Survey Year Untyped This would indicate the year in which the survey data regarding Purchasing Power Parity was obtained for the country, however this entire column is blank in the dataset
  
• Balance of Payments Manual in use Character This indicates which edition of the IMF’s Balance of Payments Manual is being used as the standard for compiling statisistics on the country
  
• External Debt Reporting Status Character This indicates whether the debt data for the country is what was actually reported by the country, if World Bank staff needed to estimate part of the data, or if the data is wholly estimated by the World Bank
  
• System of Trade Character This indicates what system of international trade is used by the country
  
• Government Accounting concept Character This indicates if the country’s finance data captures all of the central government’s fiscal activities (Consolidated), or if it is incomplete (Budgetary)
  
• IMF Data Dissemination Standard Character This indicates which of the IMF’s data dissemination standard’s the country meets
  
• Latest Population Census Character This contains the year of the latest population census for the country, as well as notes regarding the census
  
• Latest Household Survey Character This contains the year and name of the latest household survey for the country
  
• Source of Most Recent Income and Expenditure Data Character This contains the year and name of the source of the latest income and expenditure data for the country
  
• Vital Registration Complete Character This indicates whether or not the country has systems which record vital events regarding the population, and whether the registries of this are complete. This is yes if true, and blank otherwise
  
• Latest Agricultural Census Character This indicates the year of the latest agricultural census for the country, as well as the name of the census in some instances
  
• Latest Industrial Data Numeric This indicates the year from which the latest industrial data is available for the country
  
• Latest Trade Data Numeric This indicates the year from which the latest trade data is available for the country

The second dataset contains information regarding the population, net migration and Universal Health Coverage Index for all countries starting at the year 1960. The details of each variable are as below:

• Series Name Character This indicates the piece of data which is being measured, which for this dataset can be one of: UHC Service Coverage Index, Total Population or Net Migration
  
• Series Code Character This is a unique identifier for which piece of data is being measured, and functions as a unique key for the series within the broader World Bank databases
  
• Country Name Character This is the name of the country which the observations are from
  
• Country Code Character This is the 3 character unique identifier for each country/group, and these identifiers are identical to those from the first dataset
  
• Subsequent columns Numeric This is the value for the observation, with each column representing the year the observation was made. These are all numeric values, with blanks being filled with ‘..’

The two datasets are joined using a left join with the Country Code as a key. This allows for all information about each country to be shown for each observation.

# This is the R chunk for the Data Section
setwd("C:/Users/Alex D'Souza/Documents/Uni/RMIT/MATH2349/Assignment 2")
country<-read_csv("WDI_csv/WDICountry.csv")

Parsed with column specification:
cols(
  .default = col_character(),
  `PPP survey year` = col_logical(),
  `Latest industrial data` = col_double(),
  `Latest trade data` = col_double()
)
See spec(...) for full column specifications.

head(country)

migpop<-read_csv("Data_Extract_From_Health_Nutrition_and_Population_Statistics/World Net Migration Data.csv")

Parsed with column specification:
cols(
  .default = col_character()
)
See spec(...) for full column specifications.

head(migpop)

countmig<-migpop %>% left_join(country, by="Country Code")
head(countmig)

NA

Tidy & Manipulate Data I

According to tidy data principles, each instance of observation should have its own row in the dataframe. As such, the observations for each year should be in their own row. Additionally, the variables observed each year should all be shown in the same row, since they will be compared at the same point in time after tidying.

The tidying is being performed at this stage due to type manipulation and sanitisation needing to be performed on the new variables created from tidying the data set. Additionally, the Series Key is synonymous with the Series Name, and as such these two fields are being combined before being used as new column names when spreading the data to make it tidy.

# This is the R chunk for the Tidy & Manipulate Data I 
countmigt1<-countmig %>% gather(colnames(countmig)[5:64],key="Observation Year", value = "Observation Value")
countmigt1$`Observation Year` <-as.numeric(str_sub(countmigt1$`Observation Year`,1,4))
countmigt1<-countmigt1[!is.na(countmigt1$`Series Code`),]
countmigt1<-countmigt1 %>% unite(`Series`, `Series Name`, `Series Code`)
countmigtf<-spread(countmigt1,key="Series",value="Observation Value")
countmigtf<-countmigtf %>% separate(`Latest population census`, into=c("Latest Population Census Year", "Census Notes"), sep=". " , extra = "merge")

Expected 2 pieces. Missing pieces filled with `NA` in 11040 rows [1, 2, 3, 4, 6, 7, 9, 10, 11, 12, 14, 15, 17, 18, 19, 21, 22, 23, 24, 25, ...].

countmigtf<-countmigtf %>% separate(`Latest household survey`, into=c("Latest household survey name", "Latest household survey year"), sep = ", ")

Expected 2 pieces. Additional pieces discarded in 60 rows [235, 494, 753, 1012, 1271, 1530, 1789, 2048, 2307, 2566, 2825, 3084, 3343, 3602, 3861, 4120, 4379, 4638, 4897, 5156, ...].Expected 2 pieces. Missing pieces filled with `NA` in 60 rows [27, 286, 545, 804, 1063, 1322, 1581, 1840, 2099, 2358, 2617, 2876, 3135, 3394, 3653, 3912, 4171, 4430, 4689, 4948, ...].

countmigtf<-countmigtf %>% separate(`Source of most recent Income and expenditure data`, into=c("Source of most recent Income and expenditure data", "Year of most recent Income and expenditure data"), sep = ", ")
countmigtf<-countmigtf %>% separate(`Latest agricultural census`, into=c("Latest Agricultural Census Year", "Latest Agricultural Census Name"), sep = ". ", extra = "merge")

Expected 2 pieces. Missing pieces filled with `NA` in 7320 rows [2, 4, 7, 9, 10, 12, 13, 14, 16, 17, 20, 21, 25, 27, 28, 31, 32, 34, 35, 37, ...].

Scan I

Due to the use of ‘..’ to indicate missing values for certain fields, as well as many fields containing a year occasionally containing non-numeric data, the data must have missing values and inconsistencies removed prior to the appropriate data type conversions being made.

Since the purpose of using this data is to model the percentage change in population due to migration and how it correlates with the UHC index, and migration data is only gathered over 5 year periods, we do not require the population data from the years where migration data is not recorded. As such, any observations from years which do not have migration data recorded are discarded.

The column X31 which was created during data import is also removed during this step, as it contains no data. The PPP Survey Year column is also empty for all observations, and as such will also be removed.

The National Accounts Base Year column contains the text “Original chained constant price data are rescaled.” as a place-holder if the year is not available. As such this text will be replaced by NA values to allow the year (when present) to be treated as a number.

Numerous columns contain two consecutive years to indicate a year long period spanning two calendar years. These years are separated by a ‘/’, or occasionally another character such as “-”. These values will be replaced by the latest of the two years, as this would necessarily be the year in which the data was completed and presumably released. The second year value in these instances is often stored as the last two digits of the year, and as such the full year must be inferred from the context of the data. Since all data is from the years 1960-2020, we can safely assume that if the last two digits are from 0-20, the first two are 20, and otherwise the first two are 19.

For the Channel Islands (code CHI), the data format for the Census Year, and Vital Registration Completion is different, and as such this will need to be manually corrected.

# This is the R chunk for the Scan I
countmigtf$`Population, total_SP.POP.TOTL`[countmigtf$`Population, total_SP.POP.TOTL`==".."]<-NA
countmigtf$`UHC service coverage index_SH.UHC.SRVS.CV.XD`[countmigtf$`UHC service coverage index_SH.UHC.SRVS.CV.XD`==".."]<-NA
countmigtf$`Net migration_SM.POP.NETM`[countmigtf$`Net migration_SM.POP.NETM`==".."]<-NA
countmigtf<-subset(countmigtf[!is.na(countmigtf$`Net migration_SM.POP.NETM`),], select=-c(`PPP survey year`))
countmigtf$`National accounts base year`[countmigtf$`National accounts base year`=="Original chained constant price data are rescaled."]<-NA
deslash<-function(yearstr,delim = "/"){
  if (is.na(yearstr)){
    return (NA)
  }else{
  delimvec<-c(1:length(delim))
  matched<-FALSE
  for (i in delimvec){
  if(str_detect(yearstr,delim[i])) {
    slashloc<-str_locate(yearstr,delim[i])[1]
    matched<-TRUE
    returnstr<-as.numeric(str_sub(yearstr,slashloc+1,str_length(yearstr)))
    if (returnstr <100){
      if (returnstr <21){
        returnstr = returnstr+2000
      }else{
        returnstr = returnstr+1900
      }
    }
    
    return(returnstr)
  } else {
  }
    }
if (matched){
    
  } else {
    return (yearstr)
  }
  }
}
countmigtf$`National accounts base year`<-as.numeric(lapply(countmigtf$`National accounts base year`,deslash,c("/","-")))
countmigtf$`National accounts reference year`<-as.numeric(lapply(countmigtf$`National accounts reference year`,deslash,c("/","-")))
countmigtf$`Year of most recent Income and expenditure data`<-as.numeric(lapply(countmigtf$`Year of most recent Income and expenditure data`,deslash,c("/","-")))
countmigtf$`Latest household survey year`<-as.numeric(lapply(countmigtf$`Latest household survey year`,deslash,c("/","-")))

NAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercion

countmigtf$`Latest Agricultural Census Year`<-as.numeric(lapply(countmigtf$`Latest Agricultural Census Year`,deslash,c("/","-")))
countmigtf$`Latest Population Census Year`<-as.numeric(lapply(countmigtf$`Latest Population Census Year`,deslash,c("/","-")))

NAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercionNAs introduced by coercion

countmigtf$`Latest Population Census Year`[countmigtf$`Country Code`=="CHI"] <- 2015
countmigtf$`Vital registration complete`[countmigtf$`Country Code`=="CHI"] <- "Yes"

  

Understand

Summarise the types of variables and data structures, check the attributes in the data and apply proper data type conversions. In addition to the R codes and outputs, explain briefly the steps that you have taken. In this section, show that you have fulfilled minimum requirements 2-4.

Due to originally being stored as a string, the columns which store a year value will need to be coerced to a numeric type. additionally, some variables have a clear order as per the data inspection section, and as such can be coerced to a factor with levels.

# This is the R chunk for the Understand Section
str(countmigtf)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   2808 obs. of  38 variables:
 $ Country Name                                     : chr  "Afghanistan" "Albania" "Algeria" "Angola" ...
 $ Country Code                                     : chr  "AFG" "ALB" "DZA" "AGO" ...
 $ Short Name                                       : chr  "Afghanistan" "Albania" "Algeria" "Angola" ...
 $ Table Name                                       : chr  "Afghanistan" "Albania" "Algeria" "Angola" ...
 $ Long Name                                        : chr  "Islamic State of Afghanistan" "Republic of Albania" "People's Democratic Republic of Algeria" "People's Republic of Angola" ...
 $ 2-alpha code                                     : chr  "AF" "AL" "DZ" "AO" ...
 $ Currency Unit                                    : chr  "Afghan afghani" "Albanian lek" "Algerian dinar" "Angolan kwanza" ...
 $ Special Notes                                    : chr  NA NA NA NA ...
 $ Region                                           : chr  "South Asia" "Europe & Central Asia" "Middle East & North Africa" "Sub-Saharan Africa" ...
 $ Income Group                                     : chr  "Low income" "Upper middle income" "Upper middle income" "Lower middle income" ...
 $ WB-2 code                                        : chr  "AF" "AL" "DZ" "AO" ...
 $ National accounts base year                      : num  2003 NA NA 2002 2006 ...
 $ National accounts reference year                 : num  NA 2010 1999 NA NA ...
 $ SNA price valuation                              : chr  "Value added at basic prices (VAB)" "Value added at basic prices (VAB)" "Value added at basic prices (VAB)" "Value added at basic prices (VAB)" ...
 $ Lending category                                 : chr  "IDA" "IBRD" "IBRD" "IBRD" ...
 $ Other groups                                     : chr  "HIPC" NA NA NA ...
 $ System of National Accounts                      : chr  "Country uses the 1993 System of National Accounts methodology" "Country uses the 2008 System of National Accounts methodology" "Country uses the 1993 System of National Accounts methodology" "Country uses the 1993 System of National Accounts methodology" ...
 $ Alternative conversion factor                    : chr  NA NA NA "1991–96" ...
 $ Balance of Payments Manual in use                : chr  "BPM6" "BPM6" "BPM6" "BPM6" ...
 $ External debt Reporting status                   : chr  "Actual" "Actual" "Actual" "Actual" ...
 $ System of trade                                  : chr  "General trade system" "Special trade system" "Special trade system" "Special trade system" ...
 $ Government Accounting concept                    : chr  "Consolidated central government" "Consolidated central government" "Consolidated central government" "Budgetary central government" ...
 $ IMF data dissemination standard                  : chr  "Enhanced General Data Dissemination System (e-GDDS)" "Enhanced General Data Dissemination System (e-GDDS)" "Enhanced General Data Dissemination System (e-GDDS)" "Enhanced General Data Dissemination System (e-GDDS)" ...
 $ Latest Population Census Year                    : num  1979 2011 2008 2014 2011 ...
 $ Census Notes                                     : chr  NA NA NA NA ...
 $ Latest household survey name                     : chr  "Demographic and Health Survey" "Demographic and Health Survey" "Multiple Indicator Cluster Survey" "Demographic and Health Survey" ...
 $ Latest household survey year                     : num  2015 2018 2019 2016 NA ...
 $ Source of most recent Income and expenditure data: chr  "Integrated household survey (IHS)" "Living Standards Measurement Study Survey (LSMS)" "Integrated household survey (IHS)" "Integrated household survey (IHS)" ...
 $ Year of most recent Income and expenditure data  : num  2017 2012 2011 2009 NA ...
 $ Vital registration complete                      : chr  NA "Yes" NA NA ...
 $ Latest Agricultural Census Year                  : num  NA 2012 NA NA 2007 ...
 $ Latest Agricultural Census Name                  : chr  NA NA NA NA ...
 $ Latest industrial data                           : num  NA 2013 2010 NA NA ...
 $ Latest trade data                                : num  2017 2017 2017 2017 2017 ...
 $ Observation Year                                 : num  1962 1962 1962 1962 1962 ...
 $ Net migration_SM.POP.NETM                        : chr  "-20000" "-99" "-282941" "-296717" ...
 $ Population, total_SP.POP.TOTL                    : chr  "9351441" "1711319" "11619828" "5608539" ...
 $ UHC service coverage index_SH.UHC.SRVS.CV.XD     : chr  NA NA NA NA ...

countmigtf$`External debt Reporting status`<-factor(countmigtf$`External debt Reporting status`, levels=addNA(c(NA,"Estimate","Preliminary","Actual")), exclude=NULL)
countmigtf$`Income Group`<-factor(countmigtf$`Income Group`,levels=c(NA,"Low income", "Lower middle income","Upper middle income","High income"), exclude=NULL)
countmigtf$`Vital registration complete`[is.na(countmigtf$`Vital registration complete`)]<-"No"
countmigtf$`Vital registration complete`<-factor(countmigtf$`Vital registration complete`, levels=c("Yes","No"), exclude=NULL)
countmigtf$`System of National Accounts`<-  as.numeric(str_sub(countmigtf$`System of National Accounts`,18,21))
countmigtf$`Net migration_SM.POP.NETM`<-as.numeric(countmigtf$`Net migration_SM.POP.NETM`)
countmigtf$`Population, total_SP.POP.TOTL`<-as.numeric(countmigtf$`Population, total_SP.POP.TOTL`)
countmigtf$`UHC service coverage index_SH.UHC.SRVS.CV.XD`<-as.numeric(countmigtf$`UHC service coverage index_SH.UHC.SRVS.CV.XD`)
str(countmigtf)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   2808 obs. of  38 variables:
 $ Country Name                                     : chr  "Afghanistan" "Albania" "Algeria" "Angola" ...
 $ Country Code                                     : chr  "AFG" "ALB" "DZA" "AGO" ...
 $ Short Name                                       : chr  "Afghanistan" "Albania" "Algeria" "Angola" ...
 $ Table Name                                       : chr  "Afghanistan" "Albania" "Algeria" "Angola" ...
 $ Long Name                                        : chr  "Islamic State of Afghanistan" "Republic of Albania" "People's Democratic Republic of Algeria" "People's Republic of Angola" ...
 $ 2-alpha code                                     : chr  "AF" "AL" "DZ" "AO" ...
 $ Currency Unit                                    : chr  "Afghan afghani" "Albanian lek" "Algerian dinar" "Angolan kwanza" ...
 $ Special Notes                                    : chr  NA NA NA NA ...
 $ Region                                           : chr  "South Asia" "Europe & Central Asia" "Middle East & North Africa" "Sub-Saharan Africa" ...
 $ Income Group                                     : Factor w/ 5 levels NA,"Low income",..: 2 4 4 3 5 1 4 4 5 5 ...
 $ WB-2 code                                        : chr  "AF" "AL" "DZ" "AO" ...
 $ National accounts base year                      : num  2003 NA NA 2002 2006 ...
 $ National accounts reference year                 : num  NA 2010 1999 NA NA ...
 $ SNA price valuation                              : chr  "Value added at basic prices (VAB)" "Value added at basic prices (VAB)" "Value added at basic prices (VAB)" "Value added at basic prices (VAB)" ...
 $ Lending category                                 : chr  "IDA" "IBRD" "IBRD" "IBRD" ...
 $ Other groups                                     : chr  "HIPC" NA NA NA ...
 $ System of National Accounts                      : num  1993 2008 1993 1993 2008 ...
 $ Alternative conversion factor                    : chr  NA NA NA "1991–96" ...
 $ Balance of Payments Manual in use                : chr  "BPM6" "BPM6" "BPM6" "BPM6" ...
 $ External debt Reporting status                   : Factor w/ 4 levels NA,"Estimate",..: 4 4 4 4 1 1 4 4 1 1 ...
 $ System of trade                                  : chr  "General trade system" "Special trade system" "Special trade system" "Special trade system" ...
 $ Government Accounting concept                    : chr  "Consolidated central government" "Consolidated central government" "Consolidated central government" "Budgetary central government" ...
 $ IMF data dissemination standard                  : chr  "Enhanced General Data Dissemination System (e-GDDS)" "Enhanced General Data Dissemination System (e-GDDS)" "Enhanced General Data Dissemination System (e-GDDS)" "Enhanced General Data Dissemination System (e-GDDS)" ...
 $ Latest Population Census Year                    : num  1979 2011 2008 2014 2011 ...
 $ Census Notes                                     : chr  NA NA NA NA ...
 $ Latest household survey name                     : chr  "Demographic and Health Survey" "Demographic and Health Survey" "Multiple Indicator Cluster Survey" "Demographic and Health Survey" ...
 $ Latest household survey year                     : num  2015 2018 2019 2016 NA ...
 $ Source of most recent Income and expenditure data: chr  "Integrated household survey (IHS)" "Living Standards Measurement Study Survey (LSMS)" "Integrated household survey (IHS)" "Integrated household survey (IHS)" ...
 $ Year of most recent Income and expenditure data  : num  2017 2012 2011 2009 NA ...
 $ Vital registration complete                      : Factor w/ 2 levels "Yes","No": 2 1 2 2 1 2 1 1 1 1 ...
 $ Latest Agricultural Census Year                  : num  NA 2012 NA NA 2007 ...
 $ Latest Agricultural Census Name                  : chr  NA NA NA NA ...
 $ Latest industrial data                           : num  NA 2013 2010 NA NA ...
 $ Latest trade data                                : num  2017 2017 2017 2017 2017 ...
 $ Observation Year                                 : num  1962 1962 1962 1962 1962 ...
 $ Net migration_SM.POP.NETM                        : num  -20000 -99 -282941 -296717 -1880 ...
 $ Population, total_SP.POP.TOTL                    : num  9351441 1711319 11619828 5608539 55841 ...
 $ UHC service coverage index_SH.UHC.SRVS.CV.XD     : num  NA NA NA NA NA NA NA NA NA NA ...

Tidy & Manipulate Data II

For the purposes of having a unique identifier for each observation, a key will be created by combining the country code with the year of the observation. This will be useful for future indexing and using this data in a Database Management Schema.

The 5-year migration numbers for each country are currently absolute values. This number could be more useful if represented as either a proportion of the total population of the country, or as a proportion of the total change in population over that period.

As such, these proportions will be calculated for each entry in the dataset.

For instances where the data from 5 years prior is unavailable, this field will be left blank (NA).

It will also be useful to create a categorical variable to indicate whether migration comprises the entirety or just a portion of the net population change, and in which direction the population is changing.

# This is the R chunk for the Tidy & Manipulate Data II 
countmigtf$Key<-paste0(countmigtf$`Country Code`,countmigtf$`Observation Year`)

#Verify the keys are unique
sum(duplicated(countmigtf$Key))==0

[1] TRUE

countmig5<-subset(countmigtf,select=c(`Country Code`, `Observation Year`, `Population, total_SP.POP.TOTL`))
countmig5$`Observation Year`<-countmig5$`Observation Year`+5
countmig5<- countmig5 %>% unite(Key, `Country Code`, `Observation Year`,sep="")
countmigtf<-left_join(countmigtf, countmig5, by="Key", suffix=c(""," 5YA"))
countmigtf$`5 Year Population Change`<-countmigtf$`Population, total_SP.POP.TOTL`-countmigtf$`Population, total_SP.POP.TOTL 5YA`
countmigtf$`Migration Proportion of Total Population`<-countmigtf$`Net migration_SM.POP.NETM`/countmigtf$`Population, total_SP.POP.TOTL`
countmigtf$`Migration Proportion of Population Change`<-countmigtf$`Net migration_SM.POP.NETM`/countmigtf$`5 Year Population Change`
migpopstatus<-c("Insufficient Data", "Decrease Exclusively Due to Migration", "Decrease Partially Due to Migration", "Increase Exclusively Due to Migration", "Increase Partially Due to Migration", "Decrease Despite Positive Migration", "Increase Despite Negative Migration", "No Net Migration")
#Function to categorise the impact of migration upon the change in population
migcat<-function(migprop, changeprop){
  if (is.na(migprop)|is.na(changeprop)){
    #If insufficient data is available about migration or population
    return (1)
  } else {
    #If there is no net migration
    if (changeprop==0) {
      return (8)
    }else {
    #If the net migration is in the same direction as the population change
      if (changeprop>0){
        #If the change in population is exclusively due to migration
        if (changeprop>=1){
          #If the net migration is positive
          if (migprop >0) {
           return (4)
          #If the net migration is negative  
          } else {
          return (2)  
          }
        #If the change in population is partially due to migration  
        } else {
          #If the net migration is positive
          if (migprop >0) {
          return (5)
          #If the net migration is negative      
          } else {
          return (3)  
          }
        }
      #If the net migration is in the opposite direction to the population change
      } else {
        #If the net migration is negative
          if (migprop<0){
            return (7)
        #If the net migration is positive    
          } else{
            return (6)
          }
      }
    }
  }
}
countmigtf$`Change in Population Due to Migration`<-factor(mapply(migcat, countmigtf$`Migration Proportion of Total Population`, countmigtf$`Migration Proportion of Population Change`), ordered=TRUE, labels=migpopstatus)

Scan II

For all variables which store a year, this value should generally be between 1960 and 2020, since this is the time period which the data covers. As such, our outlier checks here will be looking for any year value which is outside of this range.

The UHC service coverage index is by definition a value between 0 and 100. As such, it will be scanned for values lying outside of this range.

The only other numeric data in this data set is the population and migration data. One of the common reasons for removing or modifying outliers is sampling error, with the outliers being unrepresentative of the population due to the low number of observations. However due to the dataset containing the entire population (every country), every single data point would be representative of the population, and as such we cannot remove or modify any observations due to sampling error.

Instead, the scanning for outliers will focus upon those which were due to data entry, or data processing errors; and this will be done by searching for logically impossible values. For the population totals it will only be checked that these are above 0 and for net migration it will be checked that the absolute value is not greater than the population total.

Since the 5 year ago population and the 5 year population change are derived from the population data which is already being scanned, if there are no outliers in the population totals, then this data will be free of outliers too.

The calculated proportions from Tidy & Manipulate Data II will be checked for both impossible, and non-useful values. The migration proportion of the total population must be in the range from -1 to 1, since otherwise would indicate a greater proportion than the total country’s population leaving or entering the country.

However, the migration proportion of the population change is useful regardless of what value it takes, and due to being calculated from variables which are already being scanned, there will be no outliers here beyond those identified in other variables.

# This is the R chunk for the Scan II
yearvarlist<-c("National accounts base year", "National accounts reference year", "System of National Accounts", "Latest Population Census Year", "Latest household survey year", "Year of most recent Income and expenditure data", "Latest Agricultural Census Year", "Latest industrial data", "Latest trade data", "Observation Year")
outliervarlist<-append(yearvarlist,c("Population, total_SP.POP.TOTL","Net migration_SM.POP.NETM","UHC service coverage index_SH.UHC.SRVS.CV.XD", "Migration Proportion of Total Population"))
outliercount<-rep(0,14)
outliervals<-rep(NA,14)
varlength<-c(1:length(yearvarlist))


for (i in varlength){
  thisoutlier<-as.data.frame(countmigtf[yearvarlist[i]])[(countmigtf[yearvarlist[i]]<1960|countmigtf[yearvarlist[i]]>2020)& !is.na(countmigtf[yearvarlist[i]])]
  outliercount[i]<-length(thisoutlier)
  if (outliercount[i] >0){
  outliervals[i]<-paste(unique(thisoutlier))}
}
popoutlier<-as.data.frame(countmigtf["Population, total_SP.POP.TOTL"])[!countmigtf["Population, total_SP.POP.TOTL"]>0 & !is.na(countmigtf["Population, total_SP.POP.TOTL"])]
outliercount[11]<-length(popoutlier)
if (outliercount[11] >0){
  outliervals[12]<-paste(unique(popoutlier))}

netmigoutlier<-as.data.frame(countmigtf["Net migration_SM.POP.NETM"])[(abs(countmigtf["Net migration_SM.POP.NETM"])>countmigtf["Population, total_SP.POP.TOTL"])& !is.na(countmigtf["Net migration_SM.POP.NETM"]) & !is.na(countmigtf["Population, total_SP.POP.TOTL"])]
outliercount[12]<-length(netmigoutlier)
if (outliercount[12] >0){
  outliervals[12]<-paste(unique(netmigoutlier))}

UHCoutlier<-as.data.frame(countmigtf["UHC service coverage index_SH.UHC.SRVS.CV.XD"])[(countmigtf["UHC service coverage index_SH.UHC.SRVS.CV.XD"]>100|countmigtf["UHC service coverage index_SH.UHC.SRVS.CV.XD"]<0) & !is.na(countmigtf["UHC service coverage index_SH.UHC.SRVS.CV.XD"])]
outliercount[13]<-length(UHCoutlier)
if (outliercount[13] >0){
  outliervals[13]<-paste(unique(UHCoutlier))}

migtotoutlier<-as.data.frame(countmigtf["Migration Proportion of Total Population"])[(countmigtf["Migration Proportion of Total Population"]>1|countmigtf["Migration Proportion of Total Population"]<(-1)) & !is.na(countmigtf["Migration Proportion of Total Population"])]
outliercount[14]<-length(migtotoutlier)
if (outliercount[14] >0){
  outliervals[14]<-paste(unique(migtotoutlier))}

yearoutliersummary<-data.frame(outliercount, outliervals)
rownames(yearoutliersummary)<-outliervarlist
colnames(yearoutliersummary)<-c("Number of Outliers", "Outlier Value(s)")
as.table(as.matrix(yearoutliersummary))

                                                Number of Outliers Outlier Value(s)
National accounts base year                     12                 1954            
National accounts reference year                 0                                 
System of National Accounts                      0                                 
Latest Population Census Year                   12                 1943            
Latest household survey year                     0                                 
Year of most recent Income and expenditure data  0                                 
Latest Agricultural Census Year                  0                                 
Latest industrial data                           0                                 
Latest trade data                                0                                 
Observation Year                                 0                                 
Population, total_SP.POP.TOTL                    0                                 
Net migration_SM.POP.NETM                        0                                 
UHC service coverage index_SH.UHC.SRVS.CV.XD     0                                 
Migration Proportion of Total Population         0                                 

As can be seen from this summary of outliers, there are only outliers present in the national accounts base year, and the latest population census year. These values of 1954 and 1943 however, still appear to be reasonable years for data to have been obtained from for the World Bank.

For the national accounts base year, it is quite possible for the accounts to be calculated using values from as far back as 1954.

Similarly, the latest population census being in 1943 is also possible for a country, especially one which may be undergoing some conflict.

As such, these outliers will be left in the data, as they still appear to be accurate and useful.

Transform

One might want to perform a linear regression to investigate if there is a linear relationship between the population of a country and the UHC service coverage index. However a key requirement for linear regression is that the data is normally distributed. As such, it will need to be checked whether these two variables are normally distributed; and if they are not, then an appropriate transformation will need to be applied.

Their normality can be checked by a visual inspection of a histogram of the data, and it’s quickly clear that the Population data is heavily right skewed. The UHC index however does appear to be roughly normally distributed, and as such will require no transformation.

Fortunately, the Box-Cox transformation can be applied to the data here to ensure it is normally distributed to allow for linear regression. The lambda value for this transformation will be automatically calculated through the use of the forecast package.

hist(countmigtf$`Population, total_SP.POP.TOTL`, main="Histogram of Total Population", xlab="Population")
hist(countmigtf$`UHC service coverage index_SH.UHC.SRVS.CV.XD`, main="Histogram of UHC Service Coverage Index", xlab="UHC Service Coverage Index")

countmigtf$`Box-Cox Population`<-BoxCox(countmigtf$`Population, total_SP.POP.TOTL`, lambda="auto")
hist(countmigtf$`Box-Cox Population`, main="Histogram of Box-Cox Normalised Population", xlab = "Box-Cox Normalised Population")

NOTE: Note that sometimes the order of the tasks may be different than the order given here. For example, you may need to tidy the data sets first to be able to create the common key to merge. Therefore, for such cases you may have a different ordering of the sections.

Any further or optional pre-processing tasks can be added to the template using an additional section in the R Markdown file. Make sure your code is visible (within the margin of the page). Do not use View() to show your data, instead give headers (using head() )

References

RStudio 2020, R Markdown Reference Guide, RStudio, viewed 27 March 2020, <link>
Dolgun, A 2020, Module 4, RMIT University, viewed 6 June 2020, <link>
Dolgun, A 2020, Module 5, RMIT University, viewed 6 June 2020, <link>
Dolgun, A 2020, Module 6, RMIT University, viewed 6 June 2020, <link>
Dolgun, A 2020, Module 7, RMIT University, viewed 6 June 2020, <link>
Dolgun, A 2020, Module 8, RMIT University, viewed 6 June 2020, <link>
World Bank Group 2020, Health Nutrition and Population Statistics, World Bank Group, viewed 4 June 2020, <link>
World Bank Group 2020, World Development Indicators, World Bank Group, viewed 30 May 2020, <link>
Baglin, J 2019, Module 9, RMIT University, viewed 6 June 2020, <link>
IBAN 2020, List of country codes, IBAN, viewed 7 June 2020, <link>
World Bank Group 2020, National Accounts Base Year, World Bank Data Catalog, viewed 6 June 2020, <link>
OECD 2020, Market Prices - SNA, Glossary of Statistical Terms, viewed 7 June 2020, <link>
World Bank Group 2020, How Does the World Bank Classify Countries, World Bank Data Helpdesk, viewed 7 June 2020, <link>
World Bank Group 2020, What is the DEC conversion factor?, World Bank Data Helpdesk, viewed 7 June 2020, <link>
World Bank Group 2020, Balance of Payments Manual In Use, World Bank Data Catalog, viewed 6 June 2020, <link>
World Bank Group 2020, External Debt Reporting Status, World Bank Data Catalog, viewed 6 June 2020, <link>
World Bank Group 2020, Government Finance Accounting, World Bank Data Catalog, viewed 6 June 2020, <link>
World Bank Group 2020, Special Data Dissemination Standard, World Bank Data Catalog, viewed 6 June 2020, <link>
World Bank Group 2020, Vital Registration System Coverage, World Bank Data Catalog, viewed 6 June 2020, <link>
Scholer, F 2020, Database Concepts: The Relational Model 1, ISYS1055 Week 2 Learning Materials/Activities, viewed 6 June 2020, <link>