Merge multiple group ids to form a single consolidated group id?

Question

I have following dataset in pandas Dataframe.

group_id sub_group_id
0           0  
0           1
1           0
2           0
2           1
2           2
3           0       
3           0

But the I want to those group ids and form a consolidated group id

group_id sub_group_id consolidated_group_id
0            0                    0
0            1                    1
1            0                    2
2            0                    3 
2            1                    4
2            2                    5
2            2                    5
3            0                    6
3            0                    6

Is there any generic or mathematical way to do it?

score 1 · Answer 1 · edited May 23 '17 at 11:54

You need convert values to tuples and then use factorize:

df['consolidated_group_id'] = pd.factorize(df.apply(tuple,axis=1))[0]
print (df)

   group_id  sub_group_id  consolidated_group_id
0         0             0                      0
1         0             1                      1
2         1             0                      2
3         2             0                      3
4         2             1                      4
5         2             2                      5
6         3             0                      6
7         3             0                      6

Numpy solutions are a bit modify this answer - change ordering by [::-1] with selecting by [0] for return array (numpy.unique):

a = df.values

def unique_return_inverse_2D(a): # a is array
    a1D = a.dot(np.append((a.max(0)+1)[:0:-1].cumprod()[::-1],1))
    return np.unique(a1D, return_inverse=1)[::-1][0]


def unique_return_inverse_2D_viewbased(a): # a is array
    a = np.ascontiguousarray(a)
    void_dt = np.dtype((np.void, a.dtype.itemsize * np.prod(a.shape[1:])))
    return np.unique(a.view(void_dt).ravel(), return_inverse=1)[::-1][0]

df['consolidated_group_id'] = unique_return_inverse_2D(a)
df['consolidated_group_id1'] = unique_return_inverse_2D_viewbased(a)
print (df)
   group_id  sub_group_id  consolidated_group_id  consolidated_group_id1
0         0             0                      0                       0
1         0             1                      1                       1
2         1             0                      2                       2
3         2             0                      3                       3
4         2             1                      4                       4
5         2             2                      5                       5
6         3             0                      6                       6
7         3             0                      6                       6

score 1 · Answer 2 · answered Apr 03 '17 at 08:22

cols = ['group_id', 'sub_group_id']
df.assign(
    consolidated_group_id=pd.factorize(
        pd.Series(list(zip(*df[cols].values.T.tolist())))
    )[0]
)

   group_id  sub_group_id  consolidated_group_id
0         0             0                      0
1         0             1                      1
2         1             0                      2
3         2             0                      3
4         2             1                      4
5         2             2                      5
6         3             0                      6
7         3             0                      6

Merge multiple group ids to form a single consolidated group id?

2 Answers2