ZAWD ZAWD - 3 months ago 8
R Question

calculate the average value per minute in R

I have a

data.table
with two parameters(date and value), now I want to calculate the Average Value for each minute(or each 15 minutes).


  • I thought at first I should seperate the date into hours and minutes

  • then calculate the average value for the interval time



but I really have no idea how to do, maybe you would have an idea for it

For example, the simple data.

date Value
2015-07-01 00:00:23 1.83
2015-07-01 00:00:24 1.68
2015-07-01 00:00:25 1.29
2015-07-01 00:00:40 14.23
2015-07-01 00:00:41 0.96
2015-07-01 00:00:46 4.93
2015-07-01 00:01:12 26.44
2015-07-01 00:02:02 49.66
2015-07-01 00:02:05 3.00
2015-07-01 00:02:08 3.19
2015-07-01 00:02:27 19.42
2015-07-01 00:02:32 4.44
2015-07-01 00:02:45 12.77
2015-07-01 00:02:49 4.44
2015-07-01 00:03:40 50.71
2015-07-01 00:03:50 10.64
2015-07-01 00:03:52 1.18
2015-07-01 00:03:52 0.99
2015-07-01 00:03:54 1.32
2015-07-01 00:03:56 2.20


Here is a code to generate test data:

dd <- data.table(date = c("2015-07-01 00:00:23", "2015-07-01 00:00:24", "2015-07-01 00:00:25","2015-07-01 00:00:40", "2015-07-01 00:00:46","2015-07-01 00:01:12","2015-07-01 00:02:02","2015-07-01 00:02:08","2015-07-01 00:02:27","2015-07-01 00:02:32","2015-07-01 00:02:45","2015-07-01 00:02:49","2015-07-01 00:03:40","2015-07-01 00:03:50","2015-07-01 00:03:52","2015-07-01 00:03:54","2015-07-01 00:03:56"),
value = c(1.83,1.68,1.29,14.23,0.96,4.93,26.44,3.00,3.19,19.42,4.44,50.71,10.64,1.18,0.99,1.32,2.20))

Answer

We can use the minute function from the lubridate package. Note that data.table has an hour function.

We can use the cut function to format the minutes into quarters of hours.

library(lubridate)
dd[, c('Hour', 'Minute') := .(data.table::hour(date), minute(date))
 ][, Minute_Cut := cut(Minute, breaks = c(0,15,30,45,60), include.lowest = T)
 ][, .(Avg = mean(value)), .(Hour, Minute_Cut)]

#    Hour Minute_Cut      Avg
# 1:    0     [0,15] 8.732353

If you just want to calculate by each minute, we can avoid the cut step:

dd[, c('Hour', 'Minute') := .(data.table::hour(date), minute(date))
 ][, .(Avg = mean(value)), .(Hour, Minute)]

#    Hour Minute      Avg
# 1:    0      0  3.99800
# 2:    0      1  4.93000
# 3:    0      2 17.86667
# 4:    0      3  3.26600
Comments