Matching columns of two different tables with different tablesizes

3 views (last 30 days)
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 Comments
Lukas Netzer
Lukas Netzer on 11 Aug 2021
Edited: Lukas Netzer on 11 Aug 2021
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!

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 11 Aug 2021
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 Comments
Lukas Netzer
Lukas Netzer on 21 Aug 2021
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 on 21 Aug 2021
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.

Sign in to comment.

More Answers (0)

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!