pandas join list elements to other dataframe column-CodePudding

I have the following pandas dataframes and I want to join the list column, look up and get the values.

df1 
x_id  y_id
1     [101,102,103]
2     [102, 103]
3     [103]

df2
y_id   y_name
101     abc
102     xyz
103     def

I want a dataframe df3 as

df3
x_id  y_id             y_names
1     [101,102,103]    [abc, xyz, def]   
2     [102, 103]       [xyz, def]
3     [103]            [def]

Tried using lookup and apply functions but couldnt achieve. Appreciate you help here.

CodePudding user response：

A first option using a list comprehension and a mapping Series:

s = df2.set_index('y_id')['y_name']

df1['y_names'] = [[s[x] for x in l] for l in df1['y_id']]

A second option using explode, map and groupby.agg:

(df1
 .explode('y_id')
 .assign(y_names=lambda d: d['y_id'].map(df2.set_index('y_id')['y_name']))
 .groupby('x_id', as_index=False).agg(list)
)

output:

   x_id             y_id          y_names
0     1  [101, 102, 103]  [abc, xyz, def]
1     2       [102, 103]       [xyz, def]
2     3            [103]            [def]

used input:

df = pd.DataFrame({'x_id': [1, 2, 3],
                   'y_id': [[101, 102, 103], [102, 103], [103]]})

CodePudding user response：

convert df2 to a dictionary for lookup then use a the dataframe apply to get the lookup values from the dictionary