Testing for multiple identical columns in R

Question

Is there a short way to test for identity over multiple columns? For example, over this input

data=data.table(one=c(1,2,3,4), two=c(7,8,9,10), three=c(1,2,3,4), four=c(1,2,3,4) )

Is there something that would return all the columns that are identical to data$one? Something like

allcolumnsidentity(data$one, data) # compares all columns with respect to data$one 

Should return (TRUE, FALSE, TRUE, TRUE) since data$three and data$four are identical to data$one.

I saw the identical() and comapre() commands, but they deal with comparing between two columns. Is there a generalized way to do it?

Best wishes

Answer 1

Here are 3 more possible solutions an a benchmark on a bit bigger data set

n <- 1e6
data=data.table(one=rep(1:4, n), 
                two=rep(7:10, n),
                three=rep(1:4, n), 
                four=rep(1:4, n))

library(microbenchmark)
microbenchmark(
              apply(data, 2, identical, data$one) ,
              colSums(data == data$one) == nrow(data),
              colSums(as.matrix(data) == data$one) == nrow(data),
              data[, lapply(.SD, function(x) sum(x == data$one) == .N)],
              data[, lapply(.SD, function(x) identical(x, data$one))]
)


# Unit: milliseconds
#                                                      expr        min          lq        mean      median          uq        max neval
#                       apply(data, 2, identical, data$one)  352.58769  414.846535  457.767582  437.041789  521.895046  643.77981   100
#                   colSums(data == data$one) == nrow(data) 1264.95548 1315.882084 1335.827386 1326.250976 1346.501505 1466.64232   100
#        colSums(as.matrix(data) == data$one) == nrow(data)  110.05474  114.618818  125.116033  121.631323  126.912647  185.69939   100
# data[, lapply(.SD, function(x) sum(x == data$one) == .N)]   75.36791   77.960613   85.599088   79.327108   89.369938  156.03422   100
#   data[, lapply(.SD, function(x) identical(x, data$one))]    7.00261    7.448851    8.687903    8.776724    9.491253   15.72188   100

And here are some comparisons in case you have many columns

n <- 1e7
set.seed(123)
data <- data.table(matrix(sample(n, replace = TRUE), ncol = 400))

microbenchmark(
               apply(data, 2, identical, data$V1) ,
               colSums(data == data$V1) == nrow(data),
               colSums(as.matrix(data) == data$V1) == nrow(data),
               data[, lapply(.SD, function(x) sum(x == data$V1) == .N)],
               data[, lapply(.SD, function(x) identical(x,data$V1))]
)

# Unit: milliseconds
#                                                     expr       min        lq      mean    median        uq       max neval
#                       apply(data, 2, identical, data$V1) 176.65997 185.23895 235.44088 234.60227 253.88658 331.18788   100
#                   colSums(data == data$V1) == nrow(data) 680.48398 759.82115 786.64634 774.86919 804.91661 987.26456   100
#        colSums(as.matrix(data) == data$V1) == nrow(data)  60.62470  62.86181  70.41601  63.75478  65.16708 120.30393   100
# data[, lapply(.SD, function(x) sum(x == data$V1) == .N)]  83.95790  86.72680  90.45487  88.46165  90.04441 142.08614   100
#   data[, lapply(.SD, function(x) identical(x, data$V1))]  40.86718  42.65486  45.06100  44.29602  45.49430  91.57465   100

Answer 2

Ok, that was easier than expected :)

Simply used apply like so:

apply(data, 2, identical, data$one) 
# returned:
# one   two  three  four 
# TRUE FALSE  TRUE  TRUE