CODEWITHSUNDEEP
python-3.xpandasdataframe
Lilly
Lilly

Reputation: 988

Search (row values) data from another dataframe

I have two dataframes, df1 and df2 respectively.
In one dataframe I have a list of search values (Actually Col1)

Col1   Col2
A1      val1, val2
B2      val4, val1
C3      val2, val5

I have another dataframe where I have a list of items

value   items
val1    apples, oranges
val2    honey, mustard
val3    banana, milk
val4    biscuit
val5    chocolate

I want to iterate though the first DF and try to use that val as key to search for items from the second DF

Expected output:

A1     apples, oranges, honey, mustard
B2     biscuit, appleas, oranges
C3     honey, mustard, chocolate

I am able to add the values into dataframe and iterate through 1st DF

for index, row in DF1:
     #list to hold all the values
     finalList = []
     list = df1['col2'].split(',')
     for i in list:
          print(i)

I just need help to fetch values from the second dataframe. Would appreciate any help. Thanks.

Upvotes: 2

Views: 121

Answers (1)

jezrael
jezrael

Reputation: 862611

Idea is use lambda function with split and lookup by dictionary:

d = df2.set_index('value')['items'].to_dict()

df1['Col2'] = df1['Col2'].apply(lambda x: ', '.join(d[y] for y in x.split(', ') if y in d))
print (df1)
  Col1                             Col2
0   A1  apples, oranges, honey, mustard
1   B2         biscuit, apples, oranges
2   C3        honey, mustard, chocolate

If there are lists in items values solution is changed with flattening:

d = df2.set_index('value')['items'].to_dict()

f = lambda x: ', '.join(z for y in x.split(', ') if y in d for z in d[y])
df1['Col2'] = df1['Col2'].apply(f)
print (df1)
  Col1                       Col2
0   A1            apples, oranges
1   B2   biscuit, apples, oranges
2   C3  honey, mustard, chocolate

Upvotes: 2

Related Questions