Intersect table with Id scattered
2 views (last 30 days)
Show older comments
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.
0 Comments
Answers (1)
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]
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, :)]
See also MATLAB join, innerjoin and outerjoin.
2 Comments
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)
disp(T2)
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!