How can I use one row having full data whose elements repeat [like A(:,1) below] to query another data of the same type but having some elements missing?

I have this array say

A =

[0 10

0 20

1 30

1 40

2 50

2 60

3 70

3 80

4 90

4 100

5 10

5 20]

-----------------------------------

B =

[0 110

0 210

1 300

1 405

2 606

3 707

3 801

4 100

5 204]

I want A and B to have the same length by comparing and inserting 'NAN' to rows in B that are missing.

Thus to produce an output like this where the NAN is inserted;

B =

[0 110

0 210

1 300

1 405

NAN NAN

2 606

3 707

3 801

NAN NAN

4 100

NAN NAN

5 204]

Thank you in advance :)

Answer 1

[Vinayak Choyyan]

Hi Stephen,

As per my understanding, you would like the match the elements of B with values given in a vector A. There can be repetition and in such cases, we look for the next unmatched data. But as per the output provided by you, NaN needs to come before the matches for a given element in A. That is if A has [ 1 1 1] and B has [1 100; 1 200] then output should be [NaN NaN; 1 100; 2 100].

The following code assumes that A and B are in sorted order like in the example you provided. Hope this helps solve the issue you are facing.

a=[0 0 1 1 2 2 3 3 4 4 5 5]'; % a(:,1) in question
b=[0 110;0 210; 1 300; 1 405; 2 606; 3 707; 3 801;4 100; 5 204];

btmp=b;

final=zeros(length(a),2);%assign memory, same size as A
for i=1:length(a)
    idx=find(btmp(:,1)==a(i),1);
    if(length(idx)==1)
        %if match then add it to final and remove from b temp
        value=btmp(idx,:);
        final(i,:)=value;
        btmp(idx,:)=[];
    else
        j=i;
        %if not found in current b temp but existed in b temp before, then
        %shift previous elements down and add NaN at the top. This code
        %does assume that the elements are in order like in the example. if
        %not, modify the below while loop to shift values accordingly. Or
        %you can remove the while loop entirely and how NaN be added after
        %values are exhausted.
        %{
        That is the output will look like 
     0   110
     0   210
     1   300
     1   405
     2   606
   NaN   NaN
     3   707
     3   801
     4   100
   NaN   NaN
     5   204
   NaN   NaN
 
instead of 
 
     0   110
     0   210
     1   300
     1   405
   NaN   NaN
     2   606
     3   707
     3   801
   NaN   NaN
     4   100
   NaN   NaN
     5   204
 
like you had asked for. 
 
        %}
        while j>1 && final(j-1,1)==a(i) 
            final(j,:)=final(j-1,:);
            j=j-1;
        end
        final(j,:)=[NaN, NaN];
    end
end
b=final

[Stephen Tete]

@Vinayak Choyyan thank you. I tried your code and it also does the job. I appreciate it.

[Vinayak Choyyan]

@Stephen Tete Thank you for having tried my code.

I notice that the code in the accepted answer will fail if length of A is an odd number. For example if

A = [0 10; 0 20; 0 20; 1 30; 1 40; 2 50; 2 60; 3 70; 3 80; 4 90; 4 100; 5 10; 5 20];

you would get an array index bounds exceeded exception.

If this does effect your use case, you may consider modifying your code.

[Stephen Tete]

@Vinayak Choyyan yes I did notice that nevertheless, the data at hand has an even distribution of elements

In the parent and query rows hence it does the job. I'm glad you pointed it out. Your code seem rubust in both ways after trial. Thanks.

Answer 2

[Dyuman Joshi]

A = [0 10; 0 20; 0 25; 1 30; 1 40; 2 50; 2 60; 3 70; 3 80; 4 90; 4 100; 5 10; 5 20; 5 35];
B = [0 110; 0 210; 1 300; 1 405; 2 606; 3 707; 3 801; 4 100; 5 204];
sA=size(A);
sB=size(B);
B = [B; NaN(sA(1)-sB(1),sB(2))];
for idx=1:sA(1)
    if A(idx,1)~=B(idx,1)
        ct=find(B(:,1)==A(idx,1),1);
        B=B([1:ct-1 end ct:end-1],:); %shifting NaN row
    end
end
B

B = 14×2

NaN NaN 0 110 0 210 1 300 1 405 NaN NaN 2 606 3 707 3 801 NaN NaN

Note - You should ideally use tolerance to compare two values, abs(val1-val2)<tol

[Stephen Tete]

Thanks @Dyuman Joshi it works.

[Dyuman Joshi]

@Stephen Tete, I have modified my answer for a more general case (and a working example as well)