How to make this process concisely? (

Question

Here is the table.

example<-matrix(NA,40,7)
colnames(example)=c("month1","month2","month3","month4","month5","month6","month7")
example[,1]<-rep(c("A","B","C","D","E","F","G","H",NA),len=40)
example[,2]<-rep(c("A","H",NA,"C","E",NA,"F",NA),len=40)
example[,3]<-rep(c("C","A","C",NA),len=40)
example[,4]<-rep(c(NA,"A","F","H","E",NA),len=40)
example[,5]<-rep(c("A",NA),len=40)
example[,6]<-rep(c("C",NA,NA,"E"),len=40)
example[,7]<-rep(c("C","A",NA,NA,"C",NA,"F",NA),len=40)
example<-as.data.frame(example)



example
   month1 month2 month3 month4 month5 month6 month7
1       A      A      C   <NA>      A      C      C
2       B      H      A      A   <NA>   <NA>      A
3       C   <NA>      C      F      A   <NA>   <NA>
4       D      C   <NA>      H   <NA>      E   <NA>
5       E      E      C      E      A      C      C
6       F   <NA>      A   <NA>   <NA>   <NA>   <NA>
7       G      F      C   <NA>      A   <NA>      F
8       H   <NA>   <NA>      A   <NA>      E   <NA>
9    <NA>      A      C      F      A      C      C
10      A      H      A      H   <NA>   <NA>      A
11      B   <NA>      C      E      A   <NA>   <NA>
12      C      C   <NA>   <NA>   <NA>      E   <NA>
13      D      E      C   <NA>      A      C      C
14      E   <NA>      A      A   <NA>   <NA>   <NA>
15      F      F      C      F      A   <NA>      F
16      G   <NA>   <NA>      H   <NA>      E   <NA>
17      H      A      C      E      A      C      C
18   <NA>      H      A   <NA>   <NA>   <NA>      A
19      A   <NA>      C   <NA>      A   <NA>   <NA>
20      B      C   <NA>      A   <NA>      E   <NA>
...

sub<-c("ALL","A","B","C","D","E","F","G","H")

For all component in 'sub' variable. (all, a, b, c, ..., h) I want to make the table where the number of alphabet in it. For example, put the number of values of month1 and month2 into alltable [1,1], put the number of value of month2 and month3 into alltable[2,3]

ALLtable<-matrix(NA,13,15)
ALLtable[1,1]<-dim(example %>% select('month1','month1') %>% drop_na('month1'&'month1'))[1]
ALLtable[1,2]<-dim(example %>% select('month1','month2') %>% drop_na('month1'&'month2'))[1]
ALLtable[1,3]<-dim(example %>% select('month1','month3') %>% drop_na('month1'&'month3'))[1]
ALLtable[2,2]<-dim(example %>% select('month2','month2') %>% drop_na('month2'&'month2'))[1]
ALLtable[2,3]<-dim(example %>% select('month2','month3') %>% drop_na('month2'&'month3'))[1]

example %>% select('month1','month3') %>% drop_na('month1') %>% drop_na(month3)
example %>% select('month3','month1') %>% drop_na('month3') %>% drop_na(month1)

ALLtable
      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14] [,15]
 [1,]   36   22   27   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [2,]   22   25   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [3,]   27   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [4,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [5,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [6,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [7,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [8,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [9,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
[10,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA

Put the number of 'A' value from column1 and the number of all the value from column2 into Atable[1,2] Put the number of 'A' value from column1 and the number of all the value from column3 into Atable[1,3] like

Atable<-matrix(NA,13,15)
Atable[1,1]<-dim(example %>% select(month1,month1) %>% filter(month1=='A') %>% drop_na(month1,month1))[1]
Atable[1,2]<-dim(example %>% select(month1,month2) %>% filter(month1=='A') %>% drop_na(month1,month2))[1]
Atable[1,3]<-dim(example %>% select(month1,month3) %>% filter(month1=='A') %>% drop_na(month1,month3))[1]
Atable[2,2]<-dim(example %>% select(month2,month2) %>% filter(month2=='A') %>% drop_na(month2,month2))[1]
Atable[2,3]<-dim(example %>% select(month2,month3) %>% filter(month2=='A') %>% drop_na(month2,month3))[1]

Atable
      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14] [,15]
 [1,]    5    4    4   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [2,]   NA    5    5   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [3,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [4,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [5,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA
 [6,]   NA   NA   NA   NA   NA   NA   NA   NA   NA    NA    NA    NA    NA    NA    NA

And I want to make this process easily and automatically. I can do it using eval(parse(text= function and for loop, I think there are way much easier.

Thank you for your help.

Answer 1

Based on your conditions,

cols <- names(example)
mon <- lapply(sub, function(xx) {
  if (xx == "ALL"){
    mat <- diag(colSums(!is.na(example)))
    mat[lower.tri(mat)] <- combn(cols, 2, function(x) nrow(na.omit(example[x])))
    mat[upper.tri(mat)] <- t(mat)[upper.tri(mat)]
    dimnames(mat) <- list(cols, cols)
    mat
  } else {
    mat <- diag(colSums(example == xx, na.rm = TRUE))
    mat[lower.tri(mat)] <- combn(cols, 2, function(x) sum((na.omit(example[x])[1]) == xx))
    mat[upper.tri(mat)] <- t(mat)[upper.tri(mat)]
    mat[lower.tri(mat)] <- combn(cols, 2, function(x) sum((na.omit(example[x])[2]) == xx))
    dimnames(mat) <- list(cols, cols)
    mat
    }
})

names(mon) <- sub

now mon is what you wanted. For example, mon[['A']] will return

       month1 month2 month3 month4 month5 month6 month7
month1      5      4      4      2      3      3      3
month2      4      5      5      3      5      5      5
month3      9      5     10      7      0      0      5
month4      7      3      4      7      0      3      2
month5     18     15     20     13     20     10     15
month6      0      0      0      0      0      0      0
month7      4      5      5      4      0      0      5

which is Atable in your question

Answer 2

You can use colSums to find non-NA value in each column and assign it to the diagonal of the matrix. Use combn to get non-NA value for every combination.

#column names
cols <- names(example)
#Assign the diagonal values
mat <- diag(colSums(!is.na(example)))
#For every combination of 2 columns fill the lower triangle of the matrix
mat[lower.tri(mat)] <- combn(cols, 2, function(x) nrow(na.omit(example[x])))
#Copy the value from lower triangle to upper triangle
mat[upper.tri(mat)] <- t(mat)[upper.tri(mat)]
#assign the names. 
dimnames(mat) <- list(cols, cols)
mat

#       month1 month2 month3 month4 month5 month6 month7
#month1     36     22     27     25     18     18     18
#month2     22     25     20     16     15     15     20
#month3     27     20     30     20     20     10     20
#month4     25     16     20     27     13     13     13
#month5     18     15     20     13     20     10     15
#month6     18     15     10     13     10     20     10
#month7     18     20     20     13     15     10     20