Copy If greater than column value

I have 3 tables (A, B, and C) and I want to copy the values from table A to table C if the value of column1 in table A is between the values of columns 2 and 3 in table B. Here's an example:

TableA
Col1    Col2    Col3
----    ----    ----
7       48      210
18      18      209
... (TableA has thousands of rows)

TableB
Col1    Col2    Col3
----    ----    ----
1       0       10
... (TableB has 10 rows)

So in this case, I want to copy the first row of TableA (7, 48, 210) to TableC because TableA.Col1 (7) is between the values of TableB.Col2 (0) and TableB.Col3 (10). Ultimately, I'll write an if/else statement so all rows of the data are copied to any of 10 tables according to an extension of this logic. I tried the following code:

if TableA.Col1(:,1) >= TableB.Col2(:,1) && TableA.Col1 < TableB.Col3(:,1)
    TableC = TableA;
else
    TableC = NaN;
end

When I try this, TableC only has 1 NaN in it so this is clearly not the right way to do this.

Answer 1

[Rik]

Use rowfun or a loop. Your current logical statement is checking all rows at once.

[Caitlin Ridgewell]

Thanks, that makes perfect sense! I've been looking into rowfun and loops since you said that and have changed the way this code works now, running into another issue within the same process. When I run

for i=1:height(TableA)
    if TableA(i,1) >= TableB(1,2) && TableA(i,1) < TableB(1,3)
        TableC = TableA;
    end
end

I get the error "Operator '>=' is not supported for operands of type 'table'." I tried converting the tables to cell (table2cell) and got the same message but with 'cell' instead. So that's one issue that I'm not sure how to fix given that I need only the data that meet both of the TableB criteria.

Another is that I'm concerned that all of the rows in TableA will copy over using this format, rather than only the ones that meet the criteria in the If statement. Would only the rows that meet these criteria copy over if I instead ran

for i=1:height(TableA)
    if TableA(i,1) >= TableB(1,2) && TableA(i,1) < TableB(1,3)
        TableC = TableA(i,:);
    end
end

?

[Rik]

You seem to treat tables as if they are matrices. You need to compare the value itself, not the entire table.

If you attach your data, I can write code that will directly work on your data. You can also generate fake data has the same shape and type.

[Caitlin Ridgewell]

Thank you! I've attached fake data for Tables A and B here, pre-import to Matlab.

[Rik]

Seeing as you attached your data as text files without the code you used to read them to tables, I think it is fine if I solve your problem with regular matrices. (you can get my readfile function from the FEX or from the AddOn-explorer for sufficiently new releases of Matlab)

A=readfile('https://www.mathworks.com/matlabcentral/answers/uploaded_files/348413/TableA.txt');
A=cellfun(@(x) {strsplit(x,'\t')},A);
A(cellfun('prodofsize',A)==1)=[];
A=vertcat(A{:});
A(:,end)=[];
A=cellfun(@str2double,A);

B=readfile('https://www.mathworks.com/matlabcentral/answers/uploaded_files/348416/TableB.csv');
B=cellfun(@(x) {strsplit(x,',')},B);
B=vertcat(B{:});
B=cellfun(@str2double,B);

C=NaN([size(A) size(B,1)]);
for n=1:size(B,1)
    L=A(:,1)>=B(n,2) & A(:,1) < B(n,3);
    C(L,:,n)=A(L,:);
end

Instead of a 3D matrix you could also use a cell array to store the results:

C=cell(size(B));
for n=1:size(B,1)
    L=A(:,1)>=B(n,2) & A(:,1) < B(n,3);
    C{n}=NaN(size(A));
    C{n}(L,:)=A(L,:);
end

[Caitlin Ridgewell]

Thank you! That worked perfectly.