python Pandas: VLOOKUP multiple cells on column

Question

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!

not_speshal · Accepted Answer · 2021-10-28 19:01:34Z

1

Try:

filter DataFrame to include columns that contain the word "Fruit"
Use isin to check if the values are in table1["Fruits"]
Return True if any of fruits are found
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
~~~

edited Oct 28, 2021 at 19:01

answered Oct 28, 2021 at 18:43

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rodalm Over a year ago

You don't need apply, DataFrames have also an 'isin' method. You can do .isin(table1['Fruits'].to_numpy()) instead.

Rodalm Over a year ago

Upvoted, but to_numpy() or tolist() is required, otherwise the match will be done by index alignment ;)

not_speshal Over a year ago

Correct again @HarryPlotter :)

qwebeee Over a year ago

You are awesome! Thank you very much!

Collectives™ on Stack Overflow

python Pandas: VLOOKUP multiple cells on column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related