CODEWITHSUNDEEP
pythonpandasdataframe
Lijju Mathew
Lijju Mathew

Reputation: 2091

pandas join list elements to other dataframe column

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.

Upvotes: 1

Views: 72

Answers (2)

mozway
mozway

Reputation: 260420

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]]})

Upvotes: 1

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

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

Upvotes: 0

Related Questions