For repeating values in a column calculate the corresponding adjacent columns

Hi,

I have a table T with 3 columns to make it simple:

There there is a repeat value in column 1 I would like to calculate the max of the values in column 2, min of the values in column 3 for all corresponding values of col 1 (where the same value was found) and regenerate the table without the repeat values in col 1 and the results as calculated previosly for cols 2 and 3. The number of reps in col 1 can be >2:

Thanks.

Answer 1

[Arif Hoq]

A=[1 233 456;2 3434 565; 3 4545 2323; 4 4595 595; 4 121 343; 5 1212 4145]; % assume A is table
B=diff(A(:,1));
[idx]=find(B==0);
A(idx,:)=[]  % delete the row with same value

A = 5×3

1 233 456 2 3434 565 3 4545 2323 4 121 343 5 1212 4145

or

A1=[1 233 456;2 3434 565; 3 4545 2323; 4 4595 595; 4 121 343; 5 1212 4145]; % assume A is table
[C,ia,ic]=unique(A1(:,1),'rows','stable');
output=A1(ia,:)

output = 5×3

1 233 456 2 3434 565 3 4545 2323 4 4595 595 5 1212 4145

[Gabi]

Thanks Arif, but this does not calculate the other columns (max for col 2, for the same values of col 1, min for col3 for the same values of col 1), see below for a better visualisation:

[Arif Hoq]

i don't have your data.. please attach your data

[Gabi]

Table attached Arif. Thanks.

[Gabi]

I eneded up using a variation of your code Arif, does the job, in essence I had to order the columns based on the requirements before extracting:

A=table2array(T);
B=sortrows(A,[1 2 4],{'ascend' 'descend' 'ascend'})
[C,ia,ic]=unique(B(:,1),'rows','first')
D=B(ia,:)
E=array2table(D);

Answer 2

[Davide Masiello]

I have expanded it for cases where there are multiple repeated values.

clear,clc
A  = [[(1:6)';(6:10)';(10:14)';(14:20)'],rand(23,1),rand(23,1)]
l0 = find(~diff(A(:,1)));

for i = 1:length(l0)
    A(l0(i),2) = max(A(l0(i),2),A(l0(i)+1,2));
    A(l0(i),3) = min(A(l0(i),3),A(l0(i)+1,3));
end

A(l0+1,:) = []

The result you get from this code is

% Before code
A =

            1      0.74618     0.080281
            2      0.11749      0.36047
            3      0.50902      0.82891
            4      0.16883      0.21461
            5      0.83111      0.79104
            6      0.92801      0.65469
            6      0.16948     0.026146
            7      0.88374      0.78578
            8      0.38786      0.92256
            9      0.38257      0.49231
           10      0.27145      0.83401
           10      0.86788      0.13135
           11       0.7415      0.75978
           12      0.44787      0.92574
           13      0.70964      0.83271
           14      0.94433       0.2594
           14      0.17412      0.21302
           15       0.2446      0.52231
           16      0.64093      0.39736
           17      0.80861      0.47911
           18      0.85337       0.9939
           19      0.39812      0.60448
           20      0.11549      0.94491

% After code
A =

            1      0.74618     0.080281
            2      0.11749      0.36047
            3      0.50902      0.82891
            4      0.16883      0.21461
            5      0.83111      0.79104
            6      0.92801     0.026146
            7      0.88374      0.78578
            8      0.38786      0.92256
            9      0.38257      0.49231
           10      0.86788      0.13135
           11       0.7415      0.75978
           12      0.44787      0.92574
           13      0.70964      0.83271
           14      0.94433      0.21302
           15       0.2446      0.52231
           16      0.64093      0.39736
           17      0.80861      0.47911
           18      0.85337       0.9939
           19      0.39812      0.60448
           20      0.11549      0.94491

[Gabi]

Thanks Davide, works well for the matrix example, any way of making it work for the table (attached)?

[Davide Masiello]

In the file you gave T is a structure with one field (also named T, which is a table).

At the beginning of your code, write

A=table2array(T.T);

then the rest of the code will work fine.

[Gabi]

Just noticed that this works if there are only 2 same values in column 1, if there are 3 or more it doesn't unfortunately. In other words, the number of repetions in col 1 can vary and it's not max 2.