MATLAB Answers

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

1 view (last 30 days)
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.

Accepted Answer

Guillaume
Guillaume on 25 Nov 2019
Edited: Guillaume on 25 Nov 2019
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.

  5 Comments

Show 2 older comments
Zeynab Mousavikhamene
Zeynab Mousavikhamene on 26 Nov 2019
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
Guillaume on 26 Nov 2019
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.

Sign in to comment.

More Answers (1)

Max Murphy
Max Murphy on 24 Nov 2019
Edited: Max Murphy on 24 Nov 2019
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

  5 Comments

Show 2 older comments
Zeynab Mousavikhamene
Zeynab Mousavikhamene on 24 Nov 2019
Thanks for your time. That question is resolved and it is not related to my question here.
Max Murphy
Max Murphy on 24 Nov 2019
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
Zeynab Mousavikhamene on 25 Nov 2019
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?

Sign in to comment.

Sign in to answer this question.

Tags