Use of TreatAsEmpty to replace table values

Hello, im trying to import a .xsl doc to matlab using the next code:

data=readtable('C:\Users\Alfred\OneDrive\Libro1.XLSX','TreatAsEmpty',{''})

The file contains 3 columns with different values. The problem is that some random cells are empty so I been using 'TreatAsEmpty' to convert those empty spaces to NaN.

When I open the data, only column 3 shows with the NaN. Column 1 and 2 still have empty cells "". No matter what value or character I try to substitute, only the last column is always affected.

I'm missing something?

thanks.

[Cris LaPierre]

Share you xls file. You can attach it using the paperclip icon.

[Dionisio Mendoza]

Here it is.

Answer 1

[dpb]

'TreatAsEmpty' — Placeholder text to treat as empty value
character vector | cell array of character vectors | string | string array
Placeholder text to treat as an empty value, specified as the comma-separated pair consisting of 
'TreatAsEmpty' and a character vector, cell array of character vectors, string, or string array. 
Table elements corresponding to these characters are set to NaN.

'TreatAsEmpty' only applies to numeric columns in the file, and readtable does not accept 
numeric literals, such as '-99'.

Undoubtedly, the other columns are nonnumeric. It's possible they're supposed to be interpreted as numeric but owing to the missing values are not discerned as such by the default data type recognition logic built into readtable. Prior to R2020b readtable did not do as exhaustive a search as detectImportOptions does.

As @ChrisR notes, it's much easier if we can see the file itself, but you may need to get more explicit with the import options object.

[Dionisio Mendoza]

thanks.

Here is the xsl file.

Only the 3rd column is numeric, thats the problem?

[dpb]

As for why the 'TreatAsEmpty' option had no effect on the other columns, yes. That's what the documentation explicitly says about it.

Why would you want a NaN in place of a missing string variable, anyway? What you get is an empty char() string which is the standard missing indicator for character data. Other than the command window display shows an empty char() variable as {0x0 char} for display purposes rather than {''}, what's the problem?

You might find

>> txd=readtable('xd.XLSX','TextType','string');

more to your liking if that's the problem. NB -- nothing is needed for the NaN to be returned for missing values for the numeric column;

>> txd
txd =
  21×3 table
    SampleName    TargetName      C_  
    __________    __________    ______
    "NICB 6"      "CMV 1"       28.818
    "NICB 6"      "CMV 1"       29.026
    "NICB 6"      "CMV 1"       30.775
    "NICB 5"      "CMV 1"        31.66
    <missing>     "CMV 1"        33.47
    "NICB 5"      "CMV 1"       38.326
    "NICB 4"      <missing>     33.281
    "NICB 4"      "CMV 1"          NaN
    "NICB 4"      "CMV 1"       32.304
    "NICB 3"      "CMV 1"        34.29
    "NICB 3"      "x"           34.306
    "NICB 3"      "CMV 1"          NaN
    "NICB 2"      "CMV 1"       29.946
    "NICB 2"      "CMV 1"       33.481
    "NICB 2"      "CMV 1"       32.954
    "NICB 1"      "x"           29.173
    "NICB 1"      "CMV 1"       29.351
    "NICB 1"      "CMV 1"       29.603
    "NICB 1"      "GAPDH"          NaN
    "NICB 1"      "GAPDH"        21.91
    "NICB 1"      "GAPDH"          NaN
>>

Looking at the above data, one wonders if instead one couldn't infer what the missing string data values might be although I guess the second column might be a little dicey.

[Dionisio Mendoza]

Looks nice, Thank you.

So there is no way to deleate a specific string in a table obtainig a empty cell?

[dpb]

That's what <missing> or the '0x0 char' are -- empty cells. That's MATLAB's way of displaying an empty string array element or an empty char() string.

A table view in the command window is NOT a printed report; it's a working representation of the data in memory.

Again, if it's just the visual representation you don't like, with a string array you can have an empty string...

>> s="A"; 
>> s(3)="B";
>> s
s = 
  1×3 string array
    "A"    <missing>    "B"
>>

builds a string array w/ a missing element.

>> s(ismissing(s))=""
s = 
  1×3 string array
    "A"    ""    "B"
>> 

replaces any missing array elements with the empty string.

The two are NOT the same, but maybe you'll like the way one looks better than the other.