How to count the duplicate id's within a group of a table?

Can a counter be added to a table for individual groups? For example:

• equipment_id, date, group_id, count
• 736694, 5/1/2017, 1, 1
• 736694, 5/1/2016, 1, 2
• 736694, 5/1/2015, 1, 3
• 736694, 5/1/2014, 1, 4
• 736789, 5/1/2016, 2, 1
• 736789, 5/1/2015, 2, 2
• 736789, 5/1/2015, 2, 3
• 736910, 5/1/2017, 3, 1
• 736910, 5/1/2017, 3 , 2

I am trying to view the last date entry for each equipment id.

[MHZ]

Why do you need a counter? Search for the ID, then ask for the biggest date.

[Faustino Quintanilla]

There are other columns, that I did not show. Would this still work? What do you use search ID and biggest date?

Answer 1

[Peter Perkins]

If the table is already sorted by date and ID (use sortrows), then it's straight-forward:

>> t
t =
  9×3 table
    equipment_id       date       group_id
    ____________    __________    ________
       736694       05/01/2017       1    
       736694       05/01/2016       1    
       736694       05/01/2015       1    
       736694       05/01/2014       1    
       736789       05/01/2016       2    
       736789       05/01/2015       2    
       736789       05/01/2015       2    
       736910       05/01/2017       3    
       736910       05/01/2017       3    
>> groupCounts = accumarray(t.group_id,1)
groupCounts =
     4
     3
     2
>> t.counter = repelem((1:3)',groupCounts)
t =
  9×4 table
    equipment_id       date       group_id    counter
    ____________    __________    ________    _______
       736694       05/01/2017       1           1   
       736694       05/01/2016       1           1   
       736694       05/01/2015       1           1   
       736694       05/01/2014       1           1   
       736789       05/01/2016       2           2   
       736789       05/01/2015       2           2   
       736789       05/01/2015       2           2   
       736910       05/01/2017       3           3   
       736910       05/01/2017       3           3

If you don't want to sort the table for some reason, then use rowfun: use group_id as a GroupingVariable, use date as an InputVariable, and make a function that returns a permutation of 1:n, "unsorted" by a column of dates.