Copy the content of one table to an other table with different size

I need to copy the "GroupCount" of the first table to the second table. Second table has larger size and I need to copy the GrouCount of table one to the second table with correpsonding tme and radius. For example GroupCount=1 of the first row of the first table should be copied to the seond table that its time=0 and its radous is 0.

I used this code but I got error:

  table2.x=...
         table1.GroupCount(table1.time==table2.time && table1.radius==table2.radius)
     

and the error:

         Matrix dimensions must agree.
         

[Max Murphy]

Is 'x' the name of the new column in table2 you would like to copy these values to?

[Zeynab Mousavikhamene]

@Max Murphy Yes!

Answer 1

[Guillaume]

This is trivially done with outerjoin:

mergedtable = outerjoin(yourbigtable, yourgroupcounttable, 'Keys', {'Time', 'radius'}, 'MergeKeys', true, 'Type', 'left')

Any time you want to merge tables, look at join, innerjoin, or outerjoin depending on the type of join you need.

[Zeynab Mousavikhamene]

@ Guillaume: Thaks. That would be great if I can do it with one line of code.

I got this error:

Undefined function or variable 'left'.

[Guillaume]

Oops, the left should have been enclosed in quotes. Fixed now.

[Zeynab Mousavikhamene]

@Guillaume

Worked perfectly. With one line and very fast. Thanks!

[Zeynab Mousavikhamene]

@Guillaume

A quick question:

Table 2 is in a loop and each time one column is added to it. How can I keep those columns? I think I need to change one of these properties to make it happen:

'Keys', {'Time', 'radius'}, 'MergeKeys', true, 'Type', 'left'

What does Type left do?

what does mergekeys true do?

[Guillaume]

I'm not sure I understand what you're doing. Example code would be useful.

If 'MergeKeys' is false, the 'Time' and 'radius' variables would be included twice in the output table once as 'NameOfLeftTable_Time' and 'NameOfLeftTable_radius' and then again as 'NameOfRightTable_Time' and 'NameOfRightTable_radius'. For an outer left join there's no point of that.

'Type', 'left' specifies the type of outer join. An outer left join includes all the rows of the left table and only the rows of the right table whose keys match. An outer right join includes all the rows of the right table and only the rows of the left table whose keys match. A full outer join includes all the rows of both tables merging those that match. This is where you'd want 'MergeKeys', 'false' to see which rows matched and which didn't.

See the documentation which I linked to in my answer.

Answer 2

[Max Murphy]

You need to initialize column 'x' first in table2:

nRows = size(table2,1);
x = table(nan(nRows,1),'VariableNames','x'); % Make an empty table with same rows as table2
table2 = [table2, x]; % Concatenate

There is probably a nicer way to do this next part, but a brute force approach would be as follows:

uTime = unique(table1.time);
uRad = unique(table1.rad);
% Iterate to match unique pairs of unique combinations from table1.
% This part could be improved
for iT = 1:numel(uTime)
    for iR = 1:numel(uRad)
        idxT1 = (table1.time==uTime(iT)) & (table1.radius == uRad(iR));
        if ~any(idxT1)
            continue; % Skip pairs that aren't in Table1
        else
            % Assuming you would want to combine group counts
            % (if Time and Radius is the same)
            groupCount = sum(table1.GroupCount(idxT1));  
        end
        idxT2 = (table2.time==uTime(iT)) & (table2.radius == uRad(iR));
        table2.x(idxT2) = groupCount;
    end
end

[Zeynab Mousavikhamene]

@ Max Murphy Thanks Max. It is a huge table and there are lots of tables like this that should be parsed. Is there any way to avoid two for loops ?

[Max Murphy]

I was wondering about this so I looked at your other question here; are the values in 'table1' and 'table2' related to what you're trying to match into categories there? Maybe there is a better way of getting the data into tables to begin with?

[Zeynab Mousavikhamene]

Thanks for your time. That question is resolved and it is not related to my question here.

[Max Murphy]

No problem. OK, how about this. First, make toy dataset:

%% Toy data
time = repmat(1:100,3,1);
time = time(:);
radius = repmat(1:6,50,1);
radius = radius(:);
GroupCounts = randi(100,[300 1]);
table1 = table(time,radius,GroupCounts);

time = repmat(1:10:291,5,1);
time = time(:);
radius = repmat(1:30,5,1); 
radius = radius(:);
table2 = table(time,radius);

Next, format table2 as before:

%% Add 'x' to table2 and use relevant rows of table1 to compare
% Note previous answer had bugs in first 2 rows here
nRows = size(table2,1);
x = table(nan(nRows,1),'VariableNames',{'x'});
table2 = [table2, x]; % Concatenate
t1compare = table1(:,[1,2]); % Depending on how tables set up,
t2compare = table2(:,[1,2]); % could do this part earlier to save memory

Last, match by indexing of rows in the smaller table:

%% Only 1 set of loops in this case
for iT = 1:nRows
   idx = ismember(t1compare,t2compare(iT,:),'rows');
   table2.x(iT) = sum(table1.GroupCounts(idx));
end

[Zeynab Mousavikhamene]

@Max Murphy

It worked thanks, I just added this "if" after ismember function:

for iT = 1:nRows
   idx = ismember(t1compare,t2compare(iT,:),'rows');
    if any(idx)~=0
         table2.x(iT) = table1.GroupCounts(idx);
    end
end

it still needs to parse all elements of table1 which takes lots of time when there are several tables. ANy idea to get rid of the for loop?