Summing grouped records in a dataframe in R (... again)
(I tried to ask this question earlier today, but later realized that I was oversimplifying the question, the answers I got were correct, but I couldn't use them due to my oversimplification of the problem in the original question. Here's my second attempt .. .)
I have a data frame in R that looks like this:
"Timestamp", "Source", "Target", "Length", "Content"
0.1 , P1 , P2 , 5 , "ABCDE"
0.2 , P1 , P2 , 3 , "HIJ"
0.4 , P1 , P2 , 4 , "PQRS"
0.5 , P2 , P1 , 2 , "ZY"
0.9 , P2 , P1 , 4 , "SRQP"
1.1 , P1 , P2 , 1 , "B"
1.6 , P1 , P2 , 3 , "DEF"
2.0 , P2 , P1 , 3 , "IJK"
...
and I want to convert this to:
"StartTime", "EndTime", "Duration", "Source", "Target", "Length", "Content"
0.1 , 0.4 , 0.3 , P1 , P2 , 12 , "ABCDEHIJPQRS"
0.5 , 0.9 , 0.4 , P2 , P1 , 6 , "ZYSRQP"
1.1 , 1.6 , 0.5 , P1 , P2 , 4 , "BDEF"
...
Trying to put this in English, I want to group consecutive records with the same "Source" and "Target" together, and then print one record per group showing StartTime, EndTime and Duration (= EndTime-StartTime) for that group along with the sum of the lengths for this group and the concatenation of the Content (it will all be a string) in this group.
TimeOffset values will always increase throughout the data frame.
I took a look at the melt / rework and felt like this could be used to solve the problem, but I couldn't plunge into the documentation. I suspect it might be possible to do this in R, but I really don't know where to start. As a last resort, I could export a dataframe and do it for example. Python, but I'd rather stay in R if possible.
Thanks in advance for any help you can provide
a source to share
Try the following:
id <- as.numeric(gsub("P","",paste(df$Source,df$Target,sep="")))
df$id <- cumsum(c(TRUE,diff(id)!=0))
res <- by(df, df$id,
function(x) {
len <- nrow(x)
start <- x[1,1]
end <- x[len,1]
dur <- end - start
src <- x[1,2]
trg <- x[1,3]
len <- sum(x[,4])
cont <- paste(x[,5],collapse="")
return(c(start,end,dur,src,trg,len,cont))
}
)
do.call(rbind,res)
PS: You will need to convert the result to the "correct" format, since the end result is a matrix of strings.
a source to share
Pasting in my (not elegant) way
df1 <- read.table(textConnection("
Timestamp Source Target Length Content
0.1 P1 P2 5 ABCDE
0.2 P1 P2 3 HIJ
0.4 P1 P2 4 PQRS
0.5 P2 P1 2 ZY
0.9 P2 P1 4 SRQP
1.1 P1 P2 1 B
1.6 P1 P2 3 DEF
2.0 P2 P1 3 IJK
"),header=T)
df <- adply(df1, 1 ,transform, newSource =
as.numeric(paste(substr(Source, 2, 2),substr(Target, 2, 2),sep="")) )
ind <- cbind(rle(df$newSource)[[1]],cumsum(rle(df$newSource)[[1]]))
ind2 <- apply(ind,1,function(x) c(x[2]-(x[1]-1),x[2]))
res <- ldply(apply(ind2,2,function(x) data.frame(StartTime = df[x[1],1] ,
EndTime = df[x[2],1] ,
Duration = df[x[2],1] - df[x[1],1] ,
Source = df[x[1],2] ,
Target = df[x[1],3] ,
Length=sum(df[x[1]:x[2],4]) ,
Content=paste(df[x[1]:x[2],5],collapse="")
) ))
StartTime EndTime Duration Source Target Length Content
1 0.1 0.4 0.3 P1 P2 12 ABCDEHIJPQRS
2 0.5 0.9 0.4 P2 P1 6 ZYSRQP
3 1.1 1.6 0.5 P1 P2 4 BDEF
4 2.0 2.0 0.0 P2 P1 3 IJK
a source to share