如何将非全列长格式的R数据框转换为宽格式？

原始数据 #

原始长格式数据如下：

olddata_long <- data.frame(
  A = c("X1", "X1", "X2", "X2"),
  B = c("Y1", "Y1", "Y2", "Y2"),
  C = c("Z1", "Z1", "Z2", "Z2"),
  SAMPLE_ID = c("SAMPLE1", "SAMPLE2", "SAMPLE1", "SAMPLE2"),
  `1` = c("G1", "G2", "G3", "G4"),
  `2` = c("H1", "H2", "H3", "H4"),
  `3` = c("J1", "J2", "J3", "J4"),
  `4` = c("K1", "K2", "K3", "K4"),
  `5` = c("L1", "L2", "L3", "L4")
)

目标格式 #

需转换为以下宽格式：将每个SAMPLE_ID对应的1-5列数据拼接为列名=值;格式的字符串，作为对应样本列的内容：

A   B   C    SAMPLE1                      SAMPLE2
X1  Y1  Z1   1=G1;2=H1;3=J1;4=K1;5=L1     1=G2;2=H2;3=J2;4=K2;5=L2
X2  Y2  Z2   1=G3;2=H3;3=J3;4=K3;5=L3     1=G4;2=H4;3=J4;4=K4;5=L4

尝试过的无效方法 #

使用spread直接转换多列，无法满足需求：

data_wide <- spread(olddata_long, SAMPLE_ID, c(1,2,3,4,5))

解决方案 #

高效方案（微基准测试最快） #

先按行拼接1-5列的字符串，再转换为宽格式，依赖dplyr和tidyr包：

library(dplyr)
library(tidyr)

olddata_long %>%
  rowwise() %>%
  mutate(
    value = paste(paste0(c(1:5), "=", c_across(`1`:`5`)), collapse = ";")
  ) %>%
  ungroup() %>%
  select(A, B, C, SAMPLE_ID, value) %>%
  pivot_wider(names_from = SAMPLE_ID, values_from = value)

其他可行方案（供参考） #

• 方案1

library(tidyr)
library(dplyr)

olddata_long %>%
  pivot_longer(cols = `1`:`5`, names_to = "key", values_to = "val") %>%
  mutate(combined = paste(key, val, sep = "=")) %>%
  group_by(A, B, C, SAMPLE_ID) %>%
  summarise(value = paste(combined, collapse = ";"), .groups = "drop") %>%
  pivot_wider(names_from = SAMPLE_ID, values_from = value)

• 方案2

library(data.table)

setDT(olddata_long)[, 
  value := do.call(paste, c(Map(paste, names(.SD), .SD, sep = "="), sep = ";")), 
  .SDcols = `1`:`5`, 
  by = .(A, B, C, SAMPLE_ID)
][, .(value = value[1]), by = .(A, B, C, SAMPLE_ID)] %>%
  dcast(A + B + C ~ SAMPLE_ID, value.var = "value")

微基准测试结果 #

在13MB真实数据集上的测试结果（单位：毫秒，重复20次）：

min      lq       mean     median   uq       max   neval
MrFlick   161.9813 229.5798 256.5784 240.3421 250.6077 511.7215    20
stefan    108.9270 120.2335 160.4594 174.6441 185.0129 196.7983    20
TarJae    149.7109 163.1959 226.3037 207.8249 238.0539 420.1584    20

内容的提问来源于stack exchange，提问作者Gabriel G.