Friday, 7 May 2021

How to combine queries with a single external variable using Pandas

I am trying to accept a variable input of many search terms seperated by commas via html form (@search) and query 2 columns of a dataframe.

Each column query works on its own but I cannot get them to work together in a and/or way.

First column query:

filtered = df.query ('`Drug Name` in @search')

Second column query:

filtered = df.query ('BP.str.contains(@search, na=False)', engine='python')

edit combining like this:

filtered = df.query ("('`Drug Name` in @search') and ('BP.str.contains(@search, na=False)', engine='python')")

Gives the following error, highlighting the python identifier in the engine argument

SyntaxError: Python keyword not valid identifier in numexpr query

edit 2

The dataframe is read from an excel file, with columns: Drug Name (containing a single drug name), BP, U&E (with long descriptive text entries)

The search terms will be input via html form:

search = request.values.get('searchinput').replace(" ","").split(',')

as a list of drugs which a patient may be on sometimes with the addition of specific conditions relating to medication use. sample user input:

Captopril, Paracetamol, kidney disease, chronic

I want the list to be checked against specific drug names and also to check other columns such as BP and U&E for any mention of the search terms.

edit 3

Apologies, but trying to implement the answers given is giving me stacks of errors. What I have below is giving me 90% of what I'm after, letting me search both columns including the whole contents of 'BP'. But I can only search a single term via the terminal, if I # out and swap the lines which collect the use input (taking it from the html form as apposed to the terminal) I get:

TypeError: unhashable type: 'list'

@app.route('/', methods=("POST", "GET"))

    def html_table():
        searchterms = []
        #searchterms = request.values.get('searchinput').replace(" ","").split(',')
        searchterms = input("Enter drug...")   
        filtered = df.query('`Drug Name` in @searchterms | BP.str.contains(@searchterms, na=False)', engine='python')
        return render_template('drugsafety.html', tables=[filtered.to_html(classes='data')], titles=['na', 'Drug List'])

<form action="" method="post">
  <p><label for="search">Search</label>
  <input type="text" name="searchinput"></p>        
  <p><input type="submit"></p>
</form>

Any help is much appreciated.



from How to combine queries with a single external variable using Pandas

No comments:

Post a Comment