I have the input in the following format. I wanted to assign a group number to mapped Col A to ColB. For example if you consider 222 in COL A, which is pointing to 111 and 112 in Col B, I want all the three numbers to be assigned to same group number. Please help me with this. Thanks in advance
I solved it with Sql Server, but I looked at Spark to make sure they have the same functions so it should work for you as is.
with t2 as (
select colA, colB
from t
union all
select distinct colA, colA
from t
)
select dense_rank() over(order by colA) as grpnumber
,colB as ID
from t2
| grpnumber | ID |
|---|---|
| 1 | 111 |
| 1 | 112 |
| 1 | 222 |
| 2 | 114 |
| 2 | 115 |
| 2 | 333 |
You can use factorize to create the group and melt to reshape:
out = (df
.assign(grpnumber=pd.factorize(df['colA'])[0]+1)
.melt('grpnumber', value_name='ID')
.drop_duplicates()
.sort_values(by='grpnumber', ignore_index=True)
.drop(columns='variable')
)
Output:
grpnumber ID
0 1 222
1 1 111
2 1 112
3 2 333
4 2 114
5 2 115
Check Below code using PANDAS
import pandas as pd
def list_series(val):
return list(val)
df = df.groupby(['COL A']).agg(GRP_COL_B=('COL B',list_series)).reset_index()
df['ID'] = df.apply(lambda x: x['GRP_COL_B']+[x['COL A']], axis=1)
df['grpnumber'] = df.index+1
df[['ID','grpnumber']].explode('ID')
Output:
