Here are a couple of solutions. The first uses by and the second sqldf: > Lines <- " rt dur tid mood roi x + 55 5523 200 4 subj 9 5 + 56 5523 52 4 subj 7 31 + 57 5523 209 4 subj 4 9 + 58 5523 188 4 subj 4 7 + 70 4016 264 5 indic 9 51 + 71 4016 195 5 indic 4 14" > d <- read.table(textConnection(Lines), header = TRUE) > > > # solution 1 - see ?by and ?transform > > idx <- cumsum( c(TRUE,diff(d$roi)!=0) ) > do.call(rbind, by(d, idx, function(x) + transform(x, dur = sum(dur), x = mean(x))[1,,drop = FALSE ])) rt dur tid mood roi x 1 5523 200 4 subj 9 5 2 5523 52 4 subj 7 31 3 5523 397 4 subj 4 8 4 4016 264 5 indic 9 51 5 4016 195 5 indic 4 14 > > # solution 2 - see http://sqldf.googlecode.com > > dd <- data.frame(d, idx) # idx computed above > library(sqldf) > sqldf("select rt, sum(dur) dur, tid, mood, roi, avg(x) x from dd group by > idx") rt dur tid mood roi x 1 5523 200 4 subj 9 5 2 5523 52 4 subj 7 31 3 5523 397 4 subj 4 8 4 4016 264 5 indic 9 51 5 4016 195 5 indic 4 14
On Tue, Dec 8, 2009 at 7:50 AM, Titus von der Malsburg <malsb...@gmail.com> wrote: > Hi, I have a data frame and want to merge adjacent rows if some condition is > met. There's an obvious solution using a loop but it is prohibitively slow > because my data frame is large. Is there an efficient canonical solution for > that? > >> head(d) > rt dur tid mood roi x > 55 5523 200 4 subj 9 5 > 56 5523 52 4 subj 7 31 > 57 5523 209 4 subj 4 9 > 58 5523 188 4 subj 4 7 > 70 4016 264 5 indic 9 51 > 71 4016 195 5 indic 4 14 > > The desired result would have consecutive rows with the same roi value merged. > dur values should be added and x values averaged, other values don't differ in > these rows and should stay the same. > >> head(result) > rt dur tid mood roi x > 55 5523 200 4 subj 9 5 > 56 5523 52 4 subj 7 31 > 57 5523 397 4 subj 4 8 > 70 4016 264 5 indic 9 51 > 71 4016 195 5 indic 4 14 > > There's also a solution using reshape. It uses an index for blocks > > d$index <- cumsum(c(TRUE,diff(d$roi)!=0)) > > melts and then casts for every column using an appropriate fun.aggregate. > However, this is a bit cumbersome and also I'm not sure how to make sure that > I get the original order of rows. > > Thanks for any suggestion. > > Titus > > ______________________________________________ > R-help@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. > ______________________________________________ R-help@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.