R语言：基于独立dataframe添加12个月回溯登录标识列

前置准备 #

使用lubridate处理日期、dplyr做数据转换，也可选用data.table实现大数据量场景下的高效运算：

# 基础方案依赖
library(lubridate)
library(dplyr)

第一步：统一日期格式 #

将两个表的日期字段转换为Date类型，避免字符格式比较出错：

# 转换活跃表的活跃月份字段
active_df <- active_df %>%
  mutate(MONTH = ymd(MONTH))

# 转换登录表的登录日期字段
login_df <- login_df %>%
  mutate(LOGIN = ymd(LOGIN))

第二步：计算标识列 #

按ID、所属区域匹配后，判断每个活跃月份往前推12个月的区间内是否存在对应登录记录：

result <- active_df %>%
  rowwise() %>%
  mutate(
    # 计算当前活跃月往前追溯12个月的起始边界
    start_date = MONTH %m-% years(1),
    # 匹配同ID同区域下落在时间区间内的登录记录，存在则标记为1
    LOGIN_IN_PAST_12_MONTHS = as.integer(
      nrow(login_df[login_df$ID == ID & login_df$LOCATION == LOCATION & 
                    login_df$LOGIN >= start_date & login_df$LOGIN <= MONTH, ]) > 0
    )
  ) %>%
  ungroup() %>%
  # 按需将无登录的标记替换为空，不需要可以删掉这行
  mutate(LOGIN_IN_PAST_12_MONTHS = ifelse(LOGIN_IN_PAST_12_MONTHS == 0, NA, LOGIN_IN_PAST_12_MONTHS)) %>%
  select(-start_date)

大数据量优化方案（data.table） #

如果数据量超过10万行，推荐用非等值连接优化性能：

library(data.table)
# 转换为data.table格式
setDT(active_df)
setDT(login_df)

# 新增12个月回溯起始日期字段
active_df[, start_date := MONTH %m-% years(1)]

# 非等值连接匹配后聚合判断
result <- login_df[active_df, on = .(ID, LOCATION, LOGIN >= start_date, LOGIN <= MONTH), 
                   .(ID, MONTH = i.MONTH, LOCATION = i.LOCATION, ACTIVE = i.ACTIVE, LOGIN = x.LOGIN)] %>%
  .[, .(LOGIN_IN_PAST_12_MONTHS = as.integer(sum(!is.na(LOGIN)) > 0)), by = .(ID, MONTH, LOCATION, ACTIVE)] %>%
  # 按需将无登录标记替换为空
  .[LOGIN_IN_PAST_12_MONTHS == 0, LOGIN_IN_PAST_12_MONTHS := NA]

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