[SOLVED] Convert table DT1 to DT2 fill with binary using data.table

Convert table DT1 to DT2 fill with binary using data.table

I have DT1 and try to convert this table to DT2 with 1,0

DT1<-fread("variable    bf_target_N bf_comparator_N
drugA   2   1
drugB   3   4
drugC   4   5
")

DT2<-fread("type    drugA   drugB   drugC
bf_target_N 1   1   1
bf_target_N 1   1   1
bf_target_N 0   1   1
bf_target_N 0   0   1
bf_comparator_N 1   1   1
bf_comparator_N 0   1   1
bf_comparator_N 0   1   1
bf_comparator_N 0   1   1
bf_comparator_N 0   0   1
")

DT2 <- data.table(
  type = c(rep("bf_target_N", nrow(DT1)), rep("bf_comparator_N", nrow(DT1))),
  drugA = rep(0, 2 * nrow(DT1)),
  drugB = rep(0, 2 * nrow(DT1)),
  drugC = rep(0, 2 * nrow(DT1))
)

DT2[type == "bf_target_N", c("drugA", "drugB", "drugC") := .(DT1$bf_target_N, DT1$bf_target_N, DT1$bf_target_N)]
DT2[type == "bf_comparator_N", c("drugA", "drugB", "drugC") := .(DT1$bf_comparator_N, DT1$bf_comparator_N, DT1$bf_comparator_N)]

I tried some this way but fail to fill with 1,0

Solution

You can try dcast + melt like below

dcast(
    melt(DT1, id.vars = "variable", variable.name = "type")[,
        .(b = seq_len(value)),
        by = .(variable, type)
    ],
    type + b ~ variable,
    fun = length,
    fill = 0
)[, b := NULL][]

which gives

              type drugA drugB drugC
1:     bf_target_N     1     1     1
2:     bf_target_N     1     1     1
3:     bf_target_N     0     1     1
4:     bf_target_N     0     0     1
5: bf_comparator_N     1     1     1
6: bf_comparator_N     0     1     1
7: bf_comparator_N     0     1     1
8: bf_comparator_N     0     1     1
9: bf_comparator_N     0     0     1