inner join two tables treating NaN entries as identical

10 visualizzazioni (ultimi 30 giorni)
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!

Risposta accettata

Adam Danz
Adam Danz il 3 Giu 2019
Modificato: Adam Danz il 5 Giu 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
  1 Commento
Louis
Louis il 5 Giu 2019
this works. (I don't know why MATLAB treats NaNs differently yet infs the same)
thank you!

Accedi per commentare.

Più risposte (0)

Categorie

Scopri di più su Tables in Help Center e File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by