R 101

Sometimes when you open a data file (lets say a .csv), variables will be recognized as factor whereas it should be numeric. It is therefore tempting to simply convert the variable to numeric using as.numeric(). Big mistake! If you use directly as.numeric() on a variable of the type factor, R will returns the levels of the factor rather the actual values. To overcome this hassle, you should first convert the variable into a string with as.character() and thereafter use as.numeric().

x <- as.factor(rnorm(5))
x
## [1] -0.19597467763285  -0.465926436902432 -1.16127674187728 
## [4] 0.703965602685846  -0.19106558386255 
## 5 Levels: -1.16127674187728 -0.465926436902432 ... 0.703965602685846
## Wrong way
as.numeric(x)
## [1] 3 2 1 5 4
## Right way
as.numeric(as.character(x))
## [1] -0.1959747 -0.4659264 -1.1612767  0.7039656 -0.1910656

Another way to prevent numeric to be converted into factor would be to use as.is = T when opening the data file with read.csv.

Remove all objects in the workspace**

rm(list = ls())

Replacing values of a data frame with NA

library(xtable)

## Random matrix with with some (50) zeros
df <- matrix(rnorm(100), ncol = 10)

df[sample(100, 50)] <- 0

df <- data.frame(df)

print(xtable(df), type = "html")
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
1 0.82 0.00 0.43 0.67 0.00 -0.01 0.80 -0.52 0.00 0.00
2 0.00 -1.50 0.00 0.34 0.00 -0.69 0.00 1.19 0.00 0.00
3 -0.84 0.02 0.00 0.00 -0.89 -0.04 0.00 0.00 0.49 0.00
4 -1.27 -1.37 0.00 0.00 0.00 -1.46 0.00 0.00 -1.24 0.00
5 0.07 -1.00 -1.52 0.28 0.00 0.81 0.00 0.59 0.00 0.00
6 0.00 -0.64 2.09 0.00 -1.34 0.00 0.00 0.16 1.36 -1.02
7 0.00 0.26 -1.08 0.00 0.76 0.00 1.29 -0.87 2.78 0.70
8 0.00 0.00 -1.09 -1.51 0.00 0.89 -0.52 -0.15 1.69 0.00
9 -0.49 0.00 -1.48 0.00 0.00 0.00 0.00 0.00 1.31 0.00
10 -0.16 0.00 -1.09 0.00 0.00 -0.39 0.00 0.00 0.00 0.00
## Replace the 0 with NA
df[df == 0] <- NA

print(xtable(df), type = "html", NA.string = "NA")
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
1 0.82 NA 0.43 0.67 NA -0.01 0.80 -0.52 NA NA
2 NA -1.50 NA 0.34 NA -0.69 NA 1.19 NA NA
3 -0.84 0.02 NA NA -0.89 -0.04 NA NA 0.49 NA
4 -1.27 -1.37 NA NA NA -1.46 NA NA -1.24 NA
5 0.07 -1.00 -1.52 0.28 NA 0.81 NA 0.59 NA NA
6 NA -0.64 2.09 NA -1.34 NA NA 0.16 1.36 -1.02
7 NA 0.26 -1.08 NA 0.76 NA 1.29 -0.87 2.78 0.70
8 NA NA -1.09 -1.51 NA 0.89 -0.52 -0.15 1.69 NA
9 -0.49 NA -1.48 NA NA NA NA NA 1.31 NA
10 -0.16 NA -1.09 NA NA -0.39 NA NA NA NA

##Count % of missing values per column

The other day I was working with a large database. I was looking for a way to calculate % of missing values for each variable. Here is how I calculated it.

## Generate some data
df <- matrix(rnorm(100), ncol = 10)

df[sample(100, 50)] <- NA

df <- data.frame(df)

print(xtable(df), type = "html", NA.string = "NA")
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
1 NA NA 1.37 -1.30 -0.20 NA NA -1.97 -0.64 0.27
2 NA NA 0.79 1.42 -1.00 NA 0.61 0.38 NA 0.81
3 -1.27 NA -0.96 0.37 -0.42 NA 0.12 NA NA 0.04
4 NA NA NA -0.71 NA NA -0.84 NA NA NA
5 0.02 NA NA 0.59 NA 1.70 -0.68 -0.12 0.45 -0.37
6 NA 0.14 -2.16 NA NA 0.13 NA NA NA NA
7 NA NA NA NA -1.03 -0.89 NA NA NA 1.69
8 0.38 -0.59 -1.26 0.20 NA -0.80 NA NA NA -0.34
9 -0.20 NA NA -0.43 -0.35 NA NA 1.10 0.21 NA
10 NA NA 0.41 1.13 1.29 NA NA -0.14 -1.39 0.38
## Find % of missing values in each column with apply
missing <- apply(df, 2, function(x) sum(is.na(x)))/nrow(df)*100

## Print the result
df2 <- data.frame(variable = names(df), missing = missing)

print(xtable(df2), type = "html", include.rownames = FALSE)
variable missing
X1 60.00
X2 80.00
X3 40.00
X4 20.00
X5 40.00
X6 60.00
X7 60.00
X8 50.00
X9 60.00
X10 30.00

Grouping data frame

Tsshere is various way to merge data frame in R. For me, the most easiest way is to use the join functions from the dplyr package.

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
df1 <- data.frame(ID = sample(LETTERS[1:20],10, replace = FALSE), x = rnorm(10))
df2 <- data.frame(ID = sample(LETTERS[1:20],10, replace = FALSE), y = rnorm(10))

print(xtable(df1), type = "html", include.rownames = FALSE)
ID x
Q -0.52
R 0.88
L 1.50
S -0.73
E 0.51
A -1.61
I -2.26
H -1.02
D 0.38
P 0.34
print(xtable(df2), type = "html", include.rownames = FALSE)
ID y
J 1.00
C 1.78
H -1.49
S 0.53
D 0.35
N -0.59
M -0.89
T -1.62
Q 0.08
F -1.48
inner_join(df1, df2, by = "ID")
## Warning: Column `ID` joining factors with different levels, coercing to
## character vector

ID x y 1 Q -0.5178393 0.07719421 2 S -0.7317296 0.52583016 3 H -1.0240026 -1.49112843 4 D 0.3838611 0.34942884

df_exact_match <- merge(df1,df2, by = "ID") ## Exact match
print(xtable(df_exact_match), type = "html", include.rownames = FALSE, NA.string = "NA")
ID x y
D 0.38 0.35
H -1.02 -1.49
Q -0.52 0.08
S -0.73 0.53
df_all_x <- merge(df1,df2, by = "ID", all.x = T) ## Keep all x (discard y that are not matched)
print(xtable(df_all_x), type = "html", include.rownames = FALSE, NA.string = "NA")
ID x y
A -1.61 NA
D 0.38 0.35
E 0.51 NA
H -1.02 -1.49
I -2.26 NA
L 1.50 NA
P 0.34 NA
Q -0.52 0.08
R 0.88 NA
S -0.73 0.53
df_all_y <- merge(df1,df2, by = "ID", all.y = T) ## Keep all y (discard x that are not matched)
print(xtable(df_all_y), type = "html", include.rownames = FALSE, NA.string = "NA")
ID x y
D 0.38 0.35
H -1.02 -1.49
Q -0.52 0.08
S -0.73 0.53
C NA 1.78
F NA -1.48
J NA 1.00
M NA -0.89
N NA -0.59
T NA -1.62
df_all <- merge(df1,df2, by = "ID", all = T) ## Keep everything
print(xtable(df_all), type = "html", include.rownames = FALSE, NA.string = "NA")
ID x y
A -1.61 NA
D 0.38 0.35
E 0.51 NA
H -1.02 -1.49
I -2.26 NA
L 1.50 NA
P 0.34 NA
Q -0.52 0.08
R 0.88 NA
S -0.73 0.53
C NA 1.78
F NA -1.48
J NA 1.00
M NA -0.89
N NA -0.59
T NA -1.62