numpy python: Find the highest value from a column for each unique value in another column

Question

Can somebody suggest an efficient method for getting the highest value in one column for each unique value in another

the np.array looks like this [column0,column1,column2,column3]

[[ 37367    421    231385     93]
 [ 37368    428    235156     93]
 [ 37369    408    234251     93]
 [ 37372    403    196292     93]
 [ 55523    400    247141    139]
 [ 55575    415    215818    139]
 [ 55576    402    204404    139]
 [ 69940    402    62244     175]
 [ 69941    402    38274     175]
 [ 69942    404    55171     175]
 [ 69943    416    55495     175]
 [ 69944    407    90231     175]
 [ 69945    411    75382     175]
 [ 69948    405    119129    175]] 

where i want to return the highest value of column 1 based on the unique value of column 3. After the new array should look like this:

[[ 37368    428   235156     93]
 [ 55575    415   215818    139]
 [ 69943    416    55495    175]] 

I know how to do this by looping but that is not what i am looking after because the table i am working in is quite big and i want to avoid looping

Answer 1

Here's one approach -

# Lex-sort combining cols-1,3 with col-3 setting the primary order
sidx = np.lexsort(a[:,[1,3]].T)

# Indices at intervals change for column-3. These would essentially 
# tell us the last indices for each group in a lex-sorted array
idx = np.append(np.flatnonzero(a[1:,3] > a[:-1,3]), a.shape[0]-1)    

# Finally, index into idx with lex-sorted indices to give us 
# the last indices in a lex-sorted version, which is equivalent 
# of picking up the highest of each group
out = a[sidx[idx]]

Sample run -

In [234]: a  # Input array
Out[234]: 
array([[ 25,  29,  19,  93],
       [ 27,  59,  14,  93],
       [ 24,  46,  15,  93],
       [ 79,  87,  50, 139],
       [ 13,  86,  32, 139],
       [ 56,  25,  85, 142],
       [ 62,  62,  68, 142],
       [ 27,  25,  20, 150],
       [ 29,  53,  71, 150],
       [ 64,  67,  21, 150],
       [ 96,  57,  73, 150]])

In [235]: out    # Output array
Out[235]: 
array([[ 27,  59,  14,  93],
       [ 79,  87,  50, 139],
       [ 62,  62,  68, 142],
       [ 64,  67,  21, 150]])

Performance boost with views

We can slice with a[:,1::2] instead of a[:,[1,3]] to use the same memory space and thus hopefully bring out performance improvement too. Let's verify the memory view -

In [240]: np.may_share_memory(a,a[:,[1,3]])
Out[240]: False

In [241]: np.may_share_memory(a,a[:,1::2])
Out[241]: True