How to do a group by in matlab

Hi, I have the following data:

data=[10 1 2 3; 11 4 5 6;10 0 20 30; 11 4 5 6; 12 7 8 9; 17 40 50 60]

I want to look for:

lookfor=[10;11]

and get the following result:

anwser=[10 1 22 33; 11 8 10 12]

So it's a group by...

I'm looking for a dynamic anwser, data matrix and lookfor matrix will vary and be much more bigger.

thank you in advance for your precious anwsers.

[Azzi Abdelmalek]

It's grouped by what? how did you get 1,22 and 33?

[Gimpy]

I'm looking for the data associate with 10 and 11 lookfor=[10;11]

The element associate with the look for a group sum except the data I'm looking for:

lookfor=[10;11]

1 2 3 + 0 20 30; this is for the query on value 10

for 11

4 5 6; + 4 5 6 final result:

lookfor=[10;17]

anwser=[10 1 22 33; 11 8 10 12]

Answer 1

[Azzi Abdelmalek]

data=[10 1 2 3; 11 4 5 6;10 0 20 30; 11 4 5 6; 12 7 8 9; 17 40 50 60]
lookfor=[10;11];
a=arrayfun(@(x) data(find(data(:,1)==x),:),lookfor,'un',0);
b=cell2mat(cellfun(@(x) [x(1) sum(x(:,2:end),1)],a,'un',0))

%or

data=[10 1 2 3; 11 4 5 6;10 0 20 30; 11 4 5 6; 12 7 8 9; 17 40 50 60]
lookfor=[10;11]
for k=1:numel(lookfor)
  ii=data(ismember(data(:,1),lookfor(k)),:);
  res(k,:)=[ii(1,1) sum(ii(:,2:end))];
end
res

[Gimpy]

looks goog but what part of the code do I need to change if I have more than 2 lookfor? (in the second solution)

[Azzi Abdelmalek]

nothing to change

[Gimpy]

Let's take the following example

data=[10 1 2 3; 11 4 5 6;10 0 20 30; 11 4 5 6; 12 7 8 9; 17 40 50 60; 22 40 50 60;22 40 50 60]
lookfor=[10;11;22]

for k=1:numel(lookfor)
ii=data(ismember(data(:,1),lookfor(k)),:);
res(k,:)=[ii(1,1) sum(ii(:,2:end))];
end
res

%it's working

[Gimpy]

but the following example:

data=[10 1 2 3; 11 4 5 6;10 0 20 30; 11 4 5 6; 12 7 8 9; 17 40 50 60; 22 40 50 60;22 40 50 60]
lookfor=[10;17;22]

for k=1:numel(lookfor)
ii=data(ismember(data(:,1),lookfor(k)),:);
res(k,:)=[ii(1,1) sum(ii(:,2:end))];
end
res

%I only change the lookfor 11 for 17 and it's not working. The reason seems to be that there only one row for the number 17 in the matrix compare to 2 for the rest of the lookfor. In the code I'm trying to do all element will have a different number of rows...

[Gimpy]

Any suggestion?

[Azzi Abdelmalek]

Exact,I've fixed it:

for k=1:numel(lookfor)
   ii=data(ismember(data(:,1),lookfor(k)),:);
   res(k,:)=[ii(1,1) sum(ii(:,2:end),1)];
end
res

[Gimpy]

looks goog but if I ask the following calculation:

data=[10 1 2 3; 11 4 5 6;10 0 20 30; 11 4 5 6; 12 7 8 9; 17 40 50 60; 22 40 50 60;22 40 50 60; 22 40 50 60;22 40 50 60; 22 40 50 60;22 40 50 60; 22 40 50 60;22 40 50 60; 22 40 50 60;22 40 50 60]
lookfor=[10;11]

for k=1:numel(lookfor)
 ii=data(ismember(data(:,1),lookfor(k)),:);
 res(k,:)=[ii(1,1) sum(ii(:,2:end),1)];
end
res

%it's give a result for 22 even if I don't ask it

[Azzi Abdelmalek]

You should clear the variable res

clear res

% or better, pre-allocate

res=zeros(numel(lookfor),size(data,2))

[Gimpy]

wonderful thanks a lot ! merci

Answer 2

[Andrei Bobrov]

[i1,i2] = ismember(data(:,1),lookfor);
d2 = data(i1,2:end);
[j1,j2] = ndgrid(i2(i1),1:size(d2,2));
anwser = [lookfor,accumarray([j1(:),j2(:)],d2(:))];

Answer 3

[Lola Davidson]

For those still stumbling on this, MATLAB now has several more functions to help with grouping workflows, including groupsummary and pivot.

For this problem, if you are expecting several different lookfor values on the same dataset, it may be faster to compute all the sums with groupsummary in one go:

[sums,grps] = groupsummary(data(:,2:end),data(:,1),"sum");

out = [grps sums]

On the other hand, if you only want to compute a small subset of the grouped sums per dataset, it may be quicker to filter down with ismember first, as others have mentioned.

idx = ismember(data(:,1),lookfor);

[sums,grps] = groupsummary(data(idx,2:end),data(idx,1),"sum");

out = [grps sums]