In a table I have a column made of strings which are often repeated (sort of categories), I would like to substitute them with numbers which represents the categories

Hi all, I have a table with a column filled with strings which represents some predefined attributes or categories whith some empty spots e.g. ('black', 'black', 'red', 'red', blue', 'red', ", 'blue', ", 'black'). I would like to substitute to them some numbers which represents the categories considering the empty spots as categories too e.g. (1, 1, 2, 2, 3, 2, 4, 3, 4,1).

'black' => 1 'red'=> 2 'blue'=>3 "=>4

I have tried the following, where data is the table:

for i=1:size(data,2)
    if class(data{1,i})== "cell" | class(data{1,i})== "string"
        column_to_change=double(categorical(data{:,i}));
        column_to_change(ismissing(column_to_change))=max(column_to_change)+1;
        for j=1:size(data,1)
            data{j,i}={column_to_change(j)};
        end
    end
    
end

It works but with some problems:

1) the numbers do not start from 1 and are not consequential (I am probably missing something about how the categorical function works)

2) the final assignment in the table with the for cycle it is way too slow with big tables.

I am sure there are some better ways to do it, someone can help me?

Thank you in advance

Answer 1

[Cris LaPierre]

I would first consider keeping it a categorical. You have not explained why you need it to be a numeric value. However, if you need a numeric representation, consider using findgroups. It will number the groups in alphabetical order, starting with the empty string.

A = ["black", "black", "red", "red", "blue", "red", "", "blue", "", "black"];
G = findgroups(A)

G = 1×10

2 2 4 4 3 4 1 3 1 2

[Square]

Hey Cris,

Thank you for the fast answer, the findgroups function is awesome!

Do you perhaps know also how to assign the new matrix of double in the column of the table where there was the initial list of categories (cell)? In my application your A is a cell in a table (data(:,i)) while your G is my column_to_change.

If not, don't worry I'll find a way.

Thank you again

[Cris LaPierre]

A = ["black", "black", "red", "red", "blue", "red", "", "blue", "", "black"]';
data = table(A)

data = 10×1 table
       A   
    _______

    "black"
    "black"
    "red"  
    "red"  
    "blue" 
    "red"  
    ""     
    "blue" 
    ""     
    "black"

% overwrite table variable
data.A = findgroups(data.A)

data = 10×1 table
    A
    _

    2
    2
    4
    4
    3
    4
    1
    3
    1
    2

[Square]

Thank you again Cris but that do not work in my case because the column of the table i wanted to replace is made of cells while the new column is made of double. I have solved by removing entirely the column and adding a new one in the same position. Thanks a lot for your time and insights!

data(:,i)=[];  % delete the old column containing the cell 
data = addvars(data, column_to_change, 'Before',i); %adding a new column in the same position 

[Cris LaPierre]

It doesn't matter that it is a cell. It matters how you reference the variable. Use the dot name syntax instead of parentheses. See this page: Access data in Tables.

A = {'black', 'black', 'red', 'red', 'blue', 'red', '', 'blue', '', 'black'}';
data = table(A)

data = 10×1 table
        A     
    __________

    {'black' }
    {'black' }
    {'red'   }
    {'red'   }
    {'blue'  }
    {'red'   }
    {0×0 char}
    {'blue'  }
    {0×0 char}
    {'black' }

% overwrite table variable
data.A = findgroups(data.A)

data = 10×1 table
     A 
    ___

      1
      1
      3
      3
      2
      3
    NaN
      2
    NaN
      1

Note the NaN resulting from using a char array. This is why I used strings.

[Square]

Yeah I thought about that but I did not know how to automatize the process for any columns. Maybe I can recall the name of the specific column from the Metadata of the table:

 data.Properties.VariableNames(i) 

or something like that?

Thank you again!

[Cris LaPierre]

Use the T.(expression) syntax. This is the third row of the table on the linked paged (Type of Output = Array, extracting data from one variable)

A = {'black', 'black', 'red', 'red', 'blue', 'red', '', 'blue', '', 'black'}';
data = table(A);
% overwrite table variable
data.(1) = findgroups(data.(1))

data = 10×1 table
     A 
    ___

      1
      1
      3
      3
      2
      3
    NaN
      2
    NaN
      1

[Square]

WoW! Awesome! I did not know about that!

Thank you so much Cris for all the precious insights and your time!