Perl（或R或SQL）：计算字符串在列之间出现的频率-Perl(orR,orSQL):Counthowoftenstringappearsacrosscolumns

8  

This solution seems like a bit of a hack, but it gives the desired output. It relies on using both plyr and reshape packages, though I'm sure you could find base R alternatives. The trick is that function melt lets us flatten the data out into a long format, which allows for easy(ish) manipulation from that point forward.

这个解决方案看起来有点像黑客，但它提供了所需的输出。它依赖于同时使用plyr和reshape包，但我确信你可以找到基本的R替代品。诀窍在于，函数融合让我们将数据展平为长格式，从而可以实现从那一点开始的简单（ish）操作。

library(reshape)
library(plyr)

#Recreate your data
dat <- data.frame(gene1 = c(letters[1:4], NA, NA),
                  gene2 = letters[4:9],
                  gene3 = c("c", "d", "g", NA, NA, NA)
                  )

#Melt the data. You'll need to update this if you have more columns
dat.m <- melt(dat, measure.vars = 1:3)

#Tabulate counts
counts <- as.data.frame(table(dat.m$value))

#I'm not sure what to call this column since it's a smooshing of column names
otherColumn <- ddply(dat.m, "value", function(x) paste(x$variable, collapse = " "))

#Merge the two together. You could fix the column names above, or just deal with it here
merge(counts, otherColumn, by.x = "Var1", by.y = "value")

Gives:

得到：

> merge(counts, otherColumn, by.x = "Var1", by.y = "value")
  Var1 Freq                V1
1    a    1             gene1
2    b    1             gene1
3    c    2       gene1 gene3
4    d    3 gene1 gene2 gene3
....

6  

In perl, assuming the proteins in each column don't have duplicates that need to be removed. (If they do, a hash of hashes should be used instead.)

在perl中，假设每列中的蛋白质不具有需要去除的重复。 （如果他们这样做，则应该使用散列哈希值。）

use strict;
use warnings;

my $header = <>;
my %column_genes;
while ($header =~ /(\S+)/g) {
    $column_genes{$-[1]} = "$1";
}

my %proteins;
while (my $line = <>) {
    while ($line =~ /(\S+)/g) {
        if (exists $column_genes{$-[1]}) {
            push @{ $proteins{$1} }, $column_genes{$-[1]};
        }
        else {
            warn "line $. column $-[1] unexpected protein $1 ignored\n";
        }
    }
}

for my $protein (sort keys %proteins) {
    print join("\t",
        $protein,
        scalar @{ $proteins{$protein} },
        join(' ', sort @{ $proteins{$protein} } )
    ), "\n";
}

Reads from stdin, writes to stdout.

从stdin读取，写入stdout。

5  

A one liner (or rather 3 liner)

一个衬垫（或更确切地说是3个衬垫）

ddply(na.omit(melt(dat, m = 1:3)), .(value), summarize, 
     len = length(variable), 
     var = paste(variable, collapse = " "))

1  

If it's not a lot of columns, you can do something like this in sql. You basically flatten out the data into a 2 column derived table of protein/gene and then summarize it as needed.

如果它不是很多列，你可以在sql中做这样的事情。您基本上将数据压缩成2列衍生的蛋白质/基因表，然后根据需要对其进行总结。

;with cte as (
  select gene1 as protein, 'gene1' as gene
  union select gene2 as protein, 'gene2' as gene
  union select gene3 as protein, 'gene3' as gene
)

select protein, count(*) as cnt, group_concat(gene) as gene
from cte
group by protein

1  

In mysql, like so:

在mysql中，像这样：

select protein, count(*), group_concat(gene order by gene separator ' ') from gene_protein group by protein;

assuming data like:

假设数据如下：

create table gene_protein (gene varchar(255) not null, protein varchar(255) not null);
insert into gene_protein values ('gene1','a'),('gene1','b'),('gene1','c'),('gene1','d');
insert into gene_protein values ('gene2','d'),('gene2','e'),('gene2','f'),('gene2','g'),('gene2','h'),('gene2','i');
insert into gene_protein values ('gene3','c'),('gene3','d'),('gene3','g');