Matching columns of two different tables with different tablesizes

3 次查看（过去 30 天）
Lukas Netzer
Lukas Netzer 2021-8-11
评论： Cris LaPierre 2021-8-21
I have two tables (attached):
In table1 I have two columns called WP_1 and WP_2.
In table 2 I have three columns called WP1, WP2 and WC.
For every pair of WP_1 and WP_2 I want to check if they are within any pair of WP1 and WP2.
If they are within a certain pair of WP1 and WP2, I want to take the value (0 or 1) of WC of that specific pair and print it to a new column in table1 --> table1.WC
If a pair of WP_1 and WP_2 does not fit any pair of WP1 and WP2 I want to print the value '2' in that specific line of table1.WC.
Here is an example how I want it to look like:
Explanation:
WP_1 and WP_2 in row 1 are [0,0] and fit within WP1 and WP2 in row1 [0,145] --> therefor WC in table 1 for that line is equal to WC in table2
WP_1 and WP_2 in row 2 are [0,5] and fit within WP1 and WP2 in row1 [0,145] --> therefor WC in table 1 for that line is equal to WC in table2
....
WP_1 and WP_2 in row 4 are [115,219] and DON'T fit within any pair of WP1 and WP2 in any row --> therefor WC in table 1 for that line is 2
WP_1 and WP_2 in row 5 are [219,262] and fit within WP1 and WP2 in row3 [169,1693] --> therefor WC in table 1 for that line is equal to WC in table2
My Code so far:
for n = 1:height(table1)
for m = 1:height(table2)
if table1.WP_1(n) >= table2.WP1(1:m) &...
table1.WP2_(n) <= table2.WP2(1:m)
table1.WC(n) = table2.WC(m);
else table1.WC(n) = 2;
end
end
end
I don't know how to check for every WP_1/WP_2 in table1 to check against all WP1/WP2 in table2..
Any help is very much appreciated - thank you!
  6 个评论
显示 4更早的评论
Lukas Netzer
Lukas Netzer 2021-8-11
编辑：Lukas Netzer 2021-8-11
Hey Chris, that's thrilling - as that was the extended version of my problem.
I might be able to reconstruct it, if that could help you helping me :)
edit:
https://de.mathworks.com/matlabcentral/answers/895142-using-index-to-match-rows-of-table#comment_1681102 should be kind of the same, but is the more complex version of yesterday's presented problem!

请先登录，再进行评论。

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

采纳的回答

Cris LaPierre
Cris LaPierre 2021-8-11
Ran in:
I have the details from the deleted post. Here is my code for that question. Let me know if you have any questions.
% Create table1 and table2
WP1 = [0;0;145;169;1693;0;0;255;0;0;48;1382;0;0;55;156;0];
WP2 = [0;145;169;1693;1980;0;255;260;0;48;1382;1400;0;55;156;180;0];
duration = [0;25;10;260;35;0;67;5;0;25;379;17;0;43;89;22;0];
table1 = table(WP1,WP2,duration);
WP1 = [0;0;169;1693;0;0;255;0;0;48;0;0;0];
WP2 = [0;169;1693;1980;0;255;260;0;48;1400;0;180;0];
category = [0;0;1;0;0;0;0;0;0;1;0;0;0];
table2 = table(WP1,WP2,category);
% Add a variable to track 'runs'
table1.counts = cumsum(sum(table1.Variables,2)==0)
table1 = 17×4 table
WP1 WP2 duration counts ____ ____ ________ ______ 0 0 0 1 0 145 25 1 145 169 10 1 169 1693 260 1 1693 1980 35 1 0 0 0 2 0 255 67 2 255 260 5 2 0 0 0 3 0 48 25 3 48 1382 379 3 1382 1400 17 3 0 0 0 4 0 55 43 4 55 156 89 4 156 180 22 4
table2.counts = cumsum(sum(table2.Variables,2)==0)
table2 = 13×4 table
WP1 WP2 category counts ____ ____ ________ ______ 0 0 0 1 0 169 0 1 169 1693 1 1 1693 1980 0 1 0 0 0 2 0 255 0 2 255 260 0 2 0 0 0 3 0 48 0 3 48 1400 1 3 0 0 0 4 0 180 0 4 0 0 0 5
% Create a table combining WP increments from both tables
WPs = sortrows(unique([table1.WP1 table1.WP2 table1.counts
table2.WP1 table2.WP2 table2.counts],'rows'),[3,1]);
% remove rows that are no longer needed
WPs(diff(WPs(:,1))>0 & diff(WPs(:,2))<=0,:)=[];
WPs = array2table(WPs,"VariableNames",["WP1","WP2","counts"])
WPs = 17×3 table
WP1 WP2 counts ____ ____ ______ 0 0 1 0 145 1 145 169 1 169 1693 1 1693 1980 1 0 0 2 0 255 2 255 260 2 0 0 3 0 48 3 48 1382 3 1382 1400 3 0 0 4 0 55 4 55 156 4 156 180 4
% Add category and duration information
lkupCat = @(x,y,z) min(table2.category(table2.counts == z & table2.WP1<=x & table2.WP2>=y));
WPs.category = rowfun(lkupCat,WPs,'OutputFormat',"uniform");
lkupDur = @(x,y,z,c) c*min(table1.duration(table1.counts == z & table1.WP1<=x & table1.WP2>=y));
WPs.duration = rowfun(lkupDur,WPs,'OutputFormat',"uniform");
% Remove helper variable counts
WPs.counts = []
WPs = 17×4 table
WP1 WP2 category duration ____ ____ ________ ________ 0 0 0 0 0 145 0 0 145 169 0 0 169 1693 1 260 1693 1980 0 0 0 0 0 0 0 255 0 0 255 260 0 0 0 0 0 0 0 48 0 0 48 1382 1 379 1382 1400 1 17 0 0 0 0 0 55 0 0 55 156 0 0 156 180 0 0
  4 个评论
显示 2更早的评论
Lukas Netzer
Lukas Netzer 2021-8-21
If I may get your attention again. I tried the same code for the following two tables:
Here I want to match again WP1 and WP2 of the two tables. While it is working for wc_LL248JH - it is not working for wps_LL248JH. I tried the following code:
lkupS = @(~,~,~,~,x,y) min([wps_LL248JH.speed(wps_LL248JH.WP1<=x & wps_LL248JH.WP2>=y),1]);
wp_LL248JH.speed = rowfun(lkupS,wp_LL248JH,'OutputFormat',"uniform");
But whenever WP1 and WP2 are the same (and greater than 0) in wp_LL248JH, I get the following error:
Error using tabular/rowfun>dfltErrHandler (line 517)
Applying the function '@(~,~,~,~,x,y)min([wps_LL248JH.speed(wps_LL248JH.WP1<=x&wps_LL248JH.WP2>=y),0])' to the 67th row of A generated the following error:
Dimensions of arrays being concatenated are not consistent.
Error in tabular/rowfun>@(s,varargin)dfltErrHandler(grouped,funName,s,varargin{:}) (line 262)
errHandler = @(s,varargin) dfltErrHandler(grouped,funName,s,varargin{:});
Error in tabular/rowfun (line 282)
[b_data{igrp,:}] = errHandler(struct('identifier',ME.identifier, 'message',ME.message, 'index',igrp),inArgs{:});
I don't get why this function is not working here - it's basically the same or is it not?
Thanks for reading!
Cris LaPierre
Cris LaPierre 2021-8-21
It is much easier to help if you share the actual data instead of screen shots. Save yout tables to a mat file and attach them to your post using the paperclip icon.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Logical 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by