R: Frequency of all column combinations

Question

Problem description

I have a list of strings of equal size like this:

example.list <- c('BBCD','ABBC','ADDB','ACBB')

Then I want to obtain the frequency of occurence of specific letters at specific positions. First I convert this to a matrix:

     A1 B1 C1 D1 A2 B2 C2 D2 A3 B3 C3 D3 A4 B4 C4 D4
[1,]  0  1  0  0  0  1  0  0  0  0  1  0  0  0  0  1
[2,]  1  0  0  0  0  1  0  0  0  1  0  0  0  0  1  0
[3,]  1  0  0  0  0  0  0  1  0  0  0  1  0  1  0  0
[4,]  1  0  0  0  0  0  1  0  0  1  0  0  0  1  0  0
[5,]  1  0  0  0  0  1  0  0  0  1  0  0  0  0  0  1

Now I want to obtain the frequency of each column combination. Some examples:

A1 : B2 = 2
A1 : B3 = 3
B1 : B2 = 1
.. etc

I think there is supposed to be 4 rows in the matrix shown, not 5. — G. Grothendieck, Sep 17 '18 at 16:23
Also, according your `example.list` I consider `A1 : B3 = 2` isn't it? — Chriss Paul, Sep 17 '18 at 17:46

IceCreamToucan · Answer 1 · 2018-09-19T16:04:39.670

Assuming your matrix is named mat

# get all vars present in each row
present <- lapply(seq(nrow(mat)), function(i) names(which(mat[i,] == 1)))
# get all pairs
all.pairs <- gtools::combinations(n = ncol(mat), r = 2, colnames(mat))
# count times pairs appear
count <- apply(all.pairs, 1, function(x){
  there <- lapply(x, function(y) sapply(present, `%in%`, x = y))
  sum(Reduce(`&`, there))
})

cbind(all.pairs, count)[count > 0,]

#                 count
#  [1,] "A1" "B2" "2"  
#  [2,] "A1" "B3" "3"  
#  [3,] "A1" "B4" "2"  
#  [4,] "A1" "C2" "1"  
#  [5,] "A1" "C4" "1"  
#  [6,] "A1" "D2" "1"  
#  [7,] "A1" "D3" "1"  
#  [8,] "A1" "D4" "1"  
#  [9,] "B1" "B2" "1"  
# [10,] "B1" "C3" "1"  
# [11,] "B1" "D4" "1"  
# [12,] "B2" "B3" "2"  
# [13,] "B2" "C3" "1"  
# [14,] "B2" "C4" "1"  
# [15,] "B2" "D4" "2"  
# [16,] "B3" "B4" "1"  
# [17,] "B3" "C2" "1"  
# [18,] "B3" "C4" "1"  
# [19,] "B3" "D4" "1"  
# [20,] "B4" "C2" "1"  
# [21,] "B4" "D2" "1"  
# [22,] "B4" "D3" "1"  
# [23,] "C3" "D4" "1"  
# [24,] "D2" "D3" "1"

Edit: To include reverse pairs, e.g. both A1:B2 and B2:A1, define all.pairs as below instead

all.pairs <- expand.grid(colnames(mat), colnames(mat))

Thankyou Ryan! I have one question. I want to create a heatmap for this data with both the x-axis and y-axis A1,A2...D4. However when I change the count filter to `count > -1` (to include zeros as well) I see that it does not pick al combinations: The first column does not contain `D4` and the second column does not contain `A1`. Can this be modified to include all combinations, if they are zero or if they are already (reversed) present. — CodeNoob, Sep 18 '18 at 10:04
See my edit for including reverse pairs. To include zeros, you can remove `[count > 0,]` from the last step — IceCreamToucan, Sep 19 '18 at 16:05

G. Grothendieck · Accepted Answer · 2018-09-19T22:50:19.437

Split the strings into a list, s, of vectors of single characters. Set n to their common length and create a matrix v from s whose columns contain elements such as B1, etc. Then use xtabs to create counts giving m1 and combn to get counts of pairs in m2.

s <- strsplit(example.list, "")
n <- lengths(s)[1]
v <- sapply(s, paste0, 1:n)
m1 <- xtabs(~., data.frame(colv = c(col(v)), v = c(v)))
m2 <- combn(1:ncol(m1), 2, function(ix) sum(m1[, ix[1]] * m1[, ix[2]]))
names(m2) <- combn(colnames(m1), 2, paste, collapse = "")

giving:

> m1
    v
colv A1 B1 B2 B3 B4 C2 C3 C4 D2 D3 D4
   1  0  1  1  0  0  0  1  0  0  0  1
   2  1  0  1  1  0  0  0  1  0  0  0
   3  1  0  0  0  1  0  0  0  1  1  0
   4  1  0  0  1  1  1  0  0  0  0  0

> m2
A1B1 A1B2 A1B3 A1B4 A1C2 A1C3 A1C4 A1D2 A1D3 A1D4 B1B2 B1B3 B1B4 B1C2 B1C3 B1C4 
   0    1    2    2    1    0    1    1    1    0    1    0    0    0    1    0 
B1D2 B1D3 B1D4 B2B3 B2B4 B2C2 B2C3 B2C4 B2D2 B2D3 B2D4 B3B4 B3C2 B3C3 B3C4 B3D2 
   0    0    1    1    0    0    1    1    0    0    1    1    1    0    1    0 
B3D3 B3D4 B4C2 B4C3 B4C4 B4D2 B4D3 B4D4 C2C3 C2C4 C2D2 C2D3 C2D4 C3C4 C3D2 C3D3 
   0    0    1    0    0    1    1    0    0    0    0    0    0    0    0    0 
C3D4 C4D2 C4D3 C4D4 D2D3 D2D4 D3D4 
   1    0    0    0    1    0    0

You did not define `m` in `m2 <- combn(1:ncol(m), 2, function(ix) sum(m1[, ix[1]] * m1[, ix[2]]))`, should this be `m1` ? — CodeNoob, Sep 18 '18 at 07:58
Yes, it was originally m and I changed it to m1 but had missed one instance. It should be ok now. — G. Grothendieck, Sep 19 '18 at 22:51

TinglTanglBob · Answer 3 · 2018-09-17T15:15:23.380

This should give you a list of tables with each colum_A x colum_B combination (with A and B beeing numbers from 1 to length(nchar(your_Strings)))

rm(list = ls())

example.list <- c('ABCD','ABBC','ADDB','ACBB', "BCBB", "BASD")

example.matrix = matrix(unlist(strsplit(example.list, "")), ncol = nchar(example.list[1]), nrow = length(example.list), byrow = T)

table(example.matrix[,1], example.matrix[,2])

results = list()

for(i in 1:NCOL(example.matrix))
{
  for(j in 1:NCOL(example.matrix))
  {
   temp = as.matrix(table(example.matrix[,i], example.matrix[,j])) 
   rownames(temp) = paste0("pos_",i,"_", rownames(temp))
   colnames(temp) = paste0("pos_",j,"_", colnames(temp))
   print(temp)
   results[[paste0(i,"_",j)]] = temp
  }
}

results

Something like this?

Edit: Better use Ryan's solution. Its a lot more elegant.

R: Frequency of all column combinations

3 Answers3

Linked