How can I re-code numeric variables in a table to strings?

9 views (last 30 days)
I am using Matlab version 9.1.0.441655 (R2016).
I have created a large (2496000x8) table using array2table. Although it was simpler to create the initial matrix as uniformly numeric, for analysis I would like re-code the numbers in several variables to be strings, while leaving other variable as numeric.
An example data set:
Var1=[1; 2; 1; 2; 2; 1; 1];
Var2=[2; 2; 1; 1; 1; 2; 1];
Var3=[32.1; 34.6; 56.7; 65.3; 78.6; 23.4; 43.1];
Mat=[Var1,Var2,Var3];
Table=array2table(Mat,'VariableNames',{'Var1','Var2','Var3'});
Table =
Var1 Var2 Var3
____ ____ ____
1 2 32.1
2 2 34.6
1 1 56.7
2 1 65.3
2 1 78.6
1 2 23.4
1 1 43.1
I would like to re-code the 1's and 2's in Var1 to 'Male' and 'Female', respectively and re-code the 1's and 2's in Var2 to 'Old' and 'Young', respectively. But, leave Var3 as numeric.
To result in:
Var1 Var2 Var3
________ _______ _________
'Male' 'Young' [32.1000]
'Female' 'Young' [34.6000]
'Male' 'Old' [56.7000]
'Female' 'Old' [65.3000]
'Female' 'Old' [78.6000]
'Male' 'Old' [23.4000]
'Male' 'Young' [43.1000]
I tried:
Table.Var1(strcmp(Table.Var1,1)) = {'Male'}
But that gives a conversion error:
Conversion to double from cell is not possible.

Accepted Answer

Peter Perkins
Peter Perkins on 6 Sep 2017
JLC, you say "string", but it's very likely that you would be bettter off with categorical variables. Given you're original sample data ...
>> Var1 = [1; 2; 1; 2; 2; 1; 1];
>> Var2 = [2; 2; 1; 1; 1; 2; 1];
>> Var3 = [32.1; 34.6; 56.7; 65.3; 78.6; 23.4; 43.1];
>> t = table(Var1,Var2,Var3)
t =
7×3 table
Var1 Var2 Var3
____ ____ ____
1 2 32.1
2 2 34.6
1 1 56.7
2 1 65.3
2 1 78.6
1 2 23.4
1 1 43.1
... here's what you probably want to do:
>> t.Var1 = categorical(t.Var1,1:2,{'Male' 'Female'});
>> t.Var2 = categorical(t.Var2,1:2,{'Old' 'Young'});
>> t
t =
7×3 table
Var1 Var2 Var3
______ _____ ____
Male Young 32.1
Female Young 34.6
Male Old 56.7
Female Old 65.3
Female Old 78.6
Male Young 23.4
Male Old 43.1
A few other things:
  • Don't name your table "Table". It will come back to bite you eventually.
  • No reason to concatenate [Var1,Var2,Var3] and then call array2table, which just pulls them apart. Call table directly.
  • Your "desired" result seems to have Var3 as a cell array, each cell containing a scalar double, You definitely don't want that.
  • It's possible to convert the entire table to a cell array and work on it and convert it back, as shown by ImageAnalyst. But you can work on the table directly as I showed above. The "Conversion to double from cell is not possible." errors you saw are because, as IA says, you were assigning text to only some elements of a double variable in the table. You need to assign to t.Var1 without subscripting. My code above shows that as part of converting to categorical.
  • If you really do want strings, do it like this:
genders = {'Male'; 'Female'}; t.Var1 = genders(t.Var1);
ages = {'Male'; 'Female'}; t.Var2 = ages(t.Var2);
  1 Comment
JLC
JLC on 6 Sep 2017
Edited: JLC on 7 Sep 2017
Thank you very much! Using categorical worked perfectly with my real data.

Sign in to comment.

More Answers (2)

Andrei Bobrov
Andrei Bobrov on 6 Sep 2017
Edited: Andrei Bobrov on 7 Sep 2017
c = {'Male','Female','Old','Young'};
T_out = cell2table(c(cell2mat(Table{:,:})+[0 1]));
or for the latest versions of the MATLAB:
Table(:,1:2) = array2table(categorical(Table{:,1:2}+[0 1],1:4,{'Male','Female','Old','Young'}));
or with new conditions
Var1=[1; 2; 1; 2; 2; 1; 1];
Var2=[2; 2; 1; 1; 1; 2; 1];
Var3=[32.1; 34.6; 56.7; 65.3; 78.6; 23.4; 43.1];
Table=table(Var1,Var2,Var3);
c = {'Male','Female','Old','Young'};
T_out = [cell2table(c(bsxfun(@plus,Table{:,1:2},0:1))),Table(:,3)];
  2 Comments
JLC
JLC on 6 Sep 2017
I tried this with the update more representative example data:
T_out = array2table(categorical(cell2mat(Table{:,1:2})+[0 1],1:4,{'Male','Female','Old','Young'}));
But I get the following error:
Cell contents reference from a non-cell array object.
Error in cell2mat (line 42)
cellclass = class(c{1});
Andrei Bobrov
Andrei Bobrov on 6 Sep 2017
Please see third part "with new conditions" of my answer.

Sign in to comment.


Image Analyst
Image Analyst on 6 Sep 2017
Try this:
Var1={1,2,1,2,2,1,1};
Var2={2,2,1,1,1,2,1};
Mat=[Var1',Var2'];
Table=array2table(Mat,'VariableNames',{'Var1','Var2'})
% Find where column 1 = 1.
rowsWith1 = cell2mat(Table.Var1) == 1
% Replace those rows with 'Male' and other rows with 'Female'.
Table(rowsWith1, 'Var1') = {'Male'}
Table(~rowsWith1, 'Var1') = {'Female'}
% Find where column 1 = 1.
rowsWith1 = cell2mat(Table.Var2) == 1
% Replace those rows with 'Young' and other rows with 'Old'.
Table(rowsWith1, 'Var2') = {'Young'}
Table(~rowsWith1, 'Var2') = {'Old'}
  2 Comments
JLC
JLC on 6 Sep 2017
Edited: JLC on 6 Sep 2017
This works well for the example data. Unfortunately my real data is a numeric (double) table, so this won't work. I get an error when I try to re-code from the logical vector:
Conversion to double from cell is not possible.
I have updated the original question with a closer example data set.
Image Analyst
Image Analyst on 6 Sep 2017
JLC:
The problem is that with tables, all elements in a column must be of the same type. So if you started with numbers, you can't then set some elements in some rows to 'Male' which is a string because then the column would contain both numbers and strings, which is not allowed with tables. You CAN to that with cell arrays however. So I converted your table to a cell array, then made the replacements, and then (now that they're all strings) converted back to a table. See this:
Var1=[1; 2; 1; 2; 2; 1; 1];
Var2=[2; 2; 1; 1; 1; 2; 1];
Var3=[32.1; 34.6; 56.7; 65.3; 78.6; 23.4; 43.1];
Mat=[Var1,Var2,Var3];
Table=array2table(Mat,'VariableNames',{'Var1','Var2','Var3'})
% Need to create a cell array, because with a table we can't have some rows in the Var1 column
% be numbers while others are strings. However a cell array can do that.
ca = table2cell(Table)
% Find where column 1 = 1.
rowsWith1 = Table{:, 'Var1'} == 1
% Replace those rows with 'Male' and other rows with 'Female'.
ca(rowsWith1, 1) = {'Male'}
ca(~rowsWith1, 1) = {'Female'}
% Find where column 1 = 1.
rowsWith1 = Table{:, 'Var2'} == 1
% Replace those rows with 'Young' and other rows with 'Old'.
ca(rowsWith1, 2) = {'Young'}
ca(~rowsWith1, 2) = {'Old'}
% Now convert from cell array back to table.
Table = cell2table(ca, 'VariableNames',{'Var1','Var2','Var3'})

Sign in to comment.

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!