Intersect table with Id scattered

1 次查看（过去 30 天）
Rachele Franceschini
评论： Ive J 2021-8-31
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.

请先登录，再回答此问题。

回答（1 个）

Ive J
Ive J 2021-8-27
编辑：Ive J 2021-8-27
Ran in:
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 个评论
Rachele Franceschini
Rachele Franceschini 2021-8-31
编辑：Rachele Franceschini 2021-8-31
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 2021-8-31
Ran in:
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

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Whos 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by