无timevar且多id变量的DataFrame长转宽问题

我遇到的需求是将长格式数据集转成宽格式，但存在两个难点：一是没有现成的timevar，二是包含多个ID变量（ID、Location、Time）。

数据集示例 #

ID Location Time Y
1001 A      T1   1
1001 A      T1   2
1001 B      T1   7
1001 C      T1   3
1001 C      T1   3
1001 C      T1   3
1001 D      T1   1
1001 D      T1   4
1001 A      T2   6
1001 B      T2   3
1001 B      T2   3
1001 B      T2   3
1001 C      T2   4
1001 C      T2   4
1001 C      T2   4
1001 C      T2   4
1001 D      T2   5
1001 D      T2   5
1001 D      T2   5
1001 D      T2   5
1001 D      T2   5

可复现代码 #

ID <- c(1001, 1001, 1001, 1001, 1001, 
        1001, 1001, 1001, 1001, 1001, 
        1001, 1001, 1001, 1001, 1001, 
        1001, 1001, 1001, 1001, 1001)
Location <- c ("A", "A", "B", "C", "C", 
               "C", "D", "D", "A", "B", 
               "B", "B", "C", "C", "C", 
               "C", "D", "D", "D", "D")
Time <- c ("T1", "T1", "T1", "T1", "T1",
           "T1", "T1", "T1", "T2", "T2", 
           "T2", "T2", "T2", "T2", "T2", 
           "T2", "T2", "T2", "T2", "T2" )
Y <- c (1, 2, 7, 3, 3, 
        3, 1, 4, 6, 3, 
        3, 3, 4, 4, 4, 
        4, 5, 5, 5, 5)
data <- data.frame (ID, Location, Time, Y)

期望输出格式 #

ID Location Time Y1 Y2 Y3 Y4
1001 A      T1   1  2  NA NA
1001 B      T1   7  NA NA NA
1001 C      T1   3  3  3  NA
1001 D      T1   1  4  NA NA
1001 A      T2   6  NA NA NA
1001 B      T2   3  3  3  NA
1001 C      T2   4  4  4  4 
1001 D      T2   5  5  5  5 

我的尝试代码（存在问题） #

data$uniqid <- with(data, ave(as.character(c ("ID", "Location", "Time")), c ("ID", "Location", "Time"), FUN = seq_along))
reshape(data, idvar = c ("ID", "Location", "Time"), timevar = "uniqid", direction = "wide")

解决方案 #

问题出在生成uniqid的代码上：ave函数的第一个参数需要是长度与数据集行数一致的向量，而不是仅3个元素的字符向量。以下是两种可行的解决方法：

方法1：修复基础reshape函数的用法 #

# 正确生成组内序号作为timevar：按ID、Location、Time分组，每组内生成递增序号
data$uniqid <- with(data, ave(seq_along(Y), list(ID, Location, Time), FUN = seq_along))
# 使用reshape转宽
result <- reshape(data, idvar = c("ID", "Location", "Time"), timevar = "uniqid", direction = "wide")
# 重命名列名，把Y.1/Y.2格式改为Y1/Y2
colnames(result) <- gsub("Y\\.", "Y", colnames(result))

方法2：使用tidyverse包（更简洁） #

用pivot_wider可以自动处理组内序号生成，无需手动构建timevar：

library(tidyverse)

result <- data %>%
  group_by(ID, Location, Time) %>%
  mutate(uniqid = row_number()) %>%
  pivot_wider(names_from = uniqid, values_from = Y, names_prefix = "Y") %>%
  ungroup()

两种方法都能生成符合要求的宽格式数据，缺失值自动填充为NA。

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