Finding duplicate columns in a data.table

Question

I have a pretty big data.table (500 x 2000), and I need to find out if any of the columns are duplicates, i.e., have the same values for all rows. Is there a way to efficiently do this within the data.table structure?

I have tried a naive two loop approach with all(col1 == col2) for each pair of columns, but it takes too long. I have also tried converting it to a data.frame and using the above approach, and it still takes quite a long time.

My current solution is to convert the data.table to a matrix and use the apply() function as:

similarity.matrix <- apply(m, 2, function(x) colSums(x == m)))/nrow(m)

However, the approach forces the modes of all elements to be the same, and I'd rather not have that happen. What other options do I have?

Here is a sample construction for the data.table:

m = matrix(sample(1:10, size=1000000, replace=TRUE), nrow=500, ncol=2000)
DF = as.data.frame(m)
DT = as.data.table(m)

Answer 1

Following the suggestion of @Haboryme*, you can do this using duplicated to find any duplicated vectors. duplicated usually works rowwise, but you can transpose it with t() just for finding the duplicates.

DF <- DF[ , which( !duplicated( t( DF ) ) ) ]

With a data.table, you may need to add with = FALSE (I think this depends on the version of data.table you're using).

DT <- DT[ , which( !duplicated( t( DT ) ) ), with = FALSE ]

*@Haboryme, if you were going to turn your comment into an answer, please do and I'll remove this one.

Answer 2

Here's a different approach, where you hash each column first and then call duplicated.

library(digest)
dups <- duplicated(sapply(DF, digest)) 
DF <- DF[,which(!dups)]

Depending on your data this might be a faster way.

Answer 3

I am using mtcars for a reproducible result:

library(data.table)
library(digest)

# Create  data
data <- as.data.table(mtcars)
data[, car.name := rownames(mtcars)]
data[, car.name.dup := car.name]           # create a duplicated row
data[, car.name.not.dup := car.name]       # create a second duplicated row...
data[1, car.name.not.dup := "Moon walker"] # ... but change a value so that it is no longer a duplicated column

data contains now:

> head(data)
    mpg cyl disp  hp drat    wt  qsec vs am gear carb          car.name      car.name.dup  car.name.not.dup
1: 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4         Mazda RX4         Mazda RX4       Moon walker
2: 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4     Mazda RX4 Wag     Mazda RX4 Wag     Mazda RX4 Wag
3: 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1        Datsun 710        Datsun 710        Datsun 710
4: 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    Hornet 4 Drive    Hornet 4 Drive    Hornet 4 Drive
5: 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2 Hornet Sportabout Hornet Sportabout Hornet Sportabout
6: 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1           Valiant           Valiant           Valiant

Now find the duplicated colums:

# create a vector with the checksum for each column (and keep the column names as row names)
col.checksums <- sapply(data, function(x) digest(x, "md5"), USE.NAMES = T)

# make a data table with one row per column name and hash value
dup.cols <- data.table(col.name = names(col.checksums), hash.value = col.checksums)

# self join using the hash values and filter out all column name pairs that were joined to themselves
dup.cols[dup.cols,, on = "hash.value"][col.name != i.col.name,]

Results in:

       col.name                       hash.value   i.col.name
1: car.name.dup 58fed3da6bbae3976b5a0fd97840591d     car.name
2:     car.name 58fed3da6bbae3976b5a0fd97840591d car.name.dup

Note: The result still contains both directions (col1 == col2 and col2 == col1) and should be deduplicated ;-)