How can I restructure and collapse a data set?

Question

I have a dataset that looks something like this:

0 Var1 Var 2 Var3 Var4 1 2|1 2 1|2 3 1|2 4 2|1 5 2|1 6 2|1 7 1|2 8 1|2 

I would like to turn left so that it looks something like this:

 0 GF GF1 1 2|1 Var1 2 1|2 Var1 3 1|2 Var2 4 2|1 Var2 5 2|1 Var3 6 2|1 Var3 7 1|2 Var4 8 1|2 Var4 

I have a general idea related to the reshape package, but have not yet found an elegant solution.

Answer 1

Try the following:

 library(reshape) foo = data.frame(Var1=c("2|1","1|2","","",""),Var2=c("","","","","1|2")) foo # Var1 Var2 # 1 2|1 # 2 1|2 # 3 # 4 # 5 1|2 foo = cbind("Row"=seq(nrow(foo)),foo) foo = melt(foo, "Row") setNames(foo[foo$value!="", c(3,2)], c("GF","GF1")) # GF GF1 # 1 2|1 Var1 # 2 1|2 Var1 # 10 1|2 Var2 

Answer 2

Holding the base, you get where you are going:

 #create data set foo <- data.frame(Var1=c("2|1","1|2","","",""),Var2=c("","","","","1|2"), Var3=c("", "", "1|2","2|1","") ,Var4=c("","","", "1|2", "")) #Split data frame columns into lists, counts lengths of each and put together v <- lapply(seq_along(foo), function(i) foo[, i][foo[, i] != ""]) data.frame(GF = unlist(v), GF1 = rep(colnames(foo), sapply(v, length))) 

or similarly:

 lens <- sapply(foo, function(x) sum(x != "")) x <- as.matrix(foo) data.frame(GF = x[x!= ""], GF1 = rep(colnames(foo), lens))