Compare two tables with two column combinations
17 views (last 30 days)
Show older comments
Aletta Wilbrink
on 6 Feb 2018
Commented: Bradley Stiritz
on 6 Dec 2019
I have two tables, say:
t1 = table(categorical({'a';'b';'c';'c';'a';'b'}),categorical({'d';'e';'d';'f';'e';'d'}),[1;2;3;4;5;6],datetime({'2017-1-1 12:00 AM','2017-1-3 10:20 AM','2017-1-3 1:36 PM','2017-1-4 9:45 AM','2017-1-6 12:00 AM','2017-1-7 12:12 PM'})')
t2 = table(categorical({'a';'a';'c';'b';'c'}),categorical({'f';'d';'e';'f';'d'}),[5;4;3;2;1],datetime({'2017-1-1 12:00 AM','2017-1-3 6:20 AM','2017-1-3 1:36 PM','2017-1-4 9:45 AM','2017-1-6 12:10 AM'})')
t1 =
6×4 table
Var1 Var2 Var3 Var4
____ ____ ____ ____________________
a d 1 01-Jan-2017 00:00:00
b e 2 03-Jan-2017 10:20:00
c d 3 03-Jan-2017 13:36:00
c f 4 04-Jan-2017 09:45:00
a e 5 06-Jan-2017 00:00:00
b d 6 07-Jan-2017 12:12:00
t2 =
5×4 table
Var1 Var2 Var3 Var4
____ ____ ____ ____________________
a f 5 01-Jan-2017 00:00:00
a d 4 03-Jan-2017 06:20:00
c e 3 03-Jan-2017 13:36:00
b f 2 04-Jan-2017 09:45:00
c d 1 06-Jan-2017 00:10:00
Now I want the result or the rownumbers of every combination of the first two columns in t2 that are not in t1
How can I do this?
*Edited question with a little more complicated table
0 Comments
Accepted Answer
Peter Perkins
on 6 Feb 2018
tables have all the same "set membership" functions as numeric arrays:
>> t1 = table({'a';'b';'c';'c';'a';'b'},{'d';'e';'d';'f';'e';'d'});
>> t2 = table({'a';'a';'c';'b';'c'},{'f';'d';'e';'f';'d'});
>> [t3,i1] = setdiff(t1,t2)
t3 =
4×2 table
Var1 Var2
____ ____
'a' 'e'
'b' 'd'
'b' 'e'
'c' 'f'
i1 =
5
6
2
4
3 Comments
Guillaume
on 7 Feb 2018
Well, then you use
[~, i1] = setdiff(t1(:, [1 2]), t2(:, [1 2]));
t3 = t1(i1, :)
Bradley Stiritz
on 6 Dec 2019
Hi Peter,
Your answer is very elegant, thank you. Do you have any thoughts please on also checking for differences in value vs. not-a-value status -- i.e. NaN, NaT ?
Would you recommend working along the lines of e.g. varfun(@isnan..)) ?
Thanks,
Brad
More Answers (1)
Star Strider
on 6 Feb 2018
If I understand what you want to do, this will work:
Lv = ismember(t2, t1, 'rows');
DesiredRows = find(~Lv)
DesiredRows =
1
3
4
0 Comments
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!