incrementally search excel with pandas to match string

Question

I'm iterating through excel files and logging rows that contain a string, like so:

def find_string_row(toFind, dframe):
    index = 0
    while toFind not in dframe.iloc[index].values:
        print(dframe.iloc[index].values)
        index = index+1
    return index

I have some new files that are quite messy, and contain strings with whitespace inside and outside the text. So while the above works for exact matches, it fails for loose matches. How can I rewrite this to find strings in this manner:

toFind.replace(" ", "").lower()

so if I input a string like "Address 1 23 " and the excel contains " add ress 12 3" they will match?

when you are replacing " " with "" and adding lower() only address123 will match. — Yash
– Yash, Commented Oct 12, 2021 at 13:40
yep, i tried putting the same string functions on the other side (dframe.iloc[index].values), but i think it's a pandas object, not a string so it errors out. how would I make it symmetric? — tbrk
– tbrk, Commented Oct 12, 2021 at 13:46

YoungTim · Accepted Answer · 2021-10-12 16:26:04Z

1

def find_string_row(toFind, df):
index = 0
stripped_dfrow= [x.replace(" ", "").lower() for x in df.iloc[index].values]
stripped_tofind = toFind.replace(" ", "").lower() 
while toFind not in stripped_dfrow:
    print(dframe.iloc[index].values)
    index = index+1
return index

Your locating all of the values within a row, which should return you a list-like object. So to use the string replace method you'll have to iterate over the list. I used the comprehension above which will remove all of the spaces of each item in the value list.

answered Oct 12, 2021 at 16:26

YoungTim

1735 bronze badges

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

1 Comment

tbrk Over a year ago

thanks, the list comprehension on the pandas object did the trick!

Collectives™ on Stack Overflow

incrementally search excel with pandas to match string

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