I’ve got a data frame with the following:
Id final_date_time Speed
2022484408 2016-04-12 07:21:00 97
2022484405 2016-04-12 07:21:05 102
2022484402 2016-04-12 07:21:10 105
2022484407 2016-04-12 07:21:20 103
2022484408 2016-04-12 07:21:25 101
2022484408 2016-04-12 07:22:05 95
2022484410 2016-04-12 07:22:10 91
2022484408 2016-04-12 07:22:15 93
2022484421 2016-04-12 07:22:20 94
2022484408 2016-04-12 07:22:25 93
2022484421 2016-04-12 07:22:35 92
2022484421 2016-04-12 07:22:40 89
2022484447 2016-04-12 07:22:50 83
2022484488 2016-04-12 07:22:55 61
2022484411 2016-04-12 07:23:00 60
The class of "final_date_time" is POSIXct.
How can I find the average of "Speed" on a per minute basis?
Here is the dput info:
structure(list(Id = c(2022484408, 2022484408, 2022484408, 2022484408,
2022484408, 2022484408), new_final_date = structure(c(1460438460,
1460438465, 1460438470, 1460438480, 1460438485, 1460438525), class = c("POSIXct",
"POSIXt"), tzone = ""), Value = c(97L, 102L, 105L, 103L, 101L,
95L)), row.names = c(NA, 6L), class = "data.frame")
I tried a solution that worked on a previous similar problem, (changed the labels), but I couldn’t get it to work:
library(xts)
x <- as.xts(dd[,date := as.POSIXct(date)])
period.apply(x, endpoints(x, "minutes", 15), mean)
>Solution :
library(data.table)
setDT(x)
x[, .(avg = mean(Speed)), .(Id, interval = cut(final_date_time, breaks = "1 min"))]
# Id interval avg
# 1: 2022484408 2016-04-12 07:21:00 99.000
# 2: 2022484405 2016-04-12 07:21:00 102.000
# 3: 2022484402 2016-04-12 07:21:00 105.000
# 4: 2022484407 2016-04-12 07:21:00 103.000
# 5: 2022484408 2016-04-12 07:22:00 93.667
# 6: 2022484410 2016-04-12 07:22:00 91.000
# 7: 2022484421 2016-04-12 07:22:00 91.667
# 8: 2022484447 2016-04-12 07:22:00 83.000
# 9: 2022484488 2016-04-12 07:22:00 61.000
# 10: 2022484411 2016-04-12 07:23:00 60.000
*I assumed you want to group on Id as well, if not, remove Id
x[, .(avg = mean(Speed)), .(interval = cut(final_date_time, breaks = "1 min"))]