Asked by elmar.a
on 21 May 2019 at 14:11

This question is related to How can I merge two different tables using the first column in common? but the accepted answer does not fully solve my issue.

I have a case where there are more than two arrays, some of which have the same identifier in the first column, such as

A = [1 7;

3 15]

B = [2 9;

5 10]

C = [2 5;

3 4]

From this I'd like to get

[1 7 0 0;

2 0 9 5;

3 15 0 4;

4 0 0 0;

5 0 10 0]

that means if identifiers are the same (as for row 3) the values of A, B, and C should appear in the same row.

Answer by Jos (10584)
on 21 May 2019 at 19:40

Accepted Answer

% data, (showing the drawback of storing relates things in different variables)

A = [1 7;

3 15]

B = [2 9;

5 10]

C = [2 5;

3 4]

% simple indexing engine

A(:,3) = 2, B(:,3) = 3, C(:,3) = 4 % add column numbers to input

D = cat(1,A,B,C)

sz = [max(D(:,1)), D(end,3)]

m = zeros(sz)

m(D(:,1), 1) = D(:,1)

m(sub2ind(sz, D(:,1), D(:,3))) = D(:,2)

Answer by Adam Danz
on 21 May 2019 at 15:10

Edited by Adam Danz
on 21 May 2019 at 15:18

I find it easier to first combine the matrices into a 3D array. This should work with any number of matrices as long as they are the same size. "m" is your final matrix.

ABC = cat(3,A,B,C);

% Create final matrix (all 0s except first column)

m = zeros(max(ABC(:,1,:),[],'all'),size(ABC,3)+1); %prior to r2018b: zeros(max(max(squeeze(ABC(:,1,:)))),size(ABC,3)+1)

m(:,1) = 1:size(m,1);

% Find the column and row indices of M for the elements in ABC

colID = reshape(repelem(2:size(ABC,3)+1,size(ABC,2),size(ABC,2)-1,1),[],1);

[~, rowID] = ismember(reshape(ABC(:,1,:),[],1),m(:,1));

% fill in the rest of the m matrix

m(sub2ind(size(m),rowID,colID)) = ABC(:,2:end,:);

Result

m =

1 7 0 0

2 0 9 5

3 15 0 4

4 0 0 0

5 0 10 0

Jos (10584)
on 22 May 2019 at 14:26

Note that this solution assumes that all matrices have the same size.

Jos (10584)
on 22 May 2019 at 15:01

Ah, my mistake, Adam, I overlooked that in your answer :-)

Sign in to comment.

Answer by Guillaume
on 21 May 2019 at 16:19

The question is a bit confusing. Tables are mentioned in the title, but the examples are matrices. Two tables is mentioned in the table, but the example has 3 inputs.

A = [1 7;

3 15]

B = [2 9;

5 10]

C = [2 5;

3 4]

%cell array of tables

t{1} = array2table(A, 'VariableNames', {'ID', 'A'});

t{2} = array2table(B, 'VariableNames', {'ID', 'B'});

t{3} = array2table(C, 'VariableNames', {'ID', 'C'});

result = outerjoin(t{1}, t{2}, 'MergeKeys', true); %outer join the first two

for tidx = 3:numel(t) %loop over the rest (works with any number of tables)

result = outerjoin(result, t{tidx}, 'MergeKeys', true);

end

For matrices, I'd use Adam's answer.

Opportunities for recent engineering grads.

Apply Today
## 2 Comments

## madhan ravi (view profile)

Direct link to this comment:https://de.mathworks.com/matlabcentral/answers/463294-follow-up-how-can-i-merge-two-different-tables-using-the-first-column-in-common#comment_707365

## Adam Danz (view profile)

Direct link to this comment:https://de.mathworks.com/matlabcentral/answers/463294-follow-up-how-can-i-merge-two-different-tables-using-the-first-column-in-common#comment_707366

Sign in to comment.