How to choose some rows of a table by order?

Hi,

I have a table with many rows but only 3 columns: dates, items, costs. For each date, there are many items and costs. I can use sortrows to sort the table by dates and costs; then I need to pick the top 3 costs from each day. I can loop through each day to get the top 3 costs. Is there a better simple way to do that?

Thanks a lot!

[dpb]

accumarray, maybe...

Answer 1

[Peter Perkins]

It sounds like a grouped varfun is what you're looking for:

>> item = randi([1 3],10,1);
>> date = datetime(2016,12,randi([1 3],10,1));
>> cost = round(10*rand(10,1),2);
>> t = table(item,date,cost)
t =
    item       date        cost
    ____    ___________    ____
    3       03-Dec-2016    3.52
    1       01-Dec-2016    8.31
    2       03-Dec-2016    5.85
    3       01-Dec-2016     5.5
    3       03-Dec-2016    9.17
    3       02-Dec-2016    2.86
    2       01-Dec-2016    7.57
    1       01-Dec-2016    7.54
    1       02-Dec-2016     3.8
    1       02-Dec-2016    5.68
>> varfun(@topThree,t,'GroupingVariable','date','InputVariable','cost')
ans =
       date        GroupCount    topThree_cost
    ___________    __________    _____________
    01-Dec-2016    4             8.31         
    01-Dec-2016    4              5.5         
    01-Dec-2016    4             7.57         
    02-Dec-2016    3             2.86         
    02-Dec-2016    3              3.8         
    02-Dec-2016    3             5.68         
    03-Dec-2016    3             3.52         
    03-Dec-2016    3             5.85         
    03-Dec-2016    3             9.17

where topThree looks like this:

function y = topThree(x)
xs = sort(x,'descend');
y = x(1:min(3,end));

Hope this helps.

[dpb]

That's a nice functionality and great example, Peter... :)

[JFz]

Thank you so much! This is very good.

Answer 2

[David Barry]

You could use the findgroups and splitapply pattern. Findgroups to get the date groups and then splitapply to find the first 3 costs in each group. Take a look at the doc links above for worked examples of this kind of pattern.

[JFz]

Thanks! Let me take a look at the docs first.

[David Barry]

Did this help?