CODEWITHSUNDEEP
pythonpandas
qwebeee
qwebeee

Reputation: 1

python Pandas: VLOOKUP multiple cells on column

I'm struggling with next task: I would like to identify using pandas (or any other tool on python) if any of multiple cells (Fruit 1 through Fruit 3) in each row from Table 2 contains in column Fruits of Table1. And at the end obtain "Contains Fruits Table 2?" table.

Fruits
apple
orange
grape
melon
Name Fruit 1 Fruit 2 Fruit 3 Contains Fruits Table 2?
Mike apple Yes
Bob peach pear orange Yes
Jack banana No
Rob peach banana No
Rita apple orange banana Yes

Fruits in Table 2 can be up to 40 columns. Number of rows in Table1 is about 300.

I hope it is understandable, and someone can help me resolve this.

I really appreciate the support in advance!

Upvotes: 0

Views: 214

Answers (1)

not_speshal
not_speshal

Reputation: 23146

Try:

  1. filter DataFrame to include columns that contain the word "Fruit"
  2. Use isin to check if the values are in table1["Fruits"]
  3. Return True if any of fruits are found
  4. map True/False to "Yes"/"No"
table2["Contains Fruits Table 2"] = table2.filter(like="Fruit")
                                          .isin(table1["Fruits"].tolist())
                                          .any(axis=1)
                                          .map({True: "Yes", False: "No"})

>>> table2

   Name Fruit 1 Fruit 2 Fruit 3 Contains Fruits Table 2
0  Mike   apple    None    None                     Yes
1   Bob   peach    pear  orange                     Yes
2  Jack  banana    None    None                      No
3   Rob   peach  banana    None                      No
4  Rita   apple  orange  banana                     Yes
​~~~

Upvotes: 1

Related Questions