MATLAB Answers

Go through the table with a loop and change values

15 views (last 30 days)
Megan
Megan on 12 Feb 2020
Edited: Megan on 19 Feb 2020
I have 30 columns and there are values in these columns.

  2 Comments

Behzad Navidi
Behzad Navidi on 12 Feb 2020
Hello, Do you want to replace -9 to NaN across your table? I mean You want to change every -9 in your table to NaN?

Sign in to comment.

Answers (3)

Subhadeep Koley
Subhadeep Koley on 12 Feb 2020
Edited: Subhadeep Koley on 12 Feb 2020
ds = record ("xlsfile", "dataset.csv");
data = dataset2table(ds);
[rows, cols] = size(data);
newData = data;
for i = 1: rows
for j = 1: cols
if table2array(data(i, j)) == -9
newData(i, j) = array2table(NaN);
end
end
end

  7 Comments

Show 4 older comments
Megan
Megan on 12 Feb 2020
the empty rows are coded automatically as NaN in Matlab.
In my Questionnare -9 also means Error so, I want to change -9 into NaN
Subhadeep Koley
Subhadeep Koley on 12 Feb 2020
Your "dataset.csv" is encoded with UTF-16-LE, which is not fully supported by the function readtable. Therefore, I copied and pasted all the data in a new .xlsx file (attached here).
The below code might be helpful now although it is not a very efficient solution.
clc;
data = readtable('Book1.xlsx');
[rows, cols] = size(data);
newData = data;
for i = 1: rows
for j = 1: cols
temp = table2array(data(i, j));
if iscell(temp)
temp = cell2mat(temp);
end
if temp == -9
newData(i, j) = array2table(NaN);
end
end
end

Sign in to comment.


Steven Lord
Steven Lord on 12 Feb 2020
The standardizeMissing function can accept arrays of various types, including table arrays and timetable arrays. If you only want to standardize the form in which missing data is stored for certain variables in your table you can tell it to only operate on specific 'DataVariables' as well.

  0 Comments

Sign in to comment.


Behzad Navidi
Behzad Navidi on 12 Feb 2020
Edited: Behzad Navidi on 12 Feb 2020
I think you won't need to use for loop. If A is the name of the table, then you can just use:
A= readtable('dataset.csv');
A{:,:}(A{:,:}==-9) = NaN

  9 Comments

Show 6 older comments
Behzad Navidi
Behzad Navidi on 12 Feb 2020
Oh yes I'm sorry I had a typo, use this:
A= readtable('dataset.csv');
A2 = table2array(A);
A2(A2==-9) = NaN;
Megan
Megan on 12 Feb 2020
No, it did not work :(
Undefined operator '==' for input arguments of type 'cell'.
Error in analysis (line 25)
A2(A2==-9) = NaN;

Sign in to comment.

Sign in to answer this question.