by row unique

library(data.table)
library(pipeR)

# data generation
numRows <- 3e4
numCols <- 8
s <- matrix(NA_integer_, numRows, numCols)
idx <- sample(numCols, numRows, TRUE)
for (i in 1:numCols)
  s[idx == i, 1:i] <- sample(42, i * sum(idx == i), TRUE)

time <- seq(ISOdate(2017, 9, 12) - numRows*3600, ISOdate(2017, 9, 12),
            by = "hour")
DT <- data.table(time = head(time, numRows), s)
#                       time V1 V2 V3 V4 V5 V6 V7 V8
#     1: 2014-04-11 12:00:00 33 18  2 NA NA NA NA NA
#     2: 2014-04-11 13:00:00 19 NA NA NA NA NA NA NA
#     3: 2014-04-11 14:00:00 40 30 NA NA NA NA NA NA
#     4: 2014-04-11 15:00:00 39  4 18 19 NA NA NA NA
#     5: 2014-04-11 16:00:00 29 31 14  3 24 34 25  8
#   ---
# 29996: 2017-09-12 07:00:00 13 22 NA NA NA NA NA NA
# 29997: 2017-09-12 08:00:00 31 42 NA NA NA NA NA NA
# 29998: 2017-09-12 09:00:00 16 NA NA NA NA NA NA NA
# 29999: 2017-09-12 10:00:00  7 23 27 16 21 NA NA NA
# 30000: 2017-09-12 11:00:00 10 28 22 14 31 NA NA NA

# 主程式
# 第一個問題
st <- proc.time()
melt(DT, 1, 2:9) %>>% na.omit("value") %>>%
  `[`(j = .(value = unique(value), idx = 1:uniqueN(value)), by = .(time)) %>>%
  dcast(time ~ idx, value.var = "value")
proc.time() - st
# user  system elapsed
# 0.68    0.00    0.71
#                       time  1  2  3  4  5  6  7  8
#     1: 2014-04-11 12:00:00 31 NA NA NA NA NA NA NA
#     2: 2014-04-11 13:00:00 40  4 30  8 21 25  3 11
#     3: 2014-04-11 14:00:00  7 35 33 NA NA NA NA NA
#     4: 2014-04-11 15:00:00 28 34 35  8 32 NA NA NA
#     5: 2014-04-11 16:00:00  6 10  4 18 20 19 41 NA
#    ---
# 29996: 2017-09-12 07:00:00 24 13 39  6 36 35 31 29
# 29997: 2017-09-12 08:00:00  6 27 29 NA NA NA NA NA
# 29998: 2017-09-12 09:00:00 16 NA NA NA NA NA NA NA
# 29999: 2017-09-12 10:00:00 23 15 17  1  5 NA NA NA
# 30000: 2017-09-12 11:00:00  6 33 NA NA NA NA NA NA

# 第二個問題

st <- proc.time()
melt(DT, 1, 2:9) %>>% na.omit("value") %>>%
  `[`(j = .(cnt = .N), by = .(time, value))
proc.time() - st
# user  system elapsed
# 0.14    0.03    0.17

#                        time value cnt
#      1: 2014-04-11 12:00:00    31   1
#      2: 2014-04-11 13:00:00     4   3
#      3: 2014-04-11 14:00:00    17   2
#      4: 2014-04-11 15:00:00     2   1
#      5: 2014-04-11 16:00:00    40   1
#     ---
# 128084: 2017-09-11 03:00:00    29   1
# 128085: 2017-09-11 16:00:00     6   1
# 128086: 2017-09-11 22:00:00    24   1
# 128087: 2017-09-12 05:00:00     4   1
# 128088: 2017-09-12 10:00:00    31   1