按idPerson与idAppt分组生成符合条件的date2列（R语言）

原始数据 #

现有数据框df：

idPerson idAppt decision date      
 1 A             1 a        2021-09-10
 2 A             1 b        2021-09-11
 3 A             1 c        2021-09-12
 4 A             1 d        2021-09-13
 5 A             2 a        2021-09-20
 6 A             2 b        2021-09-21
 7 A             3 a        2021-09-10
 8 A             3 b        2021-09-11
 9 B             1 a        2021-09-10
10 B             1 b        2021-09-11
11 B             1 c        2021-09-12
12 B             1 d        2021-09-13
13 B             2 a        2021-09-11
14 B             2 b        2021-09-12
15 B             3 a        2021-09-14
16 B             3 b        2021-09-15

数据结构定义：

df <- structure(list(idPerson = c("A", "A", "A", "A", "A", "A", "A", 
"A", "B", "B", "B", "B", "B", "B", "B", "B"), idAppt = c(1L, 
1L, 1L, 1L, 2L, 2L, 3L, 3L, 1L, 1L, 1L, 1L, 2L, 2L, 3L, 3L), 
    decision = c("a", "b", "c", "d", "a", "b", "a", "b", "a", 
    "b", "c", "d", "a", "b", "a", "b"), date = structure(c(18880, 
    18881, 18882, 18883, 18890, 18891, 18880, 18881, 18880, 18881, 
    18882, 18883, 18881, 18882, 18884, 18885), class = "Date")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -16L))

需求说明 #

按idPerson和idAppt分组生成date2列，规则如下：

• 若当前（idPerson × idAppt）组中decision == "a"的日期，晚于同一idPerson下其他任意idAppt组中decision == "d"的日期，则date2取该用户所有d决策的日期；
• 不满足上述条件的组，date2取该idPerson对应的最早日期。

期望结果 #

idPerson idAppt decision date       date2     
 1 A             1 a        2021-09-10 2021-09-10
 2 A             1 b        2021-09-11 2021-09-10
 3 A             1 c        2021-09-12 2021-09-10
 4 A             1 d        2021-09-13 2021-09-10
 5 A             2 a        2021-09-20 2021-09-13
 6 A             2 b        2021-09-21 2021-09-13
 7 A             3 a        2021-09-10 2021-09-10
 8 A             3 b        2021-09-11 2021-09-10
 9 B             1 a        2021-09-10 2021-09-10
10 B             1 b        2021-09-11 2021-09-10
11 B             1 c        2021-09-12 2021-09-10
12 B             1 d        2021-09-13 2021-09-10
13 B             2 a        2021-09-11 2021-09-10
14 B             2 b        2021-09-12 2021-09-10
15 B             3 a        2021-09-14 2021-09-13
16 B             3 b        2021-09-15 2021-09-13

期望结果的数据结构：

EO <- structure(list(idPerson = c("A", "A", "A", "A", "A", "A", "A", 
"A", "B", "B", "B", "B", "B", "B", "B", "B"), idAppt = c(1L, 
1L, 1L, 1L, 2L, 2L, 3L, 3L, 1L, 1L, 1L, 1L, 2L, 2L, 3L, 3L), 
    decision = c("a", "b", "c", "d", "a", "b", "a", "b", "a", 
    "b", "c", "d", "a", "b", "a", "b"), date = structure(c(18880, 
    18881, 18882, 18883, 18890, 18891, 18880, 18881, 18880, 18881, 
    18882, 18883, 18881, 18882, 18884, 18885), class = "Date"), 
    date2 = c("2021-09-10", "2021-09-10", "2021-09-10", "2021-09-10", 
    "2021-09-13", "2021-09-13", "2021-09-10", "2021-09-10", "2021-09-10", 
    "2021-09-10", "2021-09-10", "2021-09-10", "2021-09-10", "2021-09-10", 
    "2021-09-13", "2021-09-13")), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame"))

实现代码 #

使用dplyr包完成分组计算：

library(dplyr)

result <- df %>%
  # 按用户分组，计算全局信息
  group_by(idPerson) %>%
  mutate(
    person_min_date = min(date),
    d_dates = list(unique(date[decision == "d"]))
  ) %>%
  # 按用户+预约分组，计算组内条件
  group_by(idPerson, idAppt) %>%
  mutate(
    a_date = date[decision == "a"][1],
    # 判断当前组a日期是否晚于其他组的d日期
    condition = any(a_date > setdiff(unlist(d_dates), date[decision == "d"][1])),
    # 赋值date2
    date2 = ifelse(condition, as.character(date[decision == "d"][1]), as.character(person_min_date))
  ) %>%
  ungroup() %>%
  # 移除中间变量
  select(-person_min_date, -d_dates, -a_date, -condition)

# 验证结果是否与期望一致
all.equal(result, EO)

代码解释 #

1. 用户级分组：计算每个用户的最早日期，以及该用户所有decision == "d"的日期集合；
2. 用户+预约级分组：提取当前组的decision == "a"日期，判断是否晚于其他预约组的d日期；
3. 赋值逻辑：满足条件时取用户的d日期，否则取用户最早日期；
4. 结果验证：用all.equal检查输出与期望结果是否一致。

内容的提问来源于stack exchange，提问作者Maël