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
Rachele Franceschini
Rachele Franceschini on 31 Aug 2021
Edited: Rachele Franceschini on 31 Aug 2021
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
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.

Categories

Find more on Tables in Help Center and File Exchange

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!