R语言分组查找符合条件的lag值问题求助

我需要按ID分组，找到每行对应的最后一个非"hospital"的event值，但不知道怎么给行添加正确的匹配条件。试了好几段dplyr代码都没实现目标，附上示例数据、期望输出和尝试过的代码，求正确的实现方法。

示例数据 #

df <- structure(list(ID = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 
2, 3, 3, 3, 3, 3, 3), event = c("108", "hospital", "hospital", 
"hospital", "hospital", "108", "110", "hospital", "hospital", 
"110", "hospital", "hospital", "107", "107", "105", "105", "hospital", 
"hospital", "110", "110"), event_type = c("start", "start", "end", 
"start", "end", "end", "start", "start", "end", "end", "start", 
"end", "start", "end", "start", "end", "start", "end", "start", 
"end"), activity_nr = c(1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 7, 
8, 1, 2, 3, 4, 6, 7), activity_id = c(1, 1, 1, 2, 2, 1, 1, 1, 
1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 2, 2), activity_type = c("housing", 
"hospital", "hospital", "hospital", "hospital", "housing", "housing", 
"hospital", "hospital", "housing", "hospital", "hospital", "housing", 
"housing", "housing", "housing", "hospital", "hospital", "housing", 
"housing"), matched = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, 107, NA, NA, NA, NA, NA, 110, NA, NA), `Desired output` = c(NA, 
NA, 108, NA, 108, NA, NA, NA, 110, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-20L))

尝试过的代码 #

• 代码1：

df %>% 
  group_by(ID) %>% 
  arrange(activity_nr) %>% 
  mutate(desired_output = if_else(is.na(matched) &
                                   lag(event) != "hospital", 
                                 lag(event), NA)) 

• 代码2：

df %>% 
  group_by(ID) %>% 
  arrange(activity_nr) %>% 
  mutate(desired_output = if_else(is.na(matched) &
                                   str_detect(event, "1"), 
                                 lag(event), NA))

• 代码3：

df %>% 
  group_by(ID) %>% 
  arrange(activity_nr) %>% 
  mutate(desired_output = if_else(is.na(matched),
                                   lag(str_detect(event, "1")), NA))

正确实现方法 #

核心思路是：按ID分组排序后，先记录每个位置之前最后一个非"hospital"的event值，再结合matched为NA且当前行是hospital的end事件（对应期望输出的行）来赋值。

可以用fill函数来填充最近的非hospital值，再筛选符合条件的行：

library(dplyr)
library(tidyr)

df_result <- df %>%
  group_by(ID) %>%
  arrange(activity_nr) %>%
  # 创建临时列，仅保留非hospital的event值，其余设为NA
  mutate(last_non_hospital = if_else(event != "hospital", event, NA_character_)) %>%
  # 向下填充最近的非hospital值，让每行都能拿到之前最近的目标值
  fill(last_non_hospital, .direction = "down") %>%
  # 按期望规则赋值：仅当matched为NA、当前是hospital的end事件时，保留填充值，其余为NA
  mutate(desired_output = if_else(is.na(matched) & event == "hospital" & event_type == "end", last_non_hospital, NA_character_)) %>%
  # 移除临时辅助列
  select(-last_non_hospital)

# 查看结果
df_result

内容的提问来源于stack exchange，提问作者Louise Sørensen