Lookup values in ranges of a table and copy a corresponding output value in another table

Hi,

I'm trying find 'Val' value from table A given by Lap number in table B from minimum of first 3 rows coresponding to each lap in table A (with no sorting).

The answer would be in Table B:

Lap Val

1 1

2 0

5 0

Thanks.

Answer 1

[Voss]

A = load('A')

A = struct with fields:
    Events: [104×2 table]

B = load('B')

B = struct with fields:
    Events: [3×1 table]

[~,idx] = ismember(B.Events.Lap,A.Events.Lap);
result = A.Events(idx,:)

result = 3×2 table
    Lap    Var
    ___    ___

     1      1 
     2      0 
     5      0 

[Gabi]

Hi, thanks for the answer, I've just realised that both files A and B had the same table name internally (I've reuploaded with different values to make it a bit more clear.

I've reached this far with ismember, but the question is slightly different, I need to find the minumum value of the first 3 rows for each lap from file A (table Data, Lap) given by file B (table Find, Lap). In my original example indeed they were always the 1'st row for each of the required laps hence your solution works, but if they are not the 1'st row it won't.

With the new data sets the answer would be:

file B, table Find:

Lap Var

1 2 (minimum value of the first 3 rows for Lap 1 in file A table Data)

2 0 (minimum value of the first 3 rows for Lap 2 in file A table Data)

5 3 (minimum value of the first 3 rows for Lap 5 in file A table Data)

Thanks.

[Voss]

A = load('A');
B = load('B');

N = size(B.Find,1);
B.Find.Var = zeros(N,1);
for ii = 1:N
    idx = find(A.Data.Lap == B.Find{ii,'Lap'});
    if numel(idx) > 3
        idx(4:end) = [];
    end
    B.Find{ii,'Var'} = min(A.Data{idx,'Var'});
end
disp(B.Find);

    Lap    Var
    ___    ___

     1      2 
     2      0 
     5      3 

[Gabi]

Thanks, it works.

[Voss]

You're welcome!

[Gabi]

Assuming I've got a 3'rd column in File A table Data (Var2) can that be extracted based on file B table Find and the newly found Var previously, in essence it becomes a look up function based on 2 params. I'm not able to find a solution using indexing with 2 variables.

Thanks.

[Voss]

Repeating what was done before but with new A.Data table which has three columns:

A = load('A');
B = load('B');
A.Data

ans = 104×3 table
    Lap    Var    Var2
    ___    ___    ____

     1      7       1 
     1      2       2 
     1      5       3 
     1      6       4 
     1      1       5 
     1      4       6 
     1      1       7 
     1      5       8 
     1      9       9 
     1      5      10 
     2      1      11 
     2      6      12 
     2      0      13 
     2      8      14 
     2      7      15 
     2      6      16 

N = size(B.Find,1);
B.Find.Var = zeros(N,1);
for ii = 1:N
    idx = find(A.Data.Lap == B.Find{ii,'Lap'});
    if numel(idx) > 3
        idx(4:end) = [];
    end
    B.Find{ii,'Var'} = min(A.Data{idx,'Var'});
end
B.Find

ans = 3×2 table
    Lap    Var
    ___    ___

     1      2 
     2      0 
     5      3 

Now, what exactly do you want to do?

[Gabi]

Hi, thanks for taking the time to help me out, the output would become:

Lap Var Var2

1 2 2 (minimum value of the first 3 rows for Lap 1 in file A table Data and the corresponding Val2 based on Lap and Var)

2 0 13 (minimum value of the first 3 rows for Lap 2 in file A table Data and the corresponding Val2 based on Lap and Var)

5 3 75 (minimum value of the first 3 rows for Lap 5 in file A table Data and the corresponding Val2 based on Lap and Var)

Val 2 for example can be the time, or if easier (or to make it more complete): if there is number of columns in file A table Data the output would be to extract the entire row after the intial min calculation in the previous for loop.

Thanks.

[Gabi]

Probabaly not the most elegant solution but it works for Var2, how can be done for the entire row if there are even more columns in file A table Data?:

A = load('A');
B = load('B');
A.Data

ans = 104×3 table
    Lap    Var    Var2
    ___    ___    ____

     1      7       1 
     1      2       2 
     1      5       3 
     1      6       4 
     1      1       5 
     1      4       6 
     1      1       7 
     1      5       8 
     1      9       9 
     1      5      10 
     2      1      11 
     2      6      12 
     2      0      13 
     2      8      14 
     2      7      15 
     2      6      16 

N = size(B.Find,1);
B.Find.Var = zeros(N,1);
B.Find.Var2 = zeros(N,1);
for ii = 1:N
    idx = find(A.Data.Lap == B.Find{ii,'Lap'});
    if numel(idx) > 3
        idx(4:end) = [];
    end
    B.Find{ii,'Var'} = min(A.Data{idx,'Var'});
    idx2 = find(A.Data.Lap == B.Find{ii,'Lap'} & A.Data.Var == B.Find{ii,'Var'});
    B.Find{ii,'Var2'} = min(A.Data{idx2,'Var2'});
end
B.Find

ans = 3×3 table
    Lap    Var    Var2
    ___    ___    ____

     1      2       2 
     2      0      13 
     5      3      75 

[Voss]

A = load('A');
B = load('B');

% for demonstration, I make some additional 
% random variables in A.Data:
N = size(A.Data,1);
A.Data.Var3 = randi(100,N,1);
A.Data.Var4 = randi(100,N,1);
A.Data.Var5 = randi(100,N,1);
disp(A.Data)

    Lap    Var    Var2    Var3    Var4    Var5
    ___    ___    ____    ____    ____    ____

     1      7       1      94      46      78 
     1      2       2      31      85      53 
     1      5       3      49      11      14 
     1      6       4      77      61      81 
     1      1       5      93      49      73 
     1      4       6      16      19      62 
     1      1       7      40      11      53 
     1      5       8      28      10      43 
     1      9       9      28      68      55 
     1      5      10      72      42      91 
     2      1      11      43       7      84 
     2      6      12      67      82      65 
     2      0      13      99      27      58 
     2      8      14      95       9      43 
     2      7      15      12     100      29 
     2      6      16      13      38      57 
     2      0      17      67      69      79 
     2      6      18      95      68      19 
     2      5      19      54      35      26 
     2      3      20       6      90      72 
     2      9      21      39      30      28 
     2      6      22      17      35      88 
     2      8      23       2      77       9 
     2      0      24     100      47      61 
     3     10      25      18      85      29 
     3      0      26      62      51      88 
     3      5      27      66      99      58 
     3     10      28      48      41      28 
     3      4      29      92      73      20 
     3      4      30      18      49      32 
     3      0      31      75      12      23 
     3      0      32      16      20      81 
     3      3      33       3      44      73 
     3      5      34      58      78      27 
     3     10      35       9      36       7 
     3      8      36      79      30      21 
     3      8      37      91      29      31 
     3      3      38      96      90      10 
     3     10      39      61      87      98 
     3      5      40      74      10      32 
     3      5      41      92      11      77 
     4      4      42      17      92      60 
     4      0      43      24      54      98 
     4      9      44      16       4      32 
     4      6      45      63      98      54 
     4      6      46     100      81      39 
     4      5      47      14      83      26 
     4     10      48      15      63       8 
     4      5      49      98      98      50 
     4      8      50      97      27      82 
     4      8      51      78       8      22 
     4      9      52      40      29      82 
     4      4      53      67      62      43 
     4      5      54      68      46      49 
     4      5      55      92      93      74 
     4      8      56      53      40      16 
     4      2      57      49      27      52 
     4      8      58      29      89      41 
     4      8      59      21      41       6 
     4     10      60      90      33      42 
     4      4      61      79      77      88 
     4      8      62       6       2      10 
     4      3      63      80      60      37 
     4      6      64      75      85      48 
     4      1      65      81      18      29 
     4      4      66      80      28      72 
     4      9      67      77      39      76 
     4      2      68      27      49      66 
     4      3      69      88      63      46 
     4      3      70      73      38      33 
     4      9      71      93      12      16 
     4      2      72      33      91       4 
     5      5      73      52      86      81 
     5      8      74      86      22      74 
     5      3      75      71      62      86 
     5      7      76      33      95      28 
     5      2      77      71      82      99 
     5      1      78      53      69      54 
     5      2      79      28      52      36 
     5      6      80      68      40      31 
     5      6      81      36      41      83 
     5      6      82      99      77      57 
     5      0      83      71      60      97 
     5      5      84      51      39      18 
     5      9      85      26      71      58 
     5      0      86      28      88      29 
     5      8      87      79      46      87 
     5      2      88       1      90      46 
     5      5      89      98      41      84 
     5      5      90      50      49      15 
     5      8      91      40       8      65 
     5      7      92      34      47       4 
     5      4      93      57       7      89 
     5      6      94      24      78      71 
     5      9      95      36      20      79 
     5      7      96       5      32      86 
     5      5      97      80       5      13 
     5     10      98       1      90      28 
     5      3      99      30      64      90 
     5      6     100       7      54      13 
     5      1     101      21      26      98 
     5      9     102      63       6      79 
     5      4     103      80      93      26 
     5      8     104      93      18      94 

% now include all variables from A.Data in B.Find 
% (initialized to zero in B.Find):
N = size(B.Find,1);
var_names = A.Data.Properties.VariableNames;
B.Find{:,var_names(2:end)} = zeros(N,numel(var_names)-1);
disp(B.Find)

    Lap    Var    Var2    Var3    Var4    Var5
    ___    ___    ____    ____    ____    ____

     1      0      0       0       0       0  
     2      0      0       0       0       0  
     5      0      0       0       0       0  

% now set the variables in B.Find equal to their values in A.Data,
% where the row from A.Data that's used is the row with minimum 
% 'Var', among the first three rows of each given 'Lap' in A.Data:
for ii = 1:N
    idx = find(A.Data.Lap == B.Find{ii,'Lap'});
    if numel(idx) > 3
        idx(4:end) = [];
    end
    [~,min_idx] = min(A.Data{idx,'Var'});
    B.Find(ii,2:end) = A.Data(idx(min_idx),2:end);
end
B.Find

ans = 3×6 table
    Lap    Var    Var2    Var3    Var4    Var5
    ___    ___    ____    ____    ____    ____

     1      2       2      31      85      53 
     2      0      13      99      27      58 
     5      3      75      71      62      86 

[Gabi]

Thanks for your help, this is all clear now.

[Voss]

Excellent. You're welcome!