map multiple rows of first dataframe into list in another-CodePudding

Hello I am mapping databases together and stuck on the following step of our workflow.

I have a main df:

name, id
'tree', 0
'foo', 1
'apple', 2
'table', 3

And another map_df for the codes

ID, id_for_map, Code
0, 0, 'aa'
1, 1, 'ab'
2, 1, 'ac'
3, 2, 'ba'
4, 2, 'bb'
5, 3, 'bb'

I would like to get every code in a list as a column of the main df after the id matching:

name, id, code
'tree', 0, ['aa']
'foo', 1,  ['ab','ac']
'apple', 2, ['ba','bb']
'table', 3, ['bb']

I tried to use temp dataframes, with a lot of inefficent work, but still couldn't figure it out.

Thank you for your help in advance!

CodePudding user response：

You can try

df1['code'] = df1['id'].map(df2.groupby('id_for_map')['Code'].agg(list))

print(df1)

      name  id          code
0   'tree'   0        ['aa']
1    'foo'   1  ['ab', 'ac']
2  'apple'   2  ['ba', 'bb']
3  'table'   3        ['bb']