如何将小时级DataFrame按loc分组转日级数据并保留因子列

我有一个包含小时级DateTime数据的DataFrame，希望按站点（loc列）分组，对其余列计算日值。

当前代码 #

我尝试了基础代码：

dfDay <- aggregate(df, list(df$DateTime), FUN=mean)

但这段代码会让因子列返回NA，而且没有按loc分组。

数据子集 #

structure(list(DateTime = structure(c(1583427600, 1583427600, 
1583427600, 1583427600, 1583427600, 1583427600), class = c("POSIXct", 
"POSIXt"), tzone = "GMT"), mAOD = c(73.4675, 73.6048, 73.7507, 
74.0078, 74.0731, 74.1994), groundLevel = c(73.536, 73.68, 73.809, 
74.018, 73.912, 74.729), rel_ground = c(-0.0685000000000002, 
-0.0752000000000095, -0.0583000000000027, -0.0101999999999975, 
0.161100000000005, -0.529600000000002), siteTemp = c(6.75, 6.1, 
6.91, 7.52, 7.23, 5.48), baroTemp = c(7.57, 7.57, 7.57, 7.57, 
7.57, 7.57), beaverEvent = structure(c(1L, 1L, 1L, 1L, 1L, 1L
), levels = c("BB", "AB", "D"), class = "factor"), mere = structure(c(2L, 
2L, 2L, 2L, 2L, 2L), levels = c("chapel", "hatch", "coleCrose", 
"crose"), class = "factor"), loc = c("SSSI3", "SSSI2", "B6", 
"SSSI1", "B8", "B3"), spi = c(1.3363327, 1.3363327, 1.3363327, 
1.3363327, 1.3363327, 1.3363327), spigroup = c("Moderately Wet", 
"Moderately Wet", "Moderately Wet", "Moderately Wet", "Moderately Wet", 
"Moderately Wet"), damEvent = structure(c(1L, 1L, 1L, 1L, 1L, 
1L), levels = c("1", "2"), class = "factor"), year = structure(c(1L, 
1L, 1L, 1L, 1L, 1L), levels = c("1", "2", "3"), class = "factor")), row.names = c(NA, 
6L), class = "data.frame")

解决方案 #

方法1：用dplyr（tidyverse）处理 #

这种方式逻辑清晰，便于维护，可灵活处理数值列和因子列：

先加载必要的包：

library(dplyr)
library(lubridate)

然后执行数据聚合：

df_day <- df %>%
  # 提取DateTime中的日期部分
  mutate(date = as_date(DateTime)) %>%
  # 按站点和日期分组
  group_by(loc, date) %>%
  # 聚合列：数值列取均值，因子列取组内第一个值（假设同组内因子值一致）
  summarize(
    mAOD = mean(mAOD, na.rm = TRUE),
    groundLevel = mean(groundLevel, na.rm = TRUE),
    rel_ground = mean(rel_ground, na.rm = TRUE),
    siteTemp = mean(siteTemp, na.rm = TRUE),
    baroTemp = mean(baroTemp, na.rm = TRUE),
    spi = mean(spi, na.rm = TRUE),
    beaverEvent = first(beaverEvent),
    mere = first(mere),
    spigroup = first(spigroup),
    damEvent = first(damEvent),
    year = first(year),
    .groups = "drop"
  )

如果同组内因子列可能存在不同值，可以用众数聚合，先定义众数函数：

get_mode <- function(x) {
  ux <- unique(x)
  ux[which.max(tabulate(match(x, ux)))]
}

然后将summarize中的first()替换为get_mode()即可。

方法2：用base R的aggregate处理 #

针对原代码的问题，需要同时按站点和日期分组，并区分数值列、因子列分别处理：

# 提取日期列
df$date <- as.Date(df$DateTime)

# 定义需要聚合的列
num_cols <- c("mAOD", "groundLevel", "rel_ground", "siteTemp", "baroTemp", "spi")
factor_cols <- c("beaverEvent", "mere", "spigroup", "damEvent", "year")

# 聚合数值列（均值）
num_agg <- aggregate(df[num_cols], by = list(loc = df$loc, date = df$date), FUN = mean, na.rm = TRUE)
# 聚合因子列（取组内第一个值）
factor_agg <- aggregate(df[factor_cols], by = list(loc = df$loc, date = df$date), FUN = function(x) x[1])

# 合并结果
df_day <- merge(num_agg, factor_agg, by = c("loc", "date"))

这样就能得到按站点和日期分组的日值数据，且因子列不会返回NA。

内容的提问来源于stack exchange，提问作者Melanie Baker