按group聚合DataFrame：基于规则合并var为NA的行

原始数据 #

df <- data.frame(group = c(rep("a", 11),
                           rep("b", 7)),
                 dates = as.Date(c("2019-05-10", "2019-08-23", "2020-08-21", "2021-08-27", "2021-10-04", "2021-12-23", "2022-06-30", "2005-03-07", "2006-03-30", "2006-11-05", "2007-07-14",
                                   "2021-01-21", "2021-07-06", "2021-09-27", "2021-12-11", "2022-04-28", "2022-07-27", "2022-10-14")),
                 dates_2 = as.Date(c(NA, NA, NA, "2021-08-27", NA, NA, NA, "2005-03-07", NA, NA, NA,
                                     NA, "2021-07-06", NA, NA, NA, "2022-07-27", NA)),
                 counts = c(NA, NA, NA, 3, NA, NA, NA, 1, NA, NA, NA,
                            NA, 5, NA, NA, NA, 4, NA),
                 var = c("go", "go", "stop", NA, "go", "go", "go", NA, "stop", "stop", "go",
                         "go", NA, "suspend", "go", "go", NA, "go"))

原始数据展示：

#    group      dates    dates_2 counts     var
# 1      a 2019-05-10       <NA>     NA      go
# 2      a 2019-08-23       <NA>     NA      go
# 3      a 2020-08-21       <NA>     NA    stop
# 4      a 2021-08-27 2021-08-27      3    <NA>
# 5      a 2021-10-04       <NA>     NA      go
# 6      a 2021-12-23       <NA>     NA      go
# 7      a 2022-06-30       <NA>     NA      go
# 8      a 2005-03-07 2005-03-07      1    <NA>
# 9      a 2006-03-30       <NA>     NA    stop
# 10     a 2006-11-05       <NA>     NA    stop
# 11     a 2007-07-14       <NA>     NA      go
# 12     b 2021-01-21       <NA>     NA      go
# 13     b 2021-07-06 2021-07-06      5    <NA>
# 14     b 2021-09-27       <NA>     NA suspend
# 15     b 2021-12-11       <NA>     NA      go
# 16     b 2022-04-28       <NA>     NA      go
# 17     b 2022-07-27 2022-07-27      4    <NA>
# 18     b 2022-10-14       <NA>     NA      go

聚合需求 #

按group列分组处理：

• 将var列值为NA的行合并到其上方或下方**var值不为"go"**的行；
• 若该行上下的var值均为"go"，则合并到上方行。

期望输出：

#    group      dates    dates_2 counts     var
# 1      a 2019-05-10       <NA>     NA      go
# 2      a 2019-08-23       <NA>     NA      go
# 3      a 2020-08-21 2021-08-27      3    stop
# 4      a 2021-10-04       <NA>     NA      go
# 5      a 2021-12-23       <NA>     NA      go
# 6      a 2022-06-30       <NA>     NA      go
# 7      a 2006-03-30 2005-03-07      1    stop
# 8      a 2006-11-05       <NA>     NA    stop
# 9      a 2007-07-14       <NA>     NA      go
# 10     b 2021-01-21       <NA>     NA      go
# 11     b 2021-09-27 2021-07-06      5 suspend
# 12     b 2021-12-11       <NA>     NA      go
# 13     b 2022-04-28 2022-07-27      4      go
# 14     b 2022-10-14       <NA>     NA      go

当前尝试代码 #

用户尝试使用tidyverse和collapse包，但cumsum逻辑无法适配所有场景：

library(tidyverse)
library(collapse) #working on a large dataset
df <- df %>% 
  group_by(group) %>% 
  mutate(var_indicator = cumsum(!is.na(var)))
df_collapse <- collap(df, ~ group + var_indicator, custom = list(ffirst = c("dates", "var"), 
                                                                 flast = c("dates_2", "counts")))
df_collapse

解决方案 #

可以通过精准标记NA行的归属分组，再结合高效聚合实现需求，代码如下：

library(tidyverse)
library(collapse)
library(zoo)

df_processed <- df %>%
  group_by(group) %>%
  # 标记有效行，并为NA行分配归属分组ID
  mutate(
    is_non_go_valid = !is.na(var) & var != "go",
    is_go_valid = !is.na(var) & var == "go",
    # 先以非go的有效行为锚点创建分组ID
    group_id = case_when(is_non_go_valid ~ row_number(), TRUE ~ NA_integer_),
    # 向前填充非go锚点ID，优先归属非go行
    group_id = na.locf(group_id, na.rm = FALSE),
    # 若仍无归属，用go行作为锚点填充
    group_id = ifelse(is.na(group_id), na.locf(ifelse(is_go_valid, row_number(), NA), na.rm = FALSE), group_id),
    # 处理开头就有NA的极端情况，向后填充补全ID
    group_id = na.locf(group_id, fromLast = TRUE)
  ) %>%
  # 按group和归属ID聚合，匹配需求取对应值
  collap(
    ~ group + group_id,
    custom = list(
      ffirst = c("dates", "var"),
      flast = c("dates_2", "counts")
    )
  ) %>%
  select(-group_id) %>%
  ungroup()

# 查看结果
df_processed

代码说明 #

• 用zoo::na.locf实现向前/向后填充，精准匹配NA行的归属规则：优先归属最近的非"go"有效行，无则归属最近的"go"有效行；
• 保留collapse包的高效聚合能力，适配大数据集处理；
• 聚合逻辑严格贴合需求：dates保留目标行原始值，dates_2和counts保留NA行的有效值，var保留目标行的标识值。

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