Summing grouped records in a data frame
I have a data frame in R that looks like this:
> TimeOffset, Source, Length
> 0 1 1500
> 0.1 1 1000
> 0.2 1 50
> 0.4 2 25
> 0.6 2 3
> 1.1 1 1500
> 1.4 1 18
> 1.6 2 2500
> 1.9 2 18
> 2.1 1 37
> ...
and I want to convert it to
> TimeOffset, Source, Length
> 0.2 1 2550
> 0.6 2 28
> 1.4 1 1518
> 1.9 2 2518
> ...
In trying to put this in English, I want to group consecutive records with the same "source" together, and then print one record per group showing the largest time offset in that group, source and sum of lengths in that group.
TimeOffset values will always increase.
I suspect this is possible 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
First you need to create a variable id
that points to your groups without relying on them to be sequential. After that, he's pretty straightforward.
> dat <- data.frame( TimeOffset = c(0,.1,.2,.4,.6,1.1,1.4,1.6,1.9,2.1),
+ Source=c(1,1,1,2,2,1,1,2,2,1),
+ Length=c(1500,1000,50,25,3,1500,18,2500,18,37))
> dat
TimeOffset Source Length
1 0.0 1 1500
2 0.1 1 1000
3 0.2 1 50
4 0.4 2 25
5 0.6 2 3
6 1.1 1 1500
7 1.4 1 18
8 1.6 2 2500
9 1.9 2 18
10 2.1 1 37
>
> id <- cumsum(c(TRUE,diff(dat$Source)!=0))
> id
[1] 1 1 1 2 2 3 3 4 4 5
>
> cbind(TimeOffset=tapply(dat$TimeOffset,id,max),
+ Source=tapply(dat$Source,id,max),
+ Length=tapply(dat$Length,id,sum))
TimeOffset Source Length
1 0.2 1 2550
2 0.6 2 28
3 1.4 1 1518
4 1.9 2 2518
5 2.1 1 37
a source to share
I just saw and I love Ian's solution. Mine is too hard ...
df <- read.table(textConnection("
TimeOffset Source Length
0 1 1500
0.1 1 1000
0.2 1 50
0.4 2 25
0.6 2 3
1.1 1 1500
1.4 1 18
1.6 2 2500
1.9 2 18
2.1 1 37
"),header=T)
ind <- cbind(rle(df$Source)[[1]],cumsum(rle(df$Source)[[1]]))
ind2 <- apply(ind,1,function(x) c(x[2]-(x[1]-1),x[2]))
ldply(apply(ind2,2,function(x) data.frame(df[x[2],1:2], Length=sum(df[x[1]:x[2],3]) ) ))
TimeOffset Source Length
1 0.2 1 2550
2 0.6 2 28
3 1.4 1 1518
4 1.9 2 2518
5 2.1 1 37
a source to share
# 'dfx' refers to the 'input' data frame in OP Question
# use run-length encoding to get contiguous rows having the same Source value
a = rle(dfx$Source)
row_groups = a$lengths
result = matrix(rep(0,3))
attr(result, "dim") = c(1,3)
fnx = function(a_df){
c1 = max(a_df[,1])
c2 = a_df[1,2]
c3 = sum(a_df[,3])
cbind(c1, c2, c3)
}
for (itm in row_groups){
px = dfx[1:itm,]
dfx = dfx[-(1:dim(px)[1]),]
result = rbind(result, fnx(px))
}
result = result[-1,]
# returns:
c1 c2 c3
[1,] 0.2 1 2550
[2,] 0.6 2 28
[3,] 1.4 1 1518
[4,] 1.9 2 2518
[5,] 2.1 1 37
a source to share