Cell column to double from table
4 views (last 30 days)
Show older comments
Hello,
I have a txt file that i have uploaded using readtable. This gives me a table of 4000000x34 variables. Some of these variabled are doubles, while some are cells. I have tried to transform the cell coulmns to doubles using cell2mat and str2double, but only get NaNs or error messages.
Some of my variables look like this: The cell ones containt the '''.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/627608/image.png)
How can I transform the cell columns to doubles? Will need this when running my regression.
6 Comments
dpb
on 25 May 2021
Edited: dpb
on 26 May 2021
There is no item_33 in the uploaded dataset so I dunno about that.
When your data aren't properly formatted, somtimes detectimportoptions needs some extra help:
opt=detectimportoptions('b.xlsx');
opt=setvaropts(opt,'item_2','Type','categorical','FillValue','YourFillValue','Prefixes',"'",'Suffixes',"'");
opt=setvaropts(opt,'item_50108','Type','double','TrimNonNumeric',true);
tB=readtable('b.xlsx',opt);
yields
>> tB(1:8,{'item_2','item_50108'})
ans =
8×2 table
item_2 item_50108
_____________ __________
M 63.40
M 51.62
M 0.00
M 45.31
YourFillValue 51.54
M 22.22
M 70.20
M 22.12
>>
You can deal with all of the issues either by defining the import objects fields to match known data types and formats or by cleaning up the data fields afterwards.
The problem with your categorical substitution is you undoubtedly didn't have a categorical variable on the RHS and you'll have to use the form shown in the documentation for categorical to add categories for values that aren't in the dataset that is converted if don't use the fill missing on import route as shown above.
Answers (0)
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!