MATLAB Answers

How to force readtable to read only doubles?

137 views (last 30 days)
G. P.C.
G. P.C. on 3 Dec 2015
Commented: Walter Roberson on 10 Nov 2019
I am currently trying to import an excel file in Matlab via readtable.
The excel file I am working with has the following struncture:
Col1
1
2
n.d
4
3,5
As the data is not the same along the cells readtable correclty builds a table in the worksapce with the correct variable name (Col1) but the values are all read as strings, resulting in something like this:
Col1
------
'1'
'2'
'n.d'
'4'
'3,5'
As I would like to handle only the numbers (and thus it is ok for me to discard the text such as n.d) how can I specify readtable to read everything as double resulting in a table in the workspace as follows?
Col1
------
1
2
NaN
4
3.5
Of course a solution could be to use varfun and cast all the data, but is it possbile perform this task by using just readtable?

  0 Comments

Sign in to comment.

Answers (2)

Walter Roberson
Walter Roberson on 3 Dec 2015
I think you will need to do it in post-processing. Extract the strings, regexprep of comma to ',', then str2double(), and write the results back to Col1. Then you can delete the rows that became NaN.

  1 Comment

Walter Roberson
Walter Roberson on 10 Nov 2019
I do not know why I did not think of it before, but in the case where you know all of the possible text strings, you can handle this without the above effort, just by using the TreatAsEmpty option. This would require that the marker always be 'n.d' or any specific list you give, such as {'n.d', 'n.d.', 'nd', 'n/a'} .

Sign in to comment.


useruser
useruser on 5 Nov 2019
table = readtable('file.csv');
table.Col1 = str2double(table.Col1);
converts everything but numbers to MATLAB's NaN.

  0 Comments

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!