MATLAB: How to Manipulate Values in the Table

dataexcelMATLAB

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.

Best Answer

  • 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.