Clear Filters
Clear Filters

Accounting for the missing string using the number zero

4 views (last 30 days)
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.

Accepted Answer

Voss
Voss on 25 May 2022
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
  11 Comments
Voss
Voss on 26 May 2022
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).

Sign in to comment.

More Answers (1)

Seth Furman
Seth Furman on 2 Jun 2022
Edited: Seth Furman on 2 Jun 2022
It's worth mentioning that this code can probably be simplified to use outerjoin and fillmissing.
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
  1 Comment
Peter Perkins
Peter Perkins on 2 Jun 2022
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.

Sign in to comment.

Categories

Find more on Tables in Help Center and File Exchange

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!