如何将R语言中的输入数据表转换为指定目标格式？

步骤说明 #

1. 提取元数据：从输入表中获取固定的Name和浓度值。
2. 标记实验重复：给每个实验行分配重复编号（Rep1、Rep2）。
3. 格式转换：通过长格式与宽格式的转换重组数据结构，将缺失值替换为-。

完整代码 #

library(dplyr)
library(tidyr)

# 输入数据
df <- data.frame(
  V1 = c("name", "Concentration", "Plate 1", "Plate 1", "Plate 1"),
  V2 = c("Arch", "Concentration", "WT", "WT", "KNO"),
  V3 = c(NA, 1000, 70960, 68670, 49270),
  V4 = c(NA, 500, 48340, 145300, 19530),
  stringsAsFactors = FALSE
)

# 提取固定Name值
name_val <- df$V2[df$V1 == "name"]

# 提取浓度与对应列的映射
conc_info <- df %>%
  filter(V1 == "Concentration") %>%
  select(V3, V4) %>%
  pivot_longer(everything(), names_to = "col", values_to = "Concentration") %>%
  mutate(col = paste0("V", row_number() + 2))

# 处理数据并转换格式
result <- df %>%
  filter(V1 == "Plate 1") %>%
  mutate(Rep = paste0("Rep", row_number())) %>%
  pivot_longer(cols = V3:V4, names_to = "col", values_to = "value") %>%
  left_join(conc_info, by = "col") %>%
  select(Treat = V2, Concentration, Rep, value) %>%
  pivot_wider(names_from = Rep, values_from = value) %>%
  mutate(Name = name_val,
         Rep1 = ifelse(is.na(Rep1), "-", as.character(Rep1)),
         Rep2 = ifelse(is.na(Rep2), "-", as.character(Rep2))) %>%
  select(Name, Concentration, Treat, Rep1, Rep2)

# 输出结果
print(result)

运行结果 #

Name Concentration Treat  Rep1    Rep2
1 Arch          1000    WT 70960   68670
2 Arch           500    WT 48340  145300
3 Arch          1000   KNO 49270       -
4 Arch           500   KNO 19530       -

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