Python Forum

Full Version: Join each list elements with string in other DF
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
HI All,

I've sample of 2 DF's as below and want to join/vlookup for each elements inside the list in DF1 (col1) with col1 and take col2 value in DF2.

import pandas as pd

df1 = pd.DataFrame({'col1':[ ['A1','A2'], ['A1','A2','A3'], ['A4','A5'], ['A2','A5'] ]})
df2 = pd.DataFrame({'col1':['A1','A2','A3','A4','A5'],'col2':[1,2,3,4,5]})
Expected output will be like:
expected_result = pd.DataFrame({'col1':[ ['A1','A2'], ['A1','A2','A3'], ['A4','A5'], ['A2','A5'] ], 
                                'col3': [ [1,2],[1,2,3],[4,5],[2,5] ]})
In my current code, I'm using explode method to break the lists but the result is not same as I expected.

df1 = df1.explode('col1')
df1.set_index('col1', inplace=True)
df1['col1'] = df2.groupby('col1').col2.apply(list)
df1.reset_index(drop=True, inplace=True)
df1
Is it possible to get the same result like expected output? Really appreciate for any help or suggestions. Thanks