Add row from one table to another if column combination do not exist in the last mentioned table.

Hello,

Apologies in advancem for the long message but I have tried to be as concise as possible.

I have two different tables, one called bigTable and one called myTable. Each table consists of a total of 27 columns.

What I want to do is I want to see if a certain combination of column 1, 2 and 3 in myTable already exists in bigTable , if not then add the row to bigTable.

Below is a picture where the incircled rows is the rows that i want to add, since row 1-18 already exists in bigTable.

I tried to do like below:

for(i=1:height(myTable))

if(ismember(myTable(i,[1 2 3]),bigTable(:,[1 2 3]))); %

;

else

bigTable = [bigTable; myTable(i,:)];

end

Everything works fine until row 22 which is not getting added to bigTable. I am pretty sure it is because the three different columns already exists seperately in row 19,20 and 21.

My question is how I can change my code so that it checks if the combination of column 1, 2 and 3 already exists in some of the rows in bigTable or not?

Answer 1

[Ishaan Mehta]

Hello

I understand that you wish to move rows myTable to bigTable only if the combination of the first 3 columns of myTable'r does not already exist in any row in bigTable.

It can be easily done by nested loops and creating an array with values of first 3 columns of any given row in myTable, which can by compared to all the rows existing in bigTable during any given iteration.

Here is a code snippet for the same:

bigTable = table();
myTable = table();

% entering dummy data in myTable
myTable.col1(1) = randi([1 3]); % random value from 1,2,3
myTable.col2(1) = randi([1 3]);
myTable.col3(1) = randi([1 3]);

myTable.col4(1) = randi([1 8]);
myTable.col5(1) = randi([1 8]);
myTable.col6(1) = randi([1 8]);

for i = 1:1000
    newRow = [randi([1 3]) randi([1 3]) randi([1 3]) randi([1 8]) randi([1 8]) randi([1 8])];
    myTable{i,:} = newRow;
end
myTable

myTable = 1000×6 table
    col1    col2    col3    col4    col5    col6
    ____    ____    ____    ____    ____    ____

     3       2       1       6       6       3  
     1       3       3       4       8       2  
     1       2       2       1       4       6  
     3       3       3       2       1       4  
     3       1       2       2       8       3  
     2       3       2       3       5       5  
     2       3       3       1       2       7  
     1       2       2       6       1       5  
     3       1       3       7       2       8  
     3       1       1       2       4       1  
     3       3       3       8       3       6  
     1       2       1       1       6       2  
     1       3       3       3       1       4  
     2       2       2       7       5       4  
     3       1       1       4       5       3  
     3       2       3       3       5       7  

% adding rows to bigTable only for unique combination for first 3 columns

for i = 1:height(myTable)
    rowFound = 0;
    first3cols= myTable{i, 1:3}; % values of first 3 columns of current myTable row
    
    for j = 1:height(bigTable)
        if all(first3cols == bigTable{j, 1:3}) % if values match for all 3 columns
            rowFound = 1;
            break;
        end
    end

    if ~rowFound
        bigTable = [bigTable; myTable(i,:)];
    end
end

bigTable

bigTable = 27×6 table
    col1    col2    col3    col4    col5    col6
    ____    ____    ____    ____    ____    ____

     3       2       1       6       6       3  
     1       3       3       4       8       2  
     1       2       2       1       4       6  
     3       3       3       2       1       4  
     3       1       2       2       8       3  
     2       3       2       3       5       5  
     2       3       3       1       2       7  
     3       1       3       7       2       8  
     3       1       1       2       4       1  
     1       2       1       1       6       2  
     2       2       2       7       5       4  
     3       2       3       3       5       7  
     1       1       3       3       4       5  
     2       2       1       3       2       7  
     1       1       2       2       4       5  
     2       1       1       1       4       8  

Hope it helps

Ishaan Mehta