Pandas create new column ID based on values from other columns need to be matched

user_10404281 0
Viewed 761

I'm new to programming and Pandas. Therefore, please do not judge strictly and sorry for my explanations.

I have basically two-columns (DM1_ID, DM2_ID) and I need to create a new column('NewID') base on those two columns values. Basically I'm doing is creating a new ID for both columns. Here first evaluate the value in the 1st column and get that value and put it into the 'NewID' column. Also, when we do that, need to consider DM2_ID and when that id comes in DM1_ID I need to give the same DM1_ID in NewID column.

As an example in 0 indexe has DM1_ID 1 and DM2_ID 6, I need to put 1 as NewID for both ids. When DM1_ID comes to 6 (index 15) no matter what in DM2_ID I need to give the 1 as NewID since I gave both DM1_ID 1 and DM1_ID 6. So it will be 1. Also, I need to consider that DM2_ID to latter use and it'll be also 1. (index 15 DM1_ID 6, and DM2_ID 45 since I already gave newId as 1 for both 1 and 6 I have to give 1 for DM1_ID 6. Also for 45, I need to give 1 as a NewID(index 21).)

#I have a large table like this
    DM1_ID  DM2_ID
0   1       6
1   1       7
2   1       15
3   2       5
4   2       10
5   3       21
6   3       28
7   3       32
8   3       35
9   4       39
10  5       2
11  5       10
12  6       1
13  6       7
14  6       15
15  6       45
16  6       55
17  7       1
18  7       6
19  7       15
20  10      75
21  45      120
22  45      10
23  10      27
24  10      28
25  2       335

#I need to create this table

    DM1_ID  DM2_ID  abc
0   1       6        1
1   1       7        1
2   1       15       1
3   2       5        2
4   2       10       2
5   3       21       3
6   3       28       3
7   3       32       3
8   3       35       3
9   4       39       4
10  5       2        2
11  5       10       2
12  6       1        1
13  6       7        1
14  6       15       1
15  6       45       1
16  6       55       1
17  7       1        1
18  7       6        1
19  7       15       1
20  10      75       2
21  45      120      1
22  45      10       2
23  10      27       2
24  10      28       2
25  2       335      2

Any help would be appreciated. Thanks.
pandas-groupby pandas dataframe python
1 Answers

One way to achieve your goal is to persist your IDs first. You can then use this persisted map table/dictionary to assign uniqued IDs once conditions are met. I have included an example with dictionary as below but you can alternatively use a database or a JSON file for persisting your given IDs:

df['pairs'] = df.apply(lambda x: [x[0], x[1]], axis=1)
pairs = df['pairs'].tolist()

u = {}
u_ = {}
for p in pairs:
    if u:
        if not u_:
            u_ = u.copy()
        else:
            u = u_.copy()
           
        for k in list(u.keys()):
            if any(x in u[k] for x in p):
                u_.update(
                    {
                        k: list(set(u[k] + p))
                    }
                )
                
            else:
                pass
            
        vals = [j for i in list(u.values()) for j in i]
        if u == u_ and not any(x in vals for x in p):
            n = max(list(u_.keys())) + 1
            u_[n] = p

        else:
            pass
            
    else:
        u[1] = p
        
u_

Output:

{1: [1, 6, 7, 45, 15, 55, 120],
 2: [75, 2, 10, 5],
 3: [32, 35, 3, 21, 28],
 4: [4, 39]}

Now let's apply a function that assigns new ID per row based on the dictionary we have created in the previous step:

f = lambda x: next(k for k,v in u_.items() if any(i in v for i in x))
df['new_ID'] = df['pairs'].apply(f)
df.drop('pairs', axis=1, inplace=True)
df

Output:

DM1_ID  DM2_ID  new_ID
0   1   6   1
1   1   7   1
2   1   15  1
3   2   5   2
4   2   10  2
5   3   21  3
6   3   28  3
7   3   32  3
8   3   35  3
9   4   39  4
10  5   2   2
11  5   10  2
12  6   1   1
13  6   7   1
14  6   15  1
15  6   45  1
16  6   55  1
17  7   1   1
18  7   6   1
19  7   15  1
20  10  75  2
21  45  120 1
user_111472950
Related