ZAWD -4 years ago 173
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))
``````

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download