Filtering column data with the same value

Hi expert,

I have the following dataset which I wanted to filter according to the value of the data in the first column.

The condition is the following:

1. If the data in the first column is redundant, then take the data in that row that has larger value in the second column
2. if the data in the first column is not redundant (or the data is unique), then take the data in that row

how do we perform this operation using the conditional "if"? or any other ideas are also welcome.

Thank you in advance.

PS:

I want to also get the row-index of the selected data.

data=[
        0    1.0000
         0    1.0556
    1.9565    1.0000
    1.9565    1.0556
    3.9130    1.0000
    3.9130    1.0556
    5.8696    1.0000
    5.8696    1.0556
    7.8261    1.0000
    7.8261    1.0556
    9.7826    1.0000
    9.7826    1.0556
   11.7391    1.0000
   11.7391    1.0556
   13.6957    1.0000
   13.6957    1.0556
   15.6522    1.0000
   17.6087    1.0000
   19.5652    1.0000
   21.5217    1.0000
   23.4783    1.0000
   25.4348    1.0000
   27.3913    1.0000
   29.3478    1.0000
   31.3043    1.0000
   33.2609    1.0000
   35.2174    1.0000
   37.1739    1.0000
   39.1304    1.0000
   41.0870    1.0000
  138.9130    1.0000
  140.8696    1.0000
  142.8261    1.0000
  144.7826    1.0000
  146.7391    1.0000
  148.6957    1.0000
  150.6522    1.0000
  152.6087    1.0000
  154.5652    1.0000
  156.5217    1.0000
  158.4783    1.0000
  160.4348    1.0000
  162.3913    1.0000
  164.3478    1.0000
  166.3043    1.0000
  166.3043    1.0556
  168.2609    1.0000
  168.2609    1.0556
  170.2174    1.0000
  170.2174    1.0556
  172.1739    1.0000
  172.1739    1.0556
  174.1304    1.0000
  174.1304    1.0556
  176.0870    1.0000
  176.0870    1.0556
  178.0435    1.0000
  178.0435    1.0556
  180.0000    1.0000
  180.0000    1.0556
  181.9565    1.0000
  181.9565    1.0556
  183.9130    1.0000
  183.9130    1.0556
  185.8696    1.0000
  185.8696    1.0556
  187.8261    1.0000
  187.8261    1.0556
  189.7826    1.0000
  189.7826    1.0556
  191.7391    1.0000
  191.7391    1.0556
  193.6957    1.0000
  193.6957    1.0556
  195.6522    1.0000
  197.6087    1.0000
  199.5652    1.0000
  201.5217    1.0000
  203.4783    1.0000
  205.4348    1.0000
  207.3913    1.0000
  209.3478    1.0000
  211.3043    1.0000
  213.2609    1.0000
  215.2174    1.0000
  217.1739    1.0000
  219.1304    1.0000
  221.0870    1.0000
  318.9130    1.0000
  320.8696    1.0000
  322.8261    1.0000
  324.7826    1.0000
  326.7391    1.0000
  328.6957    1.0000
  330.6522    1.0000
  332.6087    1.0000
  334.5652    1.0000
  336.5217    1.0000
  338.4783    1.0000
  340.4348    1.0000
  342.3913    1.0000
  344.3478    1.0000
  346.3043    1.0000
  346.3043    1.0556
  348.2609    1.0000
  348.2609    1.0556
  350.2174    1.0000
  350.2174    1.0556
  352.1739    1.0000
  352.1739    1.0556
  354.1304    1.0000
  354.1304    1.0556
  356.0870    1.0000
  356.0870    1.0556
  358.0435    1.0000
  358.0435    1.0556
  360.0000    1.0000
  360.0000    1.0556    
];

Answer 1

[Star Strider]

Try this:

[Ud,~,ix] = unique(data(:,1));
maxVals = accumarray(ix, data(:,2), [], @max);
Result = [Ud, maxVals]

producing (for a subset of ‘data’):

Result =

         0    1.0556
    1.9565    1.0556
    3.9130    1.0556
    5.8696    1.0556
    7.8261    1.0556
    9.7826    1.0556
   11.7391    1.0556
   13.6957    1.0556
   15.6522    1.0000
   17.6087    1.0000
   19.5652    1.0000
   21.5217    1.0000
        . . .

[BeeTiaw]

Thanks! This result is exactly what I want.

But, would it be possible to use a logical language, such as "IF" and "ELSE" to perform the operation?

I am looking for something that uses less built-in function.

But, your answer gives me the result though.

[Star Strider]

My pleasure!

It might be possible, however the code would be complicated and slow, since it would have to scan the entire array for duplicate values in the first column, then compare the values in the second column for those values, and return the maximum. (Note that every function I would use would be built-in.)

In my code, the unique (link) function does the scanning and returns the positions of the duplicated values in column #1, then the accumarray (link) function does the comparisons and returns the maximum values from column #2. This is much faster and more efficient than anything I could write using conditional statements and comparisons.

[BeeTiaw]

In that case, how can we get the row-index of the filtered data?

[Star Strider]

The easiest way to do that is likely with a for loop:

for k = 1:size(Result,1)
    rowIdx(k,:) = find((data(:,1)==Result(k,1)) & (data(:,2)==Result(k,2)));
end

You can then concatenate that with the existing ‘Result’ matrix if you like:

Result(:,3) = rowIdx;

producing:

Result =
         0    1.0556    2.0000
    1.9565    1.0556    4.0000
    3.9130    1.0556    6.0000
    5.8696    1.0556    8.0000
    7.8261    1.0556   10.0000
    9.7826    1.0556   12.0000
   11.7391    1.0556   14.0000
   13.6957    1.0556   16.0000
   15.6522    1.0000   17.0000
   17.6087    1.0000   18.0000
   19.5652    1.0000   19.0000
   21.5217    1.0000   20.0000
             . . . 

for the same subset of ‘data’.