Pandas text matching like SQL's LIKE?

Question

Is there a way to do something similar to SQL's LIKE syntax on a pandas text DataFrame column, such that it returns a list of indices, or a list of booleans that can be used for indexing the dataframe? For example, I would like to be able to match all rows where the column starts with 'prefix_', similar to WHERE <col> LIKE prefix_% in SQL.

Answer 1

You can use the Series method str.startswith (which takes a regex):

In [11]: s = pd.Series(['aa', 'ab', 'ca', np.nan])

In [12]: s.str.startswith('a', na=False)
Out[12]: 
0     True
1     True
2    False
3    False
dtype: bool

You can also do the same with str.contains (using a regex):

In [13]: s.str.contains('^a', na=False)
Out[13]: 
0     True
1     True
2    False
3    False
dtype: bool

So you can do df[col].str.startswith...

See also the SQL comparison section of the docs.

Note: (as pointed out by OP) by default NaNs will propagate (and hence cause an indexing error if you want to use the result as a boolean mask), we use this flag to say that NaN should map to False.

In [14]: s.str.startswith('a')  # can't use as boolean mask
Out[14]:
0     True
1     True
2    False
3      NaN
dtype: object