Go through the table with a loop and change values

I have 30 columns and there are values in these columns.

[BN]

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?

[Megan]

yes

Answer 1

[Subhadeep Koley]

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

[Megan]

columnData(:, i) = table2array(data(:, i)); % columnData variable contains your column values

I dont get this part. Where do I have to write my if statement

pseudocode:

if data == -9

data = NaN

end

[Subhadeep Koley]

@Oz Sorry, I got your point now. I have changed the answer accordingly.

[Megan]

I get this error msg

Undefined operator '==' for input arguments of type 'cell'.

Error in analysis (line 16)

if table2array(data(i, j)) == -9

[Subhadeep Koley]

@Oz Can you post the dataset.csv file?

[Megan]

I've uploaded it

[Megan]

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]

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

Answer 2

[Steven Lord]

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.

Answer 3

[BN]

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

[Megan]

Error using analysis (line 22)

Unable to concatenate the table variables 'CASE' and 'QUESTNNR', because their types are

double and cell.

[Megan]

Error using ==

Too many input arguments.

Error in analysis (line 25)

A{:,:}(A{:,:}== -9) = NaN

Did you try it?

[BN]

Try this:

A= readtable('dataset.csv');
A2 = table2array(A);
A(A==-9) = NaN;

Hope this fix the problem. I checked it using random table in my Matlab.

[Megan]

Undefined operator '==' for input arguments of type 'table'.

Error in analysis (line 25)

A(A==-9) = NaN;

Thank you, but It did not work :(

[BN]

Undefined operator '==' for input arguments of type 'table'.

did you use this line?

A2 = table2array(A);

After that we have not any table.

[Megan]

yes I did

[Megan]

but we never use A2 right?

[BN]

Oh yes I'm sorry I had a typo, use this:

A= readtable('dataset.csv');
A2 = table2array(A);
A2(A2==-9) = NaN;

[Megan]

No, it did not work :(

Undefined operator '==' for input arguments of type 'cell'.

Error in analysis (line 25)

A2(A2==-9) = NaN;