R中Pivot Wider指定列：分组添加父母信息列失败求助

原始DataFrame #

mydata <- tibble(
  ID = c("A123", "A123", "A123", "A125", "A125", "A125", "A129", "A129"),
  name = c("Bob", "Alice", "dani", "Tom", "Maria", "jack", "cook", "Victoria"),
  age = c(10, 35, 40, 8, 30, 32, 14, 45),
  qual = c("p", "p", "q", "q", "q", "p", "p", "p"),
  school = c("a", "b", "c", "a", "a", "a", "d", "d"),
  relation = c("child", "mother", "father", "child", "mother", "father", "child", "mother")
)

期望输出 #

ID  name    age qual    school  relation    mom_name    mom_age father_name father_age
A123    Bob 10  p   a   child   Alice   35  dani    40
A123    Alice   35  p   b   mother  NA  NA  NA  NA
A123    dani    40  q   c   father  NA  NA  NA  NA
A125    Tom 8   q   a   child   Maria   30  NA  NA
A125    Maria   30  q   a   mother  NA  NA  NA  NA
A125    jack    32  p   a   father  NA  NA  NA  NA
A129    cook    14  p   d   child   Victoria    45  NA  NA
A129    Victoria    45  p   d   mother  NA  NA  NA  NA

尝试的代码（未达预期） #

mydata_result <- mydata %>%
  group_by(ID, qual) %>%
  mutate(
    moms_name = ifelse(relation == "mother", name, "NA"),
    moms_age = ifelse(relation == "mother", age, "NA"),
    fathers_name = ifelse(relation == "father", name, "NA"),
    fathers_age = ifelse(relation == "father", age, "NA")
  ) %>%
  fill(moms_name, moms_age, fathers_name, fathers_age, .direction = "down")

正确实现方法 #

原代码的问题在于：

• 按ID+qual分组错误，比如A123的父亲qual为q，和孩子的p不属于同一组，导致无法匹配到父亲信息；
• 用字符串"NA"替代缺失值，而非R原生的NA；
• fill的逻辑无法精准只给child行赋值，还会污染父母行的记录。

正确思路是先提取每个ID对应的父母信息，再关联回原数据，最后仅保留child行的父母信息：

library(dplyr)
library(tidyr)

# 提取每个ID的父母信息，转成宽格式
parent_info <- mydata %>%
  filter(relation %in% c("mother", "father")) %>%
  pivot_wider(
    id_cols = ID,
    names_from = relation,
    values_from = c(name, age),
    names_glue = "{str_replace(relation, 'mother', 'mom')}_{.value}"
  )

# 关联原数据，仅给child行保留父母信息，其余行设为NA
mydata_result <- mydata %>%
  left_join(parent_info, by = "ID") %>%
  mutate(
    across(c(mom_name, mom_age, father_name, father_age),
           ~ifelse(relation == "child", ., NA))
  )

# 查看结果
print(mydata_result)

代码说明 #

1. 提取父母信息：从原数据筛选出父母记录，用pivot_wider将每个ID的母亲/父亲的姓名、年龄转成单独列，通过names_glue把列名改成mom_name这类符合期望的格式；
2. 关联原数据：用left_join按ID把父母信息合并到原数据的每一行；
3. 清理无关行：通过mutate+across，仅保留relation == "child"的行的父母信息，其余行的父母列设为NA。

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