Pandas DataFrame: query with variables

Question

I'm working on a DataFrame query using 2 variables. The first variable is the column label and the second is a list of values. What I want to do is select all row where that column has a value contained in that list. The strange thing is that if I write the column label as a string there is no error, while referencing the variable containing the column label gives the following error:

Traceback (most recent call last):
  File "C:\Python\Python36\lib\site-packages\pandas\indexes\base.py", line 2134, in get_loc
    return self._engine.get_loc(key)
  File "pandas\index.pyx", line 132, in pandas.index.IndexEngine.get_loc (pandas\index.c:4433)
  File "pandas\index.pyx", line 151, in pandas.index.IndexEngine.get_loc (pandas\index.c:4238)
  File "pandas\index.pyx", line 388, in pandas.index.Int64Engine._check_type (pandas\index.c:8171)
KeyError: False

This is the working code:

rhs_values_list = df1["RHS"].tolist()
query = "shoe_size in @rhs_values_list"
result_set = df2.query(query)

while this rises the above error:

rhs_values_list = df1["RHS"].tolist()
col = "shoe_size"
query = "@col in @rhs_values_list"
result_set = df2.query(query)

Is there something wrong with second version of the query?

Solaxun · Accepted Answer · 2017-08-28 21:24:19Z

3

What you are doing is executing the actual query with @col in the string, not the value you bound to that variable. You can use string interpolation e.g:

  rhs_values_list = df1["RHS"].tolist()
    col = "shoe_size"
    query = "{} in @rhs_values_list".format(col)
    result_set = df2.query(relaxed_query)

answered Aug 28, 2017 at 21:24

Solaxun

2,7922 gold badges28 silver badges45 bronze badges

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

3 Comments

Solaxun Over a year ago

FYI using string interpolation like this is generally not a great idea. Ideally you want to use a library that allows parameterized queries and will sanitize any variables.

cs95 Over a year ago

In this case, SQL injection is not a concern because you cannot pass table mutation queries to df.query.

Solaxun Over a year ago

Good to know Pandas got your back :)

Collectives™ on Stack Overflow

Pandas DataFrame: query with variables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related