You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
Lookup values in ranges of a table and copy a corresponding output value in another table
3 views (last 30 days)
Show older comments
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.
Accepted Answer
Voss
on 22 Aug 2022
11 Comments
Gabi
on 22 Aug 2022
Edited: Gabi
on 22 Aug 2022
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.
Gabi
on 25 Aug 2022
Edited: Gabi
on 25 Aug 2022
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
on 26 Aug 2022
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
on 26 Aug 2022
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
on 26 Aug 2022
Edited: Gabi
on 26 Aug 2022
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
on 28 Aug 2022
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
More Answers (0)
See Also
Categories
Find more on Tables 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!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)