I have the below DF that contains
| Lookup | Definition |
|---|---|
| A | Apple |
| B | Banana |
| C | Carrot |
I have another DF2:
| SNo | Lookup Values |
|---|---|
| 1 | ['A', 'B'] |
| 2 | ['A', 'C'] |
| 3 | ['B', 'C'] |
Note: "Lookup Values" column is a list of strings
What is the most simple way of performing the JOIN to get the below:
| SNo | Lookup Values | Lookup Definitions |
|---|---|---|
| 1 | A, B | Apple, Banana |
| 2 | A, C | Apple, Carrot |
| 3 | B, C | Banana, Carrot |
>Solution :
Try explode, merge, then groupby:
(df2.explode('Lookup Values')
.merge(df1, left_on='Lookup Values', right_on='Lookup', how='left')
.groupby('SNo', as_index=False)
.agg({'Lookup':','.join, 'Definition':','.join})
)
Output:
SNo Lookup Definition
0 1 A,B Apple,Banana
1 2 B,C Banana,Carrot
2 3 C,A Carrot,Apple