How to align multiple tables/matrices based on the first column
14 views (last 30 days)
Show older comments
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
2 Comments
Walter Roberson
on 30 Sep 2011
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
Accepted Answer
Andrei Bobrov
on 30 Sep 2011
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
More Answers (1)
Oleg Komarov
on 30 Sep 2011
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)
See Also
Categories
Find more on Matrices and Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!