如何用整洁R代码将FAM/VIC值匹配到对应目标新列

核心思路 #

利用tidyverse工具集，通过动态列赋值或长-宽格式转换，将每行中FAM/VIC的测量值匹配到对应的目标列中。

步骤1：构造示例数据集 #

先模拟符合需求的输入数据（包含样本列、测量列、目标列及待填充的目标列）：

library(tidyverse)

# 模拟输入数据
df <- tibble(
  sample = c("A1", "A2", "A3", "A4", "A5", "A6"),
  FAM = c(NA, 18.5, NA, 16.7, NA, 19.2),
  VIC = c(15.9, NA, 17.1, NA, 18.3, 17.4),
  target1 = c(NA, "ATP5B", NA, "GAPDH", NA, "EIF4A2"),
  target2 = c("GAPDH", NA, "EIF4A2", NA, "ATP5B", "ATP5B"),
  ATP5B = NA_real_,
  EIF4A2 = NA_real_,
  GAPDH = NA_real_
)

方法1：按行动态赋值（适合固定2组reporter-target的场景） #

直接按行处理，将target1对应FAM、target2对应VIC的值填充到目标列：

df_filled <- df %>%
  rowwise() %>%
  mutate(
    # 把FAM值赋值给target1对应的目标列
    !!sym(target1) := ifelse(!is.na(target1), FAM, !!sym(target1)),
    # 把VIC值赋值给target2对应的目标列
    !!sym(target2) := ifelse(!is.na(target2), VIC, !!sym(target2))
  ) %>%
  ungroup()

# 查看填充结果
df_filled

代码解释：

• rowwise()：强制按行处理，确保每行的目标列映射独立生效
• !!sym(col_name)：将字符串格式的列名转换为可操作的列对象，实现动态赋值
• ifelse判断：仅当目标列名不为空时，替换为对应测量值，否则保留原NA

方法2：长-宽格式转换（适合多组reporter-target的扩展场景） #

通过转换为长格式统一匹配，再转回宽格式完成填充，灵活性更强：

df_filled_alt <- df %>%
  # 将FAM/VIC转换为长格式，保留非NA的测量值
  pivot_longer(
    cols = c(FAM, VIC),
    names_to = "reporter",
    values_to = "value",
    values_drop_na = TRUE
  ) %>%
  # 匹配reporter对应的target：FAM对应target1，VIC对应target2
  mutate(target = case_when(
    reporter == "FAM" ~ target1,
    reporter == "VIC" ~ target2
  )) %>%
  # 过滤空的target记录
  filter(!is.na(target)) %>%
  # 转回宽格式，将target作为列名填充对应值
  pivot_wider(
    id_cols = sample,
    names_from = target,
    values_from = value
  ) %>%
  # 与原数据集合并，保留所有原始列
  right_join(df %>% select(-ATP5B, -EIF4A2, -GAPDH), by = "sample") %>%
  # 调整列顺序与原数据一致
  select(sample, FAM, VIC, target1, target2, ATP5B, EIF4A2, GAPDH)

最终结果示例 #

两种方法都会得到如下填充后的数据集：

# A tibble: 6 × 8
  sample   FAM   VIC target1 target2 ATP5B EIF4A2 GAPDH
  <chr>  <dbl> <dbl> <chr>   <chr>   <dbl>   <dbl> <dbl>
1 A1       NA   15.9 NA      GAPDH     NA      NA  15.9
2 A2      18.5   NA ATP5B   NA       18.5     NA  NA  
3 A3       NA   17.1 NA      EIF4A2    NA     17.1 NA  
4 A4      16.7   NA GAPDH   NA        NA      NA  16.7
5 A5       NA   18.3 NA      ATP5B    18.3     NA  NA  
6 A6      19.2  17.4 EIF4A2  ATP5B    17.4    19.2 NA  

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