如何在R中计算数据集内每位女性的子女数量

需求说明 #

处理给定的家庭个体数据集，完成两项任务：

1. 仅保留女性个体数据
2. 根据个体与户主的关系编码规则，计算每位女性的生育子女数（子女数由关系字段字母后的最大数字决定，而非统计数据中的观测数量）

数据集变量说明 #

• HouseholdID：家庭唯一标识符
• IndividualID：家庭内个体的唯一编号
• Relationshiptothehouseholdhead：个体与户主的关系编码，规则如下：
  • "A"：户主本人
  • "B"：户主配偶
  • "C"：户主父亲
  • "D"：户主母亲
  • 户主子女及配偶："E1"代表户主第1个子女，"E2"代表第2个子女，以此类推；"F1"代表E1的配偶，"F2"代表E2的配偶，以此类推
  • 孙辈及配偶："G11"代表E1的第1个孩子，"G12"代表E1的第2个孩子，"G21"代表E2的第1个孩子，以此类推；"H11"代表G11的配偶，以此类推
• Age：个体年龄
• Gender：个体性别，取值为"male"或"female"
• Income：个体收入

原始数据集 #

数据预览 #

HouseholdID IndividualID Relationshiptothehouseholdhead  Age Gender  Income
    <dbl>  <dbl> <chr>        <dbl> <chr> <dbl>
 1      1      1 C               80 male      150
 2      1      2 D               81 female      120
 3      1      3 A               60 male      630
 4      1      4 B               59 female      500
 5      1      5 E3              35 male      380
 6      1      6 F3              30 female      220
 7      1      7 E5              33 female      170
 8      1      8 F5              30 male      160
 9      1      9 G32             20 female      290
10      1     10 G51             15 female      200
11      1     11 G52             12 female      100
12      1     12 G55              8 male       80
13      2      1 A               58 male      380
14      2      2 B               55 female      220
15      2      3 E1              35 male      170
16      2      4 F1              37 female      160
17      2      5 E2              33 male      290
18      2      6 F2              30 female      110
19      2      7 G21             17 female      210
20      2      8 G22             15 female      750
21      2      9 G23             12 female      350

数据集结构（R格式） #

data = structure(list(HouseholdID = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2), IndividualID = c(1, 2, 3, 4, 
5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9), Relationshiptothehouseholdhead = c("C", 
"D", "A", "B", "E3", "F3", "E5", "F5", "G32", "G51", "G52", "G55", 
"A", "B", "E1", "F1", "E2", "F2", "G21", "G22", "G23"), Age = c(80, 
81, 60, 59, 35, 30, 33, 30, 20, 15, 12, 8, 58, 55, 35, 37, 33, 
30, 17, 15, 12), Gender = c("male", "female", "male", "female", 
"male", "female", "female", "male", "female", "female", "female", 
"male", "male", "female", "male", "female", "male", "female", 
"female", "female", "female"), Income = c(150, 120, 630, 500, 
380, 220, 170, 160, 290, 200, 100, 80, 380, 220, 170, 160, 290, 
110, 210, 750, 350)), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -21L))

期望输出结果 #

HouseholdID IndividualID  Age Gender  Income  Numofkids
1          2     81 female    120          1
1          4     59 female    500          5
1          6     30 female    220          2
1          7     33 female    170          3
1          9     20 female    290          0
1         10     15 female    200          0
1         11     12 female    100          0
2          2     55 female    220          2
2          4     37 female    160          0
2          6     30 female    110          3
2          7     17 female    210          0
2          8     15 female    750          0
2          9     12 female    350          0

（注：修正了原期望结果中IndividualID=9的年龄错误，原始数据中该个体年龄为20）

解决方案代码 #

使用dplyr和stringr包实现需求，代码如下：

library(dplyr)
library(stringr)

# 处理数据
result <- data %>%
  # 筛选女性个体
  filter(Gender == "female") %>%
  # 按家庭分组处理关系编码
  group_by(HouseholdID) %>%
  mutate(
    # 提取关系编码的字母前缀
    rel_prefix = str_extract(Relationshiptothehouseholdhead, "^[A-Z]"),
    # 提取关系编码的数字部分
    rel_num = str_extract(Relationshiptothehouseholdhead, "\\d+"),
    # 分情况计算子女数
    Numofkids = case_when(
      # 户主配偶(B)：取家庭内E开头编码的最大数字
      rel_prefix == "B" ~ max(as.integer(str_extract(Relationshiptothehouseholdhead[startsWith(Relationshiptothehouseholdhead, "E")], "\\d+")), na.rm = TRUE),
      # 户主母亲(D)：子女数固定为1（户主为其子女）
      rel_prefix == "D" ~ 1,
      # 户主子女(E开头)：取对应G开头编码的第二位数字最大值，无则为0
      rel_prefix == "E" ~ {
        target_g = paste0("G", rel_num)
        g_nums = str_sub(str_extract(Relationshiptothehouseholdhead[startsWith(Relationshiptothehouseholdhead, target_g)], "\\d+"), 2)
        max(as.integer(g_nums), na.rm = TRUE) %>% replace_na(0)
      },
      # 子女配偶(F开头)：对应其配偶(E开头)的子女数，无则为0
      rel_prefix == "F" ~ {
        target_g = paste0("G", rel_num)
        g_nums = str_sub(str_extract(Relationshiptothehouseholdhead[startsWith(Relationshiptothehouseholdhead, target_g)], "\\d+"), 2)
        max(as.integer(g_nums), na.rm = TRUE) %>% replace_na(0)
      },
      # 孙辈及其他女性：子女数为0
      TRUE ~ 0
    )
  ) %>%
  # 保留目标列并取消分组
  select(HouseholdID, IndividualID, Age, Gender, Income, Numofkids) %>%
  ungroup()

# 输出结果
print(result)

代码说明 #

• filter：筛选出所有女性个体
• str_extract/str_sub：提取关系编码中的字母前缀和数字信息，用于匹配对应亲属关系
• case_when：针对不同关系类型制定子女数计算规则，覆盖所有女性的关系场景
• 分组处理确保每个家庭内的关系匹配仅在本家庭范围内进行

内容的提问来源于stack exchange，提问作者X.Jun