MATLAB Answers

How to convert cell columns in a table?

9 views (last 30 days)
Andrea Cecilia
Andrea Cecilia on 6 Jan 2020
Commented: Guillaume on 6 Jan 2020
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

  2 Comments

Guillaume
Guillaume on 6 Jan 2020
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
Andrea Cecilia on 6 Jan 2020
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.

Sign in to comment.

Answers (1)

Guillaume
Guillaume on 6 Jan 2020
Probably,
data = readtable('LA-01.csv', 'Delimiter', ' ', 'MultipleDelimsAsOne', 1, 'TreatAsEmpty', 'N/A')
would fix the problem. If not, please attach a problematic demo file.

  2 Comments

Andrea Cecilia
Andrea Cecilia on 6 Jan 2020
This actually solved the problem, thanks! But, just in case, I am curious to understand how to convert cells to double.
Guillaume
Guillaume on 6 Jan 2020
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);

Sign in to comment.

Sign in to answer this question.