MATLAB Answers

How to set table VariableNames from a cell?

Latest activity Edited by Walter Roberson
on 14 Oct 2018
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
on 14 Oct 2018
Edited by dpb
on 14 Oct 2018
 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
on 14 Oct 2018

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
on 14 Oct 2018

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.