Lookup values in ranges of a table and copy a corresponding output value in another table
1 次查看（过去 30 天）
显示 更早的评论
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.
采纳的回答
Voss
2022-8-22
11 个评论
Gabi
2022-8-22
编辑：Gabi
2022-8-22
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
2022-8-25
编辑：Gabi
2022-8-25
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
2022-8-26
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
2022-8-26
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
2022-8-26
编辑：Gabi
2022-8-26
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
2022-8-28
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
更多回答（0 个）
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Other Formats 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!发生错误
由于页面发生更改，无法完成操作。请重新加载页面以查看其更新后的状态。
Translated by 
您也可以从以下列表中选择网站：
如何获得最佳网站性能
选择中国网站（中文或英文）以获得最佳网站性能。其他 MathWorks 国家/地区网站并未针对您所在位置的访问进行优化。
美洲
- América Latina (Español)
- Canada (English)
- United States (English)
欧洲
- 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)
亚太
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)