Master R 8 - Polishing data

The types and origins of missing data

Missing Completely at Random (MCAR) means that every value in the dataset has the same probability of being missed, 

so no systematic error or distortion is to be expected due to missing data, and nor can we explain the pattern of missing values.

Missing at Random (MAR) is known or at least can be identified, although it has nothing to do with the actual missing values. 

For Missing Not at Random (MNAR), where data is missing for a specific reason that is highly related to the actual question, 

which classifies missing values as nonignorable non-response.

Identifying missing data

library(hflights)

table(complete.cases(hflights))

prop.table(table(complete.cases(hflights))) * 100

sort(sapply(hflights, function(x) sum(is.na(x))))

By-passing missing values

mean(cor(apply(hflights, 2, function(x) as.numeric(is.na(x)))), na.rm = TRUE)

Funs <- Filter(is.function, sapply(ls(baseenv()), get, baseenv()))

names(Filter(function(x) any(names(formals(args(x))) %in% 'na.rm'), Funs))

names(Filter(function(x) any(names(formals(args(x))) %in% 'na.rm'), Filter(is.function,sapply(ls('package:stats'), get, 'package:stats'))))

Overriding the default arguments of a function

myMean <- function(...) mean(..., na.rm = TRUE)

mean(c(1:5, NA))

myMean(c(1:5, NA))

library(rapportools)

mean(c(1:5, NA))

detach('package:rapportools')

mean(c(1:5, NA))

library(Defaults)

setDefaults(mean.default, na.rm = TRUE)

mean(c(1:5, NA))

setDefaults(mean, na.rm = TRUE)

mean

Getting rid of missing data

na.omit(c(1:5, NA))

na.exclude(c(1:5, NA))

x <- rnorm(10); y <- rnorm(10)

x[1] <- NA; y[2] <- NA

exclude <- lm(y ~ x, na.action = "na.exclude")

omit <- lm(y ~ x, na.action = "na.omit")

residuals(exclude)

m <- matrix(1:9, 3)

m[which(m %% 4 == 0, arr.ind = TRUE)] <- NA

m

na.omit(m)

Filtering missing data before or during the actual analysis

mean(hflights$ActualElapsedTime)

mean(hflights$ActualElapsedTime, na.rm = TRUE)

mean(na.omit(hflights$ActualElapsedTime))

library(microbenchmark)

NA.RM   <- function() mean(hflights$ActualElapsedTime, na.rm = TRUE)

NA.OMIT <- function() mean(na.omit(hflights$ActualElapsedTime))

microbenchmark(NA.RM(), NA.OMIT())

Data imputation

m[which(is.na(m), arr.ind = TRUE)] <- 0

m

ActualElapsedTime <- hflights$ActualElapsedTime

mean(ActualElapsedTime, na.rm = TRUE)

ActualElapsedTime[which(is.na(ActualElapsedTime))] <- mean(ActualElapsedTime, na.rm = TRUE)

mean(ActualElapsedTime)

library(Hmisc)

mean(impute(hflights$ActualElapsedTime, mean))

sd(hflights$ActualElapsedTime, na.rm = TRUE)

sd(ActualElapsedTime)

summary(iris)

library(missForest)

set.seed(81)

miris <- prodNA(iris, noNA = 0.2)

summary(miris)

iiris <- missForest(miris, xtrue = iris, verbose = TRUE)

str(iiris)

Comparing different imputation methods

miris <- miris[, 1:4]

iris_mean <- impute(miris, fun = mean)

iris_forest <- missForest(miris)

diag(cor(iris[, -5], iris_mean))

diag(cor(iris[, -5], iris_forest$ximp))

Not imputing missing values

Multiple imputation

Extreme values and outliers

detach('package:missForest')

detach('package:randomForest')

library(outliers)

outlier(hflights$DepDelay)

summary(hflights$DepDelay)

library(lattice)

bwplot(hflights$DepDelay)

IQR(hflights$DepDelay, na.rm = TRUE)

Testing extreme values

set.seed(83)

dixon.test(c(runif(10), pi))

model <- lm(hflights$DepDelay ~ 1)

model$coefficients

mean(hflights$DepDelay, na.rm = TRUE)

a <- 0.1

(n <- length(hflights$DepDelay))

(F <- qf(1 - (a/n), 1, n-2, lower.tail = TRUE))

(L <- ((n - 1) * F / (n - 2 + F))^0.5)

sum(abs(rstandard(model)) > L)

Using robust method

summary(lm(Sepal.Length ~ Petal.Length, data = miris))

lm(Sepal.Length ~ Petal.Length, data = iris)$coefficients

library(MASS)

summary(rlm(Sepal.Length ~ Petal.Length, data = miris))

f <- formula(Sepal.Length ~ Petal.Length)

cbind(orig =  lm(f, data = iris)$coefficients, lm   =  lm(f, data = miris)$coefficients, rlm  = rlm(f, data = miris)$coefficients)

miris$Sepal.Length[1] <- 14

cbind(orig = lm(f, data = iris)$coefficients, lm   = lm(f, data = miris)$coefficients, rlm  = rlm(f, data = miris)$coefficients)

How SAS handles missing values

It is important to understand how SAS procedures handle missing data if you have missing data. To know how a procedure handles missing data, you should consult the SAS manual. Here is a brief overview of how some common SAS procedures handle missing data. 

• proc means
  For each variable, the number of non-missing values are used
• proc freq
  By default, missing values are excluded and percentages are based on the number of non-missing values. If you use the missing option on the tables statement, the percentages are based on the total number of observations (non-missing and missing) and the percentage of missing values are reported in the table.
• proc corr
  By default, correlations are computed based on the number of pairs with non-missing data (pairwise deletion of missing data). The nomiss option can be used on the proc corr statement to request that correlations be computed only for observations that have non-missing data for all variables on the var statement (listwise deletion of missing data).
• proc reg
  If any of the variables on the model or var statement are missing, they are excluded from the analysis (i.e., listwise deletion of missing data)
• proc factor
  Missing values are deleted listwise, i.e., observations with missing values on any of the variables in the analysis are omitted from the analysis.
• proc glm
  The handling of missing values in proc glm can be complex to explain. If you have an analysis with just one variable on the left side of the model statement (just one outcome or dependent variable), observations are eliminated if any of the variables on the model statement are missing. Likewise, if you are performing a repeated measures ANOVA or a MANOVA, then observations are eliminated if any of the variables in the model statement are missing. For other situations, see the SAS/STAT manual about proc glm.

*Get missing values freqencies of numeric variables:
proc freq data=raw;
table variables /missing;
run;

*Get missing values levels of categorical variables:
data class;
if 0 then set sashelp.class;
do i=1 to 10;output;
end;
stop;
run;

proc format;
value allmiss ._-.z=. other=1;
value $allmiss ' '=' ' other='1';
run;

ods select nlevels;
ods output nlevels=nlevels(keep=TableVar NNonMissLevels where=(NNonMissLevels=0));
proc freq levels;
format _character_ $allmiss. _numeric_ allmiss.;
run;
ods output close; 

%missingPattern to Studying Missing Data Patterns

The macro is designed to look at missing data in four ways: the proportion of subjects with each pattern of missing data, the number and percentage of missing data for each individual variable, the concordance of missingness in any pair of variables, and possible unit nonresponse.

The SAS macro is %missingPattern:

%missingPattern(datain=, varlist=, exclude=, missPattern1=, dataout1=, missPattern2=, dataout2=, missPattern3=, dataout3=, missPattern4=, dataout4=)

Reference: T. Schwartz, Q. Chen, and N. Duan, (2011). Studying missing data patterns using a SAS macro, SAS Global Forum 2011 proceedings.

Example:
data tmp;
set out1.training_motorcycle1;
if vn0451^=. then vn0451_log=log(1+vn0451);
else vn0451_log=.;
if vn0467^=. then vn0467_log=log(1+vn0467);
else vn0467_log=.;
if vn0479^=. then vn0479_log=log(1+vn0479);
else vn0479_log=.;
if vn0712^=. then vn0712_log=log(1+vn0712);
else vn0712_log=.;
if vn0717^=. then vn0717_log=log(1+vn0717);
else vn0717_log=.;
if vn0722^=. then vn0722_log=log(1+vn0722);
else vn0722_log=.;
run;

%let varlist=vn0451_log vn0467_log vn0479_log vn0712_log vn0717_log vn0722_log;

%missingPattern(datain=tmp, varlist=&varlist, exclude='FAULT', missPattern1='TRUE',
dataout1=result1,missPattern2='TRUE', dataout2=result2,
missPattern3='TRUE', dataout3=result3, missPattern4='TRUE', dataout4=result4);

ods html path = "/u/lhuang/" (url = none)
body = "temp.html"
style = Default;
Ods listing close;

proc print data=result1;
proc print data=result2;
proc print data=result3;
*proc print data=result4;
run;

* DOWNLOAD HTML FILES;
proc download infile="/u/lhuang/temp.html"
outfile="&LocalLocation/missing pattern.html";
run;