How can I use a list of strings to search a pandas dataframe with Python 3

Question

I am trying to search a Pandas dataframe with a list in Python 3. For clarity I am on a Windows machine with python 3.8.

I have an excel file that I am looking for certain keywords in a notes column and then I want the program to return another column that contains an id number. Currently my code does this by putting the excel data into a pandas dataframe and then checking a string variable with str.contains but I have more than one keyword I want to search and I am not sure how to do that.

Here is my code so far:

import pandas as pd

searchWord1 = 'Honda'
searchWord2 = 'honda'
searchWord3 = 'Toyota'
searchWord4 = 'toyota'
searchWord5 = '350'

df = pd.read_excel('data.xlsx',sheet_name='Sheet1')

df2 = (df[df['Notes'].str.contains(searchWord1)])

print(df2['id_number'])

I have tried creating a list, using a for loop and iterating through it but no luck, maybe I am just doing it wrong? I'm pretty new to python and pandas so any help would be greatly appreciated, thank you.

SeaBean · Accepted Answer · 2021-06-22 19:16:08Z

2

You can define the search words in a list and then generate the search pattern as follows:

searchWords = ['Honda', 'honda', 'Toyota', 'toyota', '350']
pattern = rf"\b{'|'.join(searchWords)}\b"

pattern will be set up as: r'\bHonda|honda|Toyota|toyota|350\b' where:

\b at both ends are to ensure only whole word match instead of partial word match (e.g. matches '350' but not '12350'). If you require partial word match, you can remove this pair of '\b` at both ends.

| is the regex meta-character for alternative strings (like or)

Then, use the search pattern, as follows:

df = pd.read_excel('data.xlsx',sheet_name='Sheet1')

df2 = (df[df['Notes'].str.contains(pattern)])

print(df2['id_number'])

edited Jun 22, 2021 at 19:16

answered Jun 22, 2021 at 19:09

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

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

1 Comment

ssumme Over a year ago

This is exactly what I needed, thank you!

Collectives™ on Stack Overflow

How can I use a list of strings to search a pandas dataframe with Python 3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related