Add/change value in a table based on a categorical array in the same table

Hello everybody,

I have a table called testData that contains a categorical column with element names. Another table called List assignes to the element a specific numerical value. The aim of my code is to add an additional column to the testData and add the numeric values assigned in the table List.

% create List each element has a numeric value
ionName = categorical(["Co", "Cr", "Ga"]');
ionNumeric = [ 40, 30, 20]';
list = table(ionName,ionNumeric);

% create Test Data table 
ionNameTest = categorical(["Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga","Co", "Cr", "Ga"]');
testData = table(ionNameTest);

% my code:
% add the column
h = height(testData);
testNumeric = zeros(h,1); 
testData = addvars(testData, testNumeric); 

% go through each cell of list -> then trough each element in list and
% compare the names -> change the value
for i = 1:height(testData)
    for j = 1:height(list)
        if list.ionName(j) == testData.ionNameTest(i)
            testData.testNumeric(i) = list.ionNumeric(j);
        end 
    end 
end

In real life my table has more than 4 mio entities - so my code takes over an hour to run - is there any faster way ? or less complex way?

Thank you for your help!

Martina

Answer 1

[Cris LaPierre]

I would use outerjoin.

% create List each element has a numeric value
ionName = categorical(["Co", "Cr", "Ga"]');
ionNumeric = [ 40, 30, 20]';
list = table(ionName,ionNumeric);

% create Test Data table 
ionNameTest = categorical(["Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga","Co", "Cr", "Ga"]');
testData = table(ionNameTest);

% Join tables
joinedData = outerjoin(testData,list,"Type","left","LeftKeys","ionNameTest",...
    "RightKeys","ionName","MergeKeys",true)

joinedData = 15×2 table
    ionNameTest_ionName    ionNumeric
    ___________________    __________

            Co                 40    
            Co                 40    
            Co                 40    
            Co                 40    
            Co                 40    
            Cr                 30    
            Cr                 30    
            Cr                 30    
            Cr                 30    
            Cr                 30    
            Ga                 20    
            Ga                 20    
            Ga                 20    
            Ga                 20    
            Ga                 20    

[Cris LaPierre]

A slightly different syntax can be used to preserve the variable name used in testData. Rather than merging, tell outerjoin which variable(s) from list to join.

% create List each element has a numeric value
ionName = categorical(["Co", "Cr", "Ga"]');
ionNumeric = [ 40, 30, 20]';
list = table(ionName,ionNumeric);
% create Test Data table 
ionNameTest = categorical(["Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga","Co", "Cr", "Ga"]');
testData = table(ionNameTest);

% Join tables
joinedData = outerjoin(testData,list,"Type","left","LeftKeys","ionNameTest",...
    "RightKeys","ionName",'RightVariables','ionNumeric')

joinedData = 15×2 table
    ionNameTest    ionNumeric
    ___________    __________

        Co             40    
        Co             40    
        Co             40    
        Co             40    
        Co             40    
        Cr             30    
        Cr             30    
        Cr             30    
        Cr             30    
        Cr             30    
        Ga             20    
        Ga             20    
        Ga             20    
        Ga             20    
        Ga             20    

[Martina Heller]

Thank you very much for your help!

[Cris LaPierre]

If you also have outerjoin return the index of the left table, you can use that to put the table back into its original order.

% create List each element has a numeric value
ionName = categorical(["Co", "Cr", "Ga"]');
ionNumeric = [ 40, 30, 20]';
list = table(ionName,ionNumeric);
% create Test Data table 
ionNameTest = categorical(["Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga", "Co", "Cr", "Ga","Co", "Cr", "Ga"]');
testData = table(ionNameTest)

testData = 15×1 table
    ionNameTest
    ___________

        Co     
        Cr     
        Ga     
        Co     
        Cr     
        Ga     
        Co     
        Cr     
        Ga     
        Co     
        Cr     
        Ga     
        Co     
        Cr     
        Ga     

% Join tables
[joinedData,ileft] = outerjoin(testData,list,"Type","left","LeftKeys","ionNameTest",...
    "RightKeys","ionName",'RightVariables','ionNumeric');
joinedData(ileft,:)=joinedData

joinedData = 15×2 table
    ionNameTest    ionNumeric
    ___________    __________

        Co             40    
        Cr             30    
        Ga             20    
        Co             40    
        Cr             30    
        Ga             20    
        Co             40    
        Cr             30    
        Ga             20    
        Co             40    
        Cr             30    
        Ga             20    
        Co             40    
        Cr             30    
        Ga             20