Why doesn't MATLAB's "readtable" honor the 'TreatAsEmpty' option for some strings in my Excel sheet?

I have an Excel sheet test.xls with the following data:

 

Col1Col2Col35abc1 xyz21none3

I try to read this file in with the 'readtable' function using the following command:

>> x = readtable('test.xls', 'TreatAsEmpty', {'none'});

However, I see the following:

x = 
    Col1     Col2     Col3
    ____    ______    ____

      5     'abc'     1   
    NaN     'xyz'     2   
      1     'none'    3

Why does 'readtable' not replace the last entry in the second column?

Answer 1

[MathWorks Support Team]

As is explained in the documentation for 'readtable' , the 'TreatAsEmpty' option only applies to numeric columns.  Since the second column of the sheet contains only string data, the string 'none' is not replaced.As a workaround, the string can be manually replaced with a string 'NaN' using logical indexing :

>> x.Col2(strcmp(x.Col2, 'none')) = {'NaN'}
x = 
    Col1    Col2     Col3
    ____    _____    ____
      5     'abc'    1   
    NaN     'xyz'    2   
      1     'NaN'    3

Note that since this column of the table contains strings (in cells), the entry must be replaced by a cell entry and not an explicit string or numeric value.

In addition to the above, one can also use the "standardizeMissing" function to replace instances of 'none' (across all of the table's variables) with an empty string. The link for the  "standardizeMissing" is given below:

http://www.mathworks.com/help/matlab/ref/standardizemissing.html

Alternately, Col2 may usefully be converted to a categorical variable. The 'none' elements can then be turned into undefined elements simply by deleting the 'none' category.

Another way to replace the 'none' elements is to use the standardizeMissing function, which can be used to replace instances of 'none' (across all of the table's variables) with an empty string. This is a more standard way to indicate a missing string value.

Answer 2

[Peter Perkins]

A couple of other suggestions:

• It may be that Col2 could usefully be converted to a categorical variable, at which point you can turn the 'none' elements into undefined elements simply by deleting the 'none' category.
• Another way to replace the 'none' elements is to use the standardizeMissing function, which you can use to replace instances of 'none' (across all of the table's variables) with an empty string (a more standard way to indicate a missing string value).