Filter löschen
Filter löschen

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

12 Ansichten (letzte 30 Tage)
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 Kommentare
Voss
Voss am 22 Aug. 2024
Bearbeitet: Voss am 22 Aug. 2024
"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:
Peter Perkins
Peter Perkins am 22 Aug. 2024
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.

Melden Sie sich an, um zu kommentieren.

Akzeptierte Antwort

Mathieu NOE
Mathieu NOE am 22 Aug. 2024
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 Kommentar
Peter Perkins
Peter Perkins am 22 Aug. 2024
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.

Melden Sie sich an, um zu kommentieren.

Weitere Antworten (0)

Produkte


Version

R2024a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by