Creating a DF columns with elements lists of matching values from another DF

Hi All,

I’ve 2 DF pandas,

df1=pd.DataFrame({'col1':[ ['A1','A2'], ['A1','A2','A3'], ['A4','A5'], ['A2','A5'] ]})  
df2=pd.DataFrame({'col2':['A1','A2','A3','A4','A5'],'col3':[1,2,3,4,5]}). 

And I want to do vlookup between col1 in DF1 and col2 in DF2 and take col3 value, in my current code I’ve tried to break the list but the output no longer become a list

df1.set_index('col1', inplace=True)
df1['col1'] = df2.groupby('col2').col3.apply(list)
df1.reset_index(drop=True, inplace=True)
df1

And the expected output will be like this

expected = pd.DataFrame({'col4': [ [1,2],[1,2,3],[4,5],[2,5] ]})

Really appreciate for any help/suggestions. Thanks

I’ve edited your post for readability. When you enter a code block into a forum post, please precede it with a separate line of three backticks and follow it with a separate line of three backticks to make it easier to read.

You can also use the “preformatted text” tool in the editor (</>) to add backticks around text.

See this post to find the backtick on your keyboard.
Note: Backticks (`) are not single quotes (’).

1 Like