inner join two tables treating NaN entries as identical
10 views (last 30 days)
Show older comments
I would like to innerjoin multiple tables where key columns contain many NaN values, and merging the tables remove these entries since NaNs are not considered identical.
I would like to innerjoin tables treating key columns' NaN entries identical.
Below example demonstrates the problem I am having:
>> A = table({'a' 'b' 'd' 'e'}',[123, 456, 789, NaN]',[4 5 6 7]', 'VariableNames', {'Key1', 'Key2', 'Var1'})
A =
4×3 table
Key1 Key2 Var1
____ ____ ____
'a' 123 4
'b' 456 5
'd' 789 6
'e' NaN 7
>> B = table({'a' 'b' 'd' 'e'}', [123, 456, 789, NaN]', [1 2 3 4]', 'VariableNames', {'Key1', 'Key2', 'Var2'})
B =
4×3 table
Key1 Key2 Var2
____ ____ ____
'a' 123 1
'b' 456 2
'd' 789 3
'e' NaN 4
>> innerjoin(A,B)
ans =
3×4 table
Key1 Key2 Var1 Var2
____ ____ ____ ____
'a' 123 4 1
'b' 456 5 2
'd' 789 6 3
Desired output is below where Key columns NaN values are treated identical hence performing inner join on the entry where Key1 is 'e' and Key2 is NaN.
>> innerjoin(A,B)
ans =
3×4 table
Key1 Key2 Var1 Var2
____ ____ ____ ____
'a' 123 4 1
'b' 456 5 2
'd' 789 6 3
'e' NaN 7 4
Any help will be greatly appreciated!
0 Comments
Accepted Answer
Adam Danz
on 3 Jun 2019
Edited: Adam Danz
on 5 Jun 2019
Replace the NaNs with "inf", join the tables, then replace the inf with Nan.
% Change NaNs to Infs
A.Key2(isnan(A.Key2)) = inf;
B.Key2(isnan(B.Key2)) = inf;
AB = innerjoin(A,B);
% Replace Inf with nan
AB.Key2(isinf(AB.Key2)) = nan;
Result
AB =
4×4 table
Key1 Key2 Var1 Var2
____ ____ ____ ____
'a' 123 4 1
'b' 456 5 2
'd' 789 6 3
'e' NaN 7 4
More Answers (0)
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!