pandasstring-matchingsql-like

Pandas text matching like SQL's LIKE?


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.


Solution

  • 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