build an array with two columns in R-CodePudding

I have a dataframe with four columns: record, time, V1, and V2. I need to convert this dataframe into an array with dim=c(3,10,2). This means 3 unique ids (ID1, ID2, ID3), 10 timepoints (0,1,2,3,4,5,6,7,8,9) and two variables (V1,V2). Please see the data:

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"))

As you see I have missing in some of the time points (ID1 or ID2 NA in 8; ID3 NA in 9). Any suggestion?

CodePudding user response：

Using reshape2:: functions, we need to first melt the V1/V2 variables into one column, and then acast (array-cast) into the 3d array:

reshape2::acast(reshape2::melt(mydata, id = c("record", "time")),
                record ~ time ~ variable)
# , , V1
#           0     1       2    3       4     5      6     7      8      9
# ID1   33113 33113     150  150     150   150    275   150     NA    150
# ID2 4897788 33113   95657 1144  642891   518    150 73669     NA 403230
# ID3  450555 33113 2524740  150 3096225 12628 134896  1202 171157     NA
# , , V2
#        0  1  2  3  4  5  6  7  8  9
# ID1   29 29 29 29 29 37 28 24 NA 29
# ID2 2495 14 14 14 25 24 29 33 NA 30
# ID3   19 29 29 29 17 20 29 20 39 NA

CodePudding user response：

library(tidyverse)

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"))

mydata %>%
  pivot_wider(names_from = record, values_from = c(V1, V2)) %>%
  unlist() %>%
  array(dim = c(3, 10, 2))
#> , , 1
#> 
#>      [,1] [,2] [,3]  [,4] [,5] [,6]    [,7]  [,8]   [,9]  [,10]
#> [1,]    0    3    6     8  150  150     150 33113 642891  73669
#> [2,]    1    4    7 33113  150  275      NA 95657    518 403230
#> [3,]    2    5    9 33113  150  150 4897788  1144    150     NA
#> 
#> , , 2
#> 
#>         [,1]    [,2]   [,3]   [,4] [,5] [,6] [,7] [,8] [,9] [,10]
#> [1,]  450555     150 134896 171157   29   37   29   14   25    33
#> [2,]   33113 3096225   1202     29   29   28   NA   14   24    30
#> [3,] 2524740   12628     NA     29   29   24 2495   14   29    NA

^{Created on 2021-10-27 by the reprex package (v2.0.1)}