Find numbers that are not in multiple columns
5 views (last 30 days)
Show older comments
I have three tables:
A1 =
5×2 table
x Var1
__ ____
1 2524
2 2342
3 4353
4 4353
5 2348
A2 =
5×2 table
x Var2
__ ____
1 6745
3 5942
4 9569
5 2454
6 1240
A3 =
5×2 table
x Var3
__ _____
2 4252
3 9345
4 9235
5 1246
6 3965
For each table I want to find and delete the rows that contains a number in the first row that is not in the first rows of both other tables. So I want to end up with this:
A1 =
3×2 table
x Var1
__ ____
3 4353
4 4353
5 2348
3×2 table
x Var2
__ ____
3 5942
4 9569
5 2454
A3 =
3×2 table
x Var3
__ ____
3 9345
4 9235
5 1246
How can I do this? Preferably with no loops.
4 Comments
Siddharth Bhutiya
on 19 May 2021
I believe this is just a dummy example, but is there a reason you have a table with 1 two column variable instead of creating a table with 2 variables? The description of your problem makes it seem that the two columns of your Var1 are separate thing (they just seem to have the same data type).
Accepted Answer
Adam Danz
on 19 May 2021
Edited: Adam Danz
on 19 May 2021
It looks like you are deleting all rows that do not contain the Var1 value in all 3 tables. Since all the tables will then have the same Var1 values you could combine them into 1 table. Use innerjoin :
% Demo tables
A1 = table((1:5)', rand(5,1))
A2 = table([1;3;4;5;6], rand(5,1))
A3 = table((2:6)', rand(5,1))
% Join table A1 and A2 to create A12
A12 = innerjoin(A1,A2,'keys', 'Var1');
% Join A12 with A3 to create A123
A123 = innerjoin(A12, A3, 'keys','Var1');
You can rename the variables using A123.Properties.VariableNames = ____
If you'd rather not join the tables
a12mem = ismember(A1.Var1, A2.Var1);
a13mem = ismember(A1.Var1, A3.Var1);
A1new = A1(a12mem & a13mem,:)
a21mem = ismember(A2.Var1, A1.Var1);
a23mem = ismember(A2.Var1, A3.Var1);
A2new = A2(a21mem & a23mem,:)
and repeate for the 3rd table.
0 Comments
More Answers (1)
Sulaymon Eshkabilov
on 19 May 2021
Edited: Sulaymon Eshkabilov
on 19 May 2021
Simle solution is a logical indexing approach, something like ...e.g.:
IND = A1.Var1~=A2.Var2;
A1=array2table([A1.x(IND), A1.Var1(IND)], 'variablenames', {'x', 'Var1'});
...
1 Comment
Adam Danz
on 19 May 2021
This does an ordered-comparison. Notice that the first row of table A3 should be removed but none of the other tables contains a 2 in the first row of column 1.
You'd need ismember if I understand the OP correctly.
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!