I have a large table with over 13,000 rows, each row represents an experiment and containins a set of genes. I would like to get generate a list of all the genes and the number of experiments that contain that gene. For example:

Question

Adnan 2017-10-5

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/359864-i-have-a-large-table-with-over-13-000-rows-each-row-represents-an-experiment-and-containins-a-set-o

回答： Peter Perkins 2017-10-13

Hello,

tableA:

Experiment,  Gene1, Gene2, Gene3, Gene4;
'1',            'A',      'C',   'D',     '';
'2',            'A',      'C',   'G',    '';
'3',            'C',      'B',   'D',   'D';

What I would like is a count of number of experiments that each gene occurs in ignoring duplicates within the same experiment. For Example A = 2, B = 1, C = 3, D = 2

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2017-10-5

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/359864-i-have-a-large-table-with-over-13-000-rows-each-row-represents-an-experiment-and-containins-a-set-o#answer_284476

在 MATLAB Online 中打开

Here is how I'd do it:

%demo input
tableA = cell2table({...
    '1',            'A',      'C',   'D',     '';
    '2',            'A',      'C',   'G',    '';
    '3',            'C',      'B',   'D',   'D';}, ...
                    'VariableNames', ...
                    {'Experiment', 'Gene1', 'Gene2', 'Gene3', 'Gene4'})
%processing
genes = unique(tableA{:, 2:5}).';   %get list of genes
genes(1) = [];     %get rid of ''
expcount = sum(cell2mat(rowfun(@(g) ismember(genes, g), tableA, 'SeparateInputs', false, 'InputVariables', 2:5, 'OutputFormat', 'cell')));
result = array2table(expcount, 'VariableNames', compose('gene_%s', string(genes)))  %for pretty display

2 个评论
显示无隐藏无

Adnan 2017-10-5

Thank you. This is a great solution. I had to read the rowfun help page several times to understand how this is working but its an elegant answer.

So my understanding is correct, the reason you used the variable g is so you can pass one row of tableA into the ismember function?

Thanks again

Guillaume 2017-10-5

Exactly, g is the the name of the input to the anonymous function. It receives a row of the table, and check the list of genes against it. That gives you an array of 0 and 1 telling which genes are present in the row. Summing all the rows gives you the result you want.

请先登录，再进行评论。

Answer 2

Stephen 2017-10-5

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/359864-i-have-a-large-table-with-over-13-000-rows-each-row-represents-an-experiment-and-containins-a-set-o#answer_284447

编辑：Stephen 2017-10-5

在 MATLAB Online 中打开

Given your example and format, you will probably want to make use of a for loop and the "unique" function:

Gene = struct('numExp',[],...) %Add other properties of experiments
AllGenes = struct('A',Gene ,'B',Gene ,'C',Gene ,...etc) % Each gene gets a structure field
for ptr = 1:13000
    currGenes = unique(tableA(2:end));
    for ptr2 = 1:length(currGenes)
        incGene = currGenes{ptr2};
        AllGenes.(incGene).numExp = AllGenes.(incGene).numExp + 1;
    end
end

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Adnan 2017-10-5

For the below line from your code, are you finding the unique genes within a row (experiment) of tabeA?

currGenes = unique(tableA(2:end));

请先登录，再进行评论。

Answer 3

Peter Perkins 2017-10-13

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/359864-i-have-a-large-table-with-over-13-000-rows-each-row-represents-an-experiment-and-containins-a-set-o#answer_285671

在 MATLAB Online 中打开

+1 for Guillaume's use of rowfun.

Here's another possibility that might be wiorth looking at if you have very large number of experiments and a smaller number of genes:

>> tableA = cell2table({...
    '1',            'A',      'C',   'D',     '';
    '2',            'A',      'C',   'G',    '';
    '3',            'C',      'B',   'D',   'D';}, ...
                    'VariableNames', ...
                    {'Experiment', 'Gene1', 'Gene2', 'Gene3', 'Gene4'});

Turn things into categorical because categorical is useful:

>> tableA.Experiment = categorical(tableA.Experiment);
>> genes = {'A' 'B' 'C' 'D' 'G'};
>> tableA.Gene1 = categorical(tableA.Gene1,genes);
>> tableA.Gene2 = categorical(tableA.Gene2);
>> tableA.Gene3 = categorical(tableA.Gene3);
>> tableA.Gene4 = categorical(tableA.Gene4);

Create one array, then look across rows for each gene, looping over genes instead of rows. This could create a 1-row table, or a scalar struct, or a double vector (with no names). I chose the struct, just because a table with only one row is kind of overkill:

>> allGenes = [tableA.Gene1 tableA.Gene2 tableA.Gene3 tableA.Gene4]
allGenes = 
  3×4 categorical array
     A      C      D      <undefined> 
     A      C      G      <undefined> 
     C      B      D      D           
>> for i = 1:length(genes)
    counts.(genes{i}) = sum(any(allGenes==genes{i},2));
end
>> counts
counts = 
  struct with fields:
      A: 2
      B: 1
      C: 3
      D: 2
      G: 1