如何在R中从相同值的日期范围生成新的Data Frame

现有如下R语言DataFrame示例：

structure(list(date = structure(c(1514764800, 1514851200, 1514937600, 1515024000, 1515110400, 1515196800, 1515283200, 1515369600, 1515456000, 1515542400, 1515628800, 1515715200, 1515801600, 1515888000, 1515974400, 1516060800, 1516147200, 1516233600, 1516320000, 1516406400, 1516492800, 1516579200, 1516665600, 1516752000, 1516838400, 1516924800, 1517011200, 1517097600, 1517184000, 1517270400, 1517356800, 1517443200, 1517529600, 1517616000, 1517702400, 1517788800, 1517875200, 1517961600, 1518048000, 1518134400, 1518220800, 1518307200, 1518393600, 1518480000, 1518566400), tzone = "UTC", class = c("POSIXct", "POSIXt")), Al = c(NA, 21.399, 21.399, 21.399, NA, NA, NA, 8.036, 8.036, 8.036, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 36.914, 36.914, 36.914, NA, NA, NA, 11.466, 11.466, 11.466, NA, NA, NA, NA, NA, 16.471, 16.471, 16.471, NA, NA, NA, NA, 56.157, 56.157, 56.157)), row.names = c(NA, -45L), class = c("tbl_df", "tbl", "data.frame"))

需要生成新的DataFrame，仅保留相同值连续日期范围的首个日期，预期输出如下：

structure(list(startdate = structure(c(17532, 17533, 17536, 17539, 17542, 17553, 17556, 17559, 17562, 17567, 17570, 17574), class = "Date"), Al = c(NA, 21.399, NA, 8.036, NA, 36.914, NA, 11.466, NA, 16.471, NA, 56.157)), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"), row.names = c(NA, -12L), spec = structure(list(cols = list(startdate = structure(list(format = "%m/%d/%Y"), class = c("collector_date", "collector")), Al = structure(list(), class = c("collector_double", "collector"))), default = structure(list(), class = c("collector_guess", "collector")), skip = 1), class = "col_spec"))

需用R语言处理大量此类数据集。

解决方案 #

可以使用dplyr包实现需求，核心思路是为连续相同值（包括NA）的分组分配唯一ID，然后按分组筛选每组的首个日期：

代码实现 #

library(dplyr)

# 读取原始数据（假设原始数据名为df）
df <- structure(list(date = structure(c(1514764800, 1514851200, 1514937600, 1515024000, 1515110400, 1515196800, 1515283200, 1515369600, 1515456000, 1515542400, 1515628800, 1515715200, 1515801600, 1515888000, 1515974400, 1516060800, 1516147200, 1516233600, 1516320000, 1516406400, 1516492800, 1516579200, 1516665600, 1516752000, 1516838400, 1516924800, 1517011200, 1517097600, 1517184000, 1517270400, 1517356800, 1517443200, 1517529600, 1517616000, 1517702400, 1517788800, 1517875200, 1517961600, 1518048000, 1518134400, 1518220800, 1518307200, 1518393600, 1518480000, 1518566400), tzone = "UTC", class = c("POSIXct", "POSIXt")), Al = c(NA, 21.399, 21.399, 21.399, NA, NA, NA, 8.036, 8.036, 8.036, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 36.914, 36.914, 36.914, NA, NA, NA, 11.466, 11.466, 11.466, NA, NA, NA, NA, NA, 16.471, 16.471, 16.471, NA, NA, NA, NA, 56.157, 56.157, 56.157)), row.names = c(NA, -45L), class = c("tbl_df", "tbl", "data.frame"))

# 处理步骤
result_df <- df %>%
  # 为连续相同值（含NA）生成分组ID
  mutate(group_id = cumsum(ifelse(is.na(Al) != is.na(lag(Al, default = !is.na(first(Al)))), 1, 0)) %>%
           # 当Al值变化时，分组ID加1（区分非NA的不同值）
           + cumsum(ifelse(!is.na(Al) & Al != lag(Al, default = first(Al)), 1, 0))) %>%
  # 按分组ID筛选每组首个记录
  group_by(group_id) %>%
  slice(1) %>%
  ungroup() %>%
  # 重命名日期列，并保留需要的列
  rename(startdate = date) %>%
  select(startdate, Al) %>%
  # 转换日期格式为Date类型（匹配预期输出）
  mutate(startdate = as.Date(startdate))

# 查看结果
result_df

代码说明 #

• group_id的生成：分两部分，第一部分区分连续的NA和非NA组，第二部分区分非NA情况下的不同数值组，确保所有连续相同值（包括连续NA）都被分到同一组。
• slice(1)用于提取每组的第一条记录，即连续相同值范围的首个日期。
• 最后转换日期格式为Date类型，与预期输出一致。

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