Struggling to retrieve table data as numeric values

102 views (last 30 days)
I have this table (newData) with column headers as ex. xVal, dtg, rpm etc. The values looks like:
xVal dtg rpm etc.
1 '1654668291510' '1047.47' ...
2 '1654668291610' '1047.59' ...
...
etc
I'm struggling to convert the column data to numeric values. Ex. the rpm data should be put in an array of double. I've tried str2num and str2double but I keep getting NaN for some reason that I can't figure out...
Any ideas?
  6 Comments
Karim
Karim on 5 Jul 2022
In that case you can indeed import the "OnlineValue" data as double. See below for an import methodology, note that this was generated in 2020a. You can generate a similair script by using the "import data" button in the home tab.
%% Setup the Import Options and import the data
opts = delimitedTextImportOptions("NumVariables", 2);
% Specify range and delimiter
opts.DataLines = [8, Inf];
opts.Delimiter = ";";
% Specify column names and types
opts.VariableNames = ["Variable", "OnlineValue"];
opts.VariableTypes = ["string", "double"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
opts.ConsecutiveDelimitersRule = "join";
% Specify variable properties
opts = setvaropts(opts, "Variable", "WhitespaceRule", "preserve");
opts = setvaropts(opts, "Variable", "EmptyFieldRule", "auto");
opts = setvaropts(opts, "OnlineValue", "TrimNonNumeric", true);
opts = setvaropts(opts, "OnlineValue", "ThousandsSeparator", ",");
% Import the data
MyData = readtable("Stop 080622 0600.csv", opts);
MyData
MyData = 54000×2 table
Variable OnlineValue ______________________________ ___________ "GVL_LogData.aLogData2[0, 0]" 1.6547e+12 "GVL_LogData.aLogData2[1, 0]" 1.6547e+12 "GVL_LogData.aLogData2[2, 0]" 1.6547e+12 "GVL_LogData.aLogData2[3, 0]" 1.6547e+12 "GVL_LogData.aLogData2[4, 0]" 1.6547e+12 "GVL_LogData.aLogData2[5, 0]" 1.6547e+12 "GVL_LogData.aLogData2[6, 0]" 1.6547e+12 "GVL_LogData.aLogData2[7, 0]" 1.6547e+12 "GVL_LogData.aLogData2[8, 0]" 1.6547e+12 "GVL_LogData.aLogData2[9, 0]" 1.6547e+12 "GVL_LogData.aLogData2[10, 0]" 1.6547e+12 "GVL_LogData.aLogData2[11, 0]" 1.6547e+12 "GVL_LogData.aLogData2[12, 0]" 1.6547e+12 "GVL_LogData.aLogData2[13, 0]" 1.6547e+12 "GVL_LogData.aLogData2[14, 0]" 1.6547e+12 "GVL_LogData.aLogData2[15, 0]" 1.6547e+12
Walter Roberson
Walter Roberson on 5 Jul 2022
Your csv file does not have column headers such as xVal, dtg, rpm etc . Instead, it has lines such as
GVL_LogData.aLogData2[10, 0];'1654668292510';
in which the word GVL_LogData.aLogData2 is constant, and the part in the [] appears to be a 2D subscript (0 based), and the part in quotes appears to be the associated value. The data appears to be 6000 x 9.
Is it possible that those 9 columns correspond to xVal, dtg, and so on? If so then we need a list of what the column order is.

Sign in to comment.

Accepted Answer

Karim
Karim on 4 Jul 2022
Hello, normally str2double should work. See below for an example.
% allocate the table
varTypes = ["double","string","string"]; % data type for each column
varNames = ["xVal","dtg","rpm"]; % variable name for each column
MyTable = table('Size',[2 3],'VariableTypes',varTypes,'VariableNames',varNames);
% store data in the table
MyTable(1,:) = {1 , '1654668291510' , '1047.47'};
MyTable(2,:) = {2 , '1654668291610' , '1047.59'};
% have a look at the content of the table
MyTable
MyTable = 2×3 table
xVal dtg rpm ____ _______________ _________ 1 "1654668291510" "1047.47" 2 "1654668291610" "1047.59"
% convert the columns to double
MyTable.dtg = str2double(MyTable.dtg);
MyTable.rpm = str2double(MyTable.rpm);
% have a look at the table
MyTable
MyTable = 2×3 table
xVal dtg rpm ____ __________ ______ 1 1.6547e+12 1047.5 2 1.6547e+12 1047.6
% extract the rpm colum to a new array
RpmArray = MyTable.rpm
RpmArray = 2×1
1.0e+03 * 1.0475 1.0476
  3 Comments
Karim
Karim on 5 Jul 2022
Hi, i do not have 2018b only 2020a and 2022a, in the those versions the example does work. However it could be that it doesn't work in 2018b. The functionality has been updated over the years.
However, see the commment section after the original question and data upload, the reson why str2double did not work is because you data is of the type categorical
Ernst
Ernst on 5 Jul 2022
Hi Karim (and Stephen23)
Thank you for your replies. Stephen23 made me curious about the import, and Karim gave me the syntax on the convertion of data. Also you gave me some good information regarding the work with table data.
I'm suspecting that I also have a problem related to the fact that my windows is actually a danish version, and I think there is also some kind of issue with the type of comma (. vs ,). In some cases I get the correct value, and in some cases I get a faulty value.
So for now - thank you very much for your replies. It helped me to move on with the data handling - thanks.

Sign in to comment.

More Answers (0)

Categories

Find more on Tables in Help Center and File Exchange

Products


Release

R2018b

Community Treasure Hunt

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

Start Hunting!