How can I assign values to min/max of a column within each value of another column?

I have a table for which I need to programmatically identify and assign values (column 4) to the max and min Time values (column 2), for each data acquisition Device (column 1).

The earliest/minimum time for device 52 for example, is in row 1 at 8:28; that device's corresponding row at 10:47 (row 2) is the latest/maximum time. Row 1 is then identified as "Baseline" and row 2 as "Peak".

Columns 1-3 are given. The rows of the table are not necessarily ordered. I've manually filled in column 4 in the example below. There are 2 times for each device in the table.

Device    time     current    Identifier
52       08:28    1.04       "Baseline"  
52       10:47    2.02       "Peak"  
42       08:23    1.03       "Baseline"  
10       08:29    1.01       "Baseline"  
10       10:45    2.11       "Peak"  
42       10:41    2.05       "Peak"  

Thanks in advance for the help!

[A Mackay]

In other words, for each value of Column 1:

• identify the row with the min value in column 2 as "Baseline", storing that value in Column 4
• similarly, identify the max value in column 2 as "Peak".

[Matt J]

What about rows that are neither Peak nor Baseline?

[A Mackay]

No need to label those rows that are neither Peak nor Baseline. Leave the column 4 blank

Answer 1

[Matt J]

Let's call your table T, then,

T=table(randi(2,10,1), randi(100,10,1), rand(10,1),'V',{'Device','Time','current'})


str=["";"Peak";"Baseline"]; %string lookup
G=findgroups(T{:,1}); %assign group labels to column 1
I=(1:numel(G))'; %enumerate rows

fun=@(t,i)deal( {(t==max(t))+2*(t==min(t))+1},{i}); 
   %gives numeric label to every member of a fixed group: 1=non-extreme, 2=max. , 3=min.

[s,idx]=splitapply(fun ,T{:,2} ,I  ,G); %apply labelling fun to each group

newcol(cell2mat(idx))=str(cell2mat(s));%Uses numeric labels to lookup string labels.
                                       %Also restores list to its original
                                       %order.


Tnew=[T,table(newcol(:),'V',"Identifier")] %append new column

Tnew = 10×4 table
    Device    Time    current     Identifier
    ______    ____    ________    __________

      1        89      0.31783    "Peak"    
      1         9      0.38992    ""        
      1        67      0.71375    ""        
      1        86      0.12292    ""        
      2        92      0.56198    "Peak"    
      1        89      0.45877    "Peak"    
      1        42      0.87733    ""        
      2        59     0.097834    "Baseline"
      2        82      0.13001    ""        
      1         4      0.95036    "Baseline"

[A Mackay]

This is great! Thank you so much. I'm pretty new to MATLAB - would you mind commenting on the steps?

[Matt J]

I've added some comments.

[A Mackay]

Sweet. Very impressive. Thanks again!