How to convert cell columns in a table?
14 次查看(过去 30 天)
显示 更早的评论
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 个评论
Guillaume
2020-1-6
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.
回答(1 个)
Guillaume
2020-1-6
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 个评论
Guillaume
2020-1-6
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);
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!