Cell2table issues when a numerical column is read as strings
3 次查看(过去 30 天)
显示 更早的评论
Attached is a data file test5.mat, containing a varaible "A". It stores one column (out of many columns, which can either be numerical values or strings) that was read out of an Excel using the function "readcell". Perhaps because some of the cells of this column contain empty strings, A was read a string column.
When I tried to use the command below, everything is completely messed up.
B = cell2table(A)
What is the best way to convert this column into a numerical columns, with empty strings replaced with -999? Below is what I learned from another discussion. Unfortunately, it does not work for this case.
A(cellfun(@(x)~isscalar(x) || ismissing(x),dataValue)) = {-999};
2 个评论
dpb
2025-3-11
I was heading down the road @Voss took when got interrupted and when came back saw his response already posted..so, let's continue down that path
load test5
C = cellfun(@class,A,'UniformOutput',false);
[uC,~,idx] = unique(C);
unique(A(idx == 1)) % what's the content of the nonnumeric cells?
That's peculiar, indeed--the char() cells contain a numeric character that surely could have been converted -- this raises the question that one needs to see the original file in order to diagnose why the column was not all converted as numeric.
Another case where may need to do more diagnostics on the file and use the import options object to ensure that columns that are intended to be numeric are read in as numeric by setting the Vartypes field array before importing. Or, find out what is causing the input file to be created such that the values are being mistaken as char() and fix the process that creates the input file from the start.
采纳的回答
Matt J
2025-3-11
编辑:Matt J
2025-3-11
I would also have many columns, some of which could be made up of:
(a) all doubles
(b) a mix of empty strings (e.g., ' ', ' ', etc.) and doubles (the case I described above),
(c) a mix of strings and doubles.
(d) all strings,
It would be better if you were to provide a more complete example test6.mat with all these cases, but I think this will work:
load test5
map1=cellfun('isclass',A,'double');
map2=cellfun(@(x)ischar(x)&&isempty(deblank(x)),A);
tf=all(map1|map2,1),
A(tf&map2)={-999};
A(:,~tf)=cellstr(string(A(:,~tf)));
B=cell2table(A)
3 个评论
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!