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.
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) df1Is it possible to get the same result like expected output? Really appreciate for any help or suggestions. Thanks