使用R语言reshape函数转长表为宽表时Bacteria2_Level未正常转换

问题说明 #

现有记录绵羊乳房感染情况的数据集，包含EweID、DateSampled、Sample、UdderHalf、Bacteria1_ID、Bacteria1_Level、Bacteria2_ID、Bacteria2_Level共8个字段、20行数据。尝试用R原生reshape()函数将长表转宽表，期望生成Bacteria2_Level.BC1、Bacteria2_Level.BC2这类按Sample拆分的列，但实际转换后Bacteria2_Level未被转成宽格式，仍保留原列结构。

原转换代码 #

MDFSO <- reshape(data=Data,
                 idvar= c("EweID","DateSampled", "UdderHalf"),          
                 v.names = c("Bacteria1_ID","Bacteria1_Level", "Bacteria2_ID", "Bacteria2_Level"),
                 timevar = "Sample",
                 direction="wide") 

数据重建代码 #

Data <- data.frame(matrix(ncol = 8, nrow = 20))
# 注：原代码此处存在笔误，将Data写成了DataN，已修正
colnames(Data) <- c('EweID', 'DateSampled', 'Sample', 'UdderHalf', 'Bacteria1_ID', 'Bacteria1_Level', 'Bacteria2_ID', 'Bacteria2_Level')

Data$EweID <- c(1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10)

Data$DateSampled <- as.Date(c("2021-10-13", "2021-10-13", "2021-10-20", "2021-10-20", "2021-10-27", "2021-10-27", "2021-11-03", "2021-11-03", "2021-11-10", "2021-11-10", "2021-11-17", "2021-11-17", "2021-11-24", "2021-11-24", "2021-12-01", "2021-12-01", "2021-10-13", "2021-10-13", "2021-10-20", "2021-10-20"))

Data$Sample <- c("BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1","BC2", "BC1")

Data$UdderHalf <- c("L","L","L","L","L","L","L","L","L","L","L","L","L","L","L","L","R","R","R", "R")

Data$Bacteria1_ID <- c("No Growth", "Staph Auerus", "Staph Equorum", "Staph Oralis", "No Growth","No Growth", "No Growth", "No Growth", "No Growth",NA, NA, NA, "Staph Sp", "Staph Auerus", "Staph Oralis", NA, NA, NA,"No Growth","No Growth")

Data$Bacteria1_Level <- c(NA, 3, 4, 2, NA, NA, NA, NA, NA, NA, NA, NA, 2, 1, 4, NA, NA, NA,NA,NA)

Data$Bacteria2_ID <- c("No Growth", "Staph Auerus", "Staph Sp", NA, NA, NA, "Staph Aureus", "No Growth", NA, "No Growth", "No Growth", "No Growth", "No Growth", "No Growth", NA, "Staph Sp", "Staph Aureus", NA, NA, NA )

Data$Bacteria2_Level <- c(NA, 4, 1, NA, NA, NA, 2, NA, NA, NA, NA, NA, NA, NA, NA, 2, 1, NA, NA, NA)

解决方案 #

问题根源 #

1. 原数据重建代码存在笔误：colnames(DataN)应为colnames(Data)，导致初始列名未正确绑定，影响reshape()函数的变量识别。
2. 原生reshape()函数在指定v.names时，若数据中存在缺失值或分组逻辑不清晰，可能出现部分变量转换失效的情况。

修正方案 #

方案1：修复数据并使用原生reshape()

先修正数据列名的笔误，再调用reshape()时可省略v.names参数（函数会自动识别非idvar和timevar的变量），确保所有目标变量被转换：

# 修正后的数据重建（同上述修正后的代码）
# ...

# 修正后的转换代码
MDFSO <- reshape(Data,
                 idvar = c("EweID", "DateSampled", "UdderHalf"),
                 timevar = "Sample",
                 direction = "wide")

执行后会生成Bacteria2_Level.BC1、Bacteria2_Level.BC2等符合预期的宽格式列。

方案2：使用tidyr包的pivot_wider()（更直观可靠）

原生reshape()逻辑较复杂，推荐使用tidyr包的pivot_wider()函数，语法更清晰，对缺失值的处理更稳定：

library(tidyr)

MDFSO_tidy <- pivot_wider(Data,
                          id_cols = c(EweID, DateSampled, UdderHalf),
                          names_from = Sample,
                          values_from = c(Bacteria1_ID, Bacteria1_Level, Bacteria2_ID, Bacteria2_Level))

该代码会直接生成Bacteria2_Level_BC1、Bacteria2_Level_BC2这类按Sample拆分的列，完全满足需求。

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