stack command errors with multiple column variables of different data types

I have a table (imported from a poorly-designed spreadsheet) with repeated column variables I would like to stack. The fields are:

[TestID], [A1], [B1], [C1], [A2], [B2], [C2], ... , [An], [Bn], [Cn]

Any columns under A and B contain numbers. Columns under C contain text.

I would like to stack the table to remove the repeated columns. So the output table fields would be:

[TestID], [Reading], [A], [B], [C]

I am using the following commands (the first line is shortened for brevity):

vars = {{'A1', 'B1', 'C1'},  {'A2', 'B2', 'C2'}, etc, {'An', 'Bn', 'Cn'}};
tblOut = stack(tblIn, vars, 'NewDataVariableNames', {'A', 'B', 'C'}, 'IndexVariableName', 'Reading');

But I get the error:

Error using tabular/stack (line 196)

Error when stacking data variable 'C1'

Caused by:

Conversion to double from cell is not possible.

Why does stack have a problem with the columns having different data types when they are not being stacked into the same column? How do I fix this?

Answer 1

[Lei Hou]

Hi CAM,

Your code is trying to stacking {'A1' 'B1' 'C1'} into one variable. Since C1 is text while A1 and B1 are numeric, an error will be returned due to imcompatible datatypes. In your case, you want to stack {'A1' 'A2',...'An'} into one variable. So "vars" should look like

vars = {{'A1','A2', , 'An'}, {'B1','B2', , 'Bn'}, {'C1','C2', , 'Cn'}}

Here is an example.

>> TestID = [100;101;102];
>> A1 = [1;2;3];
>> A2 = [4;5;6];
>> A3 = [7;8;9];
>> B1 = [0.1;0.2;0.3];
>> B2 = [0.4;0.5;0.6];
>> B3 = [0.7;0.8;0.9];
>> C1 = ["a";"b";"c"];
>> C2 = ["e";"f";"g"];
>> C3 = ["h";"i";"j"];
>> tblIn = table(TestID,A1,B1,C1,A2,B2,C2,A3,B3,C3);
>> stack(tblIn,{["A1" "A2" "A3"] ["B1" "B2" "B3"] ["C1" "C2" "C3"]},'NewDataVariableName',{'A' 'B' 'C'},'IndexVariableName','Reading')

ans =

  9×5 table

    TestID    Reading    A     B      C 
    ______    _______    _    ___    ___

     100         2       1    0.1    "a"
     100         5       4    0.4    "e"
     100         8       7    0.7    "h"
     101         2       2    0.2    "b"
     101         5       5    0.5    "f"
     101         8       8    0.8    "i"
     102         2       3    0.3    "c"
     102         5       6    0.6    "g"
     102         8       9    0.9    "j"