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.