Sorting from highest to lowest for a particular column

I have A (3*5) matrix. I have B(14*6) matrix.

Now I want C (14*5) matrix from A and B in such a way that column 2 and 3 for each row of matrix B will be replaced by corresponding cell value of A.

For example – column 2 and 3 of 1st row of B is 1 and 4. Then we need to find the value of A(1,4) which is 10. This 10 will be second column value for 1st row of matrix C.

C(:,1)= B(:,1) ; C(:,2) = from above condition; C(:,3) = B(:,4); C(:,4) = B(:,5) ; C(:,5) = B(:,6)

Eventually, I want matrix D(14*5) from C with the second column value sorted from highest to lowest. Then all the rows in column 1, 3, 4, and 5 will be according to corresponding second column.

Can anyone please help me how to get this D matrix from A and B? I have attached A, B, C, and D matrix here for the clarification.

My B matrix row number is much larger in real case. I just made it smaller here to simplify the problem. Matrix A (3*5) is fixed.

Thanks in advance.

A=[13	12	11	10	3
13	9	8	7	2
13	6	5	4	1];

B=[1	1	4	0	3	2
2	1	2	2	0	2
3	1	2	0	5	0
4	2	2	3	2	0
5	3	5	3	0	2
6	3	4	0	0	5
7	2	5	3	2	0
8	2	4	0	3	0
9	2	3	0	2	3
10	3	2	0	3	2
11	3	3	0	0	5
12	1	3	0	5	0
13	1	1	1	2	2
14	1	5	0	2	2];

C=[1	10	0	3	2
2	12	2	0	2
3	12	0	5	0
4	9	3	2	0
5	1	3	0	2
6	4	0	0	5
7	2	3	2	0
8	7	0	3	0
9	8	0	2	3
10	6	0	3	2
11	5	0	0	5
12	11	0	5	0
13	13	1	2	2
14	3	0	2	2];

D=[13	13	1	2	2
2	12	2	0	2
3	12	0	5	0
12	11	0	5	0
1	10	0	3	2
4	9	3	2	0
9	8	0	2	3
8	7	0	3	0
10	6	0	3	2
11	5	0	0	5
6	4	0	0	5
14	3	0	2	2
7	2	3	2	0
5	1	3	0	2];

Answer 1

[Stephen23]

Simpler and no loops required with sub2ind:

>> X = sub2ind(size(A),B(:,2),B(:,3));
>> C = [B(:,1),A(X),B(:,4:end)]
C =
    1   10    0    3    2
    2   12    2    0    2
    3   12    0    5    0
    4    9    3    2    0
    5    1    3    0    2
    6    4    0    0    5
    7    2    3    2    0
    8    7    0    3    0
    9    8    0    2    3
   10    6    0    3    2
   11    5    0    0    5
   12   11    0    5    0
   13   13    1    2    2
   14    3    0    2    2
>> D = sortrows(C,-2)
D =
   13   13    1    2    2
    2   12    2    0    2
    3   12    0    5    0
   12   11    0    5    0
    1   10    0    3    2
    4    9    3    2    0
    9    8    0    2    3
    8    7    0    3    0
   10    6    0    3    2
   11    5    0    0    5
    6    4    0    0    5
   14    3    0    2    2
    7    2    3    2    0
    5    1    3    0    2

[GMDI]

Hi Stephen,

Thanks for your reply. I have a followup question-

I want to assign A(:,3:end) to B(:,3:end).. This assignment should be row by row. But I need to make sure two things-

1st condition is:::: sum(B(for each row,3:end)) <=5…if it is already greater than 5 , then the other elements of that row should be 0.

The other condition is :::: sum(B(:,3)) <=25, sum(B(:,4)) <=25, sum(B(:,5)) <=25 . That means for each column 3, 4, and 5 , maximum summation can be 25. If summation is already 25 for any column, then the other elements of that column should be 0.

Can you please give me any hints how to incorporate above two conditions in the following for loop? Or if there are any other alternative efficient methods? Please take a look when you get time. I’m new to matlab, and having difficulties with it. Thanks in advance.

B = nan(size(A,1),size(A,2)); 
for i=1:size(A,1)
    B(:,1:end) = A(:,1:end);    
end

I have attached both A and B here. If you take a look, you’ll see only change is happening in Row 12 and 13 for column 3,4, and 5 of Matrix B to satisfy above conditions.

A=[13	13	1	2	2
2	12	2	0	2
3	12	0	5	0
12	11	0	5	0
1	10	0	3	2
4	9	3	2	0
9	8	0	2	3
8	7	0	3	0
10	6	0	3	2
11	5	0	0	5
6	4	0	0	5
14	3	0	2	2
7	2	3	2	0
5	1	3	0	2];

B=[13	13	1	2	2
2	12	2	0	2
3	12	0	5	0
12	11	0	5	0
1	10	0	3	2
4	9	3	2	0
9	8	0	2	3
8	7	0	3	0
10	6	0	3	2
11	5	0	0	5
6	4	0	0	5
14	3	2	0	2
7	2	5	0	0
5	1	3	0	2];

[Stephen23]

@Gazi Iqbal: note that in your example code

B = nan(size(A,1),size(A,2)); 
for i=1:size(A,1)
    B(:,1:end) = A(:,1:end);    
end

the loop is totally superlfluous, because the indexing (:,1:end) already access all rows of those arrays. How to use indexing on arrays is a basic MATLAB concept you need to learn:

https://www.mathworks.com/help/matlab/math/array-indexing.html

I do not understand your description, e.g. " then the other elements of that row should be 0" what are the "other elements" ? Probably the simplest is if you provide the expected output using your example data.

[GMDI]

Hi Stephen.

Sorry, I mistakenly wrote “B(:,1:end)” instead of “B(i,1:end)”..Similarly for A(i,1:end)

B = nan(size(A,1), size(A,2));

for i=1:size(A,1)

B(i,1:end) = A(i,1:end);

end

For simplicity, Suppose my A matrix is 14*3. I want matrix B(14*3) based on that two conditions.

I want to keep assigning A into B. 1st row will get 1st priority.. and then 2nd row, 3rd row..etc....

It’s kind of checking summation of each column every time before assigning a new row from A into B. If that summation is already 25, then assign 0 in the rest of the elements of that column. But if it is not 25, then keep assigning from A (assignment value ranges 0 to 5 and integer), but we just want to make sure that summation of each row cannot be > 5

For example :

From row 1 to 12 of attached matrix A, both conditions (sum of all the previous elements of each colum is <=25; and sum of all the elements of each row is <=5) were satisfied. So I keep A[1:12,:]= B[1:12,:]

But for row 13 in A,

A(13,1:end)= [3 2 0] …but to satisfy summation of each column <=25 , I had to make it in B as B(13,1:end)= [5 0 0]

Here, I cannot assign anything in 2nd column of B, because the summation of previous elements of this column is already 25,

and in 3rd column it was already 0 in A. So I need to keep it 0. We could have assign any value here, as summation si till <=25 for this column. But as I said We want to make sure each row get maximum preference in their assignment. Since they had 0 in 3rd column of A, so I put 0 in matrix B as well.

The only thing I can do is assign 5 in 1st column, because if I assign 5 here, still summation of previous elements of Column 1 is <=25 . For example if it was [2 2 0] in A for this row, I will need to assign [4 0 0] in B.

So after assigning first element as 5, then the rest of the element is 0(zero) in row 13, as summation of each row cannot be >5.

Similarly, sum(A(1:9,2))= 25, so I need to make next 4 elements of this column 2 is o(zero) in the B matrix, as summation of each column cannot be >25

Hope this time I made it clear. Sorry for my poor English. My new input matrix A and output matrix B is given here.

Thank you so much for your time.

A=[1	2	2
2	0	2
0	5	0
0	5	0
0	3	2
3	2	0
0	2	3
0	3	0
0	3	2
0	0	5
0	0	5
0	2	2
3	2	0
3	0	2];

B=[1	2	2
2	0	2
0	5	0
0	5	0
0	3	2
3	2	0
0	2	3
0	3	0
0	3	2
0	0	5
0	0	5
2	0	2
5	0	0
3	0	2];

Answer 2

[per isakson]

Try this

%%
C1 = nan( size( B(:,1:end-1) ) );
%%
for rr = 1 : size(B,1)
    C1( rr, 2 ) = A( B(rr,2), B(rr,3) );
end
C1(:,[1,3,4,5]) = B(:,[1,4,5,6]);
%%
D1 = sortrows( C1, 2, 'descend' );

The names C1 and D1 to avoid overwriting C and D

[GMDI]

Hi Isakson,

Thanks for your reply. I have a followup question-

I want to assign A(:,3:end) to B(:,3:end).. This assignment should be row by row. But I need to make sure two things-

1. 1st condition is:::: sum(B(for each row,3:end)) <=5…if it is already greater than 5 , then the other elements of that row should be 0.
2. The other condition is :::: sum(B(:,3)) <=25, sum(B(:,4)) <=25, sum(B(:,5)) <=25 . That means for each column 3, 4, and 5 , maximum summation can be 25. If summation is already 25 for any column, then the other elements of that column should be 0.

Can you please give me any hints how to incorporate above two conditions in the following for loop? Or if there are any other alternative efficient methods? Please take a look when you get time. I’m new to matlab, and having difficulties with it. Thanks in advance.

B = nan(size(A,1),size(A,2)); 
for i=1:size(A,1)
    B(:,1:end) = A(:,1:end);    
end

I have attached both A and B here. If you take a look, you’ll see only change is happening in Row 12 and 13 for column 3,4, and 5 of Matrix B to satisfy above conditions.

A=[13	13	1	2	2
2	12	2	0	2
3	12	0	5	0
12	11	0	5	0
1	10	0	3	2
4	9	3	2	0
9	8	0	2	3
8	7	0	3	0
10	6	0	3	2
11	5	0	0	5
6	4	0	0	5
14	3	0	2	2
7	2	3	2	0
5	1	3	0	2];

B=[13	13	1	2	2
2	12	2	0	2
3	12	0	5	0
12	11	0	5	0
1	10	0	3	2
4	9	3	2	0
9	8	0	2	3
8	7	0	3	0
10	6	0	3	2
11	5	0	0	5
6	4	0	0	5
14	3	2	0	2
7	2	5	0	0
5	1	3	0	2];

[per isakson]

I don't get it

1. you want to create a function, B = foo( A ); A is input and B is output
2. foo implements the first and second condition
3. the sizes of A and B are equal
4. the conditions have the output B(:,3:end) as input

What have I missed?

[GMDI]

Hi Isakson,

Sorry, I mistakenly wrote “B(:,1:end)” instead of “B(i,1:end)”..Similarly for A(i,1:end)

B = nan(size(A,1), size(A,2)); 
for i=1:size(A,1)
    B(i,1:end) = A(i,1:end); 
end

For simplicity, suppose my A matrix is 14*3. I want matrix B(14*3) based on that two conditions.

I want to keep assigning A into B. 1st row will get 1st priority.. and then 2nd row, 3rd row..etc....

It’s kind of checking summation of each column every time before assigning a new row from A into B. If that summation is already 25, then assign 0 in the rest of the elements of that column. But if it is not 25, then keep assigning from A (assignment value ranges 0 to 5 and integer), but we just want to make sure that summation of each row cannot be > 5

For example :

From row 1 to 12 of attached matrix A, both conditions (sum of all the previous elements of each colum is <=25; and sum of all the elements of each row is <=5) were satisfied. So I keep A[1:12,:]= B[1:12,:]

But for row 13 in A,

A(13,1:end)= [3 2 0] …but to satisfy summation of each column <=25 , I had to make it in B as B(13,1:end)= [5 0 0]

Here, I cannot assign anything in 2nd column of B, because the summation of previous elements of this column is already 25,

and in 3rd column it was already 0 in A. So I need to keep it 0. We could have assign any value here, as summation si till <=25 for this column. But as I said We want to make sure each row get maximum preference in their assignment. Since they had 0 in 3rd column of A, so I put 0 in matrix B as well.

The only thing I can do is assign 5 in 1st column, because if I assign 5 here, still summation of previous elements of Column 1 is <=25 . For example if it was [2 2 0] in A for this row, I will need to assign [4 0 0] in B.

So after assigning first element as 5, then the rest of the element is 0(zero) in row 13, as summation of each row cannot be >5.

Similarly, sum(A(1:9,2))= 25, so I need to make next 4 elements of this column 2 is o(zero) in the B matrix, as summation of each column cannot be >25

Hope this time I made it clear. Sorry for my poor English. My new input matrix A and output matrix B is given here.

Thanks for your time.

A=[1   2      2
2      0      2
0      5      0
0      5      0
0      3      2
3      2      0
0      2      3
0      3      0
0      3      2
0      0      5
0      0      5
0      2      2
3      2      0
3      0      2];

B=[1   2      2
2      0      2
0      5      0
0      5      0
0      3      2
3      2      0
0      2      3
0      3      0
0      3      2
0      0      5
0      0      5
2      0      2
5      0      0
3      0      2]; 

[per isakson]

@Gazi Iqbal: In your April 5 comment you wrote "1st condition is:::: sum(B(for each row,3:end)) <=5…if it is already greater than 5 , then the other elements of that row should be 0." Your April 6 comment make me think it should have been sum( A (for each row,3:end)). And the same mistake in condition 2.

.. more to come

[GMDI]

Hi Isakson, please disregard my April 5 comment.

In my April 6 comment- I mentioned that A is my input matrix. So there is nothing to do in A matrix. And I modified input matrix A (it's 14*3 matrix). Similarly, output matrix B will be 14*3 .

I need to assign A into B row by row. 1st row will get 1st priority.. and then 2nd row, 3rd row..etc.... I trued to make it clear below with an example. If you please read "For example" part below - then you might be able to understand what I meant.

It’s kind of checking summation of previous values of each column every time before assigning a new row from A into B. If that summation is already 25, then assign 0 in the rest of the elements of that column. But if it is not 25, then keep assigning from A (assignment value ranges 0 to 5 and integer), but we just want to make sure that summation of each row cannot be > 5

For example :

From row 1 to 12 of attached matrix A, both conditions (sum of all the previous elements of each colum is <=25; and sum of all the elements of each row is <=5) were satisfied. So I keep A[1:12,:]= B[1:12,:]

But for row 13 in A,

A(13,1:end)= [3 2 0] …but to satisfy summation of each column <=25 , I had to make it in B as B(13,1:end)= [5 0 0]

Here, I cannot assign anything in 2nd column of B, because the summation of previous elements of this column is already 25,

and in 3rd column it was already 0 in A. So I need to keep it 0. We could have assign any value here, as summation si till <=25 for this column. But as I said We want to make sure each row get maximum preference in their assignment. Since they had 0 in 3rd column of A, so I put 0 in matrix B as well.

The only thing I can do is assign 5 in 1st column, because if I assign 5 here, still summation of previous elements of Column 1 is <=25 . For example if it was [2 2 0] in A for this row, I will need to assign [4 0 0] in B.

So after assigning first element as 5, then the rest of the element is 0(zero) in row 13, as summation of each row cannot be >5.

Similarly, sum(A(1:9,2))= 25, so I need to make next 4 elements of this column 2 is o(zero) in the B matrix, as summation of each column cannot be >25

Input matrix A and output matrix B is given below:

A=[1   2      2
2      0      2
0      5      0
0      5      0
0      3      2
3      2      0
0      2      3
0      3      0
0      3      2
0      0      5
0      0      5
0      2      2
3      2      0
3      0      2];
B=[1   2      2
2      0      2
0      5      0
0      5      0
0      3      2
3      2      0
0      2      3
0      3      0
0      3      2
0      0      5
0      0      5
2      0      2
5      0      0
3      0      2];

[per isakson]

@Gazi Iqbal: "A(13,1:end)=[3 2 0] …but to satisfy summation of each column <=25 , I had to make it in B as B(13,1:end)=[5 0 0]" couldn't that result in sum(B(:,1) > 25 ? (With another set of input data.)

[GMDI]

Hi Isakson,

sum(B(1:12,1)) is 1+2+0+0+0+3+0+0+0+0+0+2 = 8, so I assigned B(13,1) = 5, because then sum(B(1:13,1)) = 13 which satisfy sum(B(:,1)) <=25.

The thing is- from A(13,1:end)=[3 2 0], 1st of all I cannot assign 2 in B(13,2), because already sum(B(1:12,2))=25. That's why I had to make B(13,2)=0

Then, since we want to fulfill the wish of each row, that’s why I put B(13,3) = 0, because it was A(13,3) = 0

I have only one option left here which is to assign B(13,1) = 5 (we assign 5 here because sum(A(13,1:end)) was 5. If it was 4 then I had to assign B(13,1) =4, which still satisfy the condition sum(B(:,1)) <=25). But at the same time we need to make sure that sum(B(:,1)) cannot be >25.

You’re right, I assigned 5 here because it didn’t violate >=25 condition. But suppose sum(B(1:12,1)) was 25, then we had to assign 0 in B(13,1). If sum(B(1:12,1)) =23, then we had to assign B(13,1)=2. Consequently, B(14,1) will be 0.

Hope I made it clear.

[per isakson]

The sum of the rows in this particular A, satisfy sum(A(rr,:)) <= 5 for all rr. However, the algorithm cannot be based on that - or ?

I'm prepared to throw in the towel

%%
csA2 = cumsum( A, 2 );
is_row_cumsum_gt_5 = csA2 > 5;
%
csA1 = cumsum( A, 1 );
is_col_cumsum_gt_25 = csA1 > 25;
%%
B1 = A;
for rr = 2 : size( A, 1 )
    % change what's needed to fullfil the conditions
    % I envision a lot of edge cases resulting in as 
    % many if-statements, which in turn requires a lot 
    % of testing 
end

[GMDI]

Thank you so much for your reply.

You’re right. In my real input matrix, sum(A(rr,:)) <= 5 for all rr

If I want to focus only on sum(B1(:,jj)) <= 25 for all jj part, then I tried following :

csA1 = cumsum( A, 1 );
is_col_cumsum_gt_25 = csA1 > 25;
B1 = A;
B1(1,:) = A(1,:); 
for i=2:size(A,1)
    for j= 1:size(A,2)
        if is_col_cumsum_gt_25(i,j) == 0
            p = 25-sum(B1(1:i-1,j));
            if p >5
                B1(i,j) = A(i,j);
            else
                B1(i,j) = min(A(i,j),p);
            end
        else
            B1(i,j) = 0;
        end
    end
end

Above code works fine for previous input matrix A. but if my input matrix A is like below:

A=[ 1  2     2
2     0     2
0     5     0
0     5     0
0     3     2
3     2     0
5     2     3
5     3     0
0     3     2
5     0     5
0     0     5
3     2     2
3     2     0
3     0     2];

Then above code doesn’t give the result what I want. Because from this A matrix, B1[13,1] was supposed to be 1, but it is 0 from above code. And I know why this is happening. Because csA1(13,1) = 27 and that’s why is_col_cumsum_gt_25(13,1) ==1 which eventually makes B1(13,1) =0 from above code. Do you have any suggestions to solve this issue?

I’m grateful to you for all these replies. Hopefully, this is my last question for this problem.