grouping elements from a table

Hi everyone:

i have a table like this:

Week     Open       High       Low     Close
----     ----       ----       ---     -----
10       1.2        1.4        1.1     1.2
10       1.5        1.7        1.5     1.6
10       1.4        2.1        1.3     2
10       2.2        2.4        2       2.1
11       2          2.1        2       2
11       2.2        2.5        1.1     1.3
12       1.7        1.9        1.6     1.6
12       1.8        1.9        0.6     1.4
12       0.9        1.4        0.8     1

is there a way to get the first element(open), maximum element(high), minimum element(low) and last element (close) for each week? so the result table would be

Week     Open       High       Low     Close
----     ----       ----       ---     -----
10       1.2        2.4        1.1     2.1
11       2          2.5        1.1     1.3
12       1.7        1.9        0.6     1

thanks in advance

Answer 1

[Akira Agata]

Assuming your table is T, the following code can generate what you want.

[group, id] = findgroups(T.Week);
func = @(p, q, r, s) [p(1), max(q), min(r), s(end)];
result = splitapply(func, T.Open, T.High, T.Low, T.Close, group)
Tout = array2table([id, result],...
  'VariableNames', T.Properties.VariableNames);

[eric capnu]

T.Week is double, findgroups fails for double input arguments. Any idea?

[Walter Roberson]

>> findgroups([1 2.5 2 2.5 1])
ans =
     1     3     2     3     1

Looks like it works for double to me.

[eric capnu]

my mistake, i was working on a 2014 matlab version

Answer 2

[Guillaume]

Step 1: define a function in its own m file that applies the appropriate filter to each column (unfortunately, you can't use anonymous functions for this):

function [firstopen, maxhigh, minlow, lastclose] = filtercols(open, high, low, close)
   firstopen = open(1);  %first element
   maxhigh = max(high);
   minlow = min(low);
   lastclose = close(end);  %last element
end

Step 2: use that function with rowfun

t = array2table([
10       1.2        1.4        1.1     1.2
10       1.5        1.7        1.5     1.6
10       1.4        2.1        1.3     2
10       2.2        2.4        2       2.1
11       2          2.1        2       2
11       2.2        2.5        1.1     1.3
12       1.7        1.9        1.6     1.6
12       1.8        1.9        0.6     1.4
12       0.9        1.4        0.8     1], ...
'VariableNames', {'Week', 'Open', 'High', 'Low', 'Close'});

 result = rowfun(@filtercols, t, 'GroupingVariable', 'Week', 'NumOutputs', 4, 'OutputVariableNames', {'Open', 'High', 'Low', 'Close'})

The only potential issue is that the order in which the rows are grouped together by rowfun is not documented, so it may be that the first element and last element are not the correct ones. In my version of matlab (R2017a), it is correct.