Select rows by partial string with query with pandas

Question

I have a DataFrame. 1 column (name) has string values. I was wondering if there was a way to select rows based on a partial string match against a particular column, using the DataFrame.query() method.

I tried:

df.query('name.str.contains("lu")'). Error message: "TypeError: 'Series' objects are mutable, thus they cannot be hashed"
df.query('"lu" in name'). Returns an empty DataFrame.

The code I use:

import pandas as pd

df = pd.DataFrame({
    'name':['blue','red','blue'],
    'X1':[96.32,96.01,96.05]
}, columns=['name','X1'])  


print(df.query('"lu" in name').head())
print(df.query('name.str.contains("lu")').head())

I know I could use df[df['name'].str.contains("lu")] but I prefer to use query.

Seems like it is not implemented yet: github.com/pandas-dev/pandas/issues/8749 — user2285236
– user2285236, Commented Jul 5, 2017 at 18:15
@ayhan Thanks. You're welcome to convert the comment into an answer. — Franck Dernoncourt
– Franck Dernoncourt, Commented Jul 5, 2017 at 18:23

petobens · Accepted Answer · 2018-11-16 19:16:01Z

15

The issue that @ayhan refers to now shows how this can be achieved by using query's python engine:

print(df.query('name.str.contains("lu")', engine='python').head())

should work.

answered Nov 16, 2018 at 19:16

petobens

1,4201 gold badge14 silver badges24 bronze badges

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

Comments

score 4 · Accepted Answer · 2018-12-12 12:14:15Z

4

This answer is out of date. Please check @petobens' answer.

As of version 0.20.2, query doesn't support partial string matching. There is an open future request about it and one of the core developers seems to agree that it would be a nice addition.

edited Dec 12, 2018 at 12:14

answered Jul 5, 2017 at 18:33

user2285236

2 Comments

Rho Phi Over a year ago

df.query( 'ColumnName.str.contains(\'sought_string\')') works for me in 0.24.2 to search sough_string in the value stored in the column ColumnName. I would note that the 'engine=python' seems not needed (anymore?)

Hielke Walinga Over a year ago

The PR for this got closed as it bring in too much magic: github.com/pandas-dev/pandas/pull/26027 However @RhoPhi answer also just works. It even works for the recently added backtick functionality: df.query("`column name`.str.contains('sought_string')"). Much magic indeed.

Paulo Buchsbaum · Accepted Answer · 2020-02-06 01:37:24Z

The petobens solution now works with Query without engine spec, what increases the speed, acconding the manual.

Uses contains in query spec it's a powerful feature to handling string content because allow use regex.

import numpy as np
import pandas as pd
A = np.array(["Paulo", "Lucas", "Luana", "Larra", "BaLu","Bela"])
B = np.array([111, 222, 222, 333, 333, 777])
C = np.random.randint(10, 99, 6)
dt = pd.DataFrame(zip(A, B, C), columns=['A', 'B', 'C'])
dt.set_index(['A', 'B'], inplace=True)
print(dt)
print("=============")
print(dt.query('A.str.contains("Lu")'))
print("=============")
print(dt.query('A.str.contains("L(a|u)", regex=True)'))
print("=============")
print(dt.query('A.str.contains("^L", regex=True)'))  # starts with L

The result is

A   B
1.1 Paulo  57
    Lucas  49
3.3 Luana  38
    Larra  82
5.5 BaLu   37
6.6 Bela   14
=============
            C
A   B
1.1 Lucas  49
3.3 Luana  38
5.5 BaLu   37
=============
            C
A   B
1.1 Lucas  49
3.3 Luana  38
    Larra  82
5.5 BaLu   37
=============
            C
A   B
1.1 Lucas  49
3.3 Luana  38
    Larra  82

Collectives™ on Stack Overflow

Select rows by partial string with query with pandas

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related