How to convert cell columns in a table?

Hi there,

I am reading a CSV dataset with readtable, and it automatically creates some columns of the table in cell arrays. More specifically, what I'm obtaining is

data=readtable('LA-01.csv','Delimiter',' ','MultipleDelimsAsOne', 1);

>> data(1:5,:)

ans =

  5×24 table

      Var1          Var2          Var3         Var4       Var5    Var6    Var7    Var8    Var9    Var10      Var11      Var12      Var13     Var14     Var15     Var16    Var17    Var18    Var19    Var20    Var21     Var22     Var23      Var24   
    _________    __________    __________    _________    ____    ____    ____    ____    ____    ______    _______    _______    _______    _____    _______    _____    _____    _____    _____    _____    ______    ______    _____    __________

    {'LA-01'}    1.5593e+09    31/05/0019    {'23:05'}    14.1    14.1    14.1    13.3     95     1020.3    {'3.2'}    {'ESE'}    {'112'}     4.8     {'4.8'}      0        0        0        0       323     {'ND'}    {'ND'}     83      {0×0 char}
    {'LA-01'}    1.5593e+09    31/05/0019    {'23:10'}    14.2    14.2    14.1    13.4     95     1020.3    {'0.0'}    {'N/A'}    {'N/A'}     3.2     {'4.8'}      0        0        0        0       323     {'ND'}    {'ND'}     83      {0×0 char}
    {'LA-01'}    1.5593e+09    31/05/0019    {'23:15'}    14.3    14.3    14.1    13.5     95     1020.3    {'0.0'}    {'N/A'}    {'N/A'}     1.6     {'4.8'}      0        0        0        0       323     {'ND'}    {'ND'}     83      {0×0 char}
    {'LA-01'}    1.5593e+09    31/05/0019    {'23:20'}    14.3    14.3    14.1    13.5     95     1020.2    {'0.0'}    {'N/A'}    {'N/A'}       0     {'4.8'}      0        0        0        0       323     {'ND'}    {'ND'}     83      {0×0 char}
    {'LA-01'}    1.5593e+09    31/05/0019    {'23:25'}    14.2    14.3    14.1    13.4     95     1020.1    {'0.0'}    {'N/A'}    {'N/A'}     1.6     {'4.8'}      0        0        0        0       323     {'ND'}    {'ND'}     83      {0×0 char}
                                                                                                                                                                                                                

The original file is

LA-01 1559336400 31/05/19 22:00 14.8 21.3 12.4 13.8 94 1020.3 1.6 S 180 3.2 24.1 6.2 0.0 75.2 131.4 323.0 ND ND 83 
LA-01 1559336700 31/05/19 22:05 14.7 21.3 12.4 13.7 94 1020.3 0.0 N/A    N/A 3.2 24.1 6.2 0.0 75.2 131.4 323.0 ND ND 83 
LA-01 1559337000 31/05/19 22:10 14.6 21.3 12.4 13.7 94 1020.3 0.0 N/A    N/A 3.2 24.1 6.2 0.0 75.2 131.4 323.0 ND ND 83 
LA-01 1559337300 31/05/19 22:15 14.6 21.3 12.4 13.6 94 1020.3 0.0 N/A    N/A 0.0 24.1 6.2 0.0 75.2 131.4 323.0 ND ND 83 
LA-01 1559337600 31/05/19 22:20 14.5 21.3 12.4 13.5 94 1020.3 0.0 N/A    N/A 0.0 24.1 6.2 0.0 75.2 131.4 323.0 ND ND 83 

As you can see, some numeric columns are automatically stored as cell arrays in the table, and I would like to convert them to double, for doing math operations for example. I also would like to convert the text columns (like 1 and 4) to string arrays.

Is this possible?

Thank you

[Guillaume]

It would be helpful if your example file matched the one used in your demo code. Even better, please attach LA-01.csv to your question.

[Andrea Cecilia]

This is a portion of my code which gives error because it is impossible to use math operator "+" for a cell.

if ~isequal(table2array(data(ii,j)),"NaN")
       cell=table2cell(data(:,j));
       sum=sum+cell{ii};
end

You can find attached the original file.

Answer 1

[Guillaume]

Probably,

data = readtable('LA-01.csv', 'Delimiter', ' ', 'MultipleDelimsAsOne', 1, 'TreatAsEmpty', 'N/A')

would fix the problem. If not, please attach a problematic demo file.

[Andrea Cecilia]

This actually solved the problem, thanks! But, just in case, I am curious to understand how to convert cells to double.

[Guillaume]

Telling matlab beforehand that 'N/A' should be the same as NaN is faster, but to convert a cell array of text values to numeric, converting all non-numeric values to NaN is done with str2double, e.g.:

data.Var11 = str2double(data.Var11);

Note: you may want to customise further the import of your table, e.g to read the time properly or ignore the blank column at the end. For that you can use detectImportOptions:

opts = detectImportOptions('LA-01.csv', 'TreatAsMissing', 'N/A');  %basic defaults. Treat 'N/A' as a missing indicator
opts.VariableTypes{4} = 'duration';  %tell matlab that column 4 is actually a time (duration)
opts = setvaropts(opts, 4, 'InputFormat', 'hh:mm');  %and tell it what the format is
opts.ExtraColumnsRule = 'ignore';  %ignore extra column at the end
%giving better names to the variables would be a good idea as well:
%opts.VariableNames = {'Sources', 'Something', 'Date', 'Time', 'somthing', ... cell array of 23 strings}
data = readtable('LA-01.csv', opts);