Pandas Merge A List In A Dataframe Column With Another Dataframe

I have 2 dataframes: ID LIST_VALUES 1 [a,b,c] 2 [a,n,t] 3 [x] 4 [h,h] VALUE MAPPING a alpha b bravo c charlie n nove

Solution 1:

One way would be to build a dictionary from your second dataframe using set_index and to_dict. Then use a nested list comprehension to lookup the dictionary using the values in the lists:

d = df2.set_index('VALUE').MAPPING.to_dict()
# {'a': 'alpha', 'b': 'bravo', 'c': 'charlie', ...

df['new_col'] = [','.join([d[j] forjin i]) foriin df.LIST_VALUES]

print(df)

 ID LIST_VALUES                new_col
01   [a, b, c]   alpha,bravo,charlie
12   [a, b, c]   alpha,bravo,charlie
23         [x]                  xray
34      [h, h]           hotel,hotel

Setup:

print(df2)

 VALUE   MAPPING
0a     alpha
1b     bravo
2     c   charlie
3     n  november
4     h     hotel
5     t     tango
6     x      xray

print(df)

   ID LIST_VALUES
01[a, b, c]12[a, b, c]23[x]34[h, h]

Solution 2:

Use list comprehension with map by Series same way like dictionary, last remove duplicated values by dict.fromkeys trick and join values together:

d = df2.set_index('VALUE')['MAPPING']
df1['new_col'] = [', '.join(dict.fromkeys([d[y] for y in x if y in d]).keys()) 
                                                               for x in df1['LIST_VALUES']]

print (df1)
   ID LIST_VALUES                 new_col
0   1   [a, b, c]   alpha, bravo, charlie
1   2   [a, n, t]  alpha, november, tango
2   3         [x]                    xray
3   4      [h, h]                   hotel

If order is not important of new values is possible use set for remove duplicates:

d = df2.set_index('VALUE')['MAPPING']
df1['new_col'] = [', '.join(set([d[y] for y in x if y in d])) for x in df1['LIST_VALUES']]

print (df1)
   ID LIST_VALUES                 new_col
0   1   [a, b, c]   alpha, charlie, bravo
1   2   [a, n, t]  alpha, tango, november
2   3         [x]                    xray
3   4      [h, h]                   hotel