如何在R中基于家庭关系列添加母子关联的新列

数据集说明 #

我有一个包含约150万个体的数据集，通过household.ID字段区分不同家庭。其中relationship列以编码形式定义成员与户主的关系：

• code1：户主
• code2：户主配偶
• code3：户主子女
• code4：儿媳/女婿（gender=1为女婿，gender=2为儿媳）
• code5：户主孙辈
• code6：户主父母（按性别区分）
• code7：户主兄弟姐妹（按性别区分）
• code8：其他亲属
• code9：非亲属

样本数据集代码如下：

library(tidyverse)
sample <- tibble( 
  household.ID = c(12850540085,12850540085,12850540085,12850540085,11854478630,11854478630,11854478630,11854478630,11854478630,243851633,243851633,243851633,243851633,243851633,243851633 ,243851633,243851633,243851633,243851633,16523634,16523634,16523634,16523634,16525912,16525912,16540127,16540127,16598050,16598050,16611764,16611764,16611764,16643309,16643309,16652356,16652356,16652356,16672105,16672105,16672105,16672105, 14347606936,14347606936,14513544764,14513544764,14513544764,14513544764,14513544764 ),
  member.ID= c(12850560156, 12850560381, 12850560592,12850560795,23529759,23529760,23529761,23529762 ,23529763,243936326,243946420 ,243953196 ,243963749,243969681 ,243976774,243982427,243988959 ,243995522 ,244060815 ,16527193, 16529443, 16532250, 16534992,16527527, 16529230,16542499,16545263, 16616975, 16620223, 16633984,16642611,16650837,16646986,16650210, 16660335, 16665128,16668381,16676674, 16681528, 16685073,16687491, 14347619183,14347619282,14513560002,14513560086,14513560144,14513560214,14513560291),
  relationship = c(1,2,3,5,1,9,3,6,9,1,2,3,3,3,6,9,7,8,8,1,2,3,3,1,2,1,2,1,3,1,2,3,1,2,1,2,3,1,2,3,3,1,7,1,2,4,5,5),
  gender = c(1,2,2,1,1,2 ,2 ,2,1,1,2,2 ,2,2,1,2,1,2,2,1,2,1,2,1,2,1,2,1,1,1,2,1,1,2,1,2,2,1,2,1,1,1,1,1,2,2,1,1),
  age = c(64,58,38,6,42,42,1,76,19,61,59,35,33,29,96,28,45,43,15,49,47,19,18,38,28,78,75,66,21,56,52,28,51,58,74,68,27,56,43,23,13,45,42, 65 ,58 ,29,4 ,0),
  marriage.status= c(1,1,1,4,1,1,4,2,1,1,1,4,1,1,2,4,1,1,4,1,1,4,4,1,1,1,1,2,4,1,1,1,1,1,1,1,4,1,1,4,4,4,4,1,1,2,4,4),
  children.ever.born= c(NA,7,3,NA,NA,2,NA,9,NA,NA,3,NA,NA,NA,NA,NA,NA,1,NA ,NA,2,NA,NA,NA,NA,NA,6,NA,NA,NA,2,NA,NA,3,NA,3,NA,NA,2,NA,NA,NA,NA,NA,8,2,NA,NA),
  living.children  = c(NA,5,3,NA,NA,2,NA,6,NA,NA,3,NA,NA,NA,NA,NA,NA,1,NA,NA,2,NA,NA,NA,NA,NA,4,NA,NA,NA,2,NA,NA,3,NA,3,NA,NA,2,NA,NA,NA,NA,NA,4,2,NA,NA )
)

匹配规则 #

需按先明确关联、后处理多潜在母亲的顺序，为每个成员匹配母亲的member.ID，具体规则如下：

1. 明确母子关联：
   • code2（户主配偶）是code3（户主子女）的母亲；
   • 若户主为女性（gender=2），则code3是她的子女；
   • 女性code3是code4（儿媳/女婿）的母亲；
2. 孙辈匹配：若家庭无code4但有code5（户主孙辈），则code5属于户内有子女记录的code3；
3. 父母与兄弟姐妹关联：女性code6（户主父母）是户主（code1）和code7（户主兄弟姐妹）的母亲；
4. 其他亲属/非亲属匹配：对于code8（其他亲属）和code9（非亲属）的女性，若已婚且有living.children记录，户内同编码的子女按年龄相近原则匹配为其孩子。

实现代码 #

# 按家庭分组处理母子匹配
matched_data <- sample %>%
  group_by(household.ID) %>%
  mutate(
    # 初始化母亲ID列
    mother.ID = NA_integer_,
    
    # 规则1：处理明确的母子关联
    mother.ID = case_when(
      # code2是code3的母亲
      relationship == 3 & any(relationship == 2) ~ member.ID[relationship == 2],
      # 女性户主的code3是其子女
      relationship == 3 & gender[relationship == 1] == 2 ~ member.ID[relationship == 1],
      # 女性code3是code4的母亲
      relationship == 4 & any(relationship == 3 & gender == 2) ~ member.ID[relationship == 3 & gender == 2],
      TRUE ~ mother.ID
    ),
    
    # 规则3：女性code6是户主和code7的母亲
    mother.ID = case_when(
      (relationship == 1 | relationship == 7) & any(relationship == 6 & gender == 2) ~ member.ID[relationship == 6 & gender == 2],
      TRUE ~ mother.ID
    ),
    
    # 规则2：无code4时，code5匹配有子女的code3
    mother.ID = case_when(
      relationship == 5 & !any(relationship == 4) & any(relationship == 3 & !is.na(living.children)) ~ 
        member.ID[relationship == 3 & !is.na(living.children)],
      TRUE ~ mother.ID
    ),
    
    # 规则4：code8/code9已婚女性匹配同编码子女（按年龄相近）
    mother.ID = case_when(
      relationship %in% c(8,9) & gender == 2 & marriage.status == 1 & !is.na(living.children) ~ {
        # 筛选同编码且年龄更小的潜在子女
        candidates <- filter(cur_data(), relationship %in% c(8,9), age < cur_data()$age)
        if(nrow(candidates) > 0){
          # 取年龄差最小的成员作为子女
          candidates$age_diff <- abs(candidates$age - cur_data()$age)
          candidates$member.ID[which.min(candidates$age_diff)]
        } else {
          mother.ID
        }
      },
      TRUE ~ mother.ID
    )
  ) %>%
  ungroup()

# 查看匹配结果示例
head(matched_data %>% select(household.ID, member.ID, relationship, mother.ID))

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