I want to filter values from a data matrix

I have a datamatrix with the following contents:

Rownames Cond1 Cond1 Cond1 Pvals

a 15 23 21 .055

a 45 1 58 .001

b 8 98 56 .02

c 6 89 45 .004

d 9 55 15 .008

c 45 68 95 .04

I want to filter this matrix to have a matrix like below. The idea is that for any two or more similar row names, i want to remain with the one with the lowest Pvals.

Rownames Cond1 Cond1 Cond1 Pvals

a 45 1 58 .001

b 8 98 56 .02

c 6 89 45 .004

d 9 55 15 .008

[Luna]

Is your data matrix is a table or a cell array? Please share the code what have you done so far?

[Ronald Odongo]

My data is in a datamatrix object: https://www.mathworks.com/help/bioinfo/ug/representing-expression-data-values-in-datamatrix-objects.html

[Ronald Odongo]

So, what I need, in case of repeated rownames, are row names with the lowest values in the Pvals column. However, if not repeated then the row itself.

[Luna]

Unfortunately I have never worked on data matrix objects before but the idea of the algorithm may help you below answer.

Answer 1

[Luna]

Try this small code below:

dataMatrix  =  {'Rownames','Cond1',    'Cond1'  ,  'Cond1',    'Pvals';
'a',    15,    23,    21,    .055;
'a',    45,    1,    58,    .001;
'b',    8,    98,    56,    .02;
'c',    6,    89,    45,    .004;
'd',    9,    55,    15,    .008;
'c',    45,    68,    95,   .04};

sortedDataMatrix = sortrows(dataMatrix(2:end,:),5);  % sorts the rows according to 5th column
[~,idx,~] = unique(sortedDataMatrix(:,1)); % get unique values indices
resultMatrix = [dataMatrix(1,:); sortedDataMatrix(idx,:)]; % gets the indexed rows and combine it with data matrix row names(1st row).

[Ronald Odongo]

Thank you Luna! This approach works however it doesn't remove the repeated rows bearing the highest value with the set criteria. I mean the new matrix should consist only of the unique values with the lowest values.

[Luna]

The resultMatrix is what you need isn't it? It doesn't delete the rows you don't want, it sorts the values from lowest the highest first, then gets the unique values of rows(a,b,c,..etc) with this ascending order.

It actually selects the rows you wanted without removing. Please accept answer if it works correctly.

'Rownames'	'Cond1'	'Cond1'	'Cond1'	'Pvals'
'a'	        45	1	58	0,001
'b'	        8	98	56	0,02
'c'	        6	89	45	0,004
'd'	        9	55	15	0,008

[Ronald Odongo]

Yeah it works. Thank you again!