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 couldn't achieve.
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]]})
convert df2 to a dictionary for lookup then use a the dataframe apply to get the lookup values from the dictionary