在R中按ID聚合最近10/30天内带o_number的type文本元素

数据集 #

首先是创建data.table格式的数据集代码：

id <-c(1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2)
date <- c("2022-11-01 22:22:01","2022-11-01 22:22:01","2022-11-18 12:48:16","2022-11-19 20:57:44","2022-11-19 20:57:44","2022-11-28 13:33:28","2022-11-29 19:24:28",
         "2022-11-29 19:24:28","2022-11-01 10:02:47","2022-11-01 10:02:47","2022-11-08 02:48:37","2022-11-08 02:48:37","2022-11-17 17:35:17","2022-11-17 17:35:17",
         "2022-11-22 12:30:20","2022-11-22 12:30:20","2022-11-30 09:47:45")
type <- c("aaa", "aaa", "bbb", "ccc", "aaa", "ccc", "aaa", "bbb", "bbb", "aaa", "bbb", "ccc", "bbb", "aaa", "ccc", "bbb", "ddd")
o_number <- c(NA, NA, NA, NA, 11, NA, NA, 12, NA, NA, NA, NA, NA, 13, NA, NA, 14)
total <- c(0, 0, 0, 0, 100, 0, 0, 200, 0, 0, 0, 0, 0, 300, 0, 0, 400)

df <- data.table(id,date,type, o_number, total)

需求说明 #

按id分组，针对每条存在o_number的记录，汇总该记录日期往前**30天（或10天）**内同id下的所有type列文本元素，用>连接；无o_number的记录对应字段为空，且需保留原数据集所有行结构。

现有代码问题分析 #

你尝试的dplyr代码存在以下问题：

1. 筛选条件无效：filter(date >= (date - days(30)) & (date - days(30)) <= date)是恒成立的表达式，无法筛选出目标日期范围的记录。
2. 分组逻辑错误：按id和o_number分组会将相同o_number的行聚合，但每个有o_number的行对应独立的日期区间，分组后无法匹配各自的范围。
3. 数据结构丢失：summarise会将分组后的行聚合，导致原数据集的行结构被破坏，无法保留所有原始记录。

df %>%
       filter(date >= (date - days(30)) &  (date - days(30)) <= date)  %>% 
       dplyr::group_by(id, o_number)  %>%
       dplyr::summarise(type_over_last_30days_per_id = paste(type, collapse = ">"))

正确解决方案 #

方法1：使用dplyr实现 #

先转换日期为时间格式，再按id分组，对每条有o_number的行筛选对应日期范围的type并拼接：

library(dplyr)
library(lubridate)

# 转换日期字符为时间格式
df <- df %>%
  mutate(date = ymd_hms(date))

# 生成目标字段
result_df <- df %>%
  group_by(id) %>%
  mutate(
    type_over_last_30days_per_id = ifelse(
      !is.na(o_number),
      # 筛选同id下日期在当前行日期前30天到当前日期的所有type，拼接成字符串
      paste(type[date >= (cur_data()$date - days(30)) & date <= cur_data()$date], collapse = " > "),
      # 无o_number的行填充空字符串
      ""
    )
  ) %>%
  ungroup()

# 查看结果
print(result_df)

方法2：使用data.table高效实现 #

利用data.table的非等连接特性，更高效地匹配日期范围：

library(data.table)
library(lubridate)

# 转换日期格式
df[, date := ymd_hms(date)]

# 对有o_number的行，匹配同id、日期在[date-30, date]的所有type并拼接
df[!is.na(o_number), 
   type_over_last_30days_per_id := paste(df[.SD, on = .(id, date >= date - days(30), date <= date), type], collapse = " > "),
   by = .(id, date, o_number)]

# 无o_number的行填充空字符串
df[is.na(o_number), type_over_last_30days_per_id := ""]

# 查看结果
print(df)

调整为10天范围的方法 #

只需将上述代码中的days(30)替换为days(10)即可实现10天范围的汇总。

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