Intersect table with Id scattered

Help me!

I have two tables (2 file excel) with Id, text, date etc..

I would like to intersect these two tables, within of one, using Id as "landmark". There is a method to avoid to do manually. Considering that the two tables haven't the same rows number.

Therefore, as result I will have one table with all data corrisponding.

Answer 1

[Ive J]

If both tables don't share same key ids, you can simply join them as:

tOne = table(["x1", "x2", "x3"].', (1:3).', 'VariableNames', {'type', 'value'});
tTwo = table(["x5", "x7"].', [5, 7].', 'VariableNames', {'type', 'value'});
tMerged = [tOne; tTwo]

tMerged = 5×2 table
    type    value
    ____    _____

    "x1"      1  
    "x2"      2  
    "x3"      3  
    "x5"      5  
    "x7"      7  

If that's not the case you can exclude overlapping rows:

tTwo = table(["x3", "x5", "x1", "x7"].', [3, 5, 1, 7].', 'VariableNames', {'type', 'value'});
remIdx = ismember(tTwo.type, tOne.type);
tNew = [tOne; tTwo(~remIdx, :)]

tNew = 5×2 table
    type    value
    ____    _____

    "x1"      1  
    "x2"      2  
    "x3"      3  
    "x5"      5  
    "x7"      7  

See also MATLAB join, innerjoin and outerjoin.

[Rachele Franceschini]

But I need to corrisponding ids. So, for example:

1st table 2nd table table finally

id id id

1 3 1

2 5 3

3 1 5

4

5

Do you have any idea?

[Ive J]

If you're looking for intersection of two tables, you have two options. Either use innerjoin:

tOne = table(["x1", "x2", "x3"].', (1:3).', 'VariableNames', {'type', 'value'});
tTwo = table(["x3", "x5", "x1", "x7"].', [3, 5, 1, 7].', 'VariableNames', {'type', 'value'});
T1 = innerjoin(tOne, tTwo, 'Keys', 'type');

or directly get intersection indices:

keepIdx = ismember(tTwo.type, tOne.type);
T2 = tOne(keepIdx, :);

The first solution is useful if other columns have different values (in my example there is only one remaining column: 'value'), while the second is useful when they have the same values.

disp(T1)

    type    value_tOne    value_tTwo
    ____    __________    __________

    "x1"        1             1     
    "x3"        3             3     

disp(T2)

    type    value
    ____    _____

    "x1"      1  
    "x3"      3