如何在R中获得指定日期范围的时间序列的小时平均值?

Question

我有A,B,C多个地区2年来的每小时数据。我希望能够在每个区域的指定日期范围内获得小时平均值。对不起，我看完了:如何做一个伟大的R可复制的例子但不确定如何使用dput()来正确表示我拥有的数据。下面是我从dput(mydata)复制输出的示例数据:

structure(list(time = structure(c(1451606400, 1451610000, 1451613600, 
1451617200, 1451620800, 1451624400, 1451628000, 1451631600, 1451635200, 
1451638800), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
A = c(0.0173731172095063, 0.0175417882503753, 0.0175839560105925, 
0.017499620490158, 0.0173309494492891, 0.017668291531027, 
0.017836962571896, 0.017836962571896, 0.0182586401740685, 
0.0234452746807901), B = c(0.0173567013800694, 0.0173567013800694, 
0.0170744785934016, 0.0172155899867355, 0.0170744785934016, 
0.0172155899867355, 0.0172155899867355, 0.0172861456834025, 
0.0173567013800694, 0.0198261507634126), C = c(0.00791114205246669, 
0.00806936489351603, 0.00806936489351603, 0.00806936489351603, 
0.00806936489351603, 0.00822758773456536, 0.00854403341666403, 
0.00854403341666403, 0.00854403341666403, 0.012341381601848
)), class = "data.frame", row.names = c(NA, 10L))

基本上，我使用我拥有的时间数据并创建单独的列来表示年、月、日和小时。

structure(list(Year = c("2016", "2016", "2016", "2016", "2016", 
"2016", "2016", "2016", "2016", "2016"), Month = c("01", "01", 
"01", "01", "01", "01", "01", "01", "01", "01"), Day = c("01", 
"01", "01", "01", "01", "01", "01", "01", "01", "01"), hour = c("00", 
"01", "02", "03", "04", "05", "06", "07", "08", "09"), timedata = structure(c(1451606400, 
1451610000, 1451613600, 1451617200, 1451620800, 1451624400, 1451628000, 
1451631600, 1451635200, 1451638800), class = c("POSIXct", "POSIXt"
), tzone = "UTC")), class = "data.frame", row.names = c(NA, 10L
))

我希望根据指定的日期范围获得24小时内填充的平均值，如下所示。我将时间分成特定的年、月、日和小时列的原因是为了执行类似group_by()的操作，但是我有几个问题。

我希望获得指定日期范围的平均值(例如，没有周末的一月到三月)。

我期望的最终输出矩阵应该是一个矩阵25 x 4。以下0:00时的值x为a地区一月至三月工作日0:00时的平均值。

time A B C 
0:00 x
1:00
2:00
3:00

谢谢。

Answer 1

你可以试试-

library(dplyr)
library(lubridate)
df %>% 
mutate(month = month(time), 
hour = hour(time)) %>%
filter(format(time, '%u') %in% 1:5, month %in% 1:3) %>%
group_by(hour) %>%
summarise(across(A:C, mean, na.rm  =TRUE))

format(time, '%u') %in% 1:5只保留工作日(星期一至星期五)，month %in% 1:3只保留一月至三月。