MATLAB: Substitute for join/ outerjoin in cell array

cell arrayjoin;outerjoin

It seems join and outerjoin only work for tables. Is there a substitute for cell arrays?

Best Answer

Well, you can always convert your cell arrays to a table with cell2table, do the join and convert back.

Otherwise, it's not particularly hard to implement using ismember:

%INPUTS:
%leftcell: first cell array
%rightcell: second cell array
%leftkeys: column indices for left key
%rightkeys: column indices for right key
%JOIN:
[common, rows] = ismember(leftcell(:, leftkeys), righcell(:, rightkeys), 'rows');
joinedcell = [leftcell(common, :), rightcell(rows, :)];
%OUTERJOIN:
[commonleft, rows] = ismember(leftcell(:, leftkeys), righcell(:, rightkeys), 'rows');
commonright = ismember(rightcell(:, leftkeys), leftcell(:, rightkeys), 'rows');
joinedcell = [leftcell(commonleft, :), rightcell(rows, :)];
joinedcell = [joinedcell;                    ... normal join
              [cell(sum(~commonright), size(leftcell, 2)), rightcell(~commonright, :)]; ... add non matching rows from rightcell
              [leftcell(~commonleft, :), cell(sum(~commonleft), size(righcell, 2))]];  ...add non matching rows from leftcell

I've not merged the left and right keys. It's not particularlty hard to do, and you could also select which columns to include in the join, but you should get the idea from the above.

Related Solutions

MATLAB: How to horizontally concatenate tables within a cell array (same # of rows, different # of columns) that contain both numeric and string data types

So it does look like you want to join the tables. join only works on two tables at a time so you'll have to use a loop:

bigtable = join(yourcellarray{1}, yourcellarray{2});  %join first two tables
for tidx = 3:numel(yourcellarray)
   bigtable = join(bigtable, yourcellarray{tidx});   %join remaining tables
end

By default join will use all identical variable names to find which rows to merge and any row that has is not matched in the 2nd input table will be discarded, so the above is biased towards the first table in the cell array if there are unmatched rows. It may be you want innerjoin instead of join if you only want to keep only the rows that match all tables or outerjoin if you want all rows (non-matched columns will receive nan).

It may also be that you want to restrict the matching to only ID and Unit, in which case, you indeed use the key option:

join(A, B, 'Keys', {'ID', 'Unit'})

In which case you may indeed want to discard duplicates. Once again preference will be given to the 1st input. The option is indeed 'KeepOneCopy' but you have to enclose the list of names in a cell array:

join(A, B, 'KeepOneCopy', {Depth', 'Level', 'Time', 'FilamentID', 'Category'})

MATLAB: How to merge two tables when the key variables are not common

This is called an outerjoin:

A = array2table([100 200 1; 300 400 2; 500 600 3], 'VariableNames', {'X', 'Y', 'K'});
B = array2table([1000 2000 1; 3000 4000 2; 5000 6000 3; 7000 8000 4], 'VariableNames', {'P', 'Q', 'K'});
C = outerjoin(A, B, 'MergeKeys', true)

Other types of joins are innerjoin and just plain join.

Best Answer

Related Solutions

MATLAB: How to horizontally concatenate tables within a cell array (same # of rows, different # of columns) that contain both numeric and string data types

MATLAB: How to merge two tables when the key variables are not common

Related Question