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

9 views (last 30 days)
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?

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 2 Mar 2021
Edited: MathWorks Support Team on 2 Mar 2021
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:
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.

More Answers (1)

Peter Perkins
Peter Perkins on 11 Mar 2015
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).

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Tags

No tags entered yet.

Products


Release

R2014b

Community Treasure Hunt

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

Start Hunting!