Accounting for the missing string using the number zero

I am trying to match strings in corps_members with those in kimetsu, if the strings are in kimetsu, give them kimetsu values [that are in row 2] but if they are not in kimetsu, give them a value of zero. I have successfully done the former but fail to do the latter. Strings not in kimetsu seem to be ignored when the output is displayed, how do I assign them a value of zero if strcmp is false? I.e, how can I code for an else statement that does what I want? I am not looking to change my code but rather need help adding an else statement that assigns a value of zero to the string not in kimetsu

[Please see attached files].
txtf = 'kimetsu.txt';
exfile = 'corps_members.xlsx';

Members = table2cell(readtable(txtf));


sheets = sheetnames(exfile);
g = cellstr(sheets);

urokodaki = [];
for i = 1:numel(g)
    if i == 1
        var = [{g{i}}, {table2cell(readtable(exfile,'VariableNamingRule','preserve', 'Sheet', i))}];
        urokodaki = [urokodaki; var];
    else
        var = [{g{i}}, {table2cell(readtable(exfile,'VariableNamingRule','preserve', 'Sheet', i))}];
        urokodaki = [urokodaki; var];
    end
end

wisteria = [];
mant = [];

for p = 1:size(urokodaki, 1)
    boar = urokodaki{p, 2};
        for x = 1:1:size(boar, 1) 
            for v = 1:1:size(Members, 1)
                if strcmp(boar{x, 2}, Members{v,1}) == 1
                    wisteria{x} = Members{v, 2}; 
                    mant = [mant; wisteria{x}];
                end   
            end
        end
end

Output:

    10
   100
   500
    78
    99
    10
   100
    78

Desired output:

    10
   100
   500
    78
    99
    10
   100
    78
    0

0 because Zenitsu is not in kimetsu.

Answer 1

[Voss]

Here's a way to do what I think you want to do, with minimal changes to your code (although I added some comments about some things you may want to consider):

txtf = 'kimetsu.txt';
exfile = 'corps_members.xlsx';

Members = table2cell(readtable(txtf)); % you may consider: Members = readcell(txtf);

sheets = sheetnames(exfile);
g = cellstr(sheets);

urokodaki = [];
for i = 1:numel(g)
    if i == 1 % if i == 1, do a thing:
        var = [{g{i}}, {table2cell(readtable(exfile,'VariableNamingRule','preserve', 'Sheet', i))}]; % you may consider: readcell(exfile,'Sheet',i,'NumHeaderLines',1)
        urokodaki = [urokodaki; var];
    else      % else, do that same exact thing:
        var = [{g{i}}, {table2cell(readtable(exfile,'VariableNamingRule','preserve', 'Sheet', i))}];
        urokodaki = [urokodaki; var];
    end
end

wisteria = [];
mant = [];

for p = 1:size(urokodaki, 1)
    boar = urokodaki{p, 2};
        for x = 1:1:size(boar, 1) 
            
            found = false;
            
            for v = 1:1:size(Members, 1)
                if strcmp(boar{x, 2}, Members{v,1}) == 1
                    wisteria{x} = Members{v, 2}; % I don't know why you want to set wisteria{x}; the x loop can happen multiple times, overwriting the same elements of wisteria each time, but ok
                    mant = [mant; wisteria{x}];
                    found = true;
%                     break % if you want to stop looking after one match, break here
                end   
            end
            
            if ~found
                mant = [mant; 0];
            end
            
        end
end

disp(mant)

    10
   100
   500
    78
    99
    10
   100
    78
     0

[Andromeda]

Thank you, this is exactly what I required. Why does use advise against using table2cell?

[Voss]

Just that readcell is a more direct route than reading to a table and then immediately converting the table into a cell array.

[Andromeda]

Oh, I see. I will consider that. I am trying to do something else now.

I have added a few more lines to the original code. I want to store the corps member numbers in an qx2 array but before that, I am trying to break up newMant.

newCode:

txtf = 'kimetsu.txt';
exfile = 'corps_members.xlsx';

Members = table2cell(readtable(txtf)); % you may consider: Members = readcell(txtf);

sheets = sheetnames(exfile);
g = cellstr(sheets);

urokodaki = [];
for i = 1:numel(g)
    if i == 1 
        var = [{g{i}}, {table2cell(readtable(exfile,'VariableNamingRule','preserve', 'Sheet', i))}]; 
        urokodaki = [urokodaki; var];
    else      
        var = [{g{i}}, {table2cell(readtable(exfile,'VariableNamingRule','preserve', 'Sheet', i))}];
        urokodaki = [urokodaki; var];
    end
end

wisteria = [];
mant = [];

for p = 1:size(urokodaki, 1)
    boar = urokodaki{p, 2};
    for x = 1:1:size(boar, 1) 
        
        found = false;
        
        for v = 1:1:size(Members, 1)
            if strcmp(boar{x, 2}, Members{v,1}) == 1
                wisteria{x} = Members{v, 2}; 
                mant = [mant; wisteria{x}];
                found = true;
            end   
        end
        
        if ~found
            mant = [mant; 0];
        end
        
    end
end
strang = [];
for r = 1:size(urokodaki, 1)
    sed = urokodaki{r, 2};
    Sed = sed(:,1);
    strang = [strang; Sed];
end
W = num2cell(mant);
newMant = [strang, W];

newMant:

{[1]}    {[ 10]}
{[2]}    {[100]}
{[3]}    {[500]}
{[4]}    {[ 78]}
{[5]}    {[ 99]}
{[1]}    {[ 10]}
{[2]}    {[100]}
{[3]}    {[ 78]}
{[4]}    {[  0]}

into this:

{[1]}    {[ 10]}
{[2]}    {[100]}
{[3]}    {[500]}
{[4]}    {[ 78]}
{[5]}    {[ 99]}

and that:

{[1]}    {[ 10]}
{[2]}    {[100]}
{[3]}    {[ 78]}
{[4]}    {[  0]}

but with a generic code that doesn't assume that there is only 2 sheets in the excel spreadsheet, i.e. how can I generically break up newMant into 1-to-5 and 1-to-4? Say newMant has size 500x2 and the numbers go from 1-8, 1-11,1-23, 1-5, 1-6, 1-3,etc. but always starting from 1

This is what I want in the end: Result = 2×2 cell array

{'Sheet1'}    {5×2 cell} %Sheet1 has 1-5 
{'Sheet2'}    {4×2 cell} %Sheet2 has 1-4

[Voss]

Probably don't build mant based on all the sheets in the first place. Instead use a cell array with one element per sheet, each of which is a cell array (or a matrix - why not?) with 2 columns.

[Andromeda]

Yes, that is exactly what I was trying to do. How can I code it?

[Voss]

ismember makes it easy

txtf = 'kimetsu.txt';
exfile = 'corps_members.xlsx';

Members = readcell(txtf);

sheets = sheetnames(exfile);
g = cellstr(sheets);

urokodaki = cell(numel(g),2);
for i = 1:numel(g)
    urokodaki(i,:) = [g(i), {readcell(exfile,'Sheet',i,'NumHeaderLines',1)}];
end

mant = urokodaki;

for p = 1:size(urokodaki, 1)
    
    [ism,idx] = ismember(urokodaki{p,2}(:,2),Members(:,1));
    
    mant{p,2}(ism,2) = Members(idx(ism),2);
    mant{p,2}(~ism,2) = {0};
    
end

disp(mant)

    {'Sheet1'}    {5×2 cell}
    {'Sheet2'}    {4×2 cell}

mant{:,2}

ans = 5×2 cell array
    {[1]}    {[ 10]}
    {[2]}    {[100]}
    {[3]}    {[500]}
    {[4]}    {[ 78]}
    {[5]}    {[ 99]}

ans = 4×2 cell array
    {[1]}    {[ 10]}
    {[2]}    {[100]}
    {[3]}    {[ 78]}
    {[4]}    {[  0]}

[Andromeda]

Perfect! Thank you a million times

[Voss]

You're welcome!

[Andromeda]

Would it be okay if I asked a few more questions? I want to make changes to the external files and ask questions relating to the changes

[Voss]

I'm signing off for the day, so you may have better luck asking the questions to the community at large (i.e., posting them as new questions rather than in comments here).

[Andromeda]

Will do. Thanks once again

Answer 2

[Seth Furman]

It's worth mentioning that this code can probably be simplified to use outerjoin and fillmissing.

https://www.mathworks.com/help/matlab/ref/outerjoin.html

https://www.mathworks.com/help/matlab/ref/fillmissing.html

fileKimetsu = "https://www.mathworks.com/matlabcentral/answers/uploaded_files/1010890/kimetsu.txt";
fileCorpsMembers = "https://www.mathworks.com/matlabcentral/answers/uploaded_files/1010885/corps_members.xlsx";

kimetsu = readtable(fileKimetsu, TextType="string");
kimetsu.Properties.VariableNames(1) = "Names"

kimetsu = 5×3 table
      Names      Var2    Var3
    _________    ____    ____

    "Guyi"       100      34 
    "Shenobu"     99      23 
    "Rengoku"     78      10 
    "Tanjiro"     10      21 
    "Akaza"      500      89 

mant = [];
sheets = sheetnames(fileCorpsMembers);
for i = 1:numel(sheets)
    corpsMembers = readtable(fileCorpsMembers, Sheet=i, VariableNamingRule="preserve", TextType="string");
    mantTmp = outerjoin(kimetsu, corpsMembers, Type="right", Keys="Names", MergeKeys=true);
    mant = [mant; mantTmp];
end
mant = fillmissing(mant, "constant", 0, DataVariables="Var2")

mant = 9×4 table
      Names      Var2    Var3    Corps members
    _________    ____    ____    _____________

    "Akaza"      500      89           3      
    "Guyi"       100      34           2      
    "Rengoku"     78      10           4      
    "Shenobu"     99      23           5      
    "Tanjiro"     10      21           1      
    "Guyi"       100      34           2      
    "Rengoku"     78      10           3      
    "Tanjiro"     10      21           1      
    "Zenitsu"      0     NaN           4      

[Peter Perkins]

It's also worth saying explicitly something that Seth implied: calling readtable only to immediately convert that table to a cell array is almost certainly not what you should be doing. Calling readcell might be one response to that observation, but it begs the question.

Cell arrays are not a good way to store tabular data.Tables are. Seeing this

should give you pause. That's not a good way to store numeric data. You can't even sum those columns! I guess at some point the cell arrays we're talking about have both text and numeric in them, even more reason to use a table.

I took the first part of Voss's latest code, just to illustrate what I'm saying. This is not your full solution, just a small part of it. Here's three versions:

1) Make a table containing tables and sheet names:

txtf = 'kimetsu.txt';
exfile = 'corps_members.xlsx';
Members = readcell(txtf);
sheets = sheetnames(exfile);
urokodaki1 = table('Size',[length(sheets) 2], 'VariableTypes',["string" "cell"], 'VariableNames',["Name" "Data"]);
urokodaki1.Name = sheets;
for i = 1:height(urokodaki)
    urokodaki1.Data{i} = readtable(exfile,'Sheet',i,'NumHeaderLines',1);
end
urokodaki1

Make a table containing tables, with sheet names as the row names:

urokodaki2 = table();
for i = 1:length(sheets)
    urokodaki2.Data{sheets(i)} = readtable(exfile,'Sheet',i,'NumHeaderLines',1);
end
urokodaki2

Make a scalar struct containing tables, with the sheet names as field names:

urokodaki3 = struct;
for i = 1:length(sheets)
    urokodaki3.(sheets(i)) = readtable(exfile,'Sheet',i,'NumHeaderLines',1);
end
urokodaki3

Here's all three results, all together for easy comparison:

urokodaki1 =
  2×2 table
      Name         Data    
    ________    ___________
    "Sheet1"    {5×2 table}
    "Sheet2"    {4×2 table}
urokodaki2 =
  2×1 table
                 Data    
              ___________
    Sheet1    {5×2 table}
    Sheet2    {4×2 table}
urokodaki3 = 
  struct with fields:

    Sheet1: [5×2 table]
    Sheet2: [4×2 table]

Which one makes more sense depends on what you are doing next and whether or not you will add more stuff to those data. All of them allow you to refer to the tables by sheet name. All of them store the columns of numbers as columns of numbers:

>> urokodaki2.Data{'Sheet1'}
ans =
  5×2 table
    Var1       Var2    
    ____    ___________
     1      {'Tanjiro'}
     2      {'Guyi'   }
     3      {'Akaza'  }
     4      {'Rengoku'}
     5      {'Shenobu'}

I'd turn Var2 into a string, but whatever. I guess at some point there is a join between sheet1 and sheet2, I could not really follow the long thread. If you really only ever have two sheets, probably the struct sol'n is best, the other two are useful in more general cases.

Point is: cell arrays are not the right way to store tabular data. You might say, "yeah, but aren't you putting the tabular data in a cell array in two of your code versions?" Yes, but that cell array is a wrapper around the two tables, not storing the tabular data directly. Ultimately, I think you want to combine those two tables (with a join), but I'm pretty sure that you still want to end up with a table. You have both text and numbers AFAICT.