How to replace string values in a column of a table into numeric?

I have a table X

It has a column named 'fruit' with string values such as {'apple','orange','grapes'}, There are 13 different string values in the column 'fruit'

Now I need to change it as if apple or orange then 1, grapes then 2 and so on.

How can I do that?

[Ruger28]

what have you tried so far?

[Vaishali Ravi]

Once I tried using 'replace' keyword and it threw an error

First argument must be a string array, character vector, or cell array of character vectors.

Then I created two different cell arrays, one with existing values and the other with values to be replaced.

I used for loop and it showed different errors for different tries

I tried like this...

[row,col] = find(strcmpi(X{:,'fruit'},toReplace{i})); 
    % Replace the key word(s) in the table with the index value
X{row,col} = replaceWith{i}; 

I tried like this..

X.label(X.fruit,toReplace{i})=replaceWith{i}

I tried like this

ix=X.fruit==toReplace{i}
X.fruit(ix)=replaceWith{i};

Mostly my error was:

Conversion to cell from double is not possible.

So I tried to convert it into a double, it becomes 'Nan' and it is not working

[Walter Roberson]

you need to replace the entire table variable if you are changing datatype.

It is often easier to add a new variable with the derived data and either leave the old data or delete the variable.

By the way, use ismember and the second output to find the index of the string. Use the index of the string to index into a vector that maps to numeric values, the thus allowing you to map orange and apple to the same number.

This can be done in a small number of vectorized lines m

[Vaishali Ravi]

can you giva a sample code on how to use ismember?

Answer 1

[Walter Roberson]

fruit_types = {'apple', 'orange', 'grape', 'banana', 'kumquat', 'peach', 'tomato', 'strawberry', 'pineapple', 'kiwi', 'blueberry', 'durian', 'avacodo'};
fruit_value = [      1,        1,       2,      -4,        5.3,      1         3          5.3,           11,      2,        -5.3,        1,         4];

[wasfound, idx] = ismember(X.fruit, fruit_types);
f_values = nan(length(idx), 1));
f_values(wasfound) = fruit_value(idx(wasfound));

X.fruit_value = f_values;

[Hamidreza]

When I run your code I get the error message below:

Error using cell/ismember (line 34)

Input A of class double and input B of class cell must be cell arrays of character vectors, unless one is a character vector.

Any advice to resolve it?

Thanks,

Hamid

Answer 2

[Cris LaPierre]

I would first convert fruit to a categorical, then I would rename the categories.

X.fruit = categorical(X.fruit);
X.fruit = renamecats(X.fruit,{'apple','orange','grapes'},{'1','2','3'});

[Vaishali Ravi]

Thank you, but i need numeric values to be replaced

[Cris LaPierre]

I guess you're jumping through hoops either way, but you could then convert the categories to numbers by adding this to the end of the code I shared previously:

X.fruit = str2double(string(X.fruit))

[Vaishali Ravi]

Actually I tried str2double, it produced Nan values.

[Cris LaPierre]

You have to combine all 3 lines of code:

1. Convert to categorical
2. Rename categories
3. Convert categories to double

% set up a table variable with 20 repeating fruit names
fruit = {'apple','orange','grapes'}';
ind = randi(3,20,1);
fruit = fruit(ind);
X = table(fruit)

Here you see the text values for fruit

X =

  20×1 table

      fruit   
    __________

    {'apple' }
    {'apple' }
    {'grapes'}
    {'grapes'}
    {'orange'}
    {'grapes'}
    {'grapes'}
    {'apple' }
    {'orange'}
    {'orange'}
    {'grapes'}
    {'apple' }
    {'orange'}
    {'orange'}
    {'orange'}
    {'grapes'}
    {'apple' }
    {'grapes'}
    {'orange'}
    {'apple' }

Now convert the text to numbers

% Change names to numbers
X.fruit = categorical(X.fruit);
X.fruit = renamecats(X.fruit,{'apple','orange','grapes'},{'1','2','3'});
X.fruit = str2double(string(X.fruit))

Now you see the fruit values are numbers

X =

  20×1 table

    fruit
    _____

      1  
      1  
      3  
      3  
      2  
      3  
      3  
      1  
      2  
      2  
      3  
      1  
      2  
      2  
      2  
      3  
      1  
      3  
      2  
      1 

Use summary to confirm fruit is now a double.

>> summary(X)

Variables:

    fruit: 20×1 double

        Values:

            Min          1   
            Median       2   
            Max          3