python - USING LIKE inside pandas.query()

Question

I have been using Pandas for more than 3 months and I have an fair idea about the dataframes accessing and querying etc.

I have got an requirement wherein I wanted to query the dataframe using LIKE keyword (LIKE similar to SQL) in pandas.query().

i.e: Am trying to execute pandas.query("column_name LIKE 'abc%'") command but its failing.

I know an alternative approach which is to use str.contains("abc%") but this doesn't meet our requirement.

We wanted to execute LIKE inside pandas.query(). How can I do so?

Answer 1

If you have to use df.query(), the correct syntax is:

df.query('column_name.str.contains("abc")', engine='python')

You can easily combine this with other conditions:

df.query('column_a.str.contains("abc") or column_b.str.contains("xyz") and column_c>100', engine='python')

It is not a full equivalent of SQL Like, however, but can be useful nevertheless.