How to Manipulate Values in the Table

Edits: excel sheet and .m file have been attached!

Hello, I have a dataset in excel which I have imported in a MATLAB table. I want to do some manipulations of the values in the table to create a new table in a desired format. As you can see in the photo attached, what I originally have is on the left, and the desired table is on the right. In the raw table, I have some values such as "B", "ADI", "FCBGE" in column C ("Cat_A").

For each row, I want to insert "1" or "0" from Column C to Column I based on the value in Column C. For example, the value in cell C2 C is "B", then I want to fill cell D2 ("Cat_B") with "1". Since there's no other letters in cell C2, cell C2, D2,E2,F2,G2,H2,I2 should be filled with "0". By applying this rule to each row, I have a new table on the right (in the photo).

I'd appreciate if someone can give me some tips to do the manipulations.

[Image Analyst]

It would be easier for people to try things if you attached your data in a .mat file.

[Shuyuan Liu]

Thank you! Attached the excel sheet and .m file!

Answer 1

[Peter Perkins]

You could do it with rowfun, here's another way. First make your data friendlier:

>> t = table([1;2;3;4;5],[1;1;0;0;1],categorical({'a';'a';'ab';'ab';'ab'}),[0;0;0;0;0],[0;0;0;0;0]);
>> t.Properties.VariableNames = {'ID' 'Response' 'Cats' 'CatA' 'CatB'}
t = 
    ID    Response    Cats    CatA    CatB
    __    ________    ____    ____    ____
    1     1           a       0       0   
    2     1           a       0       0   
    3     0           ab      0       0   
    4     0           ab      0       0   
    5     1           ab      0       0

Now put the ones where you need them:

>> t{t.Cats == 'a',4} = 1;
>> t{t.Cats == 'ab',4:5} = 1;
>> t
t = 
    ID    Response    Cats    CatA    CatB
    __    ________    ____    ____    ____
    1     1           a       1       0   
    2     1           a       1       0   
    3     0           ab      1       1   
    4     0           ab      1       1   
    5     1           ab      1       1

You have not said how many possible combinations of letters you have, but this could be modified easily to handle a large number of combinations.

[Shuyuan Liu]

I've solved the problem. But what you provided is helpful. Thanks!