How do I find and replace text in an imported table?

I've imported data from an Excel file and have it as a table (DemographicsData). I would now like to find all cells that contain 'Male' and replace them with '1' and 'Female' with '2'. How do I run this command? I'm new with MATLAB and would appreciate any help.

Answer 1

[Adam Danz]

Presumably there's a column in your table for sex|gender and the Male|Female categories are listed in that column. You could use findgroups() to automatically replace M|F with group numbers.

% assuming the table is named T and the 
% column is named "sex"
T.sex = findgroups(T.sex); 

Another option is to assign those values directly,

 T.sex(strcmpi(T.sex,'Male')) = {1}; 
 T.sex(strcmpi(T.sex,'Female')) = {2}; 

[Darla Bonagura]

Thank you so much! This is very helpful.

When I ran the T.sex = findgroups(T.sex); command it automatically assigned the 1s and 2s. For my non-binary categories like race, ethnicity, etc., how can I choose which number is assigned to each response (e.g. White, Black, Asian)?

[Adam Danz]

If you want to assign specific values to specific categories you should use method #2 in my answer.

However, there's a 2nd output to findgroups() that lists the group for each value. You could use that to identify which category belongs to which group number.

[group, groupID] = findgroups(T.sex); 

[Darla Bonagura]

Thank you!

[Adam Danz]

Glad I could help! If anything is still unclear feel free to continue to discussion.

[Darla Bonagura]

I have a follow-up question about this topic.

I would like to find and replace text in an imported table but not based off the name of the column (e.g. Gender to make Male = 1 and Female = 2). I am now trying to search anywhere in the table that reads "Not at all" and replace it with 1, "Rarely" with 2, "Sometimes" with 3, and so forth.

[Adam Danz]

Here's a simple, easy-to-read loop method. See comments for details.

% Create demo table
T = array2table({'rarely','b','c';'Not at all','b','Not at all';9999,'Not at all',9999;'a','Sometimes','Never'}, ...
    'VariableNames',{'first','second','last'}); 

% List key words in order of the value they should have 
key = {'sometimes', 'not at all', 'never'}; 

% Replace key words with their index value 
Tc = T{:,:};
for i = 1:numel(key)
    % Identify location in T that matches key{i}
    % Note, this is not case sensitive.  If you want case sensitivity, use strcmp()
    [row,col] = find(strcmpi(Tc,key{i})); 
    % Replace the key word(s) in the table with the index value
    ind = sub2ind(size(T),row,col);
    Tc(ind) = {i}; 
end
T{:,:} = Tc;

[Lorenz Bankel]

Dear Adam,

thanks for this solution! I am new in MATLAB and I tried to make this work. In priciple I have a table from which I want to replace strings.

However I realized that your solution doesnt work if there are multiple cells contaning the key: for i = 2 ('not at all') it replaces more than the correct cells.

I am struggling to find a solution. Do you have any suggestion?

Thanks a lot!

Lorenz

[Adam Danz]

@Lorenz Bankel thanks a lot for taking the time to point out that error. I updated that comment to fix the error.

Explanation of my error:

Since matlab does not allow subscripting as in T(ind) into a table (as of r2021a), I was incorrectly subscripting rows and columns using T(row,col) which selects all rows and columns listed, not just the combinations of (row,col) indices.

Explanation of the correction:

Instead, the table is now converted to a cell array and sub2ind is used to get the linear indices that correspond to the selected rows and column. The updated cell array then replaces the values in the table.

[Lorenz Bankel]

thanks a lot for the explanantion and the solution. Works perfektly!