inner join two tables treating NaN entries as identical

9 次查看(过去 30 天)
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!

采纳的回答

Adam Danz
Adam Danz 2019-6-3
编辑:Adam Danz 2019-6-5
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

更多回答(0 个)

类别

Help CenterFile Exchange 中查找有关 Tables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by