readtable (excel) behavior for string type with empty cell vs spaces

62 次查看（过去 30 天）
J. Alex Lee
J. Alex Lee 2020-12-17
编辑： J. Alex Lee 2021-6-3
I am trying to make an excel import function robust to treat either empty cells or cells containing only spaces in a specific way, namely to fill with the string
""
(I know, this may not seem useful, but it is for me...)
% define the columns of data variable type and name
Variables = [...
"double","Index";
"string","StringData";
"string","Comment"];
% create import options for a table structure that has
% 1st row as a column type descriptor
% 2nd row as the traditional "header" with column names
% 3rd + rows as data
opts = spreadsheetImportOptions(...
"NumVariables",size(Variables,1),...
"VariableNames",Variables(:,2)',...
"VariableTypes",Variables(:,1)',...
"DataRange","A3",...
"VariableNamesRange","A2",...
"VariableDescriptionsRange","A1",...
"RowNamesRange","A3",...
"ImportErrorRule","fill");
% set the variable options for the string data column
opts = setvaropts(opts,"StringData","FillValue","");
out = readtable("example_empty_cell.xlsx",opts)
For the attached excel file, this returns
out =
6×3 table
Index StringData Comment
_____ __________ ______________________
1 1 <missing> "single space"
2 2 <missing> "three spaces"
3 3 <missing> "empty"
4 4 <missing> "apostrophe and space"
5 5 <missing> "apostrophe only"
6 6 "hello" "non empty string"
It's not what I would expect...but if my replacement string is instead
% set the variable options for the string data column
opts = setvaropts(opts,"StringData","FillValue"," ");
I get the expected
out =
6×3 table
Index StringData Comment
_____ __________ ______________________
1 1 " " "single space"
2 2 " " "three spaces"
3 3 " " "empty"
4 4 " " "apostrophe and space"
5 5 " " "apostrophe only"
6 6 "hello" "non empty string"
Perhaps this issue is not specific to readtable/setvaropts, but rather some general internal conversion of "" into <missing>?
One clue is that if I set the fill value to strings(0), it produces an error saying
Expected a value which can be converted to a character vector.
In any case, I'm wondering if there is a one-shot way to achieve what I want without having to post-process the imported data one way or another?

请先登录，再回答此问题。

采纳的回答

J. Alex Lee
J. Alex Lee 2020-12-22
编辑：J. Alex Lee 2020-12-22
According to Jemima's answer, confirmed that the issue is an internal conversion of the "" to <missing> within readtable. Further, this behavior is different in 2019b and previous. Ultimately my issue stems from the fact that these are not equivalent in other contexts that I care about, i.e., that
""==string(missing) % is false
ismissing("") % is also false
My own two work-arounds, which seem more rational to me, are:
  1. Import using the settings I already have, then convert <missing> into "" with rowfun or loops
  2. Anywhere I need to check for "", also check for missing as an alternative: (var=="" || ismissing(var))

更多回答（2 个）

Jemima Pulipati
Jemima Pulipati 2020-12-21
编辑：Jemima Pulipati 2020-12-21
Hello,
From my understanding, you want to fill the missing/empty values in table with an empty character.
Starting from R2020a, the behaviour of readtable has been modified with respect to the usage of import options (Check Compatibility Considerations). By default readtable uses the import options which implies all missing values are represented using "<missing>". So in your case, when you are trying to set the missing values using
opts = setvaropts(opts,"StringData","FillValue","");
This considers that empty character to be a missing value and uses the standard missing value representation of string i.e '<missing>'. But when you use:
opts = setvaropts(opts,"StringData","FillValue"," ");
This considers the fill value as a valid string and just sets the missing values.
This above behaviour occurs because of import options being used by readtable.
The behaviour of readtable() upto R2019b was different i.e it used the empty character representation instead of '<missing>' to represent missing values in tables.
The suggested workarounds are:
  • You may have to use 'Fomat: auto' to use the empty character representation (specifying this option chooses the behaviour that was present upto R2019b and does not by default consider the import options).
Example:
out = readtable("example_empty_cell.xlsx","Format","auto")
  • readtable can use the import options and after reading the table you may set the column values explicitly to display according to the use case.
Example :
out = readtable("example_empty_cell.xlsx",opts);
out(1:end-1,2) = {''};
  • Modifying the variable type to 'char' instead of 'string' for the column.
Variables = [...
"double","Index";
"char","StringData";
"string","Comment"];
This shows the empty character as a representation for the missing values as this is the representation for the 'char' datatype.
NaN — double, single, duration, and calendarDuration
NaT — datetime
<missing> — string
<undefined> — categorical
' ' — char
{''} — cell of character arrays
  1 个评论
J. Alex Lee
J. Alex Lee 2020-12-21
编辑：J. Alex Lee 2020-12-21
Hmm. Thanks for the exaplanation and potential workarounds...
But if I understand the first work around correctly, your advice to use "Format auto" introduces an ambiguity in code readability that has matlab version-dependence, which I believe is not desirable...
Second workaround suggests using char type instead of string for import, and then post-process later...but this is not any better to me than post-processing the <missing> string value into "", which seems cleaner...
And I'm probably barking up the wrong tree here, but I'm not sure I understand the logic behind this behavior change, especially since
string(missing)==""
ismissing("")
both return false...ultimately, I can live with a <missing> string value if I can test for missing instead of equal to empty character in all contexts.

请先登录，再进行评论。

Julian Hapke
Julian Hapke 2021-6-3
编辑：Julian Hapke 2021-6-3
If you do not use the FillValue property of the VariableImportOptions, but set EmptyFieldRule to "auto", missing strings are imported as "" instead of <missing>. Since you can set this for every variable separately, this does not make the use of "Format", "auto" necessary.
Edit: unfortunately in my test case, not all missing values are replaced by an empty string, some still come back as missing. this is really frustrating.
  3 个评论
显示 1更早的评论
Julian Hapke
Julian Hapke 2021-6-3
yes, after checking my real world data instead of a simple test case, I must agree (see edit), it's not really useful.
J. Alex Lee
J. Alex Lee 2021-6-3
编辑：J. Alex Lee 2021-6-3
Ran in:
Ah yes. i think maybe the core of the issue is not how it is imported, but rather how downstream of that we can treat the 2 things semantically...I guess you could argue the below results are logical/consistent...but could it also be argued that ismissing("") ought to return true?
ismissing("")
ans = logical
0
isempty("")
ans = logical
0
ismissing(missing)
ans = logical
1
isempty(missing)
ans = logical
0

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by