inner join two tables treating NaN entries as identical
8 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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 commentaires
Réponse acceptée
Adam Danz
le 3 Juin 2019
Modifié(e) : Adam Danz
le 5 Juin 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
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Tables dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!