Create a list according to another list in panda dataframe-CodePudding

I have two dataframes as below:

df1

Sites
['a']       
['b', 'c', 'a', 'd']        
['c']

df2

Site cells
a       2
b       4
c       3
d       5

what i need is to add a column in df1 with list of cells corresponding to list in df1 like

result_df

Sites                   Cells
['a']                   [2]
['b', 'c', 'a', 'd']    [4,3,2,5]   
['c']                   [3]

I wrote below, just the column is map object,

df1['Cells'] = df1['Sites'].map(lambda x: map(df2.set_index('Site')['cells'],x))

I tried using list to convert it to list but I get this error

df1['Cells'] = df1['Sites'].map(lambda x: list(map(df2.set_index('Site')['cells'],x)))
TypeError: 'Series' object is not callable

CodePudding user response：

Let us do

df1['value'] = df1.Sites.map(lambda x : df2.set_index('Site')['cells'].get(x).tolist())
df1
Out[728]: 
          Sites         value
0           [a]           [2]
1  [b, c, a, d]  [4, 3, 2, 5]
2           [c]           [3]

If you have some Sites in df1 not in df2 try with explode

df1['new'] = df1.Sites.explode().map(df2.set_index('Site')['cells']).groupby(level=0).agg(list)