[R] Create rows for columns in dataframe

[arun]

Hi,
This seemed to be faster than the other two methods:
vec1<- as.character(rep(dat1[,1],each=(ncol(dat1)-1)))
vec2<- as.character(unlist(t(dat1[,-1])))
vec3<- rep(rep(c(TRUE,FALSE),c(1,(ncol(dat1)-2))),nrow(dat1))
dat2<-data.frame(DSYSRTKY=vec1,CODE=vec2,PRIMAIRY=vec3,stringsAsFactors=FALSE)
dat3<- dat2[dat2[,2]!="",]
row.names(dat3)<- 1:nrow(dat3)
 dat3New<-within(dat3,{ID<-row.names(dat3)})[,c(4,1:3)]

#Out1## Output dataset
Out1$PRIMAIRY<- as.logical(Out1$PRIMAIRY)

identical(Out1,dat3New)
#[1] TRUE

#Speed test 

indx<-rep(1:nrow(dat1),6e4)
dat2<- dat1[indx,]

system.time({
vec1<- as.character(rep(dat2[,1],each=(ncol(dat2)-1)))
vec2<- as.character(unlist(t(dat2[,-1])))
vec3<- rep(rep(c(TRUE,FALSE),c(1,(ncol(dat2)-2))),nrow(dat2))
dat4<-data.frame(DSYSRTKY=vec1,CODE=vec2,PRIMAIRY=vec3,stringsAsFactors=FALSE)
dat5<- dat4[dat4[,2]!="",]
row.names(dat5)<- 1:nrow(dat5)
 dat5New<-within(dat5,{ID<-row.names(dat5)})[,c(4,1:3)]
})
# user  system elapsed 
# 12.620   0.684  13.333 
dim(dat5New)
#[1] 2880000       4

A.K.




Hi Arun, 

The second method is indeed working much faster. It worked fast for my 600.000 row record. 
Still I have 2 bigger files where processing becomes an issue even 
though I have lots of memory (32 gig) for the second statement: 
res2<-reshape(dat2,idvar="newCol",varying=list(2:26),direction="long") 

Would data.table also take less memory? Maybe even speed things up would be good. How would I do it? 
I think splitting the dataframe before merging it might also be an option and after that combining them, any ideas on that? 

Regards Dirk 




----- Original Message -----
From: arun <smartpink111 at yahoo.com>
To: R help <r-help at r-project.org>
Cc: 
Sent: Wednesday, August 14, 2013 10:39 AM
Subject: Re: [R] Create rows for columns in dataframe

Hi,
I tried the second method on a bigger dataset.  This is what I get, 

indx<-rep(1:nrow(dat1),6e4)
dat2<- dat1[indx,]

system.time({
vec1<- paste(dat2[,1],dat2[,2],colnames(dat2)[2],sep=".")
res2<-reshape(dat2,idvar="newCol",varying=list(2:26),direction="long")
res3<-res2[order(res2[,4]),]
res4<-  res3[res3[,3]!="",-4]
vec2<-paste(res4[,1],res4[,3],paste0("C",res4[,2]),sep=".")
 res4$PRIMAIRY<-vec2%in%vec1
 row.names(res4)<-1:nrow(res4)
res4$ID<- row.names(res4)
res4[,c(1,3)]<- lapply(res4[,c(1,3)],as.character)
res5<-res4[,c(5,1,3,4)]
colnames(res5)[3]<-"CODE"})
 # user  system elapsed 
#144.672   2.072 147.034  #reshape() step is taking most of the time
 dim(res5)
#[1] 2880000       4

#Comparing this to the first method on a smaller subset of dat2.
dat2New<- dat2[1:3e4,]

system.time({
res1<-do.call(rbind,lapply(seq_len(nrow(dat2New)),function(i) {x1<-as.character(unlist(dat2New[i,-1]));CODE<-x1[x1!=""];PRIMAIRY<-x1[x1!=""]==head(x1,1); DSYSRTKY=as.numeric(as.character(dat2[i,1]));data.frame(DSYSRTKY,CODE,PRIMAIRY,stringsAsFactors=FALSE) }))
 res1$ID<- row.names(res1)
res2<-res1[,c(4,1:3)]
})
#  user  system elapsed 
#166.452  15.752 182.643 
nrow(dat2)-nrow(dat2New)
#[1] 330000

You might also try library(data.table).  Should be faster..

A.K.








----- Original Message -----
From: Dark <info at software-solutions.nl>
To: r-help at r-project.org
Cc: 
Sent: Wednesday, August 14, 2013 5:41 AM
Subject: Re: [R] Create rows for columns in dataframe

Hi A.K,

Thanks for your great help.
I'm now running your first suggestion on a 600.000 row sample after
verifying it works on a smaller sample.
It's now been running for 40 minutes. 
Which method do you think will be faster?

Regards Derk



--
View this message in context: http://r.789695.n4.nabble.com/Create-rows-for-columns-in-dataframe-tp4673607p4673704.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.