如何基于双分类列与双数据列将DataFrame转为宽格式并优化列名

方法一：使用tidyr的pivot_wider（推荐，tidyverse生态） #

先提取Testphase的最后一个字符作为分组标识，再通过pivot_wider实现宽格式转换，同时指定列名拼接规则：

# 加载依赖包
library(tidyverse)

# 构建原始数据框
Testphase <- c("BG15_H_D_X","BG15_H_D_X","BG15_H_D_X","BG15_H_D_X","BG15_H_D_Y","BG15_H_D_Y","BG15_H_D_Y","BG15_H_D_Y")
RPM <- c("100","100","200","200","100","100","200","200")
Angle <- c("15","30","15","30","15","30","15","30")
Data1 <- c(1.17,0.65,0.56,0.49,1.14,0.86,0.53,0.72)
Data2 <- c(0.41,0.57,0.56,0.45,0.46,0.4,0.73,0.51)
df <- data.frame(Testphase, RPM, Angle, Data1, Data2)

# 提取Testphase最后一个字母作为分组列
df <- df %>%
  mutate(Group = substr(Testphase, nchar(Testphase), nchar(Testphase)))

# 转换为宽格式并指定列名规则
df_wide <- df %>%
  pivot_wider(
    id_cols = c(RPM, Angle),       # 保留的双ID列
    names_from = Group,            # 用于生成列名后缀的分组列
    values_from = c(Data1, Data2), # 需要转宽的数值列
    names_glue = "{.value}.{Group}"# 列名拼接规则：原数值列名.分组后缀
  )

# 查看结果
print(df_wide)

方法二：使用reshape2的melt+dcast #

如果习惯reshape2包的工作流，可以先转成长格式，处理分组标识后再转宽：

# 加载依赖包
library(reshape2)

# 构建原始数据框（同上，可省略重复代码）
# ...

# 将数据转成长格式
df_long <- melt(df, id.vars = c("Testphase", "RPM", "Angle"), variable.name = "DataCol")

# 提取Testphase最后一个字母
df_long$Group <- substr(df_long$Testphase, nchar(df_long$Testphase), nchar(df_long$Testphase))

# 转换为宽格式
df_wide <- dcast(df_long, RPM + Angle ~ DataCol + Group, value.var = "value")

# 查看结果
print(df_wide)

核心要点说明 #

• 提取后缀：substr(x, nchar(x), nchar(x))可精准获取字符串最后一个字符，适配任意长度的Testphase值。
• 双ID列处理：在pivot_wider的id_cols参数或dcast的公式左侧，同时指定RPM和Angle即可实现按这两列分组转宽。
• 列名控制：通过names_glue（tidyr）或公式拼接（reshape2），直接生成DataX.后缀的目标列名格式。

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