CODEWITHSUNDEEP
matlabsortinginner-join
Erik
Erik

Reputation: 81

Merge tables Without sorting on Keys

I have two tables (or actually I have several) that should be merged using innerjoin. It works as expected; except that it sort on the "Keys", which destroys my data since it actually must be arranged in the original row order.

From the help section:

C is sorted by the values in the key variables

t1 = Case Val  
3 1  
1 1  
2 1  

t2 = Val2 Case Subset  
2 2 2  
1 2 2  
2 3 1  

tnew = innerjoin(t1,t2)

tnew = Case Val Val2 Subset  
2 ...   
% will start with "2" since its a lower value than "3", but in t1 "3" was in lower row than "2", it is rearranged

How should I avoid the sorting? Hopeless to use innerjoin?

Upvotes: 2

Views: 1734

Answers (1)

Suever
Suever

Reputation: 65430

In addition to the resulting table, innerjoin returns two extra outputs: The row indices of the first table and the row indices of the second table that correspond to the rows in the output.

You can simply use the second output to determine the rows in t1 that were used and you could sort these. Then use the sort order to change the ordering of the rows in the result of the join.

%// Setup the data
t1 = table([3;1;2], [1;1;1], 'VariableNames', {'Case', 'Val'});
t2 = table([2;1;2],[2;2;3],[2;2;1], 'VariableNames', {'Val2', 'Case', 'Subset'});


%// Perform the inner join and keep track of where the rows were in t1
[tnew, rows_in_t1] = innerjoin(t1, t2);

%// Sort them in order to maintain the order in t1
[~, sortinds] = sort(rows_in_t1);

%// Apply this sort order to the new table
tnew = tnew(sortinds,:);

%// Case    Val    Val2    Subset
%// ____    ___    ____    ______
%// 3       1      2       1     
%// 2       1      2       2     
%// 2       1      1       2

Upvotes: 4

Related Questions