In a cell how do I extract a specific range of values from a few columns that have a certain value in another column?

I'm quite new to MATLAB so apologies if my question is a bit confusing.

I have a cell with subject names in the first column, time points in the second column and experiment values in the third column:

Bob 1 100

Bob 3 200

Bob 4 500

Bill 1 200

Bill 2 300

Bill 5 600

I want to create a struct with 2 fields using this cell; the first with the subjects name and the second field with a matrix that has the time and experiment values for that subject. For example, for the above cell I want it to make a 1x2 struct with 2 fields with the first entry being:

Name: 'Bob'

Values: [1,100 ; 3,200 ; 4,500]

And the seecond entry in the struct being:

Name: 'Bill'

Values: [1,200 ; 2,300 ; 5,600]

Answer 1

[Stephen23]

This method will correctly preserve the order of the input data:

C = {...
'Bob',1,100;
'Bob',3,200;
'Bob',4,500;
'Bill',1,200;
'Bill',2,300;
'Bill',5,600;
'Bob',8,1000;
'Anna',2,900;
'Anna',9,000;
};
[uni,idx,idy] = unique(C(:,1),'stable');
mat = cell2mat(C(:,2:3));
[idy,idz] = sort(idy);
tmp = accumarray(idy,idz,[],@(r){mat(r,:)});
S = struct('name',uni,'values',tmp)

And checking:

for k = 1:numel(S) S(k).name S(k).values end

prints:

ans =
Bob
ans =
           1         100
           3         200
           4         500
           8        1000
ans =
Bill
ans =
     1   200
     2   300
     5   600
ans =
Anna
ans =
     2   900
     9     0

[mdon]

This works great! Thank you Stephen.

Answer 2

[Guillaume]

If I understood correctly:

c ={'Bob', 1, 100; 'Bob', 3, 200; 'Bob', 4, 500; 'Bill', 1, 200; 'Bill', 2, 300; 'Bill', 5 , 600};
[names, ~, rows] = unique(c(:, 1));
allvalues = cell2mat(c(:, 2:end));

%%option 1: group using splitapply, R2015b or later
values = splitapply(@(row) {row}, allvalues, rows);

%%option 2: group using accumarray, any version
values = accumarray(rows, (1:size(c, 1))', [], @(row) {allvalues(row, :)});

s = struct('Names', names, 'Values', values)

The splitapply (requires R2015b or later) syntax is simpler as you need to subvert accumarray to make it work on several columns at once. On the other hand accumarray is probably faster as it's a compiled function whereas splitapply is a just a plain m file.

[Stephen23]

Note that the accumarray method gives a different order to that in the question:

c ={'Bob', 1, 100; 'Bob', 3, 200; 'Bob', 4, 500; 'Bill', 1, 200; 'Bill', 2, 300; 'Bill', 5 , 600};
[names, ~, rows] = unique(c(:, 1));
allvalues = cell2mat(c(:, 2:end));
values = accumarray(rows, (1:size(c, 1))', [], @(row) {allvalues(row, :)});
S = struct('Name', names, 'Values', values);

and the output is:

>> S(1).Name
ans =
Bill
>> S(1).Values
ans =
     2   300
     1   200
     5   600

which not the same order as the question requested, which was:

>> Values = [1,200 ; 2,300 ; 5,600]
Values =
     1   200
     2   300
     5   600

See my answer for a method that correctly preserves the input order.

[Guillaume]

Yes, accumarray does not preserve the order. In my limited testing, splitapply appears to, but it's not guaranteed by the function.

However, I assumed that the order of the entries did not matter (why should they?). If an ordering does matter, why should it be the order of the original cell array instead of a time ordering? The latter can be simply achieved with:

%...
values = splitapply(@(row) {row}, allvalues, rows); %or using accumarray
values = cellfun(@sortrows, values, 'UniformOutput', false);
%... construct struct as normal

[mdon]

Thank you for the responses Guillaume and Stephen! Apologies for not being as clear in my question. Yes, I was hoping to have the order preserved and I unfortunately don't have MATLAB R2015b.

Some of the entries are actually not in a normal time sequence order (ie. it isn't always increasing) and thus I don't think a sorting would work for me. Thank you though!

Answer 3

[Thorsten]

You can also achieve this without accumarray or splitat, using a for-loop.

 c ={'Bob', 1, 100; 'Bob', 3, 200; 'Bob', 4, 500; 'Bill', 1, 200; 'Bill', 2, 300; 'Bill', 5 , 600};
 [names, ~, rows] = unique(c(:, 1));
 allvalues = cell2mat(c(:, 2:end));

 for i = 1:numel(names)
   S(i).name = names{i}; 
   S(i).values = allvalues(strcmp(c(:,1), names{i}), :); 
 end

[mdon]

Thank you Thorsten, this also works well!

Answer 4

[Bekay.Kang]

hello mdon.

hope this will be helpful for you !

struct_you_want=struct('Name',cell_data(:,1),'Value',cell2mat(cell_data(:,2:3)));

:)

[Guillaume]

I believe mdon wants all entries with the same name to be regrouped. The above won't do that.

[mdon]

Thank you for the answer Bekay. Yes, I was trying to keep the same name entries in a group. Sorry for the confusion!