Multi-conditional statements for array lookup (similar to multi-conditional vlookup from Excel)

Question

Shannon 2019-5-2

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/459720-multi-conditional-statements-for-array-lookup-similar-to-multi-conditional-vlookup-from-excel

编辑： Stephen23 2019-5-3

I have a matrix which lists a 'class' and a 'thickness' of a material, i.e.:

% A = [class thickness]
A =   [p     1.2;
       c     1.8;
       b     0.5;
       p     2.6;
       p     0.8;
       c     1.0;
       p     1.2
       c     8.0
       p     1.8];

I have a second matrix which lists the attributes of each class, split into thickness ranges, i.e.:

% B = [class min_t max_t att1 att2 att3 att4 att5]
B =   [b     0.1   1.0   10   0.1  9.2  1.4  1.8;
       b     1.01  10.0  8    0.08 1.8  2.8  1.0;
       c     0.1   10.0  7    0.7  5.6  2.0  1.1;
       p     0.1   1.0   8    0.2  8.0  1.7  1.9;
       p     1.01  1.1   6    0.09 1.9  1.1  0.8;
       p     1.11  2.0   10   0.4  2.8  0.8  1.2];   

What I am trying to do is to generate a matrix, C, which steps through each row of A, finds the corresponding match in B, and then lists the attributes. So in effect it has to checking that (a) the class character is the same, and (b) the thickness is within the min-max range specified in matrix B. So the solution should look like this:

 % C = [class thickness att1 att2 att3 att4 att5]
 C =   [p     1.2       10   0.4  2.8  0.8  1.2;
        c     1.8       7    0.7  5.6  2.0  1.1;
        etc

I can't work out an efficient way to do it without a whole bunch of loops. Any suggestions would be much appreciated.

2 个评论
显示无隐藏无

Stephen23 2019-5-2

@Shannon: it is not clear what data class your variables are: are they tables? In any case, please upload same sample data in one .mat file, by clicking the paperclip button.

Shannon 2019-5-3

test.mat

@Stephen: At the moment both A and B are cells. I looked at splitting them into char arrays and numerical matrices, but not sure that makes the problem any easier. I've uploaded the mat file.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Stephen23 2019-5-3

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/459720-multi-conditional-statements-for-array-lookup-similar-to-multi-conditional-vlookup-from-excel#answer_373427

编辑：Stephen23 2019-5-3

在 MATLAB Online 中打开

test.mat

You could use tables and innerjoin:

>> tA = cell2table(A,'variableNames',{'class','thickness'})
tA = 
    class    thickness
    _____    _________
    'p'      1.2      
    'c'      1.8      
    'b'      0.5      
    'p'      2.6      
    'p'      0.8      
    'c'        1      
    'p'      1.2      
    'c'        8      
    'p'      1.8      
>> tB = cell2table(B,'variableNames',{'class','t_min','t_max','att1','att2','att3','att4','att5'})
tB = 
    class    t_min    t_max    att1    att2    att3    att4    att5
    _____    _____    _____    ____    ____    ____    ____    ____
    'b'       0.1       1      10       0.1    9.2     1.4     1.8 
    'b'      1.01      10       8      0.08    1.8     0.8       1 
    'c'       0.1      10       7       0.7    5.6       2     1.1 
    'p'       0.1       1       8       0.2      8     0.7     0.9 
    'p'      1.01     1.1       6      0.09    1.9     0.1     0.8 
    'p'      0.11       2      10       0.4    2.8     0.8     1.2 
>> [tC,ida] = innerjoin(tA,tB);
>> [~,ida] = sort(ida);
>> tC = tC(ida,:);
>> idx = tC.thickness<tC.t_min | tC.thickness>tC.t_max;
>> tC(idx,:) = []
tC = 
    class    thickness    t_min    t_max    att1    att2    att3    att4    att5
    _____    _________    _____    _____    ____    ____    ____    ____    ____
    'p'      1.2          0.11      2       10      0.4     2.8     0.8     1.2 
    'c'      1.8           0.1     10        7      0.7     5.6       2     1.1 
    'b'      0.5           0.1      1       10      0.1     9.2     1.4     1.8 
    'p'      0.8           0.1      1        8      0.2       8     0.7     0.9 
    'p'      0.8          0.11      2       10      0.4     2.8     0.8     1.2 
    'c'        1           0.1     10        7      0.7     5.6       2     1.1 
    'p'      1.2          0.11      2       10      0.4     2.8     0.8     1.2 
    'c'        8           0.1     10        7      0.7     5.6       2     1.1 
    'p'      1.8          0.11      2       10      0.4     2.8     0.8     1.2 
    

Note that defining t_min and t_max limits like that is susceptible to data silently going unhandled because they fall between limits of adjacent bins (floating point error could contribute to this). Better to just have a lower/upper limit and use adjacent limits to define the bins (this will mean one limit which corresponds to a zero-wdith set).