Aggregating a data frame

Question

Suppose I have this data.frame:

df <- data.frame(time = seq(0.2,4,0.2), behavior = c(rep(0,4),rep(1,4),rep(2,4),rep(0,4),rep(1,4)), n1 = rnorm(20), n2 = rnorm(20))

df$time are time slices (currently time slice = 0.2s).

I'm looking for an efficient function that given a time slice value - hence the grouping value is df$time, will aggregate the df accordingly by summing df$n1, df$n2,.. (in reality these columns go up to df$n200 so I'm looking for something generic) and keep the maximum value of df$behavior.

For example, if time slice = 1.0 the resulting data.frame should be:

  time behavior         n1         n2
1    1        1  0.6995929  1.5603166
2    2        2  1.8677778  0.1046646
3    3        2 -1.5957459 -5.5116914
4    4        1 -1.0757102  1.5130076

Your question boils down to how to cut the time values. Please show exactly, where you'd expect the cuts to be for different slice values. — Roland
– Roland, Commented Nov 30, 2014 at 15:05

akrun · Accepted Answer · 2014-11-30 15:29:40Z

You can try dplyr. Here, the values for n1 and n2 are different as there was no set.seed

library(dplyr)
seq1 <-  with(df, seq(floor(min(time)), ceiling(max(time)+1), by=1))

grp <- group_by(df, time=cut(time, breaks=seq1, labels=FALSE))
df1 <- grp %>%
          summarise_each(funs(sum), n1:n2)

df2 <- grp %>% 
          summarise(behavior=max(behavior))

left_join(df2,df1, by='time')
#   time behavior         n1         n2
#1    1        1  0.8960162  0.6767968
#2    2        2 -2.2237071 -4.2431708
#3    3        2 -2.0750859 -3.7181187
#4    4        1  1.0824854 -0.2501264

Or using data.table

library(data.table)
setDT(df)[, c(behavior1=max(behavior),lapply(.SD, sum)),
    by=list(time=cut(time, breaks=seq1, labels=FALSE))][,behavior:=NULL][]

If you need to slice by 1.5

 seq1 <-  with(df, seq(floor(min(time)), ceiling(max(time)+1), by= 1.5)
 grp <- group_by(df, time=cut(time, breaks=seq1))

and applying the above code gives

 #     time behavior       n1       n2
 #1 (0,1.5]        1 2.821384 2.981740
 #2 (1.5,3]        2 1.145459 5.962142
 #3 (3,4.5]        1 3.313462 2.236264

Collectives™ on Stack Overflow

Aggregating a data frame

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related