R语言按分组条件有条件mutate指定行值的实现方法

实现目标 #

目标为对给定数据集按家庭分组，按规则填充母亲姓名字段，规则如下：

• 仅对*组内所有成员state字段值为"Monogamist"*的家庭分组执行填充逻辑
• 目标填充行：组内role字段为"Child"的行
• 取值规则：提取组内所有role为"Mother"的姓名，按year_of_birth升序排列后用逗号拼接，填充到对应Child行的mother_name字段
• 不符合上述条件的行/分组，mother_name字段统一填充为NA

示例初始数据集如下：

df <- tribble(
  ~family, ~sequence, ~role,     ~state,        ~name,     ~year_of_birth,
  "A",      1,       "Father",  "Monogamist",  "Adam",     1980,
  "A",      2,       "Mother",  "Monogamist",  "Sarah",    1981,
  "A",      3,       "Child",   "Monogamist",  "Omar",     2000,
  "A",      4,       "Child",   "Monogamist",  "Joseph",   2001,
  "B",      1,       "Father",  "Polygamist",  "Ali",      1990,
  "B",      2,       "Mother",  "Polygamist",  "Miriam",   1998,
  "B",      2,       "Child",   "Polygamist",  "Noah",     1992,
  "B",      3,       "Child",   "Polygamist",  "Jacob",    1998,
  "B",      4,       "Child",   "Polygamist",  "Layla",    2014,
  "C",      1,       "Father",  "Widower",     "Ibrahim",  2020,
  "C",      3,       "Child",   "Widower",     "Zakariya", 2021,
  "C",      4,       "Child",    "Widower",     "Kahlid",  2022,
)

问题复现与原因 #

原有写法硬编码按sequence == 2提取母亲姓名，遇到同组存在多个相同sequence值、或无对应sequence值的场景就会报错，错误代码与报错信息如下：

# 错误写法
df %>%
  group_by(family) %>% 
  mutate(mother_name = case_when(!sequence %in% c(1,2) ~ name[sequence == 2],TRUE ~ "")) 

Error in `mutate()`:
! Problem while computing `mother_name = case_when(...)`.
i The error occurred in group 2: family = "B".
Caused by error in `case_when()`:
! `!sequence %in% c(1, 2) ~ name[sequence == 2]` must be length 5 or one, not 2.
Run `rlang::last_error()` to see where the error occurred.

临时可运行的写法用了filter逻辑，会直接把不符合条件的分组整组删除，无法保留全量数据，仅能得到A组的结果：

df %>%
  group_by(family) %>% 
  filter(any(state == "Monogamist")) %>% 
  mutate(mother_name = case_when(!sequence %in% c(1,2) ~ name[sequence == 2],TRUE ~ "")) 

# A tibble: 4 x 7
# Groups:   family [1]
  family sequence role   state      name   year_of_birth mother_name
  <chr>     <dbl> <chr>  <chr>      <chr>          <dbl> <chr>      
1 A             1 Father Monogamist Adam            1980 ""         
2 A             2 Mother Monogamist Sarah           1981 ""         
3 A             3 Child  Monogamist Omar            2000 "Sarah"    
4 A             4 Child  Monogamist Joseph          2001 "Sarah"      

报错核心原因：

• case_when要求每个条件分支的返回值长度要么为1，要么和当前分组的行数一致，硬编码取sequence ==2的行时，B组有2个sequence=2的行，返回长度为2，和组内5行的长度不匹配
• 分组级判断条件没有提前计算为单值，直接写在case_when里会按行判断，无法实现「整组满足条件才生效」的逻辑

正确实现代码 #

先在分组内计算两个分组级常量（每个分组仅返回1个值），再逐行判断填充即可：

library(dplyr)
library(tibble)

df %>%
  group_by(family) %>%
  mutate(
    # 分组级判断：是否整组state都是Monogamist
    is_monogamist = all(state == "Monogamist"),
    # 分组级取值：所有母亲按出生年升序拼接为单个字符串
    mother_concat = paste(
      name[role == "Mother"][order(year_of_birth[role == "Mother"])],
      collapse = ", "
    ),
    # 逐行判断填充
    mother_name = case_when(
      is_monogamist & role == "Child" ~ mother_concat,
      TRUE ~ NA_character_
    )
  ) %>%
  # 删掉中间计算的辅助列
  select(-is_monogamist, -mother_concat) %>%
  ungroup()

运行结果 #

按规则运行后输出结果如下（注：原示例期望输出中B组为Polygamist，按规则不执行填充，对应mother_name均为NA，若需调整分组判断规则修改is_monogamist的判断逻辑即可）：

# A tibble: 12 × 7
   family sequence role   state      name     year_of_birth mother_name
   <chr>     <dbl> <chr>  <chr>      <chr>            <dbl> <chr>      
 1 A             1 Father Monogamist Adam              1980 NA         
 2 A             2 Mother Monogamist Sarah             1981 NA         
 3 A             3 Child  Monogamist Omar              2000 Sarah      
 4 A             4 Child  Monogamist Joseph            2001 Sarah      
 5 B             1 Father Polygamist Ali               1990 NA         
 6 B             2 Mother Polygamist Miriam            1998 NA         
 7 B             2 Child  Polygamist Noah              1992 NA         
 8 B             3 Child  Polygamist Jacob             1998 NA         
 9 B             4 Child  Polygamist Layla             2014 NA         
10 C             1 Father Widower    Ibrahim           2020 NA         
11 C             3 Child  Widower    Zakariya          2021 NA         
12 C             4 Child  Widower    Kahlid            2022 NA      

内容的提问来源于stack exchange，提问作者Mohamed Mostafa El-Sayyad