aggregate column values at 5 min intervals and create a new dataframe

user_7163441 0
Viewed 659

I have a dataframe as:

           T1            T2           T3             timestamp
          45.37        44.48            13 2015-11-05 10:23:00
          44.94        44.55         13.37 2015-11-05 10:24:00
          45.32        44.44         13.09 2015-11-05 10:27:00
          45.46        44.51         13.29 2015-11-05 10:28:00
          45.46        44.65         13.18 2015-11-05 10:29:16
          45.96        44.85         13.23 2015-11-05 10:32:00
          45.52        44.56         13.53 2015-11-05 10:36:00
          45.36        44.62         13.25 2015-11-05 10:37:00

I want to create a new dataframe that contains vaules of T1, T2 and T3 aggregated over 5 min intervals based on the timestamp column. I did come across aggregate and it seems to use one of the columns to group/aggregate the corresponding values in other columns.

If no rows had values over 5-min interval, then the rows to represent NAs. I also like another column that indicates number items used to make the average over 5-min intervals.

Looking for a most efficient way of doing it in R. Thanks

dataframe aggregate r
1 Answers

First make sure the timestamp columns is a date.time column. You can skip this line if it already is in this format.

df1$timestamp <- as.POSIXct(df1$timestamp)

xts has some nice functions for working with timeseries. Especially for rolling functions or time aggregating functions. In this case period.apply can help out.

library(xts)

# create xts object. Be sure to exclude the timestamp column otherwise you end up with a character matrix. 
df1_xts <- as.xts(df1[, -4], order.by = df1$timestamp)

# sum per 5 minute intervals
df1_xts_summed <- period.apply(df1_xts, endpoints(df1_xts, on = "minutes", k = 5), colSums)

# count rows per 5 minute interval and add to data
df1_xts_summed$nrows <- period.apply(df1_xts$T1, endpoints(df1_xts, on = "minutes", k = 5), nrow)

df1_xts_summed 

                        T1     T2    T3 nrows
2015-11-05 10:24:00  90.31  89.03 26.37     2
2015-11-05 10:29:16 136.24 133.60 39.56     3
2015-11-05 10:32:00  45.96  44.85 13.23     1
2015-11-05 10:37:00  90.88  89.18 26.78     2

If you want it all back into a data.frame:

df_final <- data.frame(timestamp = index(df1_xts_summed), coredata(df1_xts_summed))
df_final

            timestamp     T1     T2    T3 nrows
1 2015-11-05 10:24:00  90.31  89.03 26.37     2
2 2015-11-05 10:29:16 136.24 133.60 39.56     3
3 2015-11-05 10:32:00  45.96  44.85 13.23     1
4 2015-11-05 10:37:00  90.88  89.18 26.78     2

Edit if you want everything rounded at 5 minutes with these as the timestamps you need to do the following:

First step is to replace the timestamps with the 5 minute intervals, taking into account the starting minutes of the timestamps. For this I use the ceiling_date from the lubridate package and add to it the difference between the first values of the timestamp and the ceiling of the first value of the timestamp. This will return the last values of each interval. (If you want to use the start of the interval you need to use floor_date)

df1$timestamp <- lubridate::ceiling_date(df1$timestamp, "5 mins") + difftime(lubridate::ceiling_date(first(df1$timestamp), "5 mins"), first(df1$timestamp), unit = "secs")

Next the same xts code as before which returns the same data, but the timestamp is now the last value of the 5 minute intervals.

df1_xts <- as.xts(df1[, -4], order.by = df1$timestamp)
df1_xts_summed <- period.apply(df1_xts, ep, colSums)
df1_xts_summed$nrows <- period.apply(df1_xts$T1, endpoints(df1_xts, on = "minutes", k = 5), nrow)
df_final <- data.frame(timestamp = index(df1_xts_summed), coredata(df1_xts_summed))
df_final

            timestamp     T1     T2    T3 nrows
1 2015-11-05 10:27:00  90.31  89.03 26.37     2
2 2015-11-05 10:32:00 136.24 133.60 39.56     3
3 2015-11-05 10:37:00  45.96  44.85 13.23     1
4 2015-11-05 10:42:00  90.88  89.18 26.78     2

data:

df1 <- structure(list(T1 = c(45.37, 44.94, 45.32, 45.46, 45.46, 45.96, 
45.52, 45.36), T2 = c(44.48, 44.55, 44.44, 44.51, 44.65, 44.85, 
44.56, 44.62), T3 = c(13, 13.37, 13.09, 13.29, 13.18, 13.23, 
13.53, 13.25), timestamp = c("2015-11-05 10:23:00", "2015-11-05 10:24:00", 
"2015-11-05 10:27:00", "2015-11-05 10:28:00", "2015-11-05 10:29:16", 
"2015-11-05 10:32:00", "2015-11-05 10:36:00", "2015-11-05 10:37:00"
)), class = "data.frame", row.names = c(NA, -8L))
user_49851760
Related