在R中将含缺失时间点的dataframe转换为dim=c(3,10,2)数组

需求说明 #

现有包含record、time、V1、V2四列的R数据框，存在部分ID对应时间点缺失的情况，需要转换为维度为c(3,10,2)的数组，三个维度依次对应：

• 3个唯一ID：ID1、ID2、ID3
• 10个连续时间点：0到9
• 2个观测变量：V1、V2
  缺失时间点的变量值自动填充为NA。

示例原始数据 #

mydata<- structure(list(record = c("ID1", "ID1", "ID1", "ID1", "ID1", "ID1","ID1", "ID1", "ID1", "ID2", "ID2", "ID2", "ID2", "ID2", "ID2", "ID2", "ID2", "ID2", "ID3", "ID3", "ID3", "ID3", "ID3", "ID3", "ID3", "ID3", "ID3"), time = c(0, 1, 2, 3, 4, 5, 6, 7, 9, 0, 1, 2, 3, 4, 5, 6, 7, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8), V1 = c(33113, 33113, 150, 150, 150, 150, 275, 150, 150, 4897788, 33113, 95657, 1144, 642891, 518, 150, 73669, 403230, 450555, 33113, 2524740, 150, 3096225, 12628, 134896, 1202, 171157), V2 = c(29, 29, 29, 29, 29, 37, 28, 24, 29, 2495, 14, 14, 14, 25, 24, 29, 33, 30, 19, 29, 29, 29, 17, 20, 29, 20, 39)), row.names = c(NA, -27L), class = c("tbl_df", "tbl", "data.frame"), na.action = structure(28:183, .Names = c("28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", "124", "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", "136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147", "148", "149", "150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", "174", "175","176", "177", "178", "179", "180", "181", "182", "183"), class = "omit"))

方案1：tidyverse系列包实现（代码更简洁） #

先通过complete函数补全所有ID和时间点的组合，缺失值自动填充为NA，再按要求转换为数组：

library(tidyverse)

# 补全所有ID和时间点的组合，按ID、时间排序
full_data <- mydata %>%
  complete(record = c("ID1", "ID2", "ID3"), time = 0:9) %>%
  arrange(record, time)

# 转换为指定维度的数组
result_array <- array(
  data = as.matrix(full_data[, c("V1", "V2")]),
  dim = c(3, 10, 2),
  dimnames = list(
    ID = c("ID1", "ID2", "ID3"),
    time = as.character(0:9),
    variable = c("V1", "V2")
  )
)

结果验证 #

执行dim(result_array)返回[1] 3 10 2，符合维度要求；查看缺失值位置result_array["ID1", "8", ]、result_array["ID3", "9", ]均返回NA，符合预期。 #

方案2：基础R实现（无需加载额外包） #

通过expand.grid生成全量ID-时间组合，合并原始数据后转数组：

# 生成所有ID和时间点的全组合
full_grid <- expand.grid(record = c("ID1", "ID2", "ID3"), time = 0:9)
# 左合并原始数据，缺失值自动填NA
full_data <- merge(full_grid, mydata, by = c("record", "time"), all.x = TRUE)
# 按ID、时间排序保证维度顺序正确
full_data <- full_data[order(full_data$record, full_data$time), ]

# 转换为数组
result_array <- array(
  data = as.matrix(full_data[, c("V1", "V2")]),
  dim = c(3, 10, 2),
  dimnames = list(
    ID = c("ID1", "ID2", "ID3"),
    time = as.character(0:9),
    variable = c("V1", "V2")
  )
)

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