Making function to check for matching data in excel keeps returning false

Question

I wanted to make a function using python to check whether a number (obtained based on a file string) is exist or not in an excel using pandas, if the number exist it will do something like "... is registered" and do some function, and vice versa.

The code that i tried to use is like this:

from tkinter import filedialog
import pandas as pd
import re

file = filedialog.askopenfilename(initialdir="C:/", title="choose file", filetypes=(("xlsx", "*.xlsx"), ("xls", "*.xls")))
name = (r'/(\w+)_(\w+)_excel.xlsx')
number = re.search(name, file).group(2)

references = pd.read_excel(r'C:/example.xlsx')

if float(number) == references:
    print(number + " is registered") #and more function
else:
    print(number + " is not registered") #and more function

When i used this code it will return ValueError:

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I looked up for it a little and found that (as far as i understand) this happen because the function is not specific enough and can return both true or false, and then i tried using .any or .all after the references in the function but somehow it keeps returning false even if the number does exist in excel.

When i tried to check it using print(number == references) it will show a list and it would tell me where the matching data is found, but somehow when i tried to make a function out of it, it keeps returning false.

The references excel just contains a "Title" in cell A1 and the rest is just the number registered in it.

I tried look for it and found something about pandas.DataFrame.isin but i dont understand how to put it as a function and i dont think i can use it on my problem.

I am sorry if this is a fairly easy question but somehow i cannot find the answer for this, also i am new in python and english is not my first language.

Nimantha · Accepted Answer · 2021-10-29 06:18:46Z

1

I'm still relatively new to Python, but let me think along:

You are pulling a variable from an excel-filename, let's say 1234ABCD from something like TestFile_1234ABCD_excel.xlsx. Right?
Then you are pulling in the content of a file called C:/example.xlsx into a dataframe, correct?
Last, you are checking if the variable is found somewhere in the dataframe?

So, what I think went wrong here is that you are comparing a dataframe against a variable using == which is the equality operator. This might well be the reason for returning False but I'm in no place to say that this is the actual cause since I don't fully understand Pyhton mechanics as yet.

However what does work for me replicating your issue was the following; I simply used:

if number in references.values:
    print(number + " is registered") #and more function
else:
    print(number + " is not registered") #and more function

This worked because "the dataframe class provides a member variable i.e DataFrame.values. It returns a numpy representation of all the values in dataframe. We can use the in & not in operators on these values to check if a given element exists or not." Source

edited Oct 29, 2021 at 6:18

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Mar 25, 2020 at 15:10

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

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

1 Comment

soggypotato Over a year ago

Sorry if this comment is a bit wobbly, somehow cant use enter in my phone. For the first question, yes. Second, that is correct. And third, yes. Also, thank you very much, using values indeed solved the problem!!! And thank you for the explanation, i though i cannot use it because i was pulling it from an excel (dumb me).

Collectives™ on Stack Overflow

Making function to check for matching data in excel keeps returning false

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