Create new variable if elements of different variables match in Matlab

I have 3 cell arrays: A that has 3 columns and 5000 rows. B that has 5 and 60 columns. C that has 4 columns and 500 rows. Sample of each varaible:

A= { 12  1999  41.2
     13  1999  47.7
     16  1999  52.7}

B= { 16  452  8  407  31
   1037  110  14  873  93
   1255  881  20  16  69}

C= { 13  1997  1999  2000
    141  1997  1999  2000
    174  1997  1999  2000}

If a value of the 1st column of A matches any value of the 1st column of B , I would like to add to A, columns 1, 4 and 5 of B plus a column only with the value 'one' . In case a value of the 1st column of A matches a value of the 1st column of C, I would like to add to A, columns 1 and 4 of C and an extra column with zeroes.

So the output would be:

Output= { 12  1999  41.2
          13  1999  47.7  13 2000 0    %match between A & C 
          16  1999  52.7  16 407 31 1} %match between A & B

Can someone help me? Thank you.

[Azzi Abdelmalek]

This is not clear

Answer 1

[Azzi Abdelmalek]

EDIT

a1=[A{:,1}];
b1=[B{:,1}];
c1=[C{:,1}];
n=numel(a1);
out=cell(n,7);
out(:,1:3)=A;
ii=[ismember(a1,b1)'  ismember(a1,c1)'];
for k=1:numel(a1)
  if ii(k,1)==1;
      ib=find(b1==a1(k),1)
      out(k,4:7)=[B(ib,[1 4 5]) {1}];
  elseif ii(k,2)==1
      ic=find(c1==a1(k),1);
      out(k,4:6)=[C(ic,[1 4]) {0} ];
  end
end
out

[Maria]

It goves the error: 'Index exceeds matrix dimensions.'

[Azzi Abdelmalek]

What are the sizes of your matrices A, B and C?

[Maria]

A is a 5171x3 cell

B is a 60x5 cell

C is a 507x4 cell

[Azzi Abdelmalek]

What line gives you an error?

[Maria]

The code stops running the first time there is a match between A and B. The first 24 rows are matches between A and C and it works. Then it gives the error.

[Azzi Abdelmalek]

Ok, I found the mistake, look at the edited answer

Answer 2

[Ben11]

Here is something to get you started. It's not the most elegant but I think it answers your question. Note that I added NaN in some case in order to keep dimensions consistent.

clear
clc

A= { 12  1999  41.2
     13  1999  47.7
     16  1999  52.7};

   B= { 16  452  8  407  31
     1037  110  14  873  93
     1255  881  20  16  69};

C= { 13  1997  1999  2000
    141  1997  1999  2000
    174  1997  1999  2000};

A = cell2mat(A); % transform cell arrays into numeric arrays
B = cell2mat(B);
C = cell2mat(C);

CommonValuesIndicesBinA= find(ismember(A(:,1),B(:,1))) % find indices in A corresponding to matching values in B.
CommonValuesIndicesAinB= find(ismember(B(:,1),A(:,1))) % find indices in B corresponding to matching values in A.

CommonValuesIndicesCinA = find(ismember(A(:,1),C(:,1))); % find indices in A corresponding to matching values in C.
CommonValuesIndicesAinC = find(ismember(C(:,1),A(:,1))); % find indices in C corresponding to matching values in A.

A(CommonValuesIndicesBinA,4:7) = [ B(CommonValuesIndicesAinB,1) B(CommonValuesIndicesAinB,4) B(CommonValuesIndicesAinB,5) 1]; % Add values in A with those you want from B

A(CommonValuesIndicesCinA,4:7) = [ C(CommonValuesIndicesAinC,1) C(CommonValuesIndicesAinC,4) 0 NaN]; % Add values in A with those you want from C. Add NaN at last entry to keep dimensions consistent

[Maria]

Hey. I just tried your code. It gives the error: 'Error using horzcat Dimensions of matrices being concatenated are not consistent.' I will see what's wrong. Thanks

[Ben11]

Oh that's weird; I tried it on another computer and it works. What version are you using?

[Maria]

of Matlab? R2014a!

[Ben11]

ok. Then I'm puzzled I guess you copied/pasted the exact code right?

[Maria]

Yes I did!

[Maria]

And I tried with the sample I provided here and it works, but when I do it with my data it does not work!

[Ben11]

I modified the last part with this and I think it should work

for k =1:size(CommonValuesIndicesBinA,1)

      p = CommonValuesIndicesBinA(k);

      A(p,4:7) = [ B(CommonValuesIndicesAinB(k),1) B(CommonValuesIndicesAinB(k),4) B(CommonValuesIndicesAinB(k),5) 1]; % Add values in A with those you want from B

end

for kk =1:size(CommonValuesIndicesCinA,1)

      pp = CommonValuesIndicesCinA(kk);

      A(pp,4:7) = [ C(CommonValuesIndicesAinC(kk),1) C(CommonValuesIndicesAinC(kk),4) 0 NaN]; % Add values in A with those you want from C. Add NaN at last entry to keep dimensions consistent

end