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

Question

Saeid 2024-8-22，14:37

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2147269-converting-cell-array-with-missing-elements-to-numerical-or-double-matrix

评论： Peter Perkins 2024-8-22，23:37

MATLAB_readcell.xlsx

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?

3 个评论
显示 1更早的评论隐藏 1更早的评论

Stephen23 2024-8-22，15:20

在 MATLAB Online 中打开

MATLAB_readcell.xlsx

"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 2024-8-22，16:00

编辑：Voss 2024-8-22，16:02

在 MATLAB Online 中打开

"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 2024-8-22，23:37

在 MATLAB Online 中打开

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 2024-8-22，15:17

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2147269-converting-cell-array-with-missing-elements-to-numerical-or-double-matrix#answer_1503599

在 MATLAB Online 中打开

MATLAB_readcell.xlsx

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]}

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Peter Perkins 2024-8-22，23:31

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.

请先登录，再进行评论。