Intersect table with Id scattered

1 view (last 30 days)
Rachele Franceschini
Rachele Franceschini on 27 Aug 2021
Commented: Ive J on 31 Aug 2021
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.

Answers (1)

Ive J
Ive J on 27 Aug 2021
Edited: Ive J on 27 Aug 2021
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.
  2 Comments
Ive J
Ive J on 31 Aug 2021
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

Sign in to comment.

Products


Release

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!