基于处理组检测占比生成列及数据聚合的R语言需求

原始数据 #

data <- structure(list(animal.ID = c(1, 1, 1, 1, 2, 
2, 2, 2, 2, 3, 3, 3, 4), sex = c("m", "m", "m", "m", "f", "f", "f", "f", "f", "f", "f", "m"), treatment = c("A", "A", "B", "C", "C", 
"C", "C", "C", "A", "A","NA", "NA"), conflict = c("0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "1", "1")), class = "data.frame", row.names = c(NA, -12L))

需求说明 #

• 为每个treatment生成新列，计算每个唯一animal.ID的检测记录在对应treatment中的占比（如animal.ID 1的prop.A为0.50，因其50%的检测记录属于treatment A）；
• 生成match列，标记发生conflict的动物是否同时在任意treatment中有检测记录；
• 生成detection.frequency列，统计每个animal.ID的出现次数；
• 聚合dataframe，确保无重复animal.ID，保留sex等原有列；
• 仅含NA作为treatment的动物，其所有占比列赋值为0。

期望输出 #

data <- structure(list(animal.ID = c(1, 2,3,4), sex = c("m", "f", "f", "m"), detection.frequency = c(4, 4, 3, 1), match = c(0, 0, 1, 0), prop.A = c(".50","0","1","0"), prop.B = c(".25","0","0","0"), prop.C = c(".25","1","0","0")), class = "data.frame", row.names = c(NA, -4L))

解决方案代码 #

library(tidyverse)

# 数据处理流程
result <- data %>%
  # 将字符串"NA"转为R原生NA值
  mutate(treatment = ifelse(treatment == "NA", NA, treatment)) %>%
  group_by(animal.ID) %>%
  # 提取基础信息：性别、检测次数、是否有冲突、是否有有效treatment
  mutate(
    sex = first(sex),
    detection.frequency = n(),
    has_conflict = any(conflict == "1"),
    has_valid_treatment = any(!is.na(treatment))
  ) %>%
  # 统计各treatment的记录数并计算占比
  count(treatment, .drop = FALSE) %>%
  mutate(prop = n / detection.frequency) %>%
  # 转宽格式生成各treatment占比列
  pivot_wider(
    id_cols = c(animal.ID, sex, detection.frequency, has_conflict, has_valid_treatment),
    names_from = treatment,
    values_from = prop,
    names_prefix = "prop.",
    values_fill = 0
  ) %>%
  # 对仅含NA treatment的动物，将所有占比列设为0
  mutate(across(starts_with("prop."), ~ ifelse(!has_valid_treatment, 0, .x))) %>%
  # 生成match列并格式化占比字符串
  mutate(
    match = as.integer(has_conflict & has_valid_treatment),
    across(starts_with("prop."), ~ sprintf("%.2f", .x) %>% str_remove("^0"))
  ) %>%
  # 整理列顺序并移除辅助列
  select(animal.ID, sex, detection.frequency, match, starts_with("prop.")) %>%
  ungroup()

# 查看最终结果
result

代码说明 #

1. 先统一处理字符串"NA"为R原生NA，避免后续逻辑判断出错；
2. 按animal.ID分组后，一次性计算检测次数、冲突标记、有效treatment标记等基础信息；
3. 通过count+pivot_wider快速生成各treatment的占比列，缺失的treatment自动填充0；
4. 针对仅含NA treatment的动物，批量重置所有占比列为0；
5. 生成match列的逻辑：同时满足"有冲突"和"有有效treatment记录"则标记为1，否则为0；
6. 格式化占比字符串为期望的格式（如0.50转为".50"），最后整理列顺序得到目标结果。

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