How do i refer to a column in my imported data and how do i turn all the NaN's into 0's?

This is what I started: [numbers,string,raw]=xlsread('mortgagedata',2);

now if I wanted to name each column x1, x2, x3,...x23, how to I do this?

Also the empty cells have been named 'NaN' how do I turn these into e.g. 0 or assign them a letter?

Thanks in advance

Answer 1

[KSSV]

Why there is necessity to name very column x1,x2,......x23 when the data is available in matrix form numbers? You can access your required column using numbers(:,1), numbers(:,3),...etc.

Replacing Nan's with zero:

   A = [NaN  1 2 ; NaN  NaN  3 ; 4 5 6]
   A(isnan(A)) = 0 ;

[mabdorab]

If I type: [numbers,string,raw]=xlsread('mortgagedata'); x1=(:,1)

There is an error on that second line. I don't think it is referring it to my imported table.

I wanted to rename them just for simplicity later when I apply GLM to them.

Also my table is 23 x 400 and I am unsure how to go through all this data replacing every NaN as you have shown.

Appreciate your help but still I little stuck

[KSSV]

x1 = numbers(:,1)

To replace NaN's, I used matrix A as example. In your case A would be numbers obtained using

   [numbers,string,raw]=xlsread('mortgagedata');

[mabdorab]

ok I have understood how to refer to the different columns but now I am confused again. sorry

[numbers,string,raw]=xlsread('mortgagedata');

numbers(isnan(numbers))=0 string(isnan(string))=0 %ERROR ON THIS LINE raw(isnan(raw))=0

[KSSV]

You cannot apply isnan thing to string and raw.

Answer 2

[Guillaume]

Do not use numbered variables. It does not simplify things later, it makes them more complicated. Keep your data as a matrix. Everytime you want x1, simply use numbers(:, 1). If you create numbered variables, you can't easily apply the same operation to several variables/columns, you just pollute the workspace with unnecessary variables, and you lose a lot of genericity.

Additionally, since the now ancient R2013b, you can have matrices with named columns in the form of tables. It makes it easy to access columns and it is also much easier to import excel files:

t = readtable('mortgagedata', 2, 'ReadVariableNames', false)
%and if your spreadsheet has a header, change 'ReadVariableNames' to true 
%and it'll use the header to name the variables
t.Properties.VariableNames = arrayfun(@(n) sprintf('x%d', n), 1:t.width, 'UniformOutput', false);

Replacing NaN or missing values is also trivial since R2016b:

t = fillmissing(t, 'constant', 0);

The above will work in just one line with a table or matrix but if you create 23 variables, you'll have to type it 23 times. See how creating numbered variables doesn't simplify anything.