R DataFrame按人员分组检测隐藏重复模式：匹配排名变化规则的人员筛选

实现思路 #

• 按person分组，提取每个人所有有记录的年份升序排序后，为每个年份匹配相邻的下一个记录年份
• 对每一条记录，匹配同一个人同一个位置的其他记录，筛选满足：当前记录rank=1、匹配记录的年份等于当前记录的下一个年份、匹配记录的rank不等于1的情况
• 只要存在任意一条符合上述条件的记录，对应person标记为1，否则标记为0

完整代码 #

# 加载依赖包
library(dplyr)

# 构造示例数据
df <- tibble(
  person = c("Harry","Harry","Harry","Harry","Peter","Peter","Lily","Lily","Lily","Lily","Lily","Sam","Sam","Sam","Sam","Sam","Sam","Sam"),
  year = c(2002,2006,2006,2006,2001,2002,2005,2007,2008,2011,2011,2005,2007,2008,2008,2008,2011,2011),
  location = c("Los Angeles","Boston","Los Angeles","Chicago","New York","New York","Springfield","New York","Boston","Chicago","New York","Springfield","New York","Boston","Springfield","New York","Chicago","Springfield"),
  rank = c(1,1,2,3,1,1,1,1,1,1,2,1,1,1,2,3,1,2)
)

# 统计处理
result <- df %>%
  # 为每个年份匹配同一个人下一个有记录的年份
  group_by(person) %>%
  mutate(next_year = lead(sort(unique(year)), 1)[match(year, sort(unique(year)))]) %>%
  ungroup() %>%
  # 自连接匹配同人员同位置的所有记录
  inner_join(
    df %>% select(person, location, check_year = year, check_rank = rank),
    by = c("person", "location")
  ) %>%
  # 筛选符合要求的记录
  filter(
    rank == 1,
    check_year == next_year,
    check_rank != 1
  ) %>%
  # 标记符合条件的人员
  distinct(person) %>%
  mutate(`yes/no` = 1) %>%
  # 补全所有人员，未匹配到的标记为0
  right_join(tibble(person = unique(df$person)), by = "person") %>%
  mutate(`yes/no` = coalesce(`yes/no`, 0)) %>%
  arrange(person)

# 输出结果
print(result)

输出结果 #

运行上述代码后，result的内容如下，与要求的输出格式完全一致：

# A tibble: 4 × 2
  person `yes/no`
  <chr>     <dbl>
1 Harry         1
2 Lily          0
3 Peter         0
4 Sam           1

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