I am not quite sure how to explain the desired steps, but the result I know. I have two data frames;

df1 is a data frame with two columns one is Unique IDs the other is unique descriptions 24 rows in total

the table goes on

The next data frame is also two columns, the first being different descriptions that need to which the descriptions of df1 need to be matched. The next column consists of the matching IDs from df1 this column has non-unique values and also tuples, 190 rows in total.

the desired result would be df3 as follows and would also have 190 rows.

CodePudding user response：

Use merge and explode

import ast
df2.assign(match=df2['match'].apply(ast.literal_eval))\
   .explode('match', ignore_index=True)\
   .merge(d, how='left', left_on= 'match', right_on='id')\
   .groupby('description_2').agg({'match': tuple, 'description_1': ',  '.join})

                   match             description_1                                   
desc aa             (2,)                    desc c
desc bb             (3,)                    desc d
desc cc             (0,)                    desc a
desc dd        (1, 4, 5)  desc b,  desc e,  desc f
desc ee             (1,)                    desc b
desc ff        (0, 3, 5)  desc a,  desc d,  desc f

The ast.literal_eval step is to ensure you have tuples in your cells in your match column. This enabled explode to be used.