如何简化R中逐时/日均计算？避免重复计算逐时均值

问题背景 #

已通过R代码实现逐时（60min）均值与日均计算，预处理数据存储在merged对象中，需遵循两项规则：

• 输出时间格式必须为yyyy-mm-dd hh:mm:ss+00
• 日均计算必须基于逐时均值（不可直接使用原始merged数据）

原代码中，为输出符合格式要求的逐时结果，使用mutate将POSIXct类型时间转为字符型，导致该数据无法用于日均分组计算，因此不得不重复计算两次逐时均值（avghr用于输出，avghr1保留时间格式用于日均计算）。现需简化方案，避免重复计算。

原代码 #

#Cal 60min average
avghr <- merged %>%
 select(any_of(cn)) %>%          #cn includes the selected columns in the output  
 as_tibble() %>%
 group_by(Time_sp = lubridate::floor_date(`Date/Time`, "60 mins")) %>% 
 mutate(Time_sp = format(Time_sp, "%Y-%m-%d %H:%M:%S+00")) %>%
 summarise(across(where(is.numeric), ~ if(mean(is.na(.x)) > 0.5) NA else mean(.x, na.rm = TRUE)))
write.csv(avghr, paste0(dirlist[idx],"_hr.csv"), row.names = FALSE)

#60min average to be used for daily average calc
avghr1 <- merged %>%
 select(any_of(cn)) %>%
 as_tibble() %>%
 group_by(Time_sp = lubridate::floor_date(`Date/Time`, "60 mins")) %>% 
 summarise(across(where(is.numeric), ~ if(mean(is.na(.x)) > 0.5) NA else mean(.x, na.rm = TRUE)))
 
#Calc daily average
avgdl <- avghr1 %>%
 group_by(Time_sp = lubridate::floor_date(`Time_sp`, "1 day")) %>%
 mutate(Time_sp = format(Time_sp, "%Y-%m-%d %H:%M:%S+00")) %>%
 summarise(across(where(is.numeric), ~ if(sum(is.na(.x)) > 1) NA else mean(.x, na.rm = TRUE)))
write.csv(avgdl, paste0(dirlist[idx],"_dly.csv"), row.names = FALSE)

简化方案 #

核心思路：仅计算一次保留POSIXct类型时间的逐时均值，后续分别处理输出格式与日均计算，避免重复运算。

简化后代码 #

# 仅计算一次逐时均值，保留POSIXct格式的时间列
avghr_raw <- merged %>%
  select(any_of(cn)) %>%
  as_tibble() %>%
  group_by(Time_sp = lubridate::floor_date(`Date/Time`, "60 mins")) %>%
  summarise(across(where(is.numeric), ~ if(mean(is.na(.x)) > 0.5) NA else mean(.x, na.rm = TRUE)))

# 处理逐时输出的时间格式并保存文件
avghr_output <- avghr_raw %>%
  mutate(Time_sp = format(Time_sp, "%Y-%m-%d %H:%M:%S+00"))
write.csv(avghr_output, paste0(dirlist[idx],"_hr.csv"), row.names = FALSE)

# 基于逐时均值计算日均，处理格式后保存文件
avgdl <- avghr_raw %>%
  group_by(Time_sp = lubridate::floor_date(`Time_sp`, "1 day")) %>%
  summarise(across(where(is.numeric), ~ if(sum(is.na(.x)) > 1) NA else mean(.x, na.rm = TRUE))) %>%
  mutate(Time_sp = format(Time_sp, "%Y-%m-%d %H:%M:%S+00"))
write.csv(avgdl, paste0(dirlist[idx],"_dly.csv"), row.names = FALSE)

方案说明 #

• avghr_raw：仅执行一次逐时均值计算，保留Time_sp的POSIXct类型，同时满足后续格式转换和日均分组的需求
• 逐时输出：基于avghr_raw仅做时间格式转换，无需重复计算均值
• 日均计算：直接使用avghr_raw进行日度分组计算，最后统一转换时间格式

样例数据 #

structure(list(`Date/Time` = structure(c(1614556800, 1614556860, 
1614556920, 1614556980, 1614557040, 1614557100), tzone = "UTC", class = 
c("POSIXct", "POSIXt")), `XY [XY]` = c(0.990641, 0.990641, 0.990641, 
0.990641, 0.990641, 0.990641), `C1 [C1]` = c(257, 257, 257, 256, 255, 
255), Cc = c(0, 0, 0, 0, 0, 0), `C2 [C2]` = c(285, 284, 289, 264, 
231, 223), Dc = c(0, 0, 0, 0, 0, 0), `C3 [C3]` = c(255, 255, 
255, 255, 254, 254), Ac = c(0, 0, 0, 0, 0, 0), C4 = c(0.463735, 
0.465678, 0.467612, 0.469561, 0.471472, 0.473374), `C5 [h]` = c(1013, 
NA, NA, NA, NA, NA), `C6 [%]` = c(43, NA, NA, NA, NA, NA), `C7 [E2]` = 
c(390, 390, 393, 380, 365, 361), Jc = c(0, 0, 0, 0, 0, 0), `D [S]` = 
c(62.3716, 62.2459, 62.1206, 61.9942, 61.8701, 61.7465), `Sw [S2]` = 
c(1392.95, 1392.95, 1392.95, 1392.95, 1392.95, 1392.95), `SW [Q2]` = 
c(389.164, 389.253, 392.14, 379.964, 363.91, 360.562), `QA [H2]` = 
c(646.61, 649.313, 652.002, 654.712, 657.371, 660.016), `T2 [C]` = 
c(3.7, NA, NA, NA, NA, NA), Lc = c(0, 0, 0, 0, 0, 0)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

预期输出 #

逐时输出 #

Time_sp C1 [C1c] C3 [C3c] C5 [C5c]....               
2021-03-01 01:00:00+00 257 285 255 .....
2021-03-01 02:00:00+00  .....
2021-03-01 03:00:00+00  .....
..

日均输出 #

Time_sp C1 [C1c] C3 [C3c] C5 [C5c]...               
2021-03-01 00:00:00+00 257 285 255 .....
2021-03-02 00:00:00+00  .....
2021-03-03 00:00:00+00  .....
..

内容的提问来源于stack exchange，提问作者Alexia k Boston