如何基于列信息在R中修改数据集：ASFR字段批量置零处理

我有一个包含50多万名女性的数据集，示例数据如下：

library(tidyverse)

sample <- tibble( 
  iD = c(1),
  age = c(38), 
  child.age1 = c(13),
  child.age2 = c(9), 
  child.age3 = c(NA),  
  asfr15 = 0.0000, 
  asfr16 = 7.40444,  
  asfr17 = 42.87749,  
  asfr18 = 73.05321,
  asfr19 = 98.28869,
  asfr20 = 144.9568,
  asfr21 = 164.976,
  asfr22 = 180.280,
  asfr23 = 191.304,
  asfr24 = 198.4832,
  asfr25 = 202.0248,
  asfr26 = 202.1360,
  asfr27 = 200.3920,
  asfr28 = 197.6840,
  asfr29 = 193.7632,
  asfr30 = 187.6072,
  asfr31 = 179.5616,
  asfr32 = 170.5096,
  asfr33 = 160.8376,
  asfr34 = 150.4840,
  asfr35 = 139.3304,
  asfr36 = 127.7960,
  asfr37 = 115.0280,
  asfr38 = 100.7200,
  asfr39 = 85.6256,
  asfr40 = 182.4384,
  asfr41 = 178.412,
  asfr42 = 171.928, 
  asfr43 = 163.316,
  asfr44 = 152.9056,
  asfr45 = 141.0264,
  asfr46 = 128.008,
  asfr47 = 114.180,
  asfr48 = 99.872, 
  asfr49 = 85.4136
)

需求 #

• 用母亲年龄（age字段）减去每个孩子的年龄（child.age1、child.age2、child.age3），将计算结果±1对应的asfr字段置为0。例如：38-13=25，则需将asfr24、asfr25、asfr26置为0；
• 将母亲年龄（示例中为38）以上对应的asfr字段（asfr39至asfr49）全部置为0。

解决方案 #

步骤1：处理孩子年龄对应的ASFR置零 #

通过自定义函数生成需要置零的列名，再批量更新字段值，确保处理50万行数据的效率：

# 生成需要置零的asfr列名的函数
get_zero_cols <- function(mother_age, child_ages) {
  valid_child_ages <- child_ages[!is.na(child_ages)]
  birth_ages <- mother_age - valid_child_ages
  zero_ages <- unique(c(birth_ages - 1, birth_ages, birth_ages + 1))
  # 过滤出合法的asfr年龄范围（15-49）
  zero_ages <- zero_ages[zero_ages >= 15 & zero_ages <= 49]
  paste0("asfr", zero_ages)
}

# 批量处理每行数据，将对应asfr列置零
sample_processed <- sample %>%
  rowwise() %>%
  mutate(zero_cols = list(get_zero_cols(age, c(child.age1, child.age2, child.age3)))) %>%
  ungroup() %>%
  mutate(across(all_of(unlist(zero_cols)), ~0)) %>%
  select(-zero_cols)

步骤2：处理母亲年龄以上的ASFR置零 #

针对每行母亲年龄，将age+1到49对应的asfr字段全部置零：

sample_processed <- sample_processed %>%
  rowwise() %>%
  mutate(upper_zero_cols = list(paste0("asfr", (age + 1):49))) %>%
  ungroup() %>%
  mutate(across(all_of(unlist(upper_zero_cols)), ~0)) %>%
  select(-upper_zero_cols)

验证结果 #

查看处理后的ASFR字段，确认目标列已被置零：

sample_processed %>%
  select(starts_with("asfr"))

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