Unique Raman Frequency

Question

I have a data set with 10 columns. The first column is a unique identifier. 9 other columns are related attributes. In the meantime, let them say that they are whole. If necessary, the data can be easily rotated to the key value.

Example:

id|attr1|attr2|attr3|... a | 2 | 5 | 7 |... b | 3 | 1 |null |... c | 2 |null |null |... d | 1 | 2 | 5 |... e | 2 | 1 | 3 |... 

I am essentially looking for the most common combinations of any length, at least with a pair. Therefore, my result for this would be as follows:

 unq | frequency 1,2 | 2 1,3 | 2 1,5 | 1 2,3 | 1 2,5 | 2 2,7 | 1 1,2,3 | 1 1,2,5 | 1 2,5,7 | 1 

(I did it manually - so I hope there are no errors) - the parsing order does not matter. 2,5,7 = 5,2,7 = 7,5,2 etc.

Any thoughts? I am open to various tools. I have access to R, excel, sql server, mysql, etc.

Excel is preferred but not required!

Answer 1

Here is the solution in R:

Recover data

 x <- data.frame( id = letters[1:5], attr1 = c(2,3,2,1,2), attr2 = c(5,1,NA,2,1), attr3 = c(7,NA,NA,5,3)) x id attr1 attr2 attr3 1 a 2 5 7 2 b 3 1 NA 3 c 2 NA NA 4 d 1 2 5 5 e 2 1 3 

Create a function to list all combinations

 make_combinations <- function(data, size){ t1 <- apply(data[, -1], 1, function(data)unname(sort(data))) t2 <- lapply(t1, function(xt){if(length(xt)>=size){combn(xt, size)}}) t3 <- sapply(t2[!is.na(t2)], function(chunk){if(!is.null(chunk))apply(chunk, 2, function(x)paste(x, collapse=","))}) t4 <- unlist(t3) t4 } 

Create a second function to count combinations

 count_combinations <- function(data, nn=2:3){ tmp <- unlist(lapply(nn, function(n)make_combinations(data, n))) sort(table(tmp), decreasing=TRUE) } 

Results:

 count_combinations(x, 2:3) 1,2 1,3 2,5 1,2,3 1,2,5 1,5 2,3 2,5,7 2,7 5,7 2 2 2 1 1 1 1 1 1 1 

Answer 2

Here is your data without id column.

 dfr <- data.frame( attr1 = c(2,3,2,1,2), attr2 = c(5,1,NA,2,1), attr3 = c(7,NA,NA,5,3) ) 

This extracts all the combinations, but the output form takes a little time.

 lapply( seq_len(nrow(dfr)), #loop over rows function(row) { lapply( seq_along(dfr)[-1], #loop over lengths of combination, -1 is to ignore singletons function(m) { combn(dfr[row, ], m) } ) } )