How to align multiple tables/matrices based on the first column

Hello,

I am new to programming, and trying to write a script to align multiple 4-column tables based on the first column. The first column are numbers in the order from small to large(you may think it as date). The numbers may be slightly different from table to table, and some tables may have more rows than the others. In the script I wrote below, it covers 4 tables(A,B,C,D), and generate the aligned table A. I can repeat the codes to generated aligned table B, C, D as well. However, my problem is that the number of input tables may vary from 4 to 20, and I don't want to revise my script every time. I have not figured out how to write such a script. Any suggestions are appreciated.

The script is the following:

function z=align4(A, B, C, D, reslst) % align matrices A, B, C and D

if nargin<5, reslst=[]; end  %default: all resid.

if isempty(reslst), 

    rlst=[min([A(:,1);B(:,1);C(:,1);D(:,1)]):max([A(:,1);B(:,1);C(:,1);D(:,1)])]'; % Generate a complete residue list.

    else

    rlst=reslst(:);

end

nres=length(rlst);

z=NaN*ones(nres,4);

z(:,1)=rlst;

for ii=1:nres,

    indA=find(A(:,1)==rlst(ii));

    if ~isempty(indA), z(ii,2:end)=A(indA,2:end); end 

end

return

[Walter Roberson]

You have not defined what you mean by "align" for this purpose.

Do you mean something like a database "join" operation ? http://en.wikipedia.org/wiki/Join_%28SQL%29

[DZ]

My case looks simpler but different. I want all the output matrices to have same dimensions. An example of input matrices (first column) would be:

t1(:,1)=[1; 2; 3; 5];

t2(:,1)=[2; 3; 4; 5];

t3(:,1)=[1; 2; 3; 4; 6];

t4(:,1)=[1; 3; 4; 6];

In this case, I want output matrices have the same first column [1:6]'. The missing rows will be filled with NaN.

Answer 1

[Andrei Bobrov]

variant 1

function zout = funforDZ(zin,r1)
%{
All matrixs (A,B,C ... and etc.) include in cell array 'zin'
zin = {A B C ...}
%}
c = zin(:);
M = cat(1,c{:});
if nargin < 2
    r1 = (min(M(:,1)):max(M(:,1)))';
end
s = cellfun('size',c,1);
n = numel(s);
m = numel(r1);
z = nan(n*m,4);
X = mat2cell(bsxfun(@eq,r1,M(:,1)'),m,s);
[i1,j1] = find(blkdiag(X{:}));
z(:,1) = repmat(r1,n,1) ;
z(i1,2:end) = M(j1,2:end);
zout = mat2cell(z,repmat(m,n,1),4);

variant 2 with loop

function zout = funforDZloop(zin,r1)
%{
All matrixs (A,B,C ... and etc.) include in cell array 'zin'
zin = {A B C ...}
with loop
%}
if nargin < 2
    c1 = cellfun(@(x)x(:,1),zin,'un',0);
    M = cat(1,c1{:});
    r1 = (min(M(:,1)):max(M(:,1)))';
end
n = numel(zin);
zout = cell(n,1);
z = nan(numel(r1),4);
for j1 = 1:n
 zout{j1} = z ;
 [i1,loc] = ismember(r1,zin{j1}(:,1));
 zout{j1}(i1,:) = zin{j1}(loc(loc~=0),:);
end

[DZ]

Thank you Andrei for your scripts.

variant2 works if I provide r1. Otherwise the zout would be empty.

variant 1 did not work for me because I don't have bsxfun, and C compiler on my computer.

My matlab version is 7.0.1(R14)SP1. I wonder if that's a problem why some scripts you guys suggested don't work.

Answer 2

[Oleg Komarov]

function z = align4(reslst,varargin) % align matrices A, B, C and D

if isempty(reslst)
    rlst = [min(cellfun(@(x) x(1,1),varargin)):max(cellfun(@(x) x(end,1),varargin))].';
else
    rlst = reslst(:);
end

nList = numel(rlst);
nIn   = numel(varargin);
z     = cell(nIn,1);
for n = 1:nIn
    z{n} = NaN(nList,size(varargin{n},2));
    z{n}(ismember(rlst,varargin{n}(:,1)),:) = varargin{n};
end

An example:

t1 = [[2; 4; 6] randi(100,3,4)];
t2 = [[5; 7] randi(100,2,4)];
align4([],t1,t2)

[DZ]

Actually your script works as well.

The problem I had related to "cellfun" is that because my matlab version is old. I can get rlst in some other way without a problem. So, thanks for your input.