Home > front end >  dcast data.table with multiple value.var's of different classes
dcast data.table with multiple value.var's of different classes

Time:09-24

I'm trying to dcast my input data inDT to the expected output outDT:

inDT <- data.table(
  int_value = c(2020L, 1L:10L, rep(NA_integer_, 20)),
  num_value = c(rep(NA_real_, 11), seq(0.1, 1, 0.1), rep(NA_real_, 10)),
  timestamp_value = c(rep(as.POSIXct(NA), 21), Sys.time() - 1:10),
  id = c(
    "int_id_1",
    rep("int_id_2", 10),
    rep("num_id", 10),
    rep("timestamp_id", 10)
  )
)

outDT <- data.table(
  int_id_1 = c(2020L, rep(NA_integer_, 9)),
  int_id_2 = 1L:10L,
  num_id = seq(0.1, 1, 0.1),
  timestamp_id = Sys.time() - 1:10
)

I tried several different constellations using dcast.data.table:

dcast.data.table(inDT, int_value   num_value   timestamp_value ~ id, value.var = c("int_value", "num_value", "timestamp_value"))
dcast.data.table(inDT, . ~ id, value.var = c("int_value", "num_value", "timestamp_value"))

but it seems I'm missing something here.

Any help is greatly appreciated.

CodePudding user response:

An imperfect method:

inDT[, rn := rowid(id)]
Filter(function(z) !all(is.na(z)),
       dcast(inDT, rn ~ id, value.var = list("int_value", "num_value", "timestamp_value")))
#        rn int_value_int_id_1 int_value_int_id_2 num_value_num_id timestamp_value_timestamp_id
#     <int>              <int>              <int>            <num>                       <POSc>
#  1:     1               2020                  1              0.1          2021-09-23 09:15:41
#  2:     2                 NA                  2              0.2          2021-09-23 09:15:40
#  3:     3                 NA                  3              0.3          2021-09-23 09:15:39
#  4:     4                 NA                  4              0.4          2021-09-23 09:15:38
#  5:     5                 NA                  5              0.5          2021-09-23 09:15:37
#  6:     6                 NA                  6              0.6          2021-09-23 09:15:36
#  7:     7                 NA                  7              0.7          2021-09-23 09:15:35
#  8:     8                 NA                  8              0.8          2021-09-23 09:15:34
#  9:     9                 NA                  9              0.9          2021-09-23 09:15:33
# 10:    10                 NA                 10              1.0          2021-09-23 09:15:32

Note: I had to add rn, a column indicating row number within each id, since pivoting operations require the premise of associating rows together.

  • Related