CODEWITHSUNDEEP
rdatedata.tableaverage
ZAWD
ZAWD

Reputation: 661

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).

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))

Upvotes: 3

Views: 2546

Answers (2)

Joshua Ulrich
Joshua Ulrich

Reputation: 176718

Since you meant "quarter of an hour" when you said "by quarter", then I would convert your data.table to an xts object and use xts::period.apply.

library(xts)
x <- as.xts(dd[,date := as.POSIXct(date)])
period.apply(x, endpoints(x, "minutes", 15), mean)
#                        value
# 2015-07-01 00:03:56 8.732353

If by "quarter" you mean "quarter of a year", then you can use my original answer:

You can use zoo::yearqtr to create a quarterly time value to aggregate by. Then use the normal data.table aggregation steps.

dd[, avg := mean(value), by = zoo::as.yearqtr(dd$date, "%Y-%m-%d")]

Upvotes: 4

bouncyball
bouncyball

Reputation: 10781

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

Upvotes: 4

Related Questions