Merged DataFrame by checking if values exists in list of another Data Frame-CodePudding

I have a 2 DF that looks like below:

DF1

time     id
15-06    1
16-06    3
17-06    5
18-06    7

DF2:

result  idx
'asd'   [18,3]
'qwe'   [20,5]
'zxc'   [7]
'ty'    [987,9]
'qwe'   [1000]

Output should be like this:

time     id  result idx
15-06    1   NaN    NaN
16-06    3   'asd'  [18,3]
17-06    5   'qwe'  [20,5]
18-06    7   'zxc'  [7]

So I tried to make a left merge like below but it doesn't take into account values in list

df1.merge(df2, how="left", left_on = "id", right_on="idx")

Then I try to assigned data but there was an issue with lenngth of table (there were not the same)

df3 = df.assign(id=[[s.get(y) for y in x if y in s] for x in df2['idx']])

Do you have any idea how I can solve it? Thanks for help

Regards Tomasz

CodePudding user response：

Use DataFrame.explode with new column id by column idx with left join:

df = DF1.merge(DF2.assign(id = DF2.idx).explode('id'), how="left", on = "id")
print (df)
    time id result      idx
0  15-06  1    NaN      NaN
1  16-06  3  'asd'  [18, 3]
2  17-06  5  'qwe'  [20, 5]
3  18-06  7  'zxc'      [7]