Move values based on string

I need to import the following sheet from excel:

Then I want to move every value in column C to columns D, E and F based on results of string searches.

For example, I want to search column B (description) for 'gas' inside each cell and if it is present, move corresponding value in C (Value) to column D.

The same for strings 'Restaurant' and 'Hotel'. Note that the search should be case insensitive and matlab should take my parameter as an incomplete string, ie, imagine there was a Description HotelHereNow, then matlab should find it using parameter 'hotel'.

Of course this is just an example, real database is about 2000x10.

What is the best way to import the excel sheet: table, cell array? How can I search and move?

Answer 1

[Walter Roberson]

For the searching part:

B_lower = lower(B);   %to lowercase
contains_gas = ~cellfun(@isempty, strfind(B_lower, 'gas'));
D(contains_gas) = C(contains_gas);

for the reading part: readtable() would probably be good. You would then probably modify what I wrote above, to be (for example)

    B_lower = lower(YourTable.Description);   %to lowercase
    contains_gas = ~cellfun(@isempty, strfind(B_lower, 'gas'));
    YourTable.Gas(contains_gas) = YourTable.Value(contains_gas);

[Tfm tfm]

Walter, thank you for your reply.

I imported the sheet as a cell array and tried the first line:

B_lower = lower(B);   %to lowercase

But got the following error:

Error using lower
Cell elements must be character arrays.

So I researched a bit and tried to apply it just to the B column using:

B_lower_descript = lower(sheet{:,2});

And got:

Error using lower
Too many input arguments.

I still tried to join all the elements into one array using:

strjoin(lower(sheet{:,2}));

and

cat(lower(sheet{:,2}));

But had no success.

[Walter Roberson]

B_lower_descript = lower(sheet(:,2));

That is, my B assumed you had already assigned B the content of the second column.

[Tfm tfm]

Only way I got lower to work was converting sheet(:,2) to an char array:

B_lower_descript = lower([sheet{:,2}]);   %to lowercase

cellfun won't work with B_lower_descript above, however:

Input #2 expected to be a cell array, was double instead.

And if I convert it to a cell:

 B_lower_descript = {lower([sheet{:,2}])};   %to lowercase

I get a 1x1 cell, lose indexing and cellfun returns only 1.

Sorry my code couldn't keep it up to your algorithm, Walter!

[Walter Roberson]

B_lower_descript = cellfun(@lower, sheet(:,2));

[Tfm tfm]

Your suggestion results in the error:

Error using cellfun
Non-scalar in Uniform output, at index 1, output 1.
Set 'UniformOutput' to false.

Can't find the reason, since index 1 is the string 'Description' and

lower(sheet(1,2))

works nice.

[Walter Roberson]

B_lower_descript = cellfun(@lower, sheet(:,2), 'Uniform', 0);