How to convert cell columns in a table?
14 views (last 30 days)
Show older comments
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
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.
Answers (1)
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
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);
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!