Matching columns of two different tables with different tablesizes

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!

[KSSV]

Read about ismember.

[Lukas Netzer]

thank you for your comment!

ismember only checks for exact values right? but can I do it for ranges, and if values are within that range?

[Cris LaPierre]

I seem to recall a question yesterday very similar to this, though the third column in each table had different information (duration and category). Can't find it now.

[Lukas Netzer]

Hi Chris,

thanks for your comment. I posted a similar question yesterday, but have edited the table since and simplified my Problem (hopefully) to make it more clear. Thus the Other question has been deleted - I hope that's within the rules. Best regards.

[Cris LaPierre]

It is. It's just I have a solution for what you posted yesterday.

[Lukas Netzer]

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!

Answer 1

[Cris LaPierre]

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   

[Cris LaPierre]

For the question you have asked here, I have not tested exhausttively, but this may do what you ask.

load tables.mat

lkupWC = @(~,~,~,x,y,~) min([wc_LL248JH.WC(wc_LL248JH.WP1<=x & wc_LL248JH.WP2>=y),2]);
ddwp_LL248JH.WC = rowfun(lkupWC,ddwp_LL248JH,'OutputFormat',"uniform")

ddwp_LL248JH = 35904×7 table
    duration    distance     waypoints      WP_1    WP_2    Stops    WC
    ________    ________    ____________    ____    ____    _____    __

         0            0     "[0, 0]"          0       0       1      0 
      27.5        114.6     "[0, 5]"          0       5       0      0 
     552.8       3914.2     "[5, 115]"        5     115       0      0 
     584.7       7696.2     "[115, 219]"    115     219       0      2 
     290.4       4513.3     "[219, 262]"    219     262       0      1 
     442.8       7454.2     "[262, 328]"    262     328       0      1 
     506.7       8289.7     "[328, 408]"    328     408       0      1 
        99       1003.4     "[408, 424]"    408     424       0      1 
     679.5        11139     "[424, 531]"    424     531       0      1 
     149.6       2183.6     "[531, 562]"    531     562       0      1 
     414.3       6023.8     "[562, 620]"    562     620       0      1 
     398.6       6680.4     "[620, 676]"    620     676       0      1 
     666.1        11065     "[676, 781]"    676     781       0      1 
     113.6        800.7     "[781, 805]"    781     805       0      1 
       8.9         49.6     "[805, 808]"    805     808       0      1 
     454.1       6109.9     "[808, 919]"    808     919       0      1 

You can follow a similar approach as before to combine WC from table2 and duration from table1. Since your data no longer restarts, creating a table of all intervals is easier.

% Create table of all intervals
WPs = unique([ddwp_LL248JH.WP_1; ddwp_LL248JH.WP_2; wc_LL248JH.WP1; wc_LL248JH.WP2]);
WPs = [[0;WPs(1:end-1)],WPs];

WPs = array2table(WPs,"VariableNames",["WP1","WP2"]);

% Add WC values from table2
lkupWC = @(x,y) min(wc_LL248JH.WC(wc_LL248JH.WP1<=x & wc_LL248JH.WP2>=y));
WPs.WC = rowfun(lkupWC,WPs,'OutputFormat',"uniform");
% Add duration value from table1
lkupDur = @(x,y,c) c*min(ddwp_LL248JH.duration(ddwp_LL248JH.WP_1<=x & ddwp_LL248JH.WP_2>=y));
WPs.duration = rowfun(lkupDur,WPs,'OutputFormat',"uniform")

WPs = 35321×4 table
    WP1    WP2    WC    duration
    ___    ___    __    ________

      0      0    0          0  
      0      5    0          0  
      5    115    0          0  
    115    145    0          0  
    145    169    1      584.7  
    169    219    1      584.7  
    219    262    1      290.4  
    262    328    1      442.8  
    328    408    1      506.7  
    408    424    1         99  
    424    531    1      679.5  
    531    562    1      149.6  
    562    620    1      414.3  
    620    676    1      398.6  
    676    781    1      666.1  
    781    805    1      113.6  

[Lukas Netzer]

Thank you very much - this was really helpful and exciting! All the best to you! :)

[Lukas Netzer]

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]

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.