CODEWITHSUNDEEP
matlabmergeintersection
Mace
Mace

Reputation: 1269

Intersecting several tables of different lengths in Matlab

I have more 8+ tables in Matlab of different lengths. They all include dates in the their first column. I would like to get the intersection of all these tables on the date columns. The following small example with 3 tables shows what I want:

Date1=datenum(2011,6,7,0:24:240,0,0).';
Date2=datenum(2011,6,8,0:24:240,0,0).';
Date3=datenum(2011,6,5,0:24:240,0,0).';


T1 = table(Date1,ones(11,1),'VariableNames',{'Date','Var1'})
T2 = table(Date2,ones(11,1)*2,'VariableNames',{'Date','Var2'})
T3 = table(Date3,ones(11,1)*3,'VariableNames',{'Date','Var3'})

Thus, I would like the following output:

 Date     Var1    Var2    Var3
______    ____    ____    ____

734662    1       2       3   
734663    1       2       3   
734664    1       2       3   
734665    1       2       3   
734666    1       2       3   
734667    1       2       3   
734668    1       2       3   
734669    1       2       3

Is there a function in Matlab that can do this?

Upvotes: 1

Views: 1459

Answers (2)

Divakar
Divakar

Reputation: 221574

I wrote a very closely related code for another question and I am putting down a polished function code here that finds the intersecting elements and the corresponding indices for several arrays at the same time without resorting to any kind of looping for the computation part. Here's the code -

function [out_val,out_idx] = intersect_arrays(varargin)

%// Concatenate all vector arrays into a 2D array
if isrow(varargin{1})
    M = vertcat(varargin{:});
else
    M = horzcat(varargin{:}).';  %//'
end

%// Find unique values for all elements in all arrays
unqvals = unique(M(:),'stable')';  %//'

%// Find unqiue elements common across all arrays (intersecting elements)
out_val = unqvals(all(any(bsxfun(@eq,M,permute(unqvals,[1 3 2])),2),1));

%// Find first indices across all arrays holding the intersecting elements
[~,idx] = max(bsxfun(@eq,M,permute(out_val,[1 3 2])),[],2);
out_idx = squeeze(idx).'; %//'

return

Now, to solve your case, we can use the function code like so -

num_arrays = 3; %// Number of arrays to be used

%// Find intersecting elements and their corresppinding indices in each array
[int_ele,int_idx] = intersect_arrays(T1.Date,T2.Date,T3.Date) %// Add inputs here

%// Create an array of all Var data
all_idx = cat(2,T1.Var1,T2.Var2,T3.Var3)                      %// Add inputs here

%// Select Var data based on the intersecting indices
select_idx = all_idx(bsxfun(@plus,int_idx,[0:num_arrays-1]*size(T1.Var1,1)))

%// Output results as a numeric array and table
out_array = [int_ele(:) select_idx]
out_table = cell2table(num2cell(out_array),'VariableNames',...
                                        {'Date','Var1','Var2','Var3'})

Output -

out_table = 
     Date     Var1    Var2    Var3
    ______    ____    ____    ____
    734662    1       2       3   
    734663    1       2       3   
    734664    1       2       3   
    734665    1       2       3   
    734666    1       2       3   
    734667    1       2       3   
    734668    1       2       3   
    734669    1       2       3

Upvotes: 2

Hoki
Hoki

Reputation: 11812

The intersect function may be of use for your case.

I am not sure how you use the tables, but the code below should let you find the indices of the intersection for each DateN vector. Once you have the indices, you can rebuild a global table which only incorporate the common indices between all tables.

[C,ia,ib ] = intersect(Date1,Date2) ; %// get indices of intersection of first 2 vectors (Date1&2)
[D,ja,ix3] = intersect(  C  ,Date3) ; %// get indices of intersection of last result (Date1&2) with last vector (Date 3)

ix1 = ia(ja) ; %// take only the common indices of the 2 intersection operations (For Date1)
ix2 = ib(ja) ; %// take only the common indices of the 2 intersection operations (For Date2)

%// Build the "common" table
intersectionTable = [ Date1(ix1) Var1(ix1) Var2(ix2) Var3(ix3) ] ;

Upvotes: 2

Related Questions