Comparing elemental composition with databank
Show older comments
I have a string array 22x1 (Probe_RL), which contains elemental compositions. Next to it I have a database 13x2 (Datenbank). The composition of both should be compared. If there is a fit, the group should be added to the line in Probe_RL. The problem is, that the factors should have a tolerance of 0.2. So that for example 1Fe 0.9Si can fit to group 206 in the database. I tried the following and hope somebody can help me:
% Sample data
Probe_RL = {'1Fe', '1Fe', '1Fe,1Si', '1Fe', '0.35Si,1Fe', '0.19Fe,0.57K,1Si', '0.47Fe,0.68K,1Si', '1Si', '0.81Fe,1Si', '1Fe', '1Si', '1Fe', '0.44Fe,1Si', '1Na', '1Fe', '1Si', '0.43Fe,1Si', '0.6Si,1Fe', '1Fe', '0.15Fe,1Si', '1Fe', '1Fe'};
Datenbank = {'1Na,1U Group 204', '1Bi,1V Group 205', '1Fe,1Si Group 206', '0.11Si,1Mg Group 207', '0.83Mg,1Si Group 208', '0.1Ca,0.2Al,1Si Group 209', '0.83Ca,1Si Group 210', '0.67Ca,1Al,1Si Group 211', '0.5Co,1Na Group 212', '0.5Pb,1Co Group 213', '0.5Na,1Si Group 214', '1Al,1Ca Group 215', '0.5Co,1Ni Group 216'};
Datenbank = fliplr(Datenbank);
Probe_RL = fliplr(Probe_RL);
% Probenzusammensetzung in zwei Spalten teilen
[Probe_RL_2D, ~] = cellfun(@strsplit, Probe_RL, repmat({","}, length(Probe_RL), 1), 'UniformOutput', false);
% Probenummern hinzufügen
for i = 1:length(Probe_RL)
Probe_RL_2D{i,2} = num2str(i);
end
% Proben mit der Datenbank vergleichen
for i = 1:length(Probe_RL)
if ~isempty(Datenbank)
% Proben mit der Datenbank vergleichen
if numel(Probe_RL_2D{i,1:2}) > 1
Probe_RL_2D{i,1:2} = Probe_RL_2D{i,1:2}(1);
end
% Konvertiere den Zeichenkettenvektor in einen Zellvektor
Probe_RL_2D{i,1:2} = cellstr(char(''));
Probe_RL_2D{i,1:2} = strjoin(Probe_RL_2D{i,1:2}, "");
% Datenbank und Probe_RL vertikal anordnen
Datenbank = fliplr(Datenbank);
Probe_RL_2D = fliplr(Probe_RL_2D);
j = find(abs(str2double(Probe_RL_2D{i,1:2}) - str2double(Datenbank(:,1:2))) < 0.2);
if ~isempty(j)
% Übereinstimmende Mineralien überprüfen
if length(j) > 1
Probe_RL(i,2) = "Unbekannt";
else
Probe_RL(i,2) = Datenbank(j(1),2);
end
else
Probe_RL(i,2) = "Unbekannt";
end
else
Probe_RL(i,2) = "Unbekannt";
end
end
% Ergebnisse ausgeben
disp(Probe_RL)
3 Comments
Jon
on 20 Oct 2023
Sorry, I'm not clear from your description what it is you want to do.
What do you mean when you say " If there is a fit, the group should be added to the line in Probe_RL"? Are you trying to label the elements in Probe_RL with a group number from the data base?
For the example data you give what would be the desired output?
Tatjana Mü
on 20 Oct 2023
Jon
on 20 Oct 2023
Wouldn't the 4th entry in Probe_RL be "unbekannt" as there is no pure Fe in the data base and the 4th entry in Probe_RL has Fe as it's only component?
Accepted Answer
Here is one approach, in which I put the data into tables and then use ismembertol to to do the comparision with a tolerance. I denote the group number for the samples with a numeric value of the group number (rather than a string) and if no match is found I have the group number as NaN rather than "unbekannt". You could modify this if it is important. To me it seemed better to maintain the group number, which is fundamentally a number not a string as a numerical value.
% Sample data
Probe_RL = {'1Fe', '1Fe', '1Fe,1Si', '1Fe', '0.35Si,1Fe', '0.19Fe,0.57K,1Si', '0.47Fe,0.68K,1Si', '1Si', '0.81Fe,1Si', '1Fe', '1Si', '1Fe', '0.44Fe,1Si', '1Na', '1Fe', '1Si', '0.43Fe,1Si', '0.6Si,1Fe', '1Fe', '0.15Fe,1Si', '1Fe', '1Fe'};
Datenbank = {'1Na,1U Group 204', '1Bi,1V Group 205', '1Fe,1Si Group 206', '0.11Si,1Mg Group 207', '0.83Mg,1Si Group 208', '0.1Ca,0.2Al,1Si Group 209', '0.83Ca,1Si Group 210', '0.67Ca,1Al,1Si Group 211', '0.5Co,1Na Group 212', '0.5Pb,1Co Group 213', '0.5Na,1Si Group 214', '1Al,1Ca Group 215', '0.5Co,1Ni Group 216'};
% Parameters
tol = 0.2; % tolerance for matching elements;
% Parse data and put into cell arrays
ProbeDat = cell(numel(Probe_RL),2);
for k = 1:numel(Probe_RL)
% Break up individual entries using comma delimiters
components = strsplit(Probe_RL{k},',');
% Loop through components, getting element and quantity
numComponents = numel(components);
quantity = zeros(numComponents,1); % preallocate
element = cell(numComponents,1); % preallocate
for m = 1:numel(components)
[quantity(m),element{m}] = numstr(components{m});
end
% Put into cell array
ProbeDat{k,1} = element;
ProbeDat{k,2} = quantity;
ProbeDat{k,3} = Probe_RL{k};
end
RefDat = cell(numel(Datenbank),3);
for k = 1:numel(Datenbank)
% Break up individual entries using comma and whitespace delimiters
parts = strsplit(Datenbank{k},{',',' '});
% Loop through components, getting element and quantity
numParts = numel(parts);
quantity = zeros(numParts-2,1); % preallocate
element = cell(numParts-2,1); % preallocate
for m = 1:numParts -2
[quantity(m),element{m}] = numstr(parts{m});
end
group = str2double(parts{end});
% Put into cell array
RefDat{k,1} = element;
RefDat{k,2} = quantity;
RefDat{k,3} = group;
end
% Get alphabetically sorted list of all of the possible elements
allElements = sort(unique(vertcat(ProbeDat{:,1},RefDat{:,1})));
numVbls = numel(allElements); % total number of elements
% Make tables for probe data and databank (refernce)
Probe = array2table(zeros(size(ProbeDat,1),numVbls),...
'VariableNames',allElements);
Probe.Name = cell(height(Probe),1);
Probe = Probe(:,["Name";allElements]); % Make Name the first column
DataBank = array2table(zeros(size(RefDat,1),numVbls+1),...
'VariableNames',['Group';allElements]);
% Loop through cell array assigning table row values to Probe table
% table has a column for each possible element, and entries with the
% composition. The leading column has the string name for the
% composition
for k = 1:height(Probe)
for m = 1:numel(ProbeDat{k,1})
Probe.(ProbeDat{k,1}{m})(k) = ProbeDat{k,2}(m);
end
Probe.Name{k} = ProbeDat{k,3};
end
% Loop through cell array assigning table row values to DataBank table
% table has a column for each possible element, and the entries with the
% composition, the leading column gives the group number for each
% composition
for k = 1:height(DataBank)
for m = 1:numel(RefDat{k,1})
DataBank.(RefDat{k,1}{m})(k) = RefDat{k,2}(m);
end
DataBank.Group(k) = RefDat{k,3};
end
disp(DataBank)
% Loop through rows in Probe table looking for a matching group in DataRref
% and lableling accordingly
numCol = width(Probe); % number of columns before adding label
for k = 1:height(Probe)
idl = ismembertol(DataBank{:,2:end},Probe{k,2:numCol},tol,...
'DataScale',1,'ByRows',true);
if any(idl)
% Label the group, assume just one match or none
Probe.Group(k) = DataBank.Group(idl);
else
Probe.Group(k) = NaN; % NaN for not found
end
end
% Reorder the columns so that the group match is the second column, just to
% make it be more readable
Probe = Probe(:,['Name';'Group';allElements]);
disp(Probe)
% Make summary table with just names and group numbers, NaN means no match
% found, i.e. "unbekannt"
summary = Probe(:,{'Name','Group'})
%
function [num,str] = numstr(s)
% splits string s into numeric and character parts
isNum = isstrprop(s,"digit")| isstrprop(s,"punct"); % digit or decimal point
num = str2double(s(isNum));
str = s(~isNum);
end
14 Comments
Tatjana Mü
on 21 Oct 2023
Edited: Tatjana Mü
on 21 Oct 2023
Jon
on 21 Oct 2023
So just to clarify,can you run the simple, self contained example code on your computer without errors?
I think you should be able to as it runs on the MATLAB answers site without errors.
In that case their is a problem with your adaptation/implementation of this idea for your actual problem. If so, please attach your .m files and any data files needed to reproduce the problem and I can try to help you further (probably not until Monday).
Jon
on 23 Oct 2023
Please attach your code and I can help you with your error. It looks like you have a problem on line 105 of your code, but the error message that you copied and pasted doesn't list all of line 105 that might provide some further hints as to what went wrong. From the error that I see, most likely there is a problem with how you supplied the names of the variables in your call to array2table.
Tatjana Mü
on 23 Oct 2023
When I run this code I don't get any errors. I have attached again here, with the file retrieval prompts commented out and hardoded to use 'Fake_Probe_Zusammensetzung.xlsx' just to demonstrate. I don't think it would matter unless you are running a very old version, but what version of MATLAB are you running?
clear all
close all
clc
%Blabla
warning off
clear;
%-----------------------Datenbank einlesen---------------------------------
RLImport = importdata('Datenbank_Matlab_Gruppierung2.xlsx');
Datenbank = RLImport.Tabelle1;
Datenbank=string(Datenbank);
[anzahl_lines,anzahl_columns]=size(Datenbank);
%-------------Dateien von SP_Zusammensetzung hochladen---------------------
% % % directory_name=uigetdir('','Ordner mit Messungen auswählen');
% % % [nur_file_name,pfad]=uigetfile({'*.xlsx','xlsx (*.xslx)';'*.*','all Files'},...
% % % 'PROBEN EINLESEN (probe_001.xlsx=',[directory_name '/'], 'Multiselect', 'on');
nur_file_name = 'Fake_Probe_Zusammensetzung.xlsx' % Hard code just for debugging
pfad = ''; % just for debugging
nur_file_name=cellstr(nur_file_name);
nur_file_name=sort(nur_file_name);
filename=strcat(pfad,nur_file_name);
anzahl_files=size(filename,2);
for xy=1:anzahl_files
fid_in=fopen(char(filename(xy)),'r');
filename_s = matlab.lang.makeValidName(nur_file_name);
filename_s=string(filename_s);
filename_s = erase(filename_s,"_csv");
filename_s = erase(filename_s,"LiqQuant_");
filename_c=cellstr(filename_s);
for g=1:anzahl_files
filename_f{g}=extractBefore(filename_c{g},11);
end
filename_s=string(filename_f);
filename_s=strip(filename_s,'right',"_");
%------------------------Dateien einlesen----------------------------------
tmpImport = importdata(filename{xy},',');
SP_RL = tmpImport.Tabelle1;
[anzahl_zeile,anzahl_elemente]=size(SP_RL);
SP_RL=string(SP_RL);
%---------------------------Vergleich-------------------------------------
SP_RL=cellstr(SP_RL);
for i=1:anzahl_files
Probe_RL = strcat(SP_RL);
Probe_RL=cellfun(@(s)join(unique(s),','),num2cell(Probe_RL,2));
Probe_RL = strip(Probe_RL,'left',",");
Probe_RL = strip(Probe_RL,'right',",");
end
Probe_RL=string(Probe_RL);
Probe_RL=cellstr(Probe_RL);
Probe_RL = rot90(Probe_RL);
Datenbank=cellstr(Datenbank);
% Initialisieren Sie den neuen Cell Array
Datenbank2 = cell(size(Datenbank, 1), 1);
% Schleife durch die Zeilen des ursprünglichen Cell Arrays und kombinieren Sie die Teile
for i = 1:size(Datenbank, 1)
composition = Datenbank{i, 1};
mineralName = Datenbank{i, 2};
Datenbank2{i} = [composition ' ' mineralName];
end
Datenbank2 = rot90(Datenbank2);
end
% %--------------------Vergleich SP mit Datenbank----------------------------
% Parameters
tol = 0.2; % tolerance for matching elements;
% Parse data and put into cell arrays
ProbeDat = cell(numel(Probe_RL),2);
for k = 1:numel(Probe_RL)
% Break up individual entries using comma delimiters
components = strsplit(Probe_RL{k},',');
% Loop through components, getting element and quantity
numComponents = numel(components);
quantity = zeros(numComponents,1); % preallocate
element = cell(numComponents,1); % preallocate
for m = 1:numel(components)
[quantity(m),element{m}] = numstr(components{m});
end
% Put into cell array
ProbeDat{k,1} = element;
ProbeDat{k,2} = quantity;
ProbeDat{k,3} = Probe_RL{k};
end
RefDat = cell(numel(Datenbank2),3);
for k = 1:numel(Datenbank2)
% Break up individual entries using comma and whitespace delimiters
parts = strsplit(Datenbank2{k},{',',' '});
% Loop through components, getting element and quantity
numParts = numel(parts);
quantity = zeros(numParts-2,1); % preallocate
element = cell(numParts-2,1); % preallocate
for m = 1:numParts -2
[quantity(m),element{m}] = numstr(parts{m});
end
group = str2double(parts{end});
% Put into cell array
RefDat{k,1} = element;
RefDat{k,2} = quantity;
RefDat{k,3} = group;
end
% Get alphabetically sorted list of all of the possible elements
allElements = sort(unique(vertcat(ProbeDat{:,1},RefDat{:,1})));
numVbls = numel(allElements); % total number of elements
% Make tables for probe data and databank (refernce)
Probe = array2table(zeros(size(ProbeDat,1),numVbls),...
'VariableNames',allElements);
Probe.Name = cell(height(Probe),1);
Probe = Probe(:,["Name";allElements]); % Make Name the first column
DataBank = array2table(zeros(size(RefDat,1),numVbls+1),...
'VariableNames',['Group';allElements]);
% Loop through cell array assigning table row values to Probe table
% table has a column for each possible element, and entries with the
% composition. The leading column has the string name for the
% composition
for k = 1:height(Probe)
for m = 1:numel(ProbeDat{k,1})
Probe.(ProbeDat{k,1}{m})(k) = ProbeDat{k,2}(m);
end
Probe.Name{k} = ProbeDat{k,3};
end
% Loop through cell array assigning table row values to DataBank table
% table has a column for each possible element, and the entries with the
% composition, the leading column gives the group number for each
% composition
for k = 1:height(DataBank)
for m = 1:numel(RefDat{k,1})
DataBank.(RefDat{k,1}{m})(k) = RefDat{k,2}(m);
end
DataBank.Group(k) = RefDat{k,3};
end
disp(DataBank)
% Loop through rows in Probe table looking for a matching group in DataRref
% and lableling accordingly
numCol = width(Probe); % number of columns before adding label
for k = 1:height(Probe)
idl = ismembertol(DataBank{:,2:end},Probe{k,2:numCol},tol,...
'DataScale',1,'ByRows',true);
if any(idl)
% Label the group, assume just one match or none
Probe.Group(k) = DataBank.Group(idl);
else
Probe.Group(k) = NaN; % NaN for not found
end
end
% Reorder the columns so that the group match is the second column, just to
% make it be more readable
Probe = Probe(:,['Name';'Group';allElements]);
disp(Probe)
Tatjana Mü
on 23 Oct 2023
Jon
on 23 Oct 2023
When you run the example with just the three files I have attached do you get an error?
If so please copy and paste the entire (all of the red) error mesage
Tatjana Mü
on 23 Oct 2023
Edited: Tatjana Mü
on 23 Oct 2023
Jon
on 23 Oct 2023
I don't think that the sizes you mention would be a problem. If they were, you would get an "out of memory" error, not the error messages you described. When you run the code for the full scale problem do you now get errors? If so, are you sure you are running the exact same code as attached here, just a differnce in the .xlsx file sizes. If you do get such error messages, running this exact same code please copy and paste the entire error message. Otherwise, please replace your code with what is attached here, as we know this works.
Tatjana Mü
on 23 Oct 2023
I actually got a few errors. Two were due to issues with your Datenbank_Matlab_Gruppierung2.xlsx file. On line 8 there is a whitespace between 0.5 and Na which causes parsing problems. On line 1224 you have a comma where you should have a period, 0,67Ca instead of 0.67Ca, which also causes parsing problems. You either have to be really careful with the entries, or further modify the code to be more robust to such issues.
Once those are fixed, I found a more fundamental issue, which is that with the tolerance you specify, there may be more than one match in the database. In the attached code I select the closest match (minimum sum square ), so you don't have errors.
I have attached a corrected database .xlsx sheet, called Datenbank_Matlab_Gruppierung3.xlsx, and a modified Test_Mineralcomposition_v2.m which uses this database file, along with the the min square error match.
Finally, you may want to take a look at Group 742 in your database, it is listed as 0.25Fe,1Fe. Is this correct looks strange to have 0.25Fe and also 1Fe., also conflicts with Group 2285 which is pure Fe.
Also I noticed that you turn off the warnings at the top of your code, where you have the line warning off. I wouldn't recommend this setting. The warnings can give you useful information, and hints about what may be going wrong.
Jon
on 23 Oct 2023
Here is a slightly modified version. The old one worked, but, depended upon the row numbers matching the group number. The logic here on labeling the group is better.
Tatjana Mü
on 24 Oct 2023
Jon
on 24 Oct 2023
Your welcome. If you're going to use this code alot or sharing it with others you could even further improve it. Specifically, looking at the whole code I see that you first combine the mineral and composition name, and then later in the code that I gave you I split them apart again. Could do the processing I do after splitting them apart, while they are already separate at the beginning. Also as I mentioned you could check for badly placed spaces or punctuation (, instead of .) if others will be modifying the .xlsx databank file. Otherwise if the files aren't changing and you just need it to work you should be all set with what you have. Good luck with your project.
More Answers (0)
Categories
Find more on Matrix Indexing in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
