如何基于ID关联条件筛选data.frame中符合特定存活状态的树木

我有如下data.frame：

df = data.frame(plot = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2),
                tree = c("1", "1", "1", "1", "2", "2", 
                            "3", "4", "7", "7"),
                trunk = c("1", "2", "3", "4", "1", "2", 
                             "1", "1", "1", "2"),
                name = c("A", "A", "A", "A", "A", "A",
                         "B", "C", "A", "A"),
                time_1 = c("alive", "alive", "dead", "dead",
                           "alive", "alive",
                           "alive",
                           "alive",
                           "dead", "dead"),
                time_2 = c("dead", "alive", "dead", "dead",
                             "dead", "dead",
                             "dead",
                             "dead",
                             "dead", "dead"))

背景与需求 #

每个plot包含多棵tree，每棵tree可包含单个或多个trunk。我需要筛选出满足以下条件的tree：

• 该tree的所有trunk在time_1状态为"alive"
• 该tree的所有trunk在time_2状态为"dead"（只要有任意一个trunk在time_2为"alive"，该tree就需要排除）

我的尝试 #

我已经为每个tree和trunk添加了标识符：

# 为每个plot下的trunk添加ID
df$trunk_id <- paste(df$plot, "_",
                   df$tree, "_",
                   df$trunk,
                   sep = "")

# 为每个plot下的tree添加ID  
df$tree_id <- paste(df$plot, "_",
                    df$tree,
                    sep = "")                

之后我用以下代码筛选：

df2 <- df %>% filter(time_1 == "alive" & time_2 == "dead")

但结果不符合预期，比如plot == 1且tree_id == "1_1"的树木，因为存在time_2为"alive"的trunk应该被排除，但当前筛选没有移除它。

理想输出 #

符合条件的tree_id：

output <- c("1_2", "2_3", "2_4")

你之前的filter是逐行筛选，没有考虑同一tree_id下所有trunk的整体状态，需要先按tree_id分组，再对每组的条件进行判断：

方法1：直接提取符合条件的tree_id #

library(dplyr)

valid_trees <- df %>%
  group_by(tree_id) %>%
  # 同时满足两个全局条件的组才保留
  filter(all(time_1 == "alive") & all(time_2 == "dead")) %>%
  # 去重后提取tree_id
  distinct(tree_id) %>%
  pull(tree_id)

# 查看结果
valid_trees

方法2：保留符合条件的整棵树的所有记录 #

如果需要保留原数据中符合条件树木的所有trunk记录，可以用以下代码：

df_filtered <- df %>%
  group_by(tree_id) %>%
  # 先计算每组是否满足两个条件
  mutate(
    all_time1_alive = all(time_1 == "alive"),
    all_time2_dead = all(time_2 == "dead")
  ) %>%
  # 筛选出同时满足条件的组
  filter(all_time1_alive & all_time2_dead) %>%
  ungroup() %>%
  # 移除临时计算的列
  select(-all_time1_alive, -all_time2_dead)

# 提取tree_id的话，执行：distinct(df_filtered$tree_id)

逻辑说明 #

• group_by(tree_id)：将数据按单棵树分组，确保判断逻辑针对同一棵树的所有trunk
• all(time_1 == "alive")：检查当前树的所有trunk在time_1是否全为"alive"
• all(time_2 == "dead")：检查当前树的所有trunk在time_2是否全为"dead"
• 只有同时满足上述两个条件的树木才会被保留，彻底排除存在不符合条件trunk的树木

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