Producing strings in empty column based on strings on another column

I have a spreadsheet with a blank column (57x1) that I want filled out based on the strings on another column (57x1).

Basically

If column 5 contains brad, the corresponding row in column 7 should have brad

If column 5 contains fred, the corresponding row in column 7 should have fred

If column 5 is a contains mike , angie, or alex, the corresponding row in column 7 should have oscar.

If column 5 contains any other name (jack, joe, etc), the corresponding row in column 7 should have roger

Column 5

mike

mike

brad

brad

angie

fred

fred

alex

jack

joe

matt

brad

Column 7 should look like this

oscar

oscar

brad

brad

oscar

fred

fred

oscar

roger

roger

roger

brad

How do I do this ?

[Image Analyst]

Try strcmpi() or contains(). Make it easy for people to help you. Give us your table in a .mat file, or give code to construct it. It will make it easier for people to try things and help you.

Answer 1

[Mara]

array(:,5) = ["mike" "mike" "brad" "angie"]
sz = size(array, 1);

for i = 1:sz
    name = array(i, 5);
    switch name
        case "mike"
            array(i,7) = "oscar"
        case "brad"
            array(i,7) = "brad"
        case "angie"
            array(i,7) = "oscar"
        otherwise
            array(i,7) = "roger"
    end
end

Does this help you?

[Sole]

Hi, sorry I failed to mention that it's in a table. It's an existing table saved that I'm trying to manipulate

[Mara]

You can do the same with a table. Just that the way you index is different. It also depends on the data type in the table. If the columns in the table are strings, it would be

t           = table()
t.firstn    = ["mike" "mike" "brad" "angie"]'
sz          = size(t, 1);

for i = 1:sz
    name = t.firstn(i,:)
    switch name
        case "mike"
            t.secondn(i,:) = "oscar"
        case "brad"
            t.secondn(i,:) = "brad"
        case "angie"
            t.secondn(i,:) = "oscar"
        otherwise
            t.secondn(i,:) = "roger"
    end
end

However, if the indexing does not work, it is likely that you have it stored in another data type inside your table (like for example a string in a cell). No problem, you have to check how to index into that and adjust your code. You can find out the data type by typing

class(t.firstn)

[Mara]

or actually, what Image Analyist suggested should go faster

allmikes = strcmpi(t.firstnames, "mike")
t.secondnames(allmikes, :) = "oscar";
%and so on

[Sole]

Thank you Mara, I used the latter one and it worked

[Sole]

I have a slightly more complex one using the same existing table. If column 3 is "fast" AND column 4 contains any of these characters: boat, car, or airplane. then column 5 become those strings on column 4. If it meets just the "fast" in column 3, then column 5 becomes "fast". Otherwise it's none. Is this where I use an if else statement ? Here's an example

column 3

fast

fast

slow

medium

fast

column 4

mike boat day

pax airplane oscar

ship

car

train

column 5

boat

airplane

none

none

fast

[Mara]

Yes you can probably find a way to do it with an if statement. Another way would be by using two logical vectors. Note that in the previous example "allmikes" is a logical vector of 0(false) and 1(true). You can create logical vectors for both columns and put an input in column 5 where both is true.

Note that strcmpi() will not give you any true result for column four, since it only compares the whole strings. But you can use contains().

ar((strcmpi(ar(:,1), "fast") & contains(ar(:,2), "boat")), 5) = "boat"