基于时间条件查找DataFrame最新行，实现last_utt列生成

要生成last_utt列，核心逻辑是为每一行找到所有结束时间小于等于当前消息开始时间的记录中，结束时间最晚的那条消息内容。以下是几种高效实现方式：

方法1：dplyr + purrr 逐行处理（适合小数据集） #

利用purrr::map_chr遍历每一行的开始时间，筛选符合条件的候选行后取最新的消息：

library(dplyr)
library(purrr)

s2.df <- tibble(subj = c(1,1,2,2,2,1,2,1),
                utt = c("Hi","How r u?","Good","Bad","Okay",
                        "Great!","You?","Okay!"),
                prev_subj = c(NA,"same","diff","same","same","diff",
                              "diff","diff"),
                utt_start_time = c(1,4,5,9,11,9,17,19),
                utt_end_time =   c(3,6,8,10,12,11,18,20))

s2.df <- s2.df %>%
  mutate(last_utt = map_chr(utt_start_time, ~{
    candidates <- filter(s2.df, utt_end_time <= .x)
    if (nrow(candidates) == 0) {
      NA_character_
    } else {
      candidates %>% slice_max(utt_end_time) %>% pull(utt)
    }
  }))

方法2：data.table 非等值连接（适合大数据集，效率最优） #

data.table的非等值连接可以直接匹配符合条件的最新记录，性能远高于逐行处理：

library(data.table)

s2.df <- tibble(subj = c(1,1,2,2,2,1,2,1),
                utt = c("Hi","How r u?","Good","Bad","Okay",
                        "Great!","You?","Okay!"),
                prev_subj = c(NA,"same","diff","same","same","diff",
                              "diff","diff"),
                utt_start_time = c(1,4,5,9,11,9,17,19),
                utt_end_time =   c(3,6,8,10,12,11,18,20))

setDT(s2.df)
# 非等值连接，取匹配到的最后一条记录的utt
s2.df[, last_utt := s2.df[s2.df, on = .(utt_end_time <= utt_start_time), 
                          mult = "last", x.utt]]

方法3：fuzzyjoin 模糊自连接（逻辑直观） #

通过fuzzy_left_join实现自连接，再筛选每组的最新匹配：

library(fuzzyjoin)
library(dplyr)

s2.df <- tibble(subj = c(1,1,2,2,2,1,2,1),
                utt = c("Hi","How r u?","Good","Bad","Okay",
                        "Great!","You?","Okay!"),
                prev_subj = c(NA,"same","diff","same","same","diff",
                              "diff","diff"),
                utt_start_time = c(1,4,5,9,11,9,17,19),
                utt_end_time =   c(3,6,8,10,12,11,18,20))

# 自连接：匹配结束时间 <= 当前开始时间的记录
joined_df <- fuzzy_left_join(
  s2.df, s2.df,
  by = c("utt_start_time" = "utt_end_time"),
  match_fun = `>=`
) %>%
  group_by(utt_start_time.x) %>%
  slice_max(utt_end_time.y) %>% # 取每组最晚结束的匹配
  ungroup() %>%
  select(original_utt = utt.x, last_utt = utt.y)

# 合并回原数据集
s2.df <- s2.df %>%
  left_join(joined_df, by = c("utt" = "original_utt")) %>%
  mutate(last_utt = ifelse(row_number() == 1, NA_character_, last_utt))

三种方法最终生成的last_utt列均与示例预期一致：

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