Calculate difference between two columns but skip if column has NaN

7 views (last 30 days)
I have a very large dataset (it's a table, name it MX for the example) for which I want to know the difference between two columns (one has a value of pre surgery, one a value of post surgery). For some rows one of the two values is missing, filled with NaN, and I want to skip the calculation if either one or both of the columns have NaN. I cannot delete the subjects with NaN from the dataset since I do need them for other calculations.
So for example the dataset is like this,
1 2
NaN 24
49 32
NaN 20
NaN NaN
33 NaN
45 16
36 42
64 18
34 NaN
49 NaN
55 34
64 16
And I want the difference between 1 - 2. I tried it with an if loop but got an error saying 'Operands to the logical and (&&) and or (||) operators must be convertible to logical scalar values (which is logic if it tries to do the calculation on the 'NaN').
Made a new column first with only zeros to fill the result of the loop:
MX.difference = zeros(length(MX.1),1);
if isnan(MX.1) && isnan(MX.2)
MX.difference(MX.1 - MX.2);
end
But got the error here as described above. Also tried it with an else statement to say that if one of the two or both conditions contains NaN, to write 'NaN' in the 'difference' column, but I still get the same error:
MX.difference = zeros(length(MX.1),1);
if isnan(MX.1) && isnan(MX.2)
MX.difference(MX.1 - MX.2);
else
MX.difference = 'NaN';
end
How do I fix this and make this work? Hope someone can help! I could create another script where I delete all the 'NaN' rows first but there must be a way to include it in this script without having to delete any rows. Thanks!

Accepted Answer

KSSV
KSSV on 14 Jul 2022
Edited: KSSV on 14 Jul 2022
You simply subtract the two columns....if any of the column has NaN, your difference will be NaN.
A = [NaN 24
49 32
NaN 20
NaN NaN
33 NaN
45 16
36 42
64 18
34 NaN
49 NaN
55 34
64 16] ;
dA = A(:,1)-A(:,2)
dA = 12×1
NaN 17 NaN NaN NaN 29 -6 46 NaN NaN
You can access the NaN's using isnan

More Answers (0)

Community Treasure Hunt

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

Start Hunting!