MATLAB Answers

How to set table VariableNames from a cell?

Latest activity Edited by Walter Roberson
about 12 hours ago
Accepted Answer by dpb

I have a peculiar frustrating situation: am trying to set the headers of a 1x19 table from an excel table to provide as an input to a Machine learning struct. As per the documentation of the struct i need to provide a MATLAB Table with 19 rows of numeric data with the following headers.

gender	seniorcitizen	partner	dependents	tenure	phoneservice	multiplelines	internetservice	onlinesecurity	onlinebackup	deviceprotection	techsupport	streamingtv	streamingmovies	contract	paperlessbilling	paymentmethod	monthlycharges	totalcharges
10	0	200	200	16	100	200	200	300	300	300	300	300	300	502	200	630	18.95	326.8

However, the problem is, for some reason, i get the dreaded, unintelligible error message:

Function 'subsindex' is not defined for values of class 'table'.

1. I tried importing the excel as a variable using the import facility. Does not work. 2. I tried creating the table from a cell array of headers using the cell2table. That gives another of the infamous cryptic errors. (seriously, wonder who writes those error messages in MATLAB? they seem to have a special training for making it as unfriendly as possible)

I found that the Properties of the table in MATLAB workspace look like below:

description: ''
                userdata: []
          dimensionnames: {'row'  'variables'}
           variablenames: {1×19 cell}
    variabledescriptions: {}
           variableunits: {}
                rownames: {}

I need to set the VariableNames directly as values instead of a 1x19 cell. How do i do it? (And pls avoid referring me to MATLAB table documentation. Does not work)

  7 Comments

Not sure what you mean.

filename = 'Telco Customer Churn2.xlsx'
data = xlsread(filename, 'Numeric Data');

works fine for me. Again, do you want/expect a table, a cell array, a double matrix, or a structure?

Sorry for the delay in responding.

  1. Am able to read and import the data from Excel into the ML engine.
  2. Am unable to create a new table containing 19 rows of data to use the
predictFcn function of the ML struct 
willCustomerChurn

Below is the description of willCustomerChurn struct

willCustomerChurn = 
  struct with fields:
           predictFcn: @(x)exportableModel.predictFcn(predictorExtractionFcn(x))
    RequiredVariables: {1×19 cell}
          LinearModel: [1×1 LinearModel]
                About: 'This struct is a trained model exported from Regression Learner R2017a.'
         HowToPredict: 'To make predictions on a new table, T, use: 
yfit = c.predictFcn(T) 
  replacing 'c' with the name of the variable that is this struct, e.g. 'trainedModel'.
The table, T, must contain the variables returned by: 
c.RequiredVariables 
↵Variable formats (e.g. matrix/vector, datatype) must match the original training data.
↵Additional variables are ignored. ↵ 
↵For more information, see How to predict using an exported model.'

3) When i create a new table named table2 containing the header row and 19 rows of data as below, and invoke the predictFcn(table2), as input, all i get are different errors. The table table2 is given below.

10	0	200	200	16	100	200	200	300	300	300	300	300	300	502	200	630	18.9500000000000	326.800000000000

The table table2 properties are below:

table2.Properties
ans = 
  struct with fields:
             Description: ''
                UserData: []
          DimensionNames: {'Row'  'Variables'}
           VariableNames: {1×19 cell}
    VariableDescriptions: {}
           VariableUnits: {}
                RowNames: {}

4) When i invoke the predictFcn function, i get the error as below

willCustomerChurn.predictFcn(table2)
Error using classreg.regr.modelutils.designmatrix>convertVar (line 506)
Cannot automatically convert a double variable to categorical values.
Error in classreg.regr.modelutils.designmatrix>dummyVars (line 438)
    group = convertVar(group,glevels); % convert group to be compatible with glevels
Error in classreg.regr.modelutils.designmatrix (line 290)
            [Xj,dummynames] = dummyVars(dummyCoding{j},Xj,catLevels{j});
Error in classreg.regr.CompactTermsRegression/designMatrix (line 142)
                    = classreg.regr.modelutils.designmatrix(X,'Model',terms(:,varLocs), ...
Error in LinearModel/predict (line 337)
                design = designMatrix(model,Xpred);
Error in
mlearnapp.internal.model.adapterlayer.TrainedLinearRegressionModel>@(x)predict(LinearModel,x)
Error in
mlearnapp.internal.model.transformation.TrainedManualFeatureSelection>@(x)decoratedPredictFunction(featureSelectionFunction(x))
(line 61)
functionHandle = @(x) decoratedPredictFunction(featureSelectionFunction(x));
Error in
mlearnapp.internal.model.DatasetSpecification>@(x)exportableModel.predictFcn(predictorExtractionFcn(x))
(line 167)
            newExportableModel.predictFcn = @(x)
            exportableModel.predictFcn(predictorExtractionFcn(x));

4) So i created a categorical of table2 into table3.

table3.Properties.VariableNames
ans =
  1×19 cell array
  Columns 1 through 6
    'gender'    'SeniorCitizen'    'Partner'    'Dependents'    'tenure'    'PhoneService'
    Columns 7 through 10
      'MultipleLines'    'InternetService'    'OnlineSecurity'    'OnlineBackup'
    Columns 11 through 15
      'DeviceProtection'    'TechSupport'    'StreamingTV'    'StreamingMovies'    'Contract'
    Columns 16 through 19
      'PaperlessBilling'    'PaymentMethod'    'MonthlyCharges'    'TotalCharges'

5) When i invoke the predictFcn using table3, i get a different error as below

willCustomerChurn.predictFcn(table3)
Error using classreg.regr.modelutils.designmatrix>convertVar (line 506)
Cannot automatically convert a double variable to categorical values.
Error in classreg.regr.modelutils.designmatrix>dummyVars (line 438)
    group = convertVar(group,glevels); % convert group to be compatible with glevels
Error in classreg.regr.modelutils.designmatrix (line 290)
            [Xj,dummynames] = dummyVars(dummyCoding{j},Xj,catLevels{j});
Error in classreg.regr.CompactTermsRegression/designMatrix (line 142)
                    = classreg.regr.modelutils.designmatrix(X,'Model',terms(:,varLocs), ...
Error in LinearModel/predict (line 337)
                design = designMatrix(model,Xpred);
Error in
mlearnapp.internal.model.adapterlayer.TrainedLinearRegressionModel>@(x)predict(LinearModel,x)
Error in
mlearnapp.internal.model.transformation.TrainedManualFeatureSelection>@(x)decoratedPredictFunction(featureSelectionFunction(x))
(line 61)
functionHandle = @(x) decoratedPredictFunction(featureSelectionFunction(x));
Error in
mlearnapp.internal.model.DatasetSpecification>@(x)exportableModel.predictFcn(predictorExtractionFcn(x))
(line 167)
            newExportableModel.predictFcn = @(x)
            exportableModel.predictFcn(predictorExtractionFcn(x));

6) The wilLCustomerChurn.RequiredVariables are below

willCustomerChurn.RequiredVariables
ans =
  1×19 cell array
  Columns 1 through 6
    'gender'    'SeniorCitizen'    'Partner'    'Dependents'    'tenure'    'PhoneService'
    Columns 7 through 10
      'MultipleLines'    'InternetService'    'OnlineSecurity'    'OnlineBackup'
    Columns 11 through 15
      'DeviceProtection'    'TechSupport'    'StreamingTV'    'StreamingMovies'    'Contract'
    Columns 16 through 19
      'PaperlessBilling'    'PaymentMethod'    'MonthlyCharges'    'TotalCharges'

6) Am pulling my hair out to understand what's wrong here.

I tried a normal table with 19 values and same variable names, and it fails. I try with categorical values, and it fails with a different error.

Please help me understand what am doing wrong.

To reiterate, the input i give to the predictFcn seems to be incorrect in some way.

What you DON'T show us is how you created

willCustomerChurn

so we can figure out what it is, specifically.

I'm guessing you use one of the ML prepackaged applications and Exported its results to the workspace?

If so, which app? Then we can look at the doc for it and follow along. Without, we're still guessing...

Sign in to comment.

3 Answers

Answer by dpb
about 15 hours ago
Edited by dpb
about 14 hours ago
 Accepted Answer

OK, there was enough of a hint that you used Regression Learner App. I'd never even opened it before, but since you used "Churn" in the model name and had the list of variables, I was able to build a model.

Let's see if can predict something; I just called the exported model TM for brevity--

>> TM.predictFcn(T(1:3,:))      % predict first three values from existing table
ans =
  200.0000
  200.0000
  155.5556
>> 

The easiest way to use the model is something like, presuming the original table is T

TT=T(1:3,:);           % just make a copy of the first three rows; gives the variables as exist
TT.SeniorCitizen=ones(3,1);  % let's change a variable to something different...
>> TM.predictFcn(TT)         % and see what we now predict for those...
ans =
100.0000
200.0000
111.1111
>> 

So, did make a difference in prediction for two of the three; not sure w/o more in-depth digging as to why the second is the same but seems to work as advertised.

Not sure just what you actually tried to do; perhaps building a table that doesn't match the original but only includes the predictors doesn't work??? I dunno, didn't try it.

Again, show just the exact code you tried in sequence without any effort to analyze that we can see and can probably tell where that went wrong, too.

ADDENDUM

To create a new table that just has the required variables in it, use the facility of table addressing...

>> Tpred=T(1:3,TM.RequiredVariables);   % just three rows for brevity
Tpred =
  3×19 table
    gender    SeniorCitizen    Partner    Dependents    tenure    PhoneService    MultipleLines    InternetService    OnlineSecurity    OnlineBackup    DeviceProtection    TechSupport    StreamingTV    StreamingMovies    Contract    PaperlessBilling    PaymentMethod    MonthlyCharges    TotalCharges
    ______    _____________    _______    __________    ______    ____________    _____________    _______________    ______________    ____________    ________________    ___________    ___________    _______________    ________    ________________    _____________    ______________    ____________
    20        0                100        200            1        200             700              400                200               100             200                 200            200            200                500         100                 600              29.85              29.85      
    10        0                200        200           34        100             200              400                100               200             100                 200            200            200                501         200                 610              56.95             1889.5      
    10        0                200        200            2        100             200              400                100               100             200                 200            200            200                500         100                 610              53.85             108.15      
>> 

As this shows, the cell array of names is just fine for addressing variables. I don't know where you came up against the categorical problem unless you had converted some of the input table variables to categorical earlier before importing into the App.

BTW, this is also OK for predicting with; the model object clearly matches variable names in the table; I'd venture undoubtedly one could have other variables added as well as long as the originals are there and have the correct data types as were extant when the model was fitted.

table addressing is extremely flexible but have to get the syntax right for what are trying to do; the detailed info is at Access-data-in-a-table

ADDENDUM SECOND

BTW, if you were to want or need to do so, you can build a new table from scratch with the required variables with table; if the data are in variables of the desired name, those will be the default names, otherwise the optional 'VariableNames' property will accept the cell array or you can redefine the names after table creation by setting them under the 'Properties' structure.

  5 Comments

PERFECT ! Worked. I ditched he numeric data option and went with the raw text data itself. Below is the data i extracted, based on the brilliant idea of using RequiredVariables to collect it from source.

Tpred=rawTelecomData(1:3,TMRawData.RequiredVariables)
Tpred =
   3×19 table
      gender     SeniorCitizen    Partner    Dependents    tenure    PhoneService      MultipleLines       InternetService    OnlineSecurity    OnlineBackup    DeviceProtection    TechSupport    StreamingTV    StreamingMovies        Contract        PaperlessBilling      PaymentMethod       MonthlyCharges    Churn
    ________    _____________    _______    __________    ______    ____________    __________________    _______________    ______________    ____________    ________________    ___________    ___________    _______________    ________________    ________________    __________________    ______________    _____
    'Female'    0                'Yes'      'No'           1        'No'            'No phone service'    'DSL'              'No'              'Yes'           'No'                'No'           'No'           'No'               'Month-to-month'    'Yes'               'Electronic check'    29.85             'No' 
    'Male'      0                'No'       'No'          34        'Yes'           'No'                  'DSL'              'Yes'             'No'            'Yes'               'No'           'No'           'No'               'One year'          'No'                'Mailed check'        56.95             'No' 
    'Male'      0                'No'       'No'           2        'Yes'           'No'                  'DSL'              'Yes'             'Yes'           'No'                'No'           'No'           'No'               'Month-to-month'    'Yes'               'Mailed check'        53.85             'Yes'

Ran the predictFcn and got the results as below:

yfit=TMRawData.predictFcn(Tpred);
>> yfit
 yfit =
    1.0e+03 *
     0.0213
    1.9189
    0.0907

Thank you SO Much @dpb

Glad to help...once one can get to the root of the problem it's generally not too bad! :)

"I ditched he numeric data option..."

I don't know what the means, but OK (I presume has something to do with inside the ML App, most likely).

NB: Using the '.RequiredVariables' array from the original table was the obvious way here where you already had the existing table; as noted if were building from scratch of an external data set for prediction, as noted it (the table) can be built directly as well.

The final data and results look like below

TtextData=rawTelecomData(3:6,willCustomerChurnRawData.RequiredVariables)
TtextData =
    4×19 table
       gender     SeniorCitizen    Partner    Dependents    tenure    PhoneService      MultipleLines       InternetService    OnlineSecurity    OnlineBackup    DeviceProtection    TechSupport    StreamingTV    StreamingMovies        Contract        PaperlessBilling           PaymentMethod           MonthlyCharges    TotalCharges
      ________    _____________    _______    __________    ______    ____________    __________________    _______________    ______________    ____________    ________________    ___________    ___________    _______________    ________________    ________________    ___________________________    ______________    ____________
      'Male'      0                'No'       'No'           2        'Yes'           'No'                  'DSL'              'Yes'             'Yes'           'No'                'No'           'No'           'No'               'Month-to-month'    'Yes'               'Mailed check'                 53.85             108.15      
      'Male'      0                'No'       'No'          45        'No'            'No phone service'    'DSL'              'Yes'             'No'            'Yes'               'Yes'          'No'           'No'               'One year'          'No'                'Bank transfer (automatic)'     42.3             1840.8      
      'Female'    0                'No'       'No'           2        'Yes'           'No'                  'Fiber optic'      'No'              'No'            'No'                'No'           'No'           'No'               'Month-to-month'    'Yes'               'Electronic check'              70.7             151.65      
      'Female'    0                'No'       'No'           8        'Yes'           'Yes'                 'Fiber optic'      'No'              'No'            'Yes'               'No'           'Yes'          'Yes'              'Month-to-month'    'Yes'               'Electronic check'             99.65              820.5      
>> yfit2=willCustomerChurnRawData.predictFcn(TtextData);
>> yfit2
yfit2 =
    4×1 cell array
      'No'
      'No'
      'Yes'
      'Yes'

Thank you EVERYONE. Finally got a better understanding of WHY the problem was occurring and fixed it.

Sign in to comment.


Answer by Walter Roberson
about 17 hours ago

Function 'subsindex' is not defined for values of class 'table'.

That means you cannot use a table as a subscript.

I need to set the VariableNames directly as values instead of a 1x19 cell. How do i do it?

The VariableNames property of a table may be set to either a string array or a cell array of character vectors; if a string array is used then it will be converted to a cell array of character vectors.

aaa = array2table(randi(10,5,3));
aaa.Properties.VariableNames = {'gender', 'seniorcitizen', 'partner'};

"Attached is the excel itself. The 'Numeric Data' tab is the one am trying to use."

data = readtable('Telco Customer Churn2.xlsx', 'Sheet', 'Numeric Data');
data(1:4, :)
ans =
  4×21 table
     customerID     gender    SeniorCitizen    Partner    Dependents    tenure    PhoneService    MultipleLines    InternetService    OnlineSecurity    OnlineBackup    DeviceProtection    TechSupport    StreamingTV    StreamingMovies    Contract    PaperlessBilling    PaymentMethod    MonthlyCharges    TotalCharges    Churn
    ____________    ______    _____________    _______    __________    ______    ____________    _____________    _______________    ______________    ____________    ________________    ___________    ___________    _______________    ________    ________________    _____________    ______________    ____________    _____
    '7590-VHVEG'      20            0            100         200           1          200              700               400               200              100               200               200            200              200            500             100                600             29.85             29.85        200 
    '5575-GNVDE'      10            0            200         200          34          100              200               400               100              200               100               200            200              200            501             200                610             56.95            1889.5        200 
    '3668-QPYBK'      10            0            200         200           2          100              200               400               100              100               200               200            200              200            500             100                610             53.85            108.15        100 
    '7795-CFOCW'      10            0            200         200          45          200              700               400               100              200               100               100            200              200            501             200                620              42.3           1840.75        200 

"cannot automatically convert a double variable to categorical values."

Is it possible that you have an existing table object that you are trying to add more data to?

  1 Comment

"Is it possible that you have an existing table object that you are trying to add more data to?"

I think it's clear OP does have some sort of other object is trying to set/update, but just what seems more than reluctant to share...out of not understanding what it takes to ask a question that can be answered and not thinking about what the readers are able to know/infer from posting alone vis a vis his knowledge of everything associated with the problem on his end is the likely cause. Pushing for that so far hasn't yielded what we really need to know...

Sign in to comment.


Answer by Guillaume
about 16 hours ago

Right, this is one case of a badly explained problem and one case of a user not really understanding the error message he's given or the code is using.

As stated by the by the HowToPredict field of the willCustomerChurn structure, the code requires a table with

  • variable names that are identical to those in WillCustomerChurn.RequiredVariables. Now if we look at the variable names of the table, they match. So that's not the problem and asking how to change the variable names (trivial to do b.t.w) is barking up the wrong tree.v
  • "variable formats (e.g. matrix/vector, datatype) must match the original training data". Looking at the error, the code expects a variable to be categorical. Instead it is double. That's the problem that needs fixing.

As it is, we don't have enough information to know which variable should be categorical. The best thing to do would be to use the same code that was used to load the training data into a table to load the current file.

Failing that, we can take a guess. Loading the given excel file, I get a table with 21 variables (not 19) with only 4 variables that are of type double. Among these 'SeniorCitizen' is the most likely candidate to be converted to categorical, so

yourtable.SeniorCitizen = categorical(yourtable.SeniorCitizen);

may fix the problem. However, it's possible that some of the text variables may need converting to categorical as well. Looking at the training table and comparing the type of each variable would be the best way to know.

Note: "So i created a categorical of table2 into table3" What does that mean? You cannot convert a table to categorical. You can only convert variables into categorical.

  3 Comments

Thank you all for the prompt help, despite my inability to clearly articulate the problem. Guess this is what happens when you don't what is the right question to ask :) :) After making the SeniorCitizen variable as categorical, i get a different error:

   Error using classreg.regr.modelutils.designmatrix (line 308)
    Variables that are categorical, cell array of character vectors, or char cannot be treated as
    continuous variables.

If i try it without converting to categorical, i get the older error message:

Error using classreg.regr.modelutils.designmatrix>convertVar (line 506)
  Cannot automatically convert a double variable to categorical values.

And on top of it, i don't know what is the question to ask.

"Loading the given excel file, I get a table with 21 variables (not 19) with only 4 variables that are of type double."

data = readtable('Telco Customer Churn2.xlsx', 'Sheet', 'Numeric Data');

Everything except the first column comes out as double.

Everything except the first column comes out as double

I just used a plain readtable and didn't check the excel file to see if there was more than one sheet. That's probably why it's different.

Sign in to comment.