as.character() is your friend

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] 1.641794358679     0.963356182913528  -0.752806105114263
## [4] 0.278437879536854  -0.25115986471113 
## 5 Levels: -0.752806105114263 -0.25115986471113 ... 1.641794358679


## Wrong way
as.numeric(x)

## [1] 5 4 1 3 2


## Right way
as.numeric(as.character(x))

## [1]  1.6418  0.9634 -0.7528  0.2784 -0.2512

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())

Replace 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")

## Replace the 0 with NA
df[df == 0] = NA

print(xtable(df), type = "html", NA.string = "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")

## 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)

Grouping data frame

There is various way to merge data frame in R. Here I present some variant of the simplest way using merge

df1 = data.frame(ID = sample(LETTERS[1:20], 10, replace = F), x = rnorm(10))
df2 = data.frame(ID = sample(LETTERS[1:20], 10, replace = F), y = rnorm(10))

print(xtable(df1), type = "html", include.rownames = FALSE)

print(xtable(df2), type = "html", include.rownames = FALSE)

df_exact_match = merge(df1, df2, by = "ID")  ## Exact match
print(xtable(df_exact_match), type = "html", include.rownames = FALSE, NA.string = "NA")

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")

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")

df_all = merge(df1, df2, by = "ID", all = T)  ## Keep everything
print(xtable(df_all), type = "html", include.rownames = FALSE, NA.string = "NA")