Replace NaN in table with a corresponding value in a different column

26 views (last 30 days)
I have a table A with several columns (A1, A2, A3, A4... A33). I want to check Nan values in column A5, then replace them with the value in corresponding row in column A8.
Following is my code, but it does not seem to work correctly.
NewValue = A.A5;
if isnan(A.A5)
NewValue = A.A8;
end
I also tried to identified with (:,1) in the script. It does not seem to help.
NewValue(:,1)= A.A5(:,1);
if isnan(A.A5(:,1))
NewValue(:,1) = A.A8(:,1);
end

Accepted Answer

Paolo
Paolo on 6 Jul 2018
Edited: Paolo on 6 Jul 2018
You have not provided your table so I'll provide an example which should help you solve your problem. I have placed some NaNs in Age variable.
LastName = {'Sanchez';'Johnson';'Li';'Diaz';'Brown'};
Age = [38;NaN;NaN;40;NaN];
Smoker = logical([1;0;1;0;1]);
Height = [71;69;64;67;64];
Weight = [176;163;131;133;119];
BloodPressure = [124 93; 109 77; 125 83; 117 75; 122 80];
T = table(LastName,Age,Smoker,Height,Weight,BloodPressure)
Table T:
LastName Age Smoker Height Weight BloodPressure
_________ ___ ______ ______ ______ _____________
'Sanchez' 38 true 71 176 124 93
'Johnson' NaN false 69 163 109 77
'Li' NaN true 64 131 125 83
'Diaz' 40 false 67 133 117 75
'Brown' NaN true 64 119 122 80
To replace the NaN values in the Age variable, with, let's say, the corresponding variables in Height, you can use logical indexing:
T.Age(isnan(T.Age)) = T.Height(isnan(T.Age));
The resulting table:
LastName Age Smoker Height Weight BloodPressure
_________ ___ ______ ______ ______ _____________
'Sanchez' 38 true 71 176 124 93
'Johnson' 69 false 69 163 109 77
'Li' 64 true 64 131 125 83
'Diaz' 40 false 67 133 117 75
'Brown' 64 true 64 119 122 80

More Answers (1)

dpb
dpb on 6 Jul 2018
You need the logical addressing vector on both sides and address to return table to update the table...
idx=isnan(A.A5);
A(idx,'A5')=A(ix,'A8');
See the section on accessing data in a table for the many possible addressing modes available--it takes some time and experimentation to learn the "tricks" in getting the proper form for the desired task, granted. Access data in a table
  2 Comments
dpb
dpb on 6 Jul 2018
The above works if the data are already in the table as opposed to creating the table afterwards -- that depends on which way one wants to go at it or, perhaps, if there are data added to an existing table.
I've never found the answer to the Q? as to whether the JIT optimizer is smart enough to recognize the replicated logical test as the other Answer also uses; hence I generally create the temporary to ensure that operation is only done once at the slight expense of a little more memory from the temporary that isn't released unless do so explicitly.

Sign in to comment.

Categories

Find more on Structures 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!