Dnaiel Dnaiel - 4 months ago 26
R Question

expand large data frame in R efficiently

I am looking for a solution to expand a large data frame in R into more columns and more rows, given the values of a given column.

Right now I am doing this using a for-loop approach but I am sure there are more crantastic/efficient ways to achieve the same results...

The example will make the question more clear I think. Let us imagine we have a data frame containing student's information about their grades at three different stages in life. The student IDs are s1, s2 and s3; and we have measurements of their grades in three different times in their life, m1, m2, and m3; and then at each stage we have a column called more.info with their grades in their courses, encoded as class#topic#grade across all classes taken.

library(stringr)
options(stringsAsFactors=FALSE)
example.df = data.frame(measure.id = c("m1", "m2", "m3", "m2", "m2", "m3", "m1", "m1", "m3"),
student.id = c("s1", "s1", "s1", "s2", "s3", "s3", "s2", "s3", "s2"),
more.info = c("draw#drawing#4.5;music#singing#5.6;dance#ballet#6.7", "bio#biology#5.6;math#algebra#4.5", "calculus#univariate#6.2; physics#quantum#4.5;chemistry#organic#4.5",
"bio#biology#5.6;math#algebra#4.5", "bio#biology#3.6;math#algebra#3.5", "calculus#univariate#5.2; physics#quantum#5.2;chemistry#organic#4", "draw#drawing#5;music#singing#5.6;dance#ballet#5.7",
"draw#drawing#2.5;music#singing#3.6;dance#ballet#4", "calculus#univariate#5.2; physics#quantum#6.5;chemistry#organic#5"))
measure.ids = unique(example.df$measure.id)


Then, I'd like to create a new data frame that splits the more.info information and creates a new data frame with more rows and more columns as follows,

new.df=data.frame()
splitit <- function(x){
strsplit(x, '#')
}
for(i in 1:length(measure.ids)){
measure.id = measure.ids[i]
tmp = example.df[example.df==measure.id,]
more.info = tmp$more.info
more.info = strsplit(more.info,";")
student.ids = tmp$student.id
for(j in 1:length(more.info))
{
info = more.info[[j]]
a = sapply(info, splitit)
b = sapply(a, "[[", 1)
d = sapply(a, "[[", 2)
e = sapply(a, "[[", 3)
new.df = rbind(new.df,
data.frame(measure.id = rep(measure.id, length(info)),
student.id = rep(tmp$student.id[j], length(info)),
class = b,
topic = d,
grade = e)
)
}
}


What'd be the most efficient way to achieve this in R? I am open to apply functions, map/reduce approaches, mclapply for using more cores, etc...

Answer

Solution with base functions:

# split column by all available separators 
a <- strsplit(example.df$more.info, "; |#|;")
# represent each result as a matrix with 3 columns
a <- lapply(a, function(v) matrix(v, ncol=3, byrow=TRUE))
# combine all matrixes in one big matrix
aa <- do.call(rbind, a)
# create indices of rows of initial data.frame which corresponds to the created big matrix
b <- unlist(sapply(seq_along(a), function(i) rep(i, nrow(a[[i]]))))
# combine initial data.frame and created big matrix
df <- cbind(example.df[b,], aa)
# remove unnecessary columns and rename remaining ones
df <- df[,-3]
colnames(df)[3:5] <- c("class", "topic", "grade")

To increase the speed you may replace all functions of apply family in my code with mclapply.

I cannot compare the speed since your dataset is very small.