使用dplyr基于多条件为my_id分组创建全局标记变量的问题

需要创建新变量new.var，规则为：按my_id分组，若该分组在2020年的rate为负，则分组内所有年份的新变量赋值为1，否则为0。当前使用mutate+if_else的代码仅能给2020年rate为负的行赋值1，无法覆盖整个分组，需修复。

当前错误代码 #

mydt %>% mutate(rate.sign = mutate( if_else (year == 2020 & rate < 0, 1 , 0)))

示例数据与期望效果 #

id_code variable  year  rate my_id     new.var
     <dbl> <chr>    <dbl> <dbl> <dbl>   <dbl>
 1     233 x         1950 NA        1    0
 2     233 x         1955  2.97     1    0
 ...
15     233 x         2020  0.81     1    0
18     233 x         1950  3.37     2    1
...
32     233 x         2020 -0.75     2    1

简化数据集 #

mydt = structure(list(id_code = c(233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 233, 
233), 
variable = c("x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", 
"x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x"), 
year = c(1950, 1955, 1960, 1965, 1970, 1975, 1980, 1985, 1990, 1995, 2000, 2005, 2010, 2015, 2020, 2025, 2030, 1950, 1955, 1960, 1965, 1970, 1975, 1980, 1985, 1990, 1995, 2000, 2005, 2010, 2015, 2020, 2025, 2030), 
rate = c(NA, 2.97, 2.98, 1.20, 2.01, 2.39, 2.28, 0.66, 0.66, 1.04, 0.96, 0.87, 0.86, 0.85, 0.81, 0.38, 0.58, 3.37, 2.93, 0, 2.02, 1.80, 2.37, 0, 0.72, 0.71, 1.33, 0.92, 1.06, -0.78, -0.82, -0.75, -0.45, -0.62), 
my_id = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)), 
class = c("grouped_df", "tbl_df", "tbl", "data.frame"), row.names = c(NA, -34L), groups = structure(list( id_code = 233, variable = "x", .rows = structure(list(1:34), ptype = integer(0), class = c("vctrs_list_of",  "vctrs_vctr", "list"))), row.names = c(NA, -1L), .drop = TRUE, class = c("tbl_df", "tbl", "data.frame")))

核心问题是未按my_id分组计算，导致仅单一行生效。以下两种方法可实现需求：

方法一：分组后批量赋值 #

直接按my_id分组，判断组内是否存在2020年rate为负的行，将结果应用到整个分组：

mydt %>%
  group_by(my_id) %>%
  mutate(new.var = as.integer(any(year == 2020 & rate < 0, na.rm = TRUE))) %>%
  ungroup() # 可选：不需要保留分组时取消分组

说明： #

• group_by(my_id)：按my_id对数据分组
• any(year == 2020 & rate < 0, na.rm = TRUE)：检查分组内是否存在符合条件的行，na.rm=TRUE忽略NA值干扰
• as.integer()：将逻辑值TRUE转为1，FALSE转为0，匹配需求的数值格式

方法二：先标记再合并 #

先提取2020年的分组标记，再通过左连接合并回原数据：

# 生成每个my_id的标记
id_mark <- mydt %>%
  filter(year == 2020) %>%
  mutate(new.var = as.integer(rate < 0)) %>%
  select(my_id, new.var)

# 合并标记到原数据
mydt %>%
  left_join(id_mark, by = "my_id")

说明： #

• 先筛选2020年数据，给每个my_id标记1或0
• 通过left_join将标记对应到原数据的每一行，自动覆盖整个分组

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