numpy python: Find the highest value from a column

问题:

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

回答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