Index of Max Value from by grouping using varfun

Question

I have a table with Ids and Dates. I would like to retrieve the index of the max date for each Id.

My initial approach is so: varfun(@max, table, 'Grouping Variables', 'Id', 'InputVariables','Date');

This obviously gives me the date rather than the index. I noted that the max function will return both the maxvalue and maxindex when specified: [max_val, max_idx] = max(values);

How can I define an anonymous function using max to retrieve max_idx? I would then use it in the var_fun to get my result.

I'd prefer not to declare a cover function (as opposed to a anon func)over max() as: 1. I'm working in a script and would rather not create another function file 2. I'm unwilling to change my current script to a function

Thanks a million guys,

Answer 1

I'm assuming your Ids are positive integers and and your Dates are numbers.

If you wanted the maximum Date for each Id, it would be a perfect case for accumarray with the max function. In the following I'll use f to denote a generic function passed to accumarray.

The fact that you want the index of the maximum makes it a little trickier (and more interesting!). The problem is that the Dates corresponding to a given Id are passed to f without any reference to their original index. Therefore, an f based on max can't help. But you can make the indices "pass through" accumarray as imaginary parts of the Dates.

So: if you want just one maximizing index (even if there are several) for each Id:

result = accumarray(t.Id,...  %// col vector of Id's
    t.Date+1j*(1:size(t,1)).', ... %'// col vector of Dates (real) and indices (imag)
    [], ... %// default size for output
    @(x) imag(x(find(real(x)==max(real(x))),1))); %// function f

Note that the function f here maximizes the real part and then extracts the imaginary part, which contains the original index.

Or, if you want all maximizing indices for each Id:

result = accumarray(t.Id,...  %// col vector of Id's
    t.Date+1j*(1:size(t,1)).', ... %'// col vector of Dates (real) and indices (imag)
    [], ... %// default size for output
    @(x) {imag(x(find(real(x)==max(real(x)))))}); %// function f

If your Ids are strings: transform them into numeric labels using the third output of unique, and then proceed as above:

[~, ~, NumId] = unique(t.Id);

and then either

result = accumarray(NumId,...  %// col vector of Id's
    t.Date+1j*(1:size(t,1)).', ... %'// col vector of Dates (real) and indices (imag)
    [], ... %// default size for output
    @(x) imag(x(find(real(x)==max(real(x))),1))); % function f

or

result = accumarray(NumId,...  %// col vector of Id's
    t.Date+1j*(1:size(t,1)).', ... %'// col vector of Dates (real) and indices (imag)
    [], ... %// default size for output
    @(x) {imag(x(find(real(x)==max(real(x)))))}); %// function f

Answer 2

I don't think varfun is the right approach here, as

varfun(func,A) applies the function func separately to each variable of the table A.

This would only make sense if you wanted to apply it to multiple columns.

Simple approach:

Simply go with the loop approach: First find the different IDs using unique, then for each ID find the indices of the maximum dates. (This assumes your dates are in a numerical format which can be compared directly using max.) I did rename your variable table to t, as otherwise we would be overwriting the built-in function table.

uniqueIds = unique(t.Id);
for i = 1:numel(uniqueIds)
    equalsCurrentId = t.Id==uniqueIds(i); 
    globalIdxs = find(equalsCurrentId);
    [~, localIdxsOfMax] = max(t.Date(equalsCurrentId));
    maxIdxs{i} = globalIdxs(localIdxsOfMax);
end

As you mentioned your Ids are actually strings instead of numbers, you will have to change the line: equalsCurrentId = t.Id==uniqueIds(i); to

 equalsCurrentId = strcmp(t.Id, uniqueIds{i});

Approach using accumarray:

If you prefer a more compact style, you could use this solution inspired by Luis Mendo's answer, which should work for both numerical and string Ids:

[uniqueIds, ~, global2Unique] = unique(t.Id);
maxDateIdxsOfIdxSubset = @(I) {I(nth_output(2, @max, t.Date(I)))};
maxIdxs = accumarray(global2Unique, 1:length(t.Id), [], maxDateIdxsOfIdxSubset);

This uses nth_output of gnovice's great answer.

Usage:

Both above solutions will yield: A vector uniqueIds with a corresponding cell-array maxIdxs, in a way that maxIdxs{i} are the indices of the maximum dates of uniqueIds(i). If you only want a single index, even though there are multiple entries where the maximum is attained, use the following to strip away the unwanted data:

maxIdxs = cellfun(@(X) X(1), maxIdxs);