How to average time series by groups in R

2
votes

I have a dataset that looks like this:

datetime <- seq.POSIXt(from=as.POSIXct("2017-05-09 11:45:01", tz="GMT"), 
            to=as.POSIXct("2017-05-09 12:45:00", tz="GMT"), by="sec")
group <- rep(1:120, each = 30)
sample.dat <- data.frame(datetime,group)
head(sample.dat)
         datetime      group
 1 2017-05-09 11:45:01     1
 2 2017-05-09 11:45:02     1
 3 2017-05-09 11:45:03     1
 4 2017-05-09 11:45:04     1
 5 2017-05-09 11:45:05     1
 6 2017-05-09 11:45:06     1

I want to average the time by group, e.g. to a new data frame with the datetime column as the averaged time and the group column as the group numbers. Desired output example: 

       new.datetime        group
 1 2017-05-09 11:45:15         1
 2 2017-05-09 11:45:45         2
 3 2017-05-09 11:46:15         3

I have tried using aggregate() but the returning datetime column is in the format of numbers, for example:

   group       date
 1     1 1493984723
 2     2 1493984753
 3     3 1493984783
 4     4 1493984813
 5     5 1493984843
 6     6 1493984873

So how do I average the time in the desired output formate, and by group?

2
rtimeaverage

2 Answers

2
votes

aggregate seems to work?

aggregate(sample.dat$datetime,FUN=mean,by=list(group))
#       Group.1        x
# 1         1 2017-05-09 14:45:15
# 2         2 2017-05-09 14:45:45
# 3         3 2017-05-09 14:46:15
# 4         4 2017-05-09 14:46:45
# 5         5 2017-05-09 14:47:15
# 6         6 2017-05-09 14:47:45
1
votes

Try

library(dplyr)
sample.dat %>% group_by(group) %>% summarise(mean(datetime))
# A tibble: 120 × 2
   group    `mean(datetime)`
   <int>              <dttm>
1      1 2017-05-09 11:45:15
2      2 2017-05-09 11:45:45
3      3 2017-05-09 11:46:15
4      4 2017-05-09 11:46:45
5      5 2017-05-09 11:47:15