Find numbers that are not in multiple columns

2 views (last 30 days)
Dylan den Hartog
Dylan den Hartog on 18 May 2021
Edited: Adam Danz on 19 May 2021
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
Dylan den Hartog
Dylan den Hartog on 19 May 2021
You are right. I made I mistake. The tables are 5x2. I changed it in the problem description.

Sign in to comment.

Accepted Answer

Adam Danz
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))
A1 = 5×2 table
Var1 Var2 ____ _______ 1 0.84504 2 0.19125 3 0.23803 4 0.32501 5 0.41045
A2 = table([1;3;4;5;6], rand(5,1))
A2 = 5×2 table
Var1 Var2 ____ _______ 1 0.7915 3 0.85265 4 0.30851 5 0.1263 6 0.32535
A3 = table((2:6)', rand(5,1))
A3 = 5×2 table
Var1 Var2 ____ _______ 2 0.37713 3 0.26054 4 0.91348 5 0.9767 6 0.97345
% 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,:)
A1new = 3×2 table
Var1 Var2 ____ _______ 3 0.23803 4 0.32501 5 0.41045
a21mem = ismember(A2.Var1, A1.Var1);
a23mem = ismember(A2.Var1, A3.Var1);
A2new = A2(a21mem & a23mem,:)
A2new = 3×2 table
Var1 Var2 ____ _______ 3 0.85265 4 0.30851 5 0.1263
and repeate for the 3rd table.

More Answers (1)

Sulaymon Eshkabilov
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
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.

Sign in to comment.

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!