Backlash error. Pandas filter dataframe using dynamic query string.

Question

Hi all, The problem is related to the Python backlash error. I am creating a dynamic query string for filtering in pandas. The code is:

       filters = dict(wlbWellType=['EXPLORATION'])
       query_string = ''
       index = 0
       for (k,v) in filters.iteritems():
          for i in v:
              if (index == 0):
                  query_string += '"{}"'.format((k) + ' == '+"'{}'".format(i)) 
              else:
              query_string += ' & ' '"{}"'.format((k) + ' == ' + 
              "'{}'".format(i))
             index += 1

If I do "print query_string" the output I got is

             "wlbWellType == 'EXPLORATION'"

If I do "query_string" the output I got is

             '"wlbWellType == \'EXPLORATION\'"'

I want

             "wlbWellType == 'EXPLORATION'"

as the output without using print statement. Seems there is an error related to backlash.

The query_string output will then be used as:

            df.query(query_string)

Can anyone please help me with the above problem?

Thanks in advance

can you post a small reproducible sample of filters DF/Series? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Aug 16, 2017 at 10:30
and the error I got is : 'the label ["wlbWellType == \'EXPLORATION\'"] is not in the [index]' — khushal adlakha
– khushal adlakha, Commented Aug 16, 2017 at 10:31
@MaxU- I got an error when I use it in DF. The error is posted in the comment above. — khushal adlakha
– khushal adlakha, Commented Aug 16, 2017 at 10:33

Jon Clements · Accepted Answer · 2017-08-16 11:05:57Z

2

You can write your own helper function (similar to what you're trying now but making use of **kwargs) and use the @varname syntax as the value placeholder.

def my_filter(df, **kwargs):
    qs = ' & '.join('{0} == @{0}'.format(k) for k in kwargs)
    return df.query(qs, local_dict=kwargs)

Then use as follows:

new_df = my_filter(df, wlbWellType='EXPLORATION', otherColumn='SOMETHING')

This method is safer than manually escaping values as the @varname syntax will do that appropriately for you depending on the value's type.

edited Aug 16, 2017 at 11:05

answered Aug 16, 2017 at 10:57

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

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

Comments

mrlarssen · Accepted Answer · 2017-08-16 10:42:59Z

1

The reason is that you are wrapping your key in python format string ( "'{}'".format ) as well. Try this solution:

query_string = ""
index = 0
for (k,v) in filters.iteritems():
  for i in v:
    if (index == 0):
      query_string += str(k) + " == " + "'{}'".format(i)
    else:
      query_string += " & " + str(k) + " == " + "'{}'".format(i)
  index += 1

answered Aug 16, 2017 at 10:42

mrlarssen

3238 silver badges19 bronze badges

Comments

MaxU - stand with Ukraine · Accepted Answer · 2017-08-16 10:49:38Z

0

Consider the following approach:

In [44]: filters
Out[44]:
           col          val
0  wlbWellType  EXPLORATION
1          bbb          BBB

In [45]: qry = filters['col'].add(' == "').add(filters['val']).add('"').str.cat(sep=' & ')

In [46]: print(qry)
wlbWellType == "EXPLORATION" & bbb == "BBB"

slightly different syntax:

In [50]: qry = (filters['col'] + ' == "' + filters['val'] + '"').str.cat(sep=' & ')

In [51]: qry
Out[51]: 'wlbWellType == "EXPLORATION" & bbb == "BBB"'

answered Aug 16, 2017 at 10:49

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

Backlash error. Pandas filter dataframe using dynamic query string.

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related