Search in tables.

Hi. I'm going to try to explain my problem with an example.

I have a data table called "EjemploElementos" with two categorical variables:

I have an other data table called "EjemploMaq" with two categorical variables and a numeric variable:

I want to obtain the following table:

I want to search in "EjemploMaq" the values in "EjemploElementos". I don't know if it's possible to do that with MatLab.

EjemploElementos1 and EjemploMaq2 represents the same feature. I have to search the letter in EjemploElementos1 that correspond with the categories in EjemploMaq. For example, for EjemploElementos1= '2 400 E' I want to find EjemploMaq2 = E for the corresponding value of EjemploElementos2 ('M1' in this case).

My real problem is much bigger than this with around 17000 examples. Thank you for your help.

Answer 1

[Vidya Viswanathan]

Hi Manuel,

Based on your data and requirement, I came up with a code snippet that might be applicable. Try it out and let me know if it helps.

clear 
clc

%%Creating the two tables
EjemploElementos1=categorical({'2 400 E';'B VT 080'; '3 200 B';'EB WT 052';'Cali B';'Cali EE'});
catnames1={'M1';'M2';'M3';'M4';'M5'};
catnames2={'B';'E';'EE';'EB'};
EjemploElementos2=categorical([1 1 2 3 5 4]',1:5,catnames1);
EjemploElementos=table(EjemploElementos1,EjemploElementos2)

A=reshape(repmat(1:5,[4 1]),[],1);
EjemploMaq1=categorical(A,1:5,catnames1);
B=reshape(repmat(1:4,[5 1])',[],1);
EjemploMaq2=categorical(B,1:4,catnames2);

EjemploMaq3=rand([20 1]);
EjemploMaq=table(EjemploMaq1,EjemploMaq2,EjemploMaq3)

%%Searching through the table

searchCategories=categories(EjemploMaq.EjemploMaq2);

for i=1:size(EjemploElementos,1)
    for j=1:size(searchCategories,1)
        if strfind(char(EjemploElementos1(i)),searchCategories{j})
            TempColumn{i}=searchCategories{j};
        end
    end
end
TempColumn=table(categorical(TempColumn'));
EjemploElementos=[EjemploElementos TempColumn];
EjemploElementos.Properties.VariableNames{2} = 'EjemploMaq1';
EjemploElementos.Properties.VariableNames{3} = 'EjemploMaq2'; % Change the name of the temporary column that you created
C=join(EjemploElementos,EjemploMaq)
C(:,3)=[] % Remove the temporary column that you created

I hope this helps. You could neglect the first section of the code snippet (that basically generates the table) and replace it with your table data.

Regards,

Vidya Viswanathan

[Manuel Valenciano]

I have an error with join.

I think that it fails because in EjemploElementos1 some values are repeated.

This is the error:

Error using table/join (line 111)
The key variable for B must have unique values.

This is a most real example for EjemploElementos: