how to select from identical rows based on their associated value in one column?

I have a matrix of 2 columns and lots of rows. First column has numeric values that occurred twice and second column has probabilities associated with it.

for e.g., a = [1 , 0.6

2 , 0.9

1, 0.4

3 , 0.5

4 , 0.2

2 , 0.1

4 , 0.8

3 , 0.5]

I want choose rows which have elements with higher probability i.e.,

ans = [1 , 0.6

2 , 0.9

4 , 0.8]

I want ignore rows with same probability. How to do this in fastest manner possible?

[madhan ravi]

Which version of matlab?

[Deepika Vatsa]

It's Matlab R2015a

Answer 1

[Star Strider]

Another approach using accumarray:

a = [1 , 0.6 
     2 , 0.9
     1,  0.4
     3 , 0.5
     4 , 0.2
     2 , 0.1
     4 , 0.8
     3 , 0.5];

[Ua,~,ix] = unique(a(:,1));                                 % Unique Values & Indices
L = accumarray(ix, a(:,2), [], @(x)max(sum(x)~=2*x));       % Eliminate Duplicates (Returns Logical Vector)
R = accumarray(ix, a(:,2), [], @max);                       % Maximum Probabilities
Out = [Ua(L), R(L)]                                         % Report Only Maxima For Non-Duplicated Probabilities

producing:

Out =
            1          0.6
            2          0.9
            4          0.8

[Deepika Vatsa]

Thanks for the solution Star! I haven't tried accumarray. I have got my solution using varfun. but I will give this a try to check for computation time though.

[Deepika Vatsa]

Hi Star, I have tried this solution. Definitely faster than varfun on large arrays (41 sec using varfun and 2 secs using ur method of accumarray).

But what i posted as a question here is just a part of the problem.

Actual matrix I have is : [ 1 , 0 , 0.6

1 , 1 , 0.4

2 , 0 , 0.1

2 , 1 , 0.9

... ]

That is, each entry that occurs twice in column 1 has two signs(0 and 1) in column 2 and associated prob in column 3.

Using your method on columns 1 and 3, I am able to select entries in column 1 with higher prob but I actually wanted to correct sign also.

That is, desired output: [1 , 0 , 0.6

2 , 1, 0.9]

How to do this with accumarray?

[Deepika Vatsa]

Hey! I have got it. Using intersect I got the indexes for rows with correct sign as well. Your solution is fastest. Many thanks.

[Star Strider]

As always, my pleasure.

I had to think about that.

This works:

a = [1 , 0 , 0.6 
     1 , 1 , 0.4
     2 , 0 , 0.1
     2 , 1 , 0.9];

[Ua,~,ix] = unique(a(:,1));                                 % Unique Values
L = accumarray(ix, a(:,3), [], @(x)max(sum(x)~=2*x));       % Eliminate Duplicates (Returns Logical Vector)
R = accumarray(ix, a(:,3), [], @max);                       % Maximum Probabilities
S = accumarray(ix, a(:,3), [], @(x) a(find(x==max(x)),2));  % Get ‘sign’
Out = [Ua(L) S(L), R(L)]                                    % Report Only Maxima & Sign For Non-Duplicated Probabilities

producing:

Out =
            1            0          0.6
            2            1          0.9

Answer 2

[Bob Thompson]

There might be some fancy function I don't know, but it's certainly possible to do this with a loop. I cannot comment on the speed of this method.

a = [1 , 0.6 
     2 , 0.9 
     1, 0.4 
     3 , 0.5
     4 , 0.2
     2 , 0.1
     4 , 0.8
     3 , 0.5];
 
uni = unique(a(:,1));

b = [];
for i = 1:length(uni)
    b(i,:) = a(a(:,2) == max(a(a(:,1)==uni(i),:)),:);
end

EDIT* Changed from uniques in second column to uniques in first column. I misread the question.

[madhan ravi]

+1 for the try, I misread it too but after on:

a2 = a(:,2);  
u = unique(a2);                                
idx = a2.'==u;
a=a(~ismember(a2 ,u.*(sum(idx,2)>=2)).',:);
A = array2table(a); 
B = varfun(@max,A,'GroupingVariables','a1'); 
B(:,[1,3])

[Bob Thompson]

@madhan, is your method quicker? If so, do you know why?

[madhan ravi]

Bob,I am not able to run your code but according to what I understand from the question is that if the probabilities are identical OP wants to omit those rows and find the max of probabilities of each group (which belong to column 1). Haven’t tested with the timings though.

[Deepika Vatsa]

Thank you Bob and Madhan for the solutions! I have got my solution by first eliminating same probability rows using 'unique' and then using 'varfun' as suggested by Madhan. Bob, my matrix has lots of rows(in millions), I don't think using a for loop would help me with computation time. So, I think using varfun is quicker. Thanks again.