MATLAB not indexing table with correct data type, how to specify data type when indexing table?

I wrote a script that takes in an excel table using the "readtable" command.

inputTable = readtable(completeTableFilePath,'Sheet',sheetChoiceFileName,'TextType','string');

This, to my knowledge, should import all the cells of the excel file as strings. One part of the excel file is a column that has hex numbers (they cold be just "92" or "13C" etc...).

I had a really long excel table (around 300 lines) that had this column of hex numbers. I tried the program with a smaller table, maybe only 8 lines, and now it is having issues, particularly in the hex column.

The code:

% I just added these for testing (the idName)
idName = upper(inputTable{currentRowNumber,'ID'});
idName

if upper(inputTable{currentRowNumber,'ID'}) ~= "TBD"
    if upper(inputTable{currentRowNumber,'ID'}) ~= recurringID
        recurringID = upper(inputTable{currentRowNumber,'ID'});
        messageIDDecimal = hex2dec(recurringID);
    end
end

As I said, I changed nothing about this between runs, it works perfectly with the large table and it does not work with the smaller table. When I try to run it with the smaller table (which is just the larger table with a lot of the rows chopped off) I get idName as a "double" data type.

I figured I could fix this, by instead of relying on MATLAB to have the correct data type (which it should anyways because I specified so earlier!) I force the data type of strings using string(...).

% idName for testing
    idName = upper(string(inputTable{currentRowNumber,'ID'}));
    idName

    if upper(string(inputTable{currentRowNumber,'ID'})) ~= "TBD"
        if upper(string(inputTable{currentRowNumber,'ID'})) ~= recurringID
            recurringID = upper(string(inputTable{currentRowNumber,'ID'}));
            messageIDDecimal = hex2dec(recurringID);
        end
    end

I ran the above code on the smaller table and got the error: <missing> string element not supported; error on line with the hex2dec. idName shows up as <missing>.

I changed the code again to show the raw table indexing:

% idName for testing
    idName = upper(string(inputTable{currentRowNumber,'ID'}));
    idName
    idTest = inputTable{currentRowNumber,'ID'};
    idTest

    if upper(string(inputTable{currentRowNumber,'ID'})) ~= "TBD"
        if upper(string(inputTable{currentRowNumber,'ID'})) ~= recurringID
            recurringID = upper(string(inputTable{currentRowNumber,'ID'}));
            messageIDDecimal = hex2dec(recurringID);
        end
    end    

idTest was coming up as the double data type, until the final go. idName was <missing> and idTest was "NaN". To my knowledge, "NaN" is "Not a Number". It is giving an error because it's trying to input a hex number as a regular double data type. I cannot find a way to fix this. I already specified that the table is to be imported as strings. I cannot cast to this, because its not just holding the data, but being the wrong type. It is throwing an error and not holding the data at all.

There is nothing I can do, unless there is some way to make MATLAB only import as a specific data type. I am having a lot of issues, because MATLAB assumes data types. Maybe coming from C just has me think differently. I can see how it can be useful, but the fact that there is no way around it (that I know of), makes it not useful.

I ran the script with "idName" and "idTest" on the larget excel table. It properly imported them as strings. I was able to index and both idTest and idName showed as strings, even the hex numbers that only had the regular 0-9 numbers. So it is not the code. It is just MATLAB sometimes deciding to import as strings and sometimes to not.

[Stephen23]

@Kealan: please upload a sample data file by clicking the paperclip button.

Answer 1

[Voss]

Setting 'TextType' is for specifying whether the data readtable imports as text should be returned as string arrays or character vectors. It does not set the imported data type in general, as you have found. For instance, if readtable determines that some data is numeric, then 'TextType' has no effect on that data since it is not text.

To specify imported data types you can use detectImportOptions and setvartype.

Example:

% the file's contents
type('test.csv')

ID
92
2A

% the problem
T = readtable('test.csv')

T = 2x1 table
    ID 
    ___

     92
    NaN

% a solution
opts = detectImportOptions('test.csv'); % default import options for the file
opts = setvartype(opts,'ID','string');  % set 'ID' variable to be of type 'string'
T = readtable('test.csv',opts)          % readtable with the new options

T = 2x1 table
     ID 
    ____

    "92"
    "2A"

[Kealan]

What is the significance/purpose of using the "detectImportOptions" function? what does opts be equal to after that line is run?

Is there any way I can blanket apply that to a whole table, or would I need to set every column individually?

[Voss]

"What is the significance/purpose of using the "detectImportOptions" function? what does opts be equal to after that line is run?"

detectImportOptions returns a structure containing information about the default way readtable will read and interpret the file.

"Is there any way I can blanket apply that to a whole table, or would I need to set every column individually?"

See the setvartype line below for how you can apply a type to all variables in the whole table at once.

T = readtable('test.csv') % for reference, first read this file with the default options

T = 2x4 table
    ID       name       tooth_size    life
    ___    _________    __________    ____

     92    {'baba' }     {'xl'  }      2  
    NaN    {'booey'}     {'xxxl'}      9  

opts = detectImportOptions('test.csv') % default import options structure

opts = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'ID', 'name', 'tooth_size' ... and 1 more}
                VariableTypes: {'double', 'char', 'char' ... and 1 more}
        SelectedVariableNames: {'ID', 'name', 'tooth_size' ... and 1 more}
              VariableOptions: [1-by-4 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [2 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

opts.VariableTypes % check the default imported variable types - they are the same as in the table above

ans = 1x4 cell array
    {'double'}    {'char'}    {'char'}    {'double'}

opts = setvartype(opts,opts.VariableNames,'string') % now set all variables to be imported as strings

opts = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'ID', 'name', 'tooth_size' ... and 1 more}
                VariableTypes: {'string', 'string', 'string' ... and 1 more}
        SelectedVariableNames: {'ID', 'name', 'tooth_size' ... and 1 more}
              VariableOptions: [1-by-4 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [2 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

opts.VariableTypes % check the new imported variable types

ans = 1x4 cell array
    {'string'}    {'string'}    {'string'}    {'string'}

T = readtable('test.csv',opts) % import with new options - now all variables are string arrays

T = 2x4 table
     ID      name      tooth_size    life
    ____    _______    __________    ____

    "92"    "baba"       "xl"        "2" 
    "2A"    "booey"      "xxxl"      "9" 

[Kealan]

That is very cool. I will have to see about doing that. This will be awesome. This adds a lot of certainty to the code, with all the columns of data. I will try it later and mark this as an answer when it works (which I believe it really will anyways).

Thank you a lot!

[Voss]

You're welcome!

[Kealan]

Wait, how would I change multiple rows to differnent data types? I do not think I can do something like this:

% a solution
opts = detectImportOptions('test.csv'); % default import options for the file
opts = setvartype(opts,'ID','string');  % set 'ID' variable to be of type 'string'
opts = setvartype(opts,'Factor','string');
opts = setvartype(opts,'Unit','string');
...
T = readtable('test.csv',opts)          % readtable with the new options

could I define arrays or something like that?

Also, I used the command:

opts = detectImportOptions(completeTableFilePath);
opts

I got the output:

opts =

SpreadsheetImportOptions with properties:

Sheet Properties:

Sheet: ''

Replacement Properties:

MissingRule: 'fill'

ImportErrorRule: 'fill'

Variable Import Properties: Set types by name using setvartype

VariableNames: {}

VariableTypes: {}

SelectedVariableNames: {}

VariableOptions: Show all 0 VariableOptions

Access VariableOptions sub-properties using setvaropts/getvaropts

VariableNamingRule: 'modify'

Range Properties:

DataRange: 'A1' (Start Cell)

VariableNamesRange: ''

RowNamesRange: ''

VariableUnitsRange: ''

VariableDescriptionsRange: ''

To display a preview of the table, use preview

It seems to be missing variable import properties. I do not know if that would affect anything.

[Voss]

"how would I change multiple rows to differnent data types?"

Do you mean multiple columns (aka table variables)?

If so, you can do them one at a time like that, or you can do them all at once:

opts = detectImportOptions('test.csv');                  % default import options for the file
opts = setvartype(opts,["ID","Factor","Unit"],'string'); % set ID, Factor, and Unit variables to be of type 'string'
T = readtable('test.csv',opts)                           % readtable with the new options

"Also ... It seems to be missing variable import properties. I do not know if that would affect anything."

Seems like it's not detecting any variables/columns. Upload the file using the paperclip button and I can take a look.

[Steven Lord]

Wait, how would I change multiple rows to differnent data types?

A variable (represented as a column) in a table array can only contain data of one type. You can't "mix and match" inside the same table variable.

Each variable in a table array can be a different type from the other variables.

So the following is a valid table, with the LastName variable being a string array, Height and Weight being double, and Smoker being logical.

load patients
T = table(LastName, Height, Weight, Smoker);
T.LastName = string(T.LastName);
head(T)

     LastName     Height    Weight    Smoker
    __________    ______    ______    ______

    "Smith"         71       176      true  
    "Johnson"       69       163      false 
    "Williams"      64       131      false 
    "Jones"         67       133      false 
    "Brown"         64       119      false 
    "Davis"         68       142      false 
    "Miller"        64       142      true  
    "Wilson"        68       180      false 

If you wanted to flip this around to make a table with one variable per patient, you would not be able to do so and keep the first row of that new table a string array, the second and third rows double, and the fourth row logical. If you tried with a function like rows2vars, in this case it is technically able to make it work because MATLAB can convert doubles and logicals into a string and so turn each variable into a string array. But if T had contained another variable that wasn't able to be converted into a string, it wouldn't have worked.

T2 = rows2vars(T(1:5, :))

T2 = 4x6 table
    OriginalVariableNames     Var1        Var2          Var3        Var4       Var5  
    _____________________    _______    _________    __________    _______    _______

        {'LastName'}         "Smith"    "Johnson"    "Williams"    "Jones"    "Brown"
        {'Height'  }         "71"       "69"         "64"          "67"       "64"   
        {'Weight'  }         "176"      "163"        "131"         "133"      "119"  
        {'Smoker'  }         "true"     "false"      "false"       "false"    "false"

[Kealan]

@Voss

So if I need to set multiple columns to multiple different data types, I cannot do those in the same line, but I can do one line per data type, like below:

opts = detectImportOptions('test.csv');                  
opts = setvartype(opts,["ID","Factor","Unit"],'string');
opts = setvartype(opts,["Length","Numbers","Value"],'double');
T = readtable('test.csv',opts)                           

Also, concerning the file, I think it is because I need the properties of a specific sheet. The excel file has multiple sheets in it. So, it seems when I do "detectImportOptions" and maybe "setvartype" I need to specify filepath and the sheet. Do you know the proper format for this?

MATLAB specifies the detectImportOptions function to follow the format:

opts = detectImportOptions(filename)

or

opts = detectImportOptions(filename,name,value)

MATLAB specifies the readtable function to follow the format:

T = readtable(filename,name,value)

or

T = readtable(filename,opts,name,value)

My MATLAB code that works to open the specific sheet properly is:

inputTable = readtable(completeTableFilePath,'Sheet',sheetChoiceFileName,'TextType','string');

So, I believe I should maybe use:

opts = detectImportOptions(completeTableFilePath,'Sheet',sheetChoiceFileName);
opts = opts = setvartype(opts,["ID","Factor","Unit"],'string');
opts = setvartype(opts,["Length","Numbers","Value"],'double');
T = readtable(completeTableFilePath,opts,'Sheet',sheetChoiceFileName,'TextType','string');

Sorry, but I cannot share the file, it has some important info to me. Thank you for the help so far!

[Kealan]

@Steven Lord

Hey, I'm sorry about that. I misspoke. I meant multiple columns as different data types. I did read what you said, and it is interesting. I did have a little trouble following though. I am new to MATLAB.

[Kealan]

So, I was able to get it to work. I had to use:

opts = detectImportOptions(completeTableFilePath,'Sheet',sheetChoiceFileName);

opts = setvartype(opts,'ID','string');
% ... a list of the same type of thing as above with different names and
% data types
opts = setvartype(opts,'Unit','string');

inputTable = readtable(completeTableFilePath,opts,'Sheet',sheetChoiceFileName);

I had to get rid of the 'TextType', 'string' option. It said invalid parameter or something of the like, but if I can force the data type now, then I do not need to worry about that.

This works great!! Thank you all who were involved!!

[dpb]

",,,I cannot do those in the same line, but I can do one line per data type"

Yes, per setvartype documentation, the type argument must be a scalar. A seemingly useful enhancement would be to let both selection and type be cellstr or string arrays with a 1:1 correspondence.

There's also a feature vartype with a table that allows you to index by the current type and convertvars will let you then change by that indexing method. Keep poking at the doc and don't stop reading at the first line; study it in depth; there's a lot hidden in the bowels a cursory glance won't tell you.

Answer 2

[dpb]

There are even more alternatives available with setvaropts including identifying the column(s) as hex. This adds the limitation that have to use an integer type, but that can then be cast to a double.

c=[[{'ID1'};{'0x92'};{'0x2A'}], [{'ID2'};{'92'};{'2A'}]];
fn='test.csv';
writecell(c,fn)
type test.csv

ID1,ID2
0x92,92
0x2A,2A

opt=detectImportOptions(fn);
opt=setvaropts(opt,'ID2','NumberSystem','hex','Type','int32');

opt = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'ID1', 'ID2'}
                VariableTypes: {'auto', 'int32'}
        SelectedVariableNames: {'ID1', 'ID2'}
              VariableOptions: [1-by-2 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [2 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

tT=readtable(fn,opt,'ReadVariableNames',1)

tT = 2x2 table
    ID1    ID2
    ___    ___

    146    146
     42     42