CODEWITHSUNDEEP
rperlcountmatch
user3637642
user3637642

Reputation: 41

Match and Count the Data Matrix in R

The dataset looks like this:

 Gene SampleName
gene1    sample1
gene1    sample2
gene1    sample3
gene2    sample2
gene2    sample3
gene2    sample4
gene3    sample1
gene3    sample5

My goal is to make a data matrix like this:

       gene1 gene2 gene3
gene1      -     2     1
gene2      -     -     0
gene3      -     -     -

gene1 vs gene2 is 2 because they share the same samples sample2 and sample3. gene1 vs gene3 is 1 because they only share one same sample - sample1.

My question is how can I achieve this goal in R or Perl? The actual data set is much larger. I highly appreciate your help.

Here's the dput(df) output for R:

df <- structure(list(Gene = c("gene1", "gene1", "gene1", "gene2", "gene2", 
"gene2", "gene3", "gene3"), SampleName = c("sample1", "sample2", 
"sample3", "sample2", "sample3", "sample4", "sample1", "sample5"
)), .Names = c("Gene", "SampleName"), row.names = c(NA, -8L), class = "data.frame")

Upvotes: 3

Views: 131

Answers (2)

mpapec
mpapec

Reputation: 50647

perl -lane'
  $s{$F[0]}++ or push @k, $F[0];
  $h{$F[1]}{$F[0]} = 1;
END {
  $, = "\t";
  print "", @k;
  for $c (@k) {
    print $c, map {
      $u = $_;      
      ($c eq $u) ? "-" : scalar grep $_->{$c} && $_->{$u}, values %h;
    } @k;
  }
}
' file

output

        gene1   gene2   gene3
gene1   -       2       1
gene2   2       -       0
gene3   1       0       -

Upvotes: 2

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193597

You can look at the crossprod (or tcrossprod) function along with table:

out <- tcrossprod(table(df))
out
#        Gene
# Gene    gene1 gene2 gene3
#   gene1     3     2     1
#   gene2     2     3     0
#   gene3     1     0     2

Drop the diagonal and the lower-triangle to get the exact output you show.

diag(out) <- NA
out[lower.tri(out)] <- NA
print.table(out)  ## print.table deals with NAs differently
#        Gene
# Gene    gene1 gene2 gene3
#   gene1           2     1
#   gene2                 0
#   gene3

Upvotes: 8

Related Questions