Converting cell array with <missing> elements to numerical or double matrix

I have to import a cell array that is composed of strings and numbers from excel to MATLAB using readcell, and one part of the larger input data that is of interest to me is an array with mostly numbers, and otherwise some strings in the first column and empty spaces in the numerical section that appear as <missing>. Usually, I used to do this using xlsread, but due to the increasing pressure by MATLAB to use its more specific functions for such operations, and also as it seems like the readcell function DOES work faster, I am trying to get familiar with readcell here.

I would like to treat the particular section of interest as a table with a numerical array, and it would be OK for me to e.g. convert the <missing> elements to number 0 in my final numerical matrix. Also keep in my mind that this section is just a small part of a much larger data file, and using readtable will not work.

Below is the code:

clc
InputData=readcell('MATLAB_readcell.xlsx',"Sheet","Sample Data")     
InputString=string(InputData)  
Numbers=InputString(:,2:end)
[ii,jj]=find(ismissing(Numbers))
Numbers(ii,jj)='0'

But here I have a problem: So far I can only convert the input to a string first, then look for <missing> in the resulting string array using ismissing, then replace those elements with a string zeor (as '0'), then convert the whole thing to a numerical array using str2num.

First of all, I assume there must be an easier way to do this, but regardless of that, the resulting numerical matrix here has saome faults, in that all the vales that appear as 70 in the string array, are equal to 0 in final matrix. In other words, although I want the program to only set the string value of every <missing> element with in particular ii&jj posiztion, it actually sets the whole row equal to '0'.

is there an easier way to do this and if not, how can I at least solve the problem with the faulty rows?

[Stephen23]

"is there an easier way to do this"

Use READTABLE:

F = 'MATLAB_readcell.xlsx';
T = readtable(F, 'NumHeaderLines',0)

T = 21x8 table
       Var1       Var2    Var3    Var4    Var5    Var6    Var7    Var8
    __________    ____    ____    ____    ____    ____    ____    ____

    {'P1'    }      30      30      30      30      30      30      30
    {'V1'    }       5       5       5       5       5       5       5
    {'U500'  }      90      90      90      90      90      90      90
    {'VIV500'}      31      31      31      31      31      31      31
    {'AFL'   }       3       3       3       3       3       3       3
    {'Palm'  }       1       1       1       1       1       1       1
    {'VULK1' }       2       2       2       2       2       2       2
    {'VULK2' }       2       2       2       2       2       2       2
    {'RHEN1' }    3.75    3.75    3.75    3.75    3.75    3.75    3.75
    {'PP1'   }      70     NaN     NaN     NaN     NaN     NaN     NaN
    {'ANT'   }       2       2       2       2       2       2       2
    {'SI'    }     7.2     7.2     7.2     7.2     7.2     7.2     7.2
    {'SULF'  }     1.7     1.7     1.7     1.7     1.7     1.7     1.7
    {'RHEN2' }     2.5     2.5     2.5     2.5     2.5     2.5     2.5
    {'RHEN3' }       2       2       2       2       2       2       2
    {'PP2'   }     NaN      70     NaN     NaN     NaN     NaN     NaN

M = T{:,2:end};
M(isnan(M)) = 0

M = 21x7

30.0000 30.0000 30.0000 30.0000 30.0000 30.0000 30.0000 5.0000 5.0000 5.0000 5.0000 5.0000 5.0000 5.0000 90.0000 90.0000 90.0000 90.0000 90.0000 90.0000 90.0000 31.0000 31.0000 31.0000 31.0000 31.0000 31.0000 31.0000 3.0000 3.0000 3.0000 3.0000 3.0000 3.0000 3.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 3.7500 3.7500 3.7500 3.7500 3.7500 3.7500 3.7500 70.0000 0 0 0 0 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

M(end-8:end,:)

ans = 9x7

1.7000 1.7000 1.7000 1.7000 1.7000 1.7000 1.7000 2.5000 2.5000 2.5000 2.5000 2.5000 2.5000 2.5000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

or READMATRIX:

M = readmatrix(F);
M = M(:,2:end);
M(isnan(M)) = 0

M = 21x7

30.0000 30.0000 30.0000 30.0000 30.0000 30.0000 30.0000 5.0000 5.0000 5.0000 5.0000 5.0000 5.0000 5.0000 90.0000 90.0000 90.0000 90.0000 90.0000 90.0000 90.0000 31.0000 31.0000 31.0000 31.0000 31.0000 31.0000 31.0000 3.0000 3.0000 3.0000 3.0000 3.0000 3.0000 3.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 3.7500 3.7500 3.7500 3.7500 3.7500 3.7500 3.7500 70.0000 0 0 0 0 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

M(end-8:end,:)

ans = 9x7

1.7000 1.7000 1.7000 1.7000 1.7000 1.7000 1.7000 2.5000 2.5000 2.5000 2.5000 2.5000 2.5000 2.5000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 2.0000 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000 0 0 0 0 0 0 0 70.0000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

[Voss]

"although I want the program to only set the string value of every <missing> element with in particular ii&jj posiztion, it actually sets the whole row equal to '0'."

@Saeid: To understand what's happening, consider the following behavior:

First, create a 4-by-4 matrix M, and attempt to set the elements of M at (1,2) and (3,4) to 100:

M = magic(4);
ii = [1; 3];
jj = [2; 4];
M(ii,jj) = 100

M = 4x4

16 100 3 100 5 11 10 8 9 100 6 100 4 14 15 1

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

That does not work as intended because it sets the elements in rows 1 and 3 in columns 2 and 4 instead of the element at row 1, column 2 and the element at row 3, column 4.

To achieve the intended result, calculate the linear index that corresponds to each (ii,jj) pair, and then set those elements of M:

M = magic(4);
ii = [1; 3];
jj = [2; 4];
idx = sub2ind(size(M),ii,jj); % sub2ind converts row+column indices to linear indices
M(idx) = 100

M = 4x4

16 100 3 13 5 11 10 8 9 7 6 100 4 14 15 1

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

See:

https://www.mathworks.com/company/technical-articles/matrix-indexing-in-matlab.html (section "Linear Indexing")

https://www.mathworks.com/help/matlab/math/array-indexing.html (section "Indexing with a Single Index")

https://www.mathworks.com/help/matlab/ref/sub2ind.html

[Peter Perkins]

The isnan thing tat Stephen shows is probably better done using fillmissing on the table you get from readtable. Something like

T = fillmissing(T,"constant",0,DataVariables=2:8)

I will say, though, that replacing missing values with 0 seems dubious, but you may have a reason.

Answer 1

[Mathieu NOE]

hello

if you want to stick with readcell, you could do that :

clc
InputData=readcell('MATLAB_readcell.xlsx',"Sheet","Sample Data")  ;   
InputData(cellfun(@(x) any(ismissing(x)), InputData)) = {0} % replace 'missin' with 0's

InputData = 21x8 cell array
    {'P1'    }    {[    30]}    {[    30]}    {[    30]}    {[    30]}    {[    30]}    {[    30]}    {[    30]}
    {'V1'    }    {[     5]}    {[     5]}    {[     5]}    {[     5]}    {[     5]}    {[     5]}    {[     5]}
    {'U500'  }    {[    90]}    {[    90]}    {[    90]}    {[    90]}    {[    90]}    {[    90]}    {[    90]}
    {'VIV500'}    {[    31]}    {[    31]}    {[    31]}    {[    31]}    {[    31]}    {[    31]}    {[    31]}
    {'AFL'   }    {[     3]}    {[     3]}    {[     3]}    {[     3]}    {[     3]}    {[     3]}    {[     3]}
    {'Palm'  }    {[     1]}    {[     1]}    {[     1]}    {[     1]}    {[     1]}    {[     1]}    {[     1]}
    {'VULK1' }    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}
    {'VULK2' }    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}
    {'RHEN1' }    {[3.7500]}    {[3.7500]}    {[3.7500]}    {[3.7500]}    {[3.7500]}    {[3.7500]}    {[3.7500]}
    {'PP1'   }    {[    70]}    {[     0]}    {[     0]}    {[     0]}    {[     0]}    {[     0]}    {[     0]}
    {'ANT'   }    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}
    {'SI'    }    {[7.2000]}    {[7.2000]}    {[7.2000]}    {[7.2000]}    {[7.2000]}    {[7.2000]}    {[7.2000]}
    {'SULF'  }    {[1.7000]}    {[1.7000]}    {[1.7000]}    {[1.7000]}    {[1.7000]}    {[1.7000]}    {[1.7000]}
    {'RHEN2' }    {[2.5000]}    {[2.5000]}    {[2.5000]}    {[2.5000]}    {[2.5000]}    {[2.5000]}    {[2.5000]}
    {'RHEN3' }    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}    {[     2]}
    {'PP2'   }    {[     0]}    {[    70]}    {[     0]}    {[     0]}    {[     0]}    {[     0]}    {[     0]}

[Peter Perkins]

readcell would work for sure, but these data are tabular, and readtable is clearly what you want. The cell array you get from readcell is going to be difficult to work with.