grouping elements from a table
34 views (last 30 days)
Show older comments
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
0 Comments
Accepted Answer
Akira Agata
on 8 Sep 2017
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);
3 Comments
Walter Roberson
on 18 Sep 2017
>> findgroups([1 2.5 2 2.5 1])
ans =
1 3 2 3 1
Looks like it works for double to me.
More Answers (1)
Guillaume
on 8 Sep 2017
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
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.
0 Comments
See Also
Categories
Find more on Characters and Strings in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!