Struggling to retrieve table data as numeric values

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?

[Stephen23]

"I have this table (newData) ... I'm struggling to convert the column data to numeric values."

How did you create that table in the first place? If you imported it from file, you could probably import the numeric data as numeric. Please upload a sample data file by clicking the paperclip button.

[Ernst]

Thanks.

Correct - I was reading it using "importfile". Please find attached the table (data).

[Karim]

thanks for the file share.

load('data.mat')

data

data = 54002×2 table
              VarName1                   INPUT     
    ____________________________    _______________

    //Variable                      //Online Value 
    <undefined>                     <undefined>    
    GVL_LogData.aLogData2[0, 0]     '1654668291510'
    GVL_LogData.aLogData2[1, 0]     '1654668291610'
    GVL_LogData.aLogData2[2, 0]     '1654668291711'
    GVL_LogData.aLogData2[3, 0]     '1654668291810'
    GVL_LogData.aLogData2[4, 0]     '1654668291910'
    GVL_LogData.aLogData2[5, 0]     '1654668292010'
    GVL_LogData.aLogData2[6, 0]     '1654668292111'
    GVL_LogData.aLogData2[7, 0]     '1654668292210'
    GVL_LogData.aLogData2[8, 0]     '1654668292310'
    GVL_LogData.aLogData2[9, 0]     '1654668292410'
    GVL_LogData.aLogData2[10, 0]    '1654668292510'
    GVL_LogData.aLogData2[11, 0]    '1654668292610'
    GVL_LogData.aLogData2[12, 0]    '1654668292710'
    GVL_LogData.aLogData2[13, 0]    '1654668292810'

data.VarName1(3)

ans = categorical
     GVL_LogData.aLogData2[0, 0] 

data.INPUT(3)

ans = categorical
     '1654668291510' 

As you can see you imported the data as categorical, this is why you cannot use str2double. To convert the column "INPUT" to doubles, you will need to delete the first two elements, afterwards you can use the double command:

data([1 2],:) = [];
data.INPUT = double( data.INPUT )

data = 54000×2 table
              VarName1              INPUT
    ____________________________    _____

    GVL_LogData.aLogData2[0, 0]     1625 
    GVL_LogData.aLogData2[1, 0]     1626 
    GVL_LogData.aLogData2[2, 0]     1627 
    GVL_LogData.aLogData2[3, 0]     1628 
    GVL_LogData.aLogData2[4, 0]     1629 
    GVL_LogData.aLogData2[5, 0]     1630 
    GVL_LogData.aLogData2[6, 0]     1631 
    GVL_LogData.aLogData2[7, 0]     1632 
    GVL_LogData.aLogData2[8, 0]     1633 
    GVL_LogData.aLogData2[9, 0]     1634 
    GVL_LogData.aLogData2[10, 0]    1635 
    GVL_LogData.aLogData2[11, 0]    1636 
    GVL_LogData.aLogData2[12, 0]    1637 
    GVL_LogData.aLogData2[13, 0]    1638 
    GVL_LogData.aLogData2[14, 0]    1639 
    GVL_LogData.aLogData2[15, 0]    1640 

However, is this the intend? Can you share the original data, right now i have the impression that something not correct with the import.

[Ernst]

Ohh - sorry. I think I misunderstood you - you are asking for the .csv file..?

Please find attached the .csv file. I'm removing the header before data handling.

[Karim]

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]

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.

Answer 1

[Karim]

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

[Ernst]

Thank you for your reply.

When I run your example I get:

"Error using table (line xxx)

Specify variable types as a cell array of character vectors, indicating the type of each variable to be created."

Does your example comply to R2018b?

Below is the "help table" answer - I don't see the 'VariableTypes' propertiy - but maybe I'm not looking the right place..?

help table

table Table.

Tables are used to collect heterogeneous data and metadata into a single

container. Tables are suitable for storing column-oriented or tabular data

that are often stored as columns in a text file or in a spreadsheet. Tables

can accommodate variables of different types, sizes, units, etc. They are

often used to store experimental data, with rows representing different

observations and columns representing different measured variables.

Use the table constructor to create a table from variables in the MATLAB

workspace. Use the readtable function to create a table by reading data

from a text or spreadsheet file.

The table constructor can also be used to create tables without

providing workspace variables, by providing the size and variable

types.

Tables can be subscripted using parentheses much like ordinary numeric

arrays, but in addition to numeric and logical indices, you can use a

table's variable and row names as indices. You can access individual

variables in a table much like fields in a structure, using dot

subscripting. You can access the contents of one or more variables using

brace subscripting.

Tables can contain different kinds of variables, including numeric, logical,

character, categorical, and cell. However, a table is a different class

than the variables that it contains. For example, even a table that

contains only variables that are double arrays cannot be operated on as if

it were itself a double array. However, using dot subscripting, you can

operate on a variable in a table as if it were a workspace variable. Using

brace subscripting, you can operate on one or more variables in a table as

if they were in a homogeneous array.

A table T has properties that store metadata such as its variable and row

names. Access or assign to a property using P = T.Properties.PropName or

T.Properties.PropName = P, where PropName is one of the following:

table metadata properties:

Description - A character vector describing the table

DimensionNames - A two-element cell array of character vectors containing names of

the dimensions of the table

VariableNames - A cell array containing names of the variables in the table

VariableDescriptions - A cell array of character vectors containing descriptions of the

variables in the table

VariableUnits - A cell array of character vectors containing units for the variables

in table

VariableContinuity - An array containing a matlab.tabular.Continuity value for each table

variable, specifying whether a variable represents continuous or discrete

data values. You can assign 'unset', 'continuous', 'step', or 'event' to

elements of VariableContinuity.

RowNames - A cell array of nonempty, distinct character vectors containing names

of the rows in the table

UserData - A variable containing any additional information associated

with the table. You can assign any value to this property.

table methods and functions:

Construction and conversion:

table - Create a table from workspace variables.

array2table - Convert homogeneous array to table.

cell2table - Convert cell array to table.

struct2table - Convert structure array to table.

table2array - Convert table to a homogeneous array.

table2cell - Convert table to cell array.

table2struct - Convert table to structure array.

Import and export:

readtable - Create a table by reading from a file.

writetable - Write a table to a file.

write - Write a table to a file.

Size and shape:

istable - True for tables.

size - Size of a table.

width - Number of variables in a table.

height - Number of rows in a table.

ndims - Number of dimensions of a table.

numel - Number of elements in a table.

horzcat - Horizontal concatenation for tables.

vertcat - Vertical concatenation for tables.

Set membership:

intersect - Find rows common to two tables.

ismember - Find rows in one table that occur in another table.

setdiff - Find rows that occur in one table but not in another.

setxor - Find rows that occur in one or the other of two tables, but not both.

unique - Find unique rows in a table.

union - Find rows that occur in either of two tables.

Data manipulation and reorganization:

summary - Print summary of a table.

addvars - Insert new variables at a specified location in a table.

movevars - Move table variables to a specified location.

removevars - Delete the specified table variables.

splitvars - Splits multi-column variables into separate variables.

mergevars - Merges multiple variables into one multi-column variable or a nested table.

sortrows - Sort rows of a table.

stack - Stack up data from multiple variables into a single variable.

unstack - Unstack data from a single variable into multiple variables.

join - Merge two tables by matching up rows using key variables.

innerjoin - Inner join between two tables.

outerjoin - Outer join between two tables.

rows2vars - Reorient rows to be variables of output table.

inner2outer - Invert a nested table-in-table hierarchy.

ismissing - Find elements in a table that contain missing values.

standardizeMissing - Insert missing data indicators into a table.

Computations on tables:

varfun - Apply a function to variables in a table.

rowfun - Apply a function to rows of a table.

Examples:

% Create a table from individual workspace variables.

load patients

patients = table(LastName,Gender,Age,Height,Weight,Smoker,Systolic,Diastolic)

% Select the rows for patients who smoke, and a subset of the variables.

smokers = patients(patients.Smoker == true, {'LastName' 'Gender' 'Systolic' 'Diastolic'})

% Convert the two blood pressure variables into a single variable.

patients.BloodPressure = [patients.Systolic patients.Diastolic];

patients(:,{'Systolic' 'Diastolic'}) = []

% Pick out two specific patients by the LastName variable.

patients(ismember(patients.LastName,{'Smith' 'Jones'}), :)

% Convert the LastName variable into row names.

patients.Properties.RowNames = patients.LastName;

patients.LastName = []

% Use the row names to pick out two specific patients.

patients({'Smith' 'Jones'},:)

% Add metadata to the table.

patients.Properties.Description = 'Simulated patient data';

patients.Properties.VariableUnits = {'' 'Yrs' 'In' 'Lbs' '' 'mm Hg'};

patients.Properties.VariableDescriptions{6} = 'Systolic/Diastolic';

summary(patients)

% Create a new variable in the table from existing variables.

patients.BMI = (patients.Weight * 0.453592) ./ (patients.Height * 0.0254).^2

patients.Properties.VariableUnits{'BMI'} = 'kg/m^2';

patients.Properties.VariableDescriptions{'BMI'} = 'Body Mass Index';

% Sort the table based on the new variable.

sortrows(patients,'BMI')

% Make a scatter plot of two of the table's variables.

plot(patients.Height,patients.Weight,'o')

% Create tables from text and spreadsheet files

patients2 = readtable('patients.dat','ReadRowNames',true)

patients3 = readtable('patients.xls','ReadRowNames',true)

% Create a table from a numeric matrix

load tetmesh.mat

t = array2table(X,'VariableNames',{'x' 'y' 'z'});

plot3(t.x,t.y,t.z,'.')

See also table, categorical

Reference page for table

Other functions named table

[Karim]

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]

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.