Comparing multiple rows and creating a matrix in R or in Excel

Question

I have a file containing, multiple rows as follows

In file1:

a  8|2|3|4   4
b  2|3|5|6|7 5
c  8|5|6|7|9 5

a to a has 4 overlaps, similarly a to b had 2 overlaps, so to check the overlaps between various entity, I need to generate a matrix with the above details, and the output should be a matrix like

Please give me a suggestion, how to do this? Is there any way to do this using excel or using a shell script or using R? I have written this following code but since I am not a good coder, I couldn't get the output printed in a right format.

setwd('C:\\Users\\Desktop\\')
newmet1<-file("file.txt")
newmet2<-strsplit(readLines(newmet1),"\t")
Newmet<-sapply(newmet2, function(x) x[2:length(x)], simplify=F )

for (i in 1:length(Newmet))
{
  for (j in 1:length(Newmet)
  {
  c <- ((intersect(Newmet[[i]], Newmet[[j]]))
  print (length(c))
  } 
}

Edited: Thanks for all the answers.. I got the matrix using both excel and R with the help of following answers.

What is the logic behind the conversion? and what did you try? — Inian, Sep 09 '16 at 05:40

score 2 · Answer 1 · answered Sep 09 '16 at 11:51

Here is a function in R that returns the counts of each columns matches as a new matrix

First we get your data into a R data.frame object:

A <- c(8,2,3,4,NA)
B <- c(2,3,5,6,7)
C <- c(8,5,6,7,9)
dataset <- data.frame(A,B,C)

Then we create a function:

count_matches <- function (x) {
  if (is.data.frame(x)) {
    y <- NULL
    for (i in 1:dim(x)[2]) {
      for (j in 1:dim(x)[2]) {
        count <- sum(x[[i]][!is.na(x[i])] %in% x[[j]][!is.na(x[j])])
        y <- c(y, count)
      }
    }
    y <- matrix(y, dim(x)[2], )
    colnames(y) <- names(x)
    rownames(y) <- names(x)
    return(y)
  } else {
    print('Argument must be a data.frame')
  }
}

We test the function on our dataset:

count_matches(dat)

Which returns a matrix:

Tom Sharpe · Accepted Answer · 2016-09-09T17:06:50.947

1

If the numbers are in separate cells starting in Sheet1!A1, try

=SUM(--ISNUMBER(MATCH(Sheet1!$A1:$E1,INDEX(Sheet1!$A$1:$E$3,COLUMN(),0),0)))

starting at Sheet2!A1.

Must be entered as an array formula using CtrlShiftEnter

Alternative formula that doesn't have to start at Sheet2!A1

SUM(--ISNUMBER(MATCH(Sheet1!$A1:$E1,INDEX(Sheet1!$A$1:$E$3,COLUMNS($A:A),0),0)))

edited Sep 09 '16 at 17:06

answered Sep 09 '16 at 09:28

Tom Sharpe

30,727
5
24
37

I am not getting 4, I am getting 1s and 0s – sp2 Sep 09 '16 at 10:16
Are you sure you entered it with Ctrl-Shift held down together and then Enter? – Tom Sharpe Sep 09 '16 at 10:23
1 3 5 0 0 0 0 0 0 This is how it looks like – sp2 Sep 09 '16 at 10:33
Well 1 3 5 are the matches for the third row with the other two rows. Definitely starting at A1 in both sheets with no row or column headers? Otherwise I don't understand what's happening. – Tom Sharpe Sep 09 '16 at 10:50
1

I'm also putting an alternative formula in my answer that doesn't have to start at Sheet2!A1 – Tom Sharpe Sep 09 '16 at 10:56

score 1 · Answer 3 · edited May 23 '17 at 12:22

1

Using R:

# dummy data
df1 <- read.table(text = "a  8|2|3|4   4
b  2|3|5|6|7 5
c  8|5|6|7|9 5", as.is = TRUE)

df1
#   V1        V2 V3
# 1  a   8|2|3|4  4
# 2  b 2|3|5|6|7  5
# 3  c 8|5|6|7|9  5

# convert 2nd column to a splitted list
myList <- unlist(lapply(df1$V2, strsplit, split = "|", fixed = TRUE), recursive = FALSE)
names(myList) <- df1$V1
myList
# $a
# [1] "8" "2" "3" "4"
# $b
# [1] "2" "3" "5" "6" "7"
# $c
# [1] "8" "5" "6" "7" "9"

# get overlap counts
crossprod(table(stack(myList)))
#    ind
# ind a b c
#   a 4 2 1
#   b 2 5 3
#   c 1 3 5

If we remove data processing bit, this answer is already provided by similar post: Intersect all possible combinations of list elements

edited May 23 '17 at 12:22

Community

1
1

answered Sep 09 '16 at 11:54

zx8754

52,746
12
114
209

why am I getting this error for the above code "Error in FUN(X[[1L]], ...) : non-character argument" when I use it for other similar kind of dataset – sp2 Sep 13 '16 at 09:58
Please provide your data for testing. – zx8754 Sep 13 '16 at 10:11

Comparing multiple rows and creating a matrix in R or in Excel

3 Answers3