Aggegate min max values in one column based on another column of array - Python

Question

I am trying to concatenate the values if they have the same indices. I am working with rectangular shape so I know:

• There will always at least 2 of the same indices.
• If there are more than 2 indices, I just need to store the maxs and mins.

Basically,

From:

a = array([
       [ 1,  5],
       [ 1,  7],
       [ 2,  8],
       [ 2, 10],
       [ 2, 22],
       [ 3, 55],
       [ 3, 77]])

To:

b = np.array([
       [ 1, 5, 7],
       [ 2, 8, 22], # [2,8,10,22] but the min is 8 and max is 22
       [ 3, 55, 77]])

I have tried to convert it to a list and going through each value using a for loop but it takes a considerable amount of time.

I've also tried sorting the array, np.sort(a, axis=0) and taking every other row, but since there can be more than two of the indices, it fails.

I am new to numpy, so don't know what else to try.

Any and all suggestion would be helpful, Thank You.

Edit: Its behavior is like a dictionary where the keys are a[0] and values are a[1:]

If there are more than 2 values, I only keep the min and max.

Answer 1

A way to do this using pandas

import pandas as pd
# create a dataframe with 2 columns corresponding to the columns of a
df = pd.DataFrame({ 'indices':a[:,0],'values':a[:,1]}) 
# compute min and max by indices
df2 = df.groupby('indices').agg({'values': ['min', 'max']}).reset_index()
# convert to numpy array
np.asarray(df2)
#array([[ 1,  5,  7],
#       [ 2,  8, 22],
#       [ 3, 55, 77]], dtype=int64)

Answer 2

A way of doing it with numpy, You can use numpy.split for splitting them into separete arrays, based on the value in the first axis. Then you can find the min and max.

For more information on splitting, and how it works, you can look at the answer here. I am not repeating the same here.

ar = np.split(a, np.flatnonzero(a[1:,0] != a[:-1,0])+1,axis=0)

The above line splits and produces a list of arrays for each unique value in axis 0.

The above line will produce an output like,

[
array([[1, 5],
       [1, 7]]),
array([[ 2,  8],
       [ 2, 10],
       [ 2, 22]]), 
array([[ 3, 55],
       [ 3, 77]])
]

Then you can iterate them to find the nature of the list you are expecting in your output.

final_list = []
for i in ar:
  final_list.append([i[1][0],np.min(i[:,1]),np.max(i[:,1])])
print(final_list)

The above code will produce the output like

[[1, 5, 7], [2, 8, 22], [3, 55, 77]]

Answer 3

Approach #1

A vectorized NumPy way would be -

def agg_minmax(a):
    sidx = np.lexsort(a[:,::-1].T)
    b = a[sidx]
    m = np.r_[True,b[:-1,0]!=b[1:,0],True]
    return np.c_[b[m[:-1],:2], b[m[1:],1]]

Sample run -

# Generic case with input not-necessarily sorted by first col
In [35]: a
Out[35]: 
array([[ 3, 77],
       [ 2,  8],
       [ 1,  7],
       [ 2, 10],
       [ 1,  5],
       [ 3, 55],
       [ 2, 22]])

In [36]: agg_minmax(a)
Out[36]: 
array([[ 1,  5,  7],
       [ 2,  8, 22],
       [ 3, 55, 77]])

Approach #2

We can improve on memory to sort only the first row by sidx, like so -

def agg_minmax_v2(a):
    sidx = np.lexsort(a[:,::-1].T)
    b = a[sidx,0]
    m = np.r_[True,b[:-1]!=b[1:],True]
    return np.c_[a[sidx[m[:-1]]],a[sidx[m[1:]],1]]

This could be better with a lot of entries per group.

---

Alternative #1 : Get sidx using linear-index-mapping

For positive int numbers, we can assume them to be on 2D grid and hence get linear index equivalents for each row. Thus, we will skip lexsort and get sidx like so -

sidx = (a[:,0]*(a[:,1].max()+1) + a[:,1]).argsort()

Rest of the code after getting sidx stays the same in both of the earlier posted approaches.

Alternative #2 : Get sidx using views

We could use views to get sidx and hence again skip lexsort, like so -

# https://stackoverflow.com/a/44999009/ @Divakar
def view1D(a): # a is array
    a = np.ascontiguousarray(a)
    void_dt = np.dtype((np.void, a.dtype.itemsize * a.shape[1]))
    return a.view(void_dt).ravel()

A = view1D(a)
sidx = A.argsort()

Answer 4

One way of doing this (not so nicely) is by using normal lists.

# convert to list and sort if not already sorted
alist = a.tolist()
alist.sort()

# initial values for looping
currval = alist[0][0]
min     = alist[0][1]
max     = alist[0][1]

# new list to store results in
result = []

# loop through all rows of alist
for row in alist:
    if currval == row[0]: # still same index
        max = row[1]   # update max
    else:
        result.append([currval, min, max]) # save row
        currval = row[0] # update to next index
        min     = row[1]
        max     = row[1]

# save last row
result.append([currval, min, max]) # save row
currval = row[0] # update to next index
min     = row[1]
max     = row[1]

# convert output to nparray
b = np.array(result)

It makes use of Python's sort behaviour on lists which orders the lists nicely by grouping those with the same index and having the values in increasing order.